{"record":{"id":"a79c03025c8caf43","repo":"yiisoft/yii2","slug":"yii-db-mssql-conditions-inconditionbuilder-builds","errorCode":null,"errorMessage":"yii\\db\\mssql\\conditions\\InConditionBuilder::buildSubqueryInCondition is not supported by MSSQL.","messagePattern":"yii\\\\db\\\\mssql\\\\conditions\\\\InConditionBuilder::buildSubqueryInCondition is not supported by MSSQL\\.","errorType":"exception","errorClass":"yii\\base\\NotSupportedException","httpStatus":null,"severity":"error","filePath":"framework/db/mssql/conditions/InConditionBuilder.php","lineNumber":29,"sourceCode":"use yii\\base\\NotSupportedException;\nuse yii\\db\\Expression;\n\n/**\n * {@inheritdoc}\n *\n * @author Dmytro Naumenko <d.naumenko.a@gmail.com>\n * @since 2.0.14\n */\nclass InConditionBuilder extends \\yii\\db\\conditions\\InConditionBuilder\n{\n    /**\n     * {@inheritdoc}\n     * @throws NotSupportedException if `$columns` is an array\n     */\n    protected function buildSubqueryInCondition($operator, $columns, $values, &$params)\n    {\n        if (is_array($columns)) {\n            throw new NotSupportedException(__METHOD__ . ' is not supported by MSSQL.');\n        }\n\n        return parent::buildSubqueryInCondition($operator, $columns, $values, $params);\n    }\n\n    /**\n     * {@inheritdoc}\n     */\n    protected function buildCompositeInCondition($operator, $columns, $values, &$params)\n    {\n        $quotedColumns = [];\n        foreach ($columns as $i => $column) {\n            if ($column instanceof Expression) {\n                $column = $column->expression;\n            }\n            $quotedColumns[$i] = strpos($column, '(') === false ? $this->queryBuilder->db->quoteColumnName($column) : $column;\n        }\n        $vss = [];","sourceCodeStart":11,"sourceCodeEnd":47,"githubUrl":"https://github.com/yiisoft/yii2/blob/66f00d18a29b520f85e8e8f1e32d1e7e7b556cac/framework/db/mssql/conditions/InConditionBuilder.php#L11-L47","documentation":"yii\\db\\mssql\\conditions\\InConditionBuilder::buildSubqueryInCondition() (framework/db/mssql/conditions/InConditionBuilder.php:29) overrides the base builder to reject composite IN conditions where $columns is an array and $values is a Query — e.g. ['in', ['col1','col2'], $subquery]. SQL Server does not support row-value (tuple) comparisons like (a,b) IN (SELECT x,y ...), so Yii throws NotSupportedException instead of emitting invalid SQL. A single column with a subquery falls through to the parent implementation and works.","triggerScenarios":"->where(['in', ['order_id','line'], (new Query())->select(['o','l'])->from('selected')]) on a SQL Server connection; queries written and tested on MySQL/PostgreSQL (whose builders support composite IN with subqueries) then executed on MSSQL; composite-key matching against junction tables.","commonSituations":"Cross-DBMS applications where dev uses MySQL and production SQL Server; composite primary keys on junction/mapping tables; upgrading a project to run on MSSQL without auditing IN conditions.","solutions":["Rewrite as a correlated EXISTS: ->andWhere(['exists', (new Query())->select('1')->from('selected s')->where('s.o = t.order_id AND s.l = t.line')])","Replace IN-subquery with a JOIN against the subquery and DISTINCT selection","If the composite key can be reduced to one expression (e.g. a computed key), filter on that single column instead"],"exampleFix":"// before (works on MySQL/PG, throws NotSupportedException on MSSQL)\n$query->andWhere(['in', ['order_id', 'line'], (new Query())->select(['order_id', 'line'])->from('selected_rows')]);\n\n// after (portable)\n$query->andWhere(['exists', (new Query())\n    ->select('1')\n    ->from('selected_rows sr')\n    ->where('sr.order_id = t.order_id AND sr.line = t.line'),\n]);","handlingStrategy":"validation","validationCode":"function isMssqlCompositeInSubquery($columns, $values, string $driverName): bool\n{\n    return $driverName === 'sqlsrv'\n        && is_array($columns)\n        && $values instanceof \\yii\\db\\Query;\n}\n\nif (isMssqlCompositeInSubquery($columns, $values, $db->driverName)) {\n    throw new \\RuntimeException('Composite IN with subquery is not supported by MSSQL — use EXISTS.');\n}\n$query->andWhere(['in', $columns, $values]);","typeGuard":"function isCompositeColumns($columns): bool\n{\n    return is_array($columns) && count($columns) > 1;\n}","tryCatchPattern":"try {\n    $rows = $query->all();\n} catch (\\yii\\db\\NotSupportedException $e) {\n    // composite IN + subquery on MSSQL — rewrite as EXISTS\n    \\Yii::error($e->getMessage(), __METHOD__);\n    throw new \\RuntimeException('Rewrite this IN-subquery as EXISTS for SQL Server.', 0, $e);\n}","preventionTips":["Prefer correlated EXISTS over IN-subqueries when targeting multiple DBMSs","Test query code against every driver in the support matrix, not just dev's MySQL/PostgreSQL","Remember single-column IN with a subquery IS supported on MSSQL — only the array-columns form throws"],"tags":["yii2","mssql","sqlserver","database","query-builder","in-condition","subquery","composite-key","not-supported"],"backgroundTag":"unsupported-database-feature","analyzedSha":"66f00d18a29b520f85e8e8f1e32d1e7e7b556cac","analyzedAt":"2026-08-17T05:17:23.470Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}