MSSQL Server supports a maximum of 2100 parameters

[aliechti]

In some cases there is a problem how Yii2 handles the [id] IN (1,2,3..) statement.

SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 
parameters. The SQL being executed was:
SELECT COUNT(*) 
FROM [tablexy] 
WHERE [id] IN ('107518', '96017', '79543', '96016', '82370', '96638', ...

[SilverFire]

This problem related not the MSSQL only. See #10305

[samdark]

Usually it's not a huge issue. If there are more than 100 ids in a query per page, there's probably an issue in software logic.

[o-rey]

Not a bug, just use joinWith() instead.

[cebe]

Not a bug, just use joinWith() instead.

this should make no difference since with() is called by joinWith() too...

[dryu]

If I have AR relation defined that in runtime resolves to more than 2100 records that needs to be looked up in the database, the application will fail.

[dryu]

It seems that this error message text is misleading and the error is thrown by Microsoft SQL Server PHP driver and not by MS SQL Server itself . MSSQL by itself has no problems with the size of parameter arrays passed to it.

Correction: MSSQL has a hard limit in a number of parameters you can pass to stored procedure. And it looks like Yii2 uses stored procedures to prepare SQL statement. The query composed is fine - when I copy it from the error log and run in MSSQL Management Studio it runs fine.

[samdark]

Makes sense to use non-prepared statements in this case.

[64bitint]

I have a work around that fixes this issue for active relations that use via(), may also be faster for relations with lots of records. At least in my case using MSSQL.

Basically this just uses the subquery in the where in condition instead of using filterByModels()

https://gist.github.com/kevm256/cb4557ec5f60d8723d45b4e845d60712

I haven't done extensive testing, so not sure if there are other unintended side effects.

[samdark]

@kevm256 that's a good workaround but not a permanent solution. That would likely break relationships between multiple database types that work perfectly fine now.

[64bitint]

@samdark yeah, I don't think it will work across databases. I did try adding a buildValues() override in the mssql InConditionBuilder class to put int values directly into the condition instead of binding them:

protected function buildValues(ConditionInterface $condition, $values, &$params)
{
    if(count($values) > 2000){
        $intValues = [];
        foreach ($values as $i => $value)
        {
            if(is_int($value)){
                $intValues[] = strval($value);
                unset($values[$i]);
            }
        }

        $sqlValues = parent::buildValues($condition, $values,$params);

        return array_merge( $sqlValues, $intValues);
    }

    return parent::buildValues($condition, $values,$params);
}

However this still doesn't solve for the general case, and adding or binding 2000+ params to the where in condition uses a lot of memory and in my case was way too slow.

I think I will just use joinWith to replace the relation.

[darkdef]

It's not Yii bug. It's bug of MS SQL driver.
Read this comment:
microsoft/msphpsql#410 (comment)
And see in the code of driver
https://github.com/Microsoft/msphpsql/blob/v4.2.0-preview/source/shared/core_sqlsrv.h#L172

[darkdef]

Usage another MS SQL driver
With DBLIB driver - all perfect work

With PDO_SQLSRV - failed (Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.)