PostgreSQL JSONB contains operator is broken

[alvaro-octal]

BC Break Report

Q	A
BC Break	yes
Version	3.0.2

Summary

The migrations worked fine with 3.0.1, but version 3.0.2 seems to be trying to replace the ? PostgreSQL operand by $1 of queries like this:

UPDATE "user" u
SET permissions = permissions::jsonb || '["dashboard"]'::jsonb
WHERE (u.permissions_old->'dashboard'->'api')::jsonb ? 'read'

https://www.postgresql.org/docs/9.4/functions-json.html#FUNCTIONS-JSONB-OP-TABLE

Previous behavior

This migration was valid

Current behavior

[error] Migration App\Migrations\Version20201023161652 failed during Execution. Error: "An exception occurred while executing '
            UPDATE "user" u
            SET permissions = permissions::jsonb || '["dashboard"]'::jsonb
            WHERE (u.permissions_old->'dashboard'->'api')::jsonb ? 'read'
        ':
SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"
LINE 4: ...ERE (u.permissions_old->'dashboard'->'api')::jsonb $1 'read'
                                                              ^"
In AbstractPostgreSQLDriver.php line 73:
                                                                              
  An exception occurred while executing '                                     
              UPDATE "user" u                                                 
              SET permissions = permissions::jsonb || '["dashboard"]'::jsonb  
              WHERE (u.permissions_old->'dashboard'->'api')::jsonb ? 'read'   
          ':                                                                  
                                                                              
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"       
  LINE 4: ...ERE (u.permissions_old->'dashboard'->'api')::jsonb $1 'read'     

How to reproduce

Make any migration with the ? operand on a PostgreSQL database on (at least) PHP 7.3

[goetas]

This is actually a PHP bug, solved in 7.4 by https://wiki.php.net/rfc/pdo_escape_placeholders (see also https://bugs.php.net/bug.php?id=71885)

[shadowhand]

I do not believe this is purely a PHP bug. The same query works just fine in doctrine/migrations v3.0.1 and breaks in v3.0.2.

[adrianrudnik]

Same happening here, just by upgrading to 3.0.2 on PHP 7.4.13.

I traced it down with xdebug to d3cf285#diff-234fe782577400df58580164cb830c2a2067c411c7a33f649a220719794d76fbL297 (takes while until it jumps).

Incoming params and types are an empty array.

The change from executeUpdate (deprecated, but using PDO statements) to executeQuery (using PDO query) introduced this bug. Most likely as query tries to analyze possible placeholders, while a statement needs them given as parameters.

The deprecation notice for executeUpdate also states the replacement should be executeStatement, not executeQuery.

Hope thats the right hunch.