Postgres SQL Parser erases parameters in JSON query

[dwgebler]

Bug Report

Q	A
Version	3.6.4

Summary

When executing a NativeQuery like the following example, where bar is a JSONB column:

SELECT * FROM foo WHERE x = :x AND y = :y AND bar @> ANY (ARRAY[:bar_1,:bar_2]::jsonb[]);

the parameters are expanded to positional parameters, but those inside the ARRAY construct are lost from the parameter list, while the placeholders remain. So the query is converted to:

SELECT * FROM foo WHERE x = ? AND y = ? AND bar @> ANY (ARRAY[:bar_1,:bar_2]::jsonb[]);

While the parameter array is stripped of the parameters bar_1 and bar_2, so if it had 4 elements, it drops to 2. Attempting to execute the query will then result in an error over mixed use of positional and named parameters.

Current behaviour

As above.

How to reproduce

Create a DBAL connection to a Postgres 15 database with a table with a JSONB column and attempt to query it in the manner of the supplied example. Using Doctrine ORM as an example:

$sql = 'SELECT * FROM foo WHERE x = :x AND y = :y AND bar @> ANY (ARRAY[:bar_1,:bar_2]::jsonb[]);';
$query = $em->createNativeQuery($sql, $resultSetMapping);
$query->setParameter('x', 'x');
$query->setParameter('y', 'y');
$query->setParameter('bar_1', '{"key":"value"}');
$query->setParameter('bar_2', '{"other_key":"other_value"}');
$query->getResult(); // Exception occurs

Expected behaviour

The query should execute fine and return any results, same as if you used PDO directly.

[derrabus]

1. Which Postgres driver are you using?
2. Does it work if you use positional parameters instead?

[dwgebler]

Hi @derrabus

1. Connection driver is PDO. Running the original query via PDO directly with parameters bound by name works fine.

2. It's actually not as simple as I thought, it doesn't occur in any case as per the example I gave when I opened the issue, it's only a more specific, complex query which is causing the parser to create a dodgy transformation. It does work using positional parameters only, but this is difficult in my case because the query and the parameters (and number of parameters which need to be bound) are a dynamic composite depending on inputs.

SELECT COUNT(f.*) FROM customPostgresFunction(:lat,:lng,:distance) f LEFT JOIN x_group xg ON xg.id = (rec).x_group_id  WHERE (rec).active = true AND (xg.active = true OR (rec).x_group_id IS NULL) AND (rec).services @> ANY (ARRAY [:service_1, :service_2]::jsonb[]);

is converted to

SELECT COUNT(f.*) FROM customPostgresFunction(?,?,?) f LEFT JOIN x_group xg ON xg.id = (rec).x_group_id  WHERE (rec).active = true AND (xg.active = true OR (rec).x_group_id IS NULL) AND (rec).services @> ANY (ARRAY [:service_1, :service_2]::jsonb[]);

This happens in Doctrine\DBAL\Connection::executeQuery() specifically at this portion of code:

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

                $stmt = $connection->prepare($sql);

                $this->bindParameters($stmt, $params, $types);

                $result = $stmt->execute();
            } else {
                $result = $connection->query($sql);
            }

When the Visitor parses the SQL, it transforms the first three function parameters in the SQL to positional ?, but leaves the named parameters :service_1 and :service_2 as named and drops their supplied bound values from the converted parameter list, hence the error.

[dwgebler]

@derrabus I've specifically tracked down the source of the problem is this in the query: ANY (ARRAY [:service_1, :service_2] - I have an accidental space after ARRAY, when I remove it, problem solved. This shouldn't really make a difference though - Postgres doesn't care if you have extra whitespace on ARRAY constructor.

[derrabus]

It does work using positional parameters only, but this is difficult in my case

Sure. I didn't mean to suggest that you'd switch everything to positional parameters. I'm trying to narrow down the possible root cause of the bug.

This happens in Doctrine\DBAL\Connection::executeQuery() specifically at this portion of code:

So, you're saying the array parameter expansion causes the issue? That would be odd indeed, given that you don't bind any array parameters, do you?

[dwgebler]

So step to reproduce is to run a query with SELECT * FROM foo WHERE x = :x AND y = :y AND bar @> ANY (ARRAY [:bar_1,:bar_2]::jsonb[]); (notice superfluous space between ARRAY and [ which is what causes the issue with parsing) - presumably some regex matching weirdness is the cause here.

[derrabus]

Since you've already dug that deep: Would you be able to work on a fix?

[dwgebler]

I can indeed, but probably not today. Leave it with me though.

[dwgebler]

@derrabus fix in https://github.com/doctrine/dbal/pull/6087