[postgres] JDBC driver can't distinguish between jsonb operators (?, ?|) and query arguments (?)

[prashantvithani]

Sample Schema

Users {
  id: int
  company_id: int
  options: json/jsonb
}

Writing query to retreive user records with company_id = 1, and whose options contains keys key1 and key2

Case 1 with Success

[JRuby] SQL Query (prepared_statements: false)

SELECT * FROM users WHERE users.company_id = 1 AND (users.options::jsonb ?| array['key1', 'key2']) LIMIT 5

binds (Array<>): [] (no need as the values are embedded in query already)

[MRI] SQL Query (prepared_statements: true/false)

SELECT * FROM users WHERE users.company_id = $1 AND (users.options::jsonb ?| array['key1', 'key2']) LIMIT $2

binds (Array<ActiveRecord::Relation::QueryAttribute>): [argument1, argument2] (values to be embedded in query replacing $n)

Case 2 with Failure

[JRuby] SQL Query (prepared_statements: true)

SELECT * FROM users WHERE users.company_id = ? AND (users.options::jsonb ?| array['key1', 'key2']) LIMIT ?

binds (Array<ActiveRecord::Relation::QueryAttribute>): [argument1, argument2] (values to be embedded in query replacing ?)

Error Statement

ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: No value specified for parameter 3: SELECT * FROM users WHERE users.company_id = ? AND (users.options::jsonb ?| array['key1', 'key2']) LIMIT ?.

Rails's connection adapter translates arguments of query in $n format, whereas JDBC-adapter translates it into ? and it conflicts with the PostgreSQL's jsonb operators ? or ?| and seeks for the extra argument for jsonb operator ?.

JRuby:

jruby 9.2.0.0 (2.5.0) 2018-05-24 81156a8 Java HotSpot(TM) 64-Bit Server VM 10.0.2+13 on 10.0.2+13 [darwin-x86_64]

Java:

java version "10.0.2" 2018-07-17
Java(TM) SE Runtime Environment 18.3 (build 10.0.2+13)
Java HotSpot(TM) 64-Bit Server VM 18.3 (build 10.0.2+13, mixed mode)

PostgreSQL version: 10.4

[dr-itz]

The problem is, JDBC uses ? as placeholder instead of the PG-specific $n. So for prepared statements, the JSONB operator becomes ?? instead of a single ?. See https://github.com/pgjdbc/pgjdbc/issues/643