Paginator with fetch collection and dbal 3 => empty query

[maisoui]

Bug Report

Q	A
BC Break	yes
Version	2.10.1

Summary

After upgrading to doctrine/dbal from 2 to 3, Paginator is not working anymore. When using parameter "fetch collection" to true, an exception is thrown "PDO query couldn't be empty".

How to reproduce

...$qb is a query builder... $query = $qb->getQuery(); $paginator = new Paginator($query, true); foreach($paginator as $result) { => EXCEPTION }

It seems that doctrine/dbal Connection does not resolve parameters ('?') correctly. Regular expression is "not correct" : ~\G\?\~s.

Workaround

Adding "doctrine/dbal": "^2.13" in composer.json to force using dbal 2 instead of 3.

[heminei]

I have the same issue. When I downgrade "doctrine/dbal" to v2.x working properly.

[derrabus]

Would any of you able to trace down the issue and work on a fix?

[AlexeyKosov]

It happens when an SQL query is very long, e.g.:

        $result = $this->em->getConnection()->executeQuery('
            SELECT
                table1.field_name_1 AS table1_field_name_1,
                table1.field_name_2 AS table1_field_name_2,
                table1.field_name_3 AS table1_field_name_3,
                table1.field_name_4 AS table1_field_name_4,
                table1.field_name_5 AS table1_field_name_5,
                table1.field_name_6 AS table1_field_name_6,
                table1.field_name_7 AS table1_field_name_7,
                table1.field_name_8 AS table1_field_name_8,
                table1.field_name_9 AS table1_field_name_9,
                table1.field_name_10 AS table1_field_name_10,
                table1.field_name_11 AS table1_field_name_11,
                table1.field_name_12 AS table1_field_name_12,
                table1.field_name_13 AS table1_field_name_13,
                table1.field_name_14 AS table1_field_name_14,
                table1.field_name_15 AS table1_field_name_15,
                table1.field_name_16 AS table1_field_name_16,
                table1.field_name_17 AS table1_field_name_17,
                table1.field_name_18 AS table1_field_name_18,
                table1.field_name_19 AS table1_field_name_19,
                table1.field_name_20 AS table1_field_name_20,
                table1.field_name_21 AS table1_field_name_21,
                table1.field_name_22 AS table1_field_name_22,
                table1.field_name_23 AS table1_field_name_23,
                table1.field_name_24 AS table1_field_name_24,
                table1.field_name_25 AS table1_field_name_25,
                table1.field_name_26 AS table1_field_name_26,
                table2.field_name_1 AS table2_field_name_1,
                table2.field_name_2 AS table2_field_name_2,
                table2.field_name_3 AS table2_field_name_3,
                table2.field_name_4 AS table2_field_name_4,
                table2.field_name_5 AS table2_field_name_5,
                table2.field_name_6 AS table2_field_name_6,
                table2.field_name_7 AS table2_field_name_7,
                table2.field_name_8 AS table2_field_name_8,
                table2.field_name_9 AS table2_field_name_9,
                table2.field_name_10 AS table2_field_name_10,
                table2.field_name_11 AS table2_field_name_11,
                table2.field_name_12 AS table2_field_name_12,
                table2.field_name_13 AS table2_field_name_13,
                table2.field_name_14 AS table2_field_name_14,
                table2.field_name_15 AS table2_field_name_15,
                table2.field_name_16 AS table2_field_name_16,
                table2.field_name_17 AS table2_field_name_17,
                table2.field_name_18 AS table2_field_name_18,
                table2.field_name_19 AS table2_field_name_19,
                table2.field_name_20 AS table2_field_name_20,
                table2.field_name_21 AS table2_field_name_21,
                table2.field_name_22 AS table2_field_name_22,
                table2.field_name_23 AS table2_field_name_23,
                table2.field_name_24 AS table2_field_name_24,
                table2.field_name_25 AS table2_field_name_25,
                table2.field_name_26 AS table2_field_name_26,
                table3.field_name_1 AS table3_field_name_1,
                table3.field_name_2 AS table3_field_name_2,
                table3.field_name_3 AS table3_field_name_3,
                table3.field_name_4 AS table3_field_name_4,
                table3.field_name_5 AS table3_field_name_5,
                table3.field_name_6 AS table3_field_name_6,
                table3.field_name_7 AS table3_field_name_7,
                table3.field_name_8 AS table3_field_name_8,
                table3.field_name_9 AS table3_field_name_9,
                table3.field_name_10 AS table3_field_name_10,
                table3.field_name_11 AS table3_field_name_11,
                table3.field_name_12 AS table3_field_name_12,
                table3.field_name_13 AS table3_field_name_13,
                table3.field_name_14 AS table3_field_name_14,
                table3.field_name_15 AS table3_field_name_15,
                table3.field_name_16 AS table3_field_name_16,
                table3.field_name_17 AS table3_field_name_17,
                table3.field_name_18 AS table3_field_name_18,
                table3.field_name_19 AS table3_field_name_19,
                table3.field_name_20 AS table3_field_name_20,
                table3.field_name_21 AS table3_field_name_21,
                table3.field_name_22 AS table3_field_name_22,
                table3.field_name_23 AS table3_field_name_23,
                table3.field_name_24 AS table3_field_name_24,
                table3.field_name_25 AS table3_field_name_25,
                table3.field_name_26 AS table3_field_name_26,
                table4.field_name_1 AS table4_field_name_1,
                table4.field_name_2 AS table4_field_name_2,
                table4.field_name_3 AS table4_field_name_3,
                table4.field_name_4 AS table4_field_name_4,
                table4.field_name_5 AS table4_field_name_5,
                table4.field_name_6 AS table4_field_name_6,
                table4.field_name_7 AS table4_field_name_7,
                table4.field_name_8 AS table4_field_name_8,
                table4.field_name_9 AS table4_field_name_9,
                table4.field_name_10 AS table4_field_name_10,
                table4.field_name_11 AS table4_field_name_11,
                table4.field_name_12 AS table4_field_name_12,
                table4.field_name_13 AS table4_field_name_13,
                table4.field_name_14 AS table4_field_name_14,
                table4.field_name_15 AS table4_field_name_15,
                table4.field_name_16 AS table4_field_name_16,
                table4.field_name_17 AS table4_field_name_17,
                table4.field_name_18 AS table4_field_name_18,
                table4.field_name_19 AS table4_field_name_19,
                table4.field_name_20 AS table4_field_name_20,
                table4.field_name_21 AS table4_field_name_21,
                table4.field_name_22 AS table4_field_name_22,
                table4.field_name_23 AS table4_field_name_23,
                table4.field_name_24 AS table4_field_name_24,
                table4.field_name_25 AS table4_field_name_25,
                table4.field_name_26 AS table4_field_name_26,
                table5.field_name_1 AS table5_field_name_1,
                table5.field_name_2 AS table5_field_name_2,
                table5.field_name_3 AS table5_field_name_3,
                table5.field_name_4 AS table5_field_name_4,
                table5.field_name_5 AS table5_field_name_5,
                table5.field_name_6 AS table5_field_name_6,
                table5.field_name_7 AS table5_field_name_7,
                table5.field_name_8 AS table5_field_name_8,
                table5.field_name_9 AS table5_field_name_9,
                table5.field_name_10 AS table5_field_name_10,
                table5.field_name_11 AS table5_field_name_11,
                table5.field_name_12 AS table5_field_name_12,
                table5.field_name_13 AS table5_field_name_13,
                table5.field_name_14 AS table5_field_name_14,
                table5.field_name_15 AS table5_field_name_15,
                table5.field_name_16 AS table5_field_name_16,
                table5.field_name_17 AS table5_field_name_17,
                table5.field_name_18 AS table5_field_name_18,
                table5.field_name_19 AS table5_field_name_19,
                table5.field_name_20 AS table5_field_name_20,
                table5.field_name_21 AS table5_field_name_21,
                table5.field_name_22 AS table5_field_name_22,
                table5.field_name_23 AS table5_field_name_23,
                table5.field_name_24 AS table5_field_name_24,
                table5.field_name_25 AS table5_field_name_25,
                table5.field_name_26 AS table5_field_name_26,
                table6.field_name_1 AS table6_field_name_1,
                table6.field_name_2 AS table6_field_name_2,
                table6.field_name_3 AS table6_field_name_3,
                table6.field_name_4 AS table6_field_name_4,
                table6.field_name_5 AS table6_field_name_5,
                table6.field_name_6 AS table6_field_name_6,
                table6.field_name_7 AS table6_field_name_7,
                table6.field_name_8 AS table6_field_name_8,
                table6.field_name_9 AS table6_field_name_9,
                table6.field_name_10 AS table6_field_name_10,
                table6.field_name_11 AS table6_field_name_11,
                table6.field_name_12 AS table6_field_name_12,
                table6.field_name_13 AS table6_field_name_13,
                table6.field_name_14 AS table6_field_name_14,
                table6.field_name_15 AS table6_field_name_15,
                table6.field_name_16 AS table6_field_name_16,
                table6.field_name_17 AS table6_field_name_17,
                table6.field_name_18 AS table6_field_name_18,
                table6.field_name_19 AS table6_field_name_19,
                table6.field_name_20 AS table6_field_name_20,
                table6.field_name_21 AS table6_field_name_21,
                table6.field_name_22 AS table6_field_name_22,
                table6.field_name_23 AS table6_field_name_23,
                table6.field_name_24 AS table6_field_name_24,
                table6.field_name_25 AS table6_field_name_25,
                table6.field_name_26 AS table6_field_name_26
            FROM
                 table1
                LEFT JOIN table2 ON table2.id = table1.id
                LEFT JOIN table3 ON table3.id = table2.id
                LEFT JOIN table4 ON table4.id = table3.id
                LEFT JOIN table5 ON table5.id = table4.id
                LEFT JOIN table6 ON table6.id = table5.id
                LEFT JOIN table7 ON table7.id = table6.id
                LEFT JOIN table8 ON table8.id = table7.id
                LEFT JOIN table9 ON table9.id = table8.id
                LEFT JOIN table10 ON table10.id = table9.id
                LEFT JOIN table11 ON table11.id = table10.id
                LEFT JOIN table12 ON table12.id = table11.id
                LEFT JOIN table13 ON table13.id = table12.id
                LEFT JOIN table14 ON table14.id = table13.id
                LEFT JOIN table15 ON table15.id = table14.id
                LEFT JOIN table16 ON table16.id = table15.id
                LEFT JOIN table17 ON table17.id = table16.id
                LEFT JOIN table18 ON table18.id = table17.id
                LEFT JOIN table19 ON table19.id = table18.id
                LEFT JOIN table20 ON table20.id = table19.id
                LEFT JOIN table21 ON table21.id = table20.id
            WHERE 
                table1.id IN (?) 
            ORDER BY 
                table1.id DESC
        ', [
            0 => [
                0 => "b55417a9-cfef-4d27-a0ce-bc4febed9df6",
                1 => "812f59d3-9fb9-4916-b42e-b8c60a89dd2a",
            ],
        ], [
            0 => Connection::PARAM_STR_ARRAY,
        ]);

The result is:

PDO::prepare(): Argument #1 ($query) cannot be empty

and the culprit is

                if ($this->needsArrayParameterConversion($params, $types)) {
                    [$sql, $params, $types] = $this->expandArrayParameters($sql, $params, $types);
                }

in Doctrine\DBAL\Connection which calls \Doctrine\DBAL\SQL\Parser::parse.

It looks like the regex patterns defined in the Parser class don't work with very long strings for some reason.

[AlexeyKosov]

Submitted an issue to DBAL repo as it's not a bug in the ORM.
doctrine/dbal#4980

[derrabus]

Thanks!

[derrabus]

Can we close this issue?

[AlexeyKosov]

The bug was fixed in DBAL 3.1.4