Problems with question marks in operators (JDBC, ECPG, ...) - Mailing list pgsql-hackers

<div dir="ltr">Hello,<br /><br />I've been trying to use the new JSONB format using JDBC, and ran into trouble with the
questionmark operators (?, ?| and ?&).<br />I realise there has already been a discussion about this (actually, it
wasabout hstore, not jsonb, but that's more or less the same problem):<br />- <a
href="http://www.postgresql.org/message-id/51114165.4070106@abshere.net">http://www.postgresql.org/message-id/51114165.4070106@abshere.net</a><br
/>-<a
href="http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html">http://mail.openjdk.java.net/pipermail/jdbc-spec-discuss/2013-February/000048.html</a><br
/><br/><br />From what I gather, the JDBC team seems to think that using ? in operators is not in line with the SQL
standards,but the outcome on the PostgreSQL list team suggested that a fix could be implemented in the PostgreSQL JDBC
driveranyway.<br /><br />I think this problem might actually affect a number of other places, unfortunately. I must
admitI don't know the SQL specifications very well (a quick look at a draft seemed to suggest the question mark was
indeeda reserved character, but this is probably out of context), and this isn't about finding out who is right or who
iswrong, but from a practical point of view, this also seemed to affect other kinds of clients, for example:<br />-
Perl:<a
href="http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html">http://blog.endpoint.com/2015/01/dbdpg-escaping-placeholders-with.html</a><br
/>-JavaScript: <a
href="https://github.com/tgriesser/knex/issues/519">https://github.com/tgriesser/knex/issues/519</a><br/>Of course,
therecan be workarounds in some cases, but even if they work, they can be quite awkward, especially if they differ from
onelanguage to another (in particular if you want to be able to re-use the same query from multiple languages).<br
/><br/>As far, as I can tell, question mark operators are also incompatible with PostgreSQL's ECPG when using dynamic
SQL.<a
href="http://www.postgresql.org/docs/current/static/ecpg-dynamic.html">http://www.postgresql.org/docs/current/static/ecpg-dynamic.html</a><br
/>(I'mpasting an example at the end of this message, tried with a PostgreSQL 9.4 server.)<br /><br />I realise it's a
bitlate to raise this concern, considering that these operators have been around for a few versions now (at least as
faras hstore), but wouldn't it be better to provide official alternative notations altogether, something that is less
likelyto conflict with most client implementations? Perhaps a function or a notation similar to what 'CAST(x AS y)' is
to'x::y' would be suitable if other symbols aren't better (although I think a short operator would still be
preferable).<br/><br /><br />Best wishes,<br /><br />Bruno.<br /><br /><br /><br /><br />____ ECPG test output:<br
/><br/>** Using query: SELECT ('{"key1":123,"key2":"Hello"}'::jsonb ->> ?::text)::text<br /><br />Result should
be123 for 'key1': 123<br />Result should be empty for 'key3': <br /><br /><br />** Using query: SELECT
('{"key1":123,"key2":"Hello"}'::jsonb? ?::text)::text<br /><br />SQL error: syntax error at or near "$1" on line 52<br
/>SQLerror: invalid statement name "mystmt3" on line 55<br />Result should be true for 'key1': <br />SQL error: invalid
statementname "mystmt3" on line 59<br />Result should be false for 'key3': <br />SQL error: invalid statement name
"mystmt3"on line 62<br /><br /><br /><br />____ ECPG test code:<br /><br /><br />#include <stdio.h><br />#include
<stdlib.h><br/><br />int main()<br />{<br />    EXEC SQL BEGIN DECLARE SECTION;<br />        char* target =
"unix:postgresql://localhost/mydatabase";<br/>        char result1[2048];<br />        int result1_ind;<br />       
char*key1_str = "key1";<br />        char *key3_str = "key3";<br />        char *stmt2 = "SELECT
('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb->> ?::text)::text";<br />        char *stmt3 = "SELECT
('{\"key1\":123,\"key2\":\"Hello\"}'::jsonb? ?::text)::text";<br />    EXEC SQL END DECLARE SECTION;<br />    <br />   
EXECSQL WHENEVER SQLWARNING SQLPRINT;<br />    EXEC SQL WHENEVER SQLERROR SQLPRINT;<br />    EXEC SQL CONNECT TO
:targetAS testdb;<br />    <br /><br />    printf("\n\n** Using query: %s\n\n", stmt2);<br />    EXEC SQL PREPARE
mystmt2FROM :stmt2;<br />    <br />    result1[0] = 0;<br />    EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind
USING:key1_str;<br />    printf("Result should be 123 for 'key1': %s\n", result1);<br />    <br />    result1[0] =
0;<br/>    EXEC SQL EXECUTE mystmt2 INTO :result1 :result1_ind USING :key3_str;<br />    printf("Result should be empty
for'key3': %s\n", result1);<br />    <br />    EXEC SQL DEALLOCATE PREPARE mystmt2;<br /><br /><br />    printf("\n\n**
Usingquery: %s\n\n", stmt3);<br />    EXEC SQL PREPARE mystmt3 FROM :stmt3;<br />    <br />    result1[0] = 0;<br />   
EXECSQL EXECUTE mystmt3 INTO :result1_ind USING :key1_str;<br />    printf("Result should be true for 'key1': %s\n",
result1);<br/>    <br />    result1[0] = 0;<br />    EXEC SQL EXECUTE mystmt3 INTO :result1_ind USING :key3_str;<br
/>   printf("Result should be false for 'key3': %s\n", result1);<br />    <br />    EXEC SQL DEALLOCATE PREPARE
mystmt3;<br /><br />    EXEC SQL DISCONNECT ALL;<br />    <br />    return 0;<br />}<br /></div>