Does the JDBC spec prevent '?' from being used as an operator (outside of quotes)?

From Sam Macbeth's question:

Is there anything in the JDBC spec which allows a ? to be escaped and be anything other than a parameter placeholder?

For example, Postgres allows you to use ? as an operator:

SELECT * FROM tbl WHERE tbl.data ? 'abc'

Would a JDBC driver that lets you use ? as an operator still be JDBC-compliant?

If you have the latest postgresql driver you can use:

??

Changing the original query:

SELECT * FROM tbl WHERE tbl.data ?? 'abc'

Details are in this pull request - https://github.com/pgjdbc/pgjdbc/pull/227 This change was made long after the original question, but it's worth noting that there's an easy answer now.

I think it would be perfectly acceptable if the JDBC driver would allow for the ? operator to be unescaped and used as it is, but it might 1) complicate your parser to actually identify parameters from this operator and 2) might confuse people (and maybe tools) who are used to ? only meaning parameter placeholder.

So my suggestion would be to provide some sort of escape (or alternative operator). However looking at the JDBC spec, drivers should only use the JDBC escape syntax to implement the escapes defined in the JDBC spec (13.4.2: "The escape syntax is not intended to be used to invoke user-defined or vendor specific scalar functions."; although this is specifically about the {fn ...} escape).

So either you need to use an alternative escape, or 'break' the rules (I don't think anyone would mind). If you want a more authorative answer, you could send your question to the jdbc-spec-discuss mailinglist. I am sure Lance Andersen (JDBC spec lead) will provide an answer.

Edit:

Also interesting to note that the JDBC spec section 6.2 (Guidelines and Requirements) says:

Drivers should provide access to every feature implemented by the underlying data source, including features that extend the JDBC API. The intent is for applications using the JDBC API to have access to the same feature set as native applications.

So based on that you should (not must) support the ?-operator, you just need to find a practical way to do it.

Update based on the discussion on jdbc-spec-discuss

According to Lance Andersen the JDBC specification follows the SQL specification with regard to question marks: they can only be used as parameter placeholders in query text (except of course in comments and quoted text), as such use of ? as in the PostgreSQL hstore operators wouldn't be allowed. (see this message)

The option available is to either provide an alias or an escape for the operator, provided this does not conflict with future changes (which is rather hard to do without clairvoyance ;). The best solution - to prevent problems with future JDBC changes - is probably a custom escape.

JDBC does not actually define a vendor escape, but Lance Andersen does suggest an escape that is similar to the JDBC escapes: {postgres <thing to be escaped>}; use of the vendorname or drivername in this escape will provide a form of namespacing that should prevent conflict with the specification. (see this message)

To be in line with 'normal' JDBC function escapes, I would suggest to define an escape that will allow the query in your question to be stated as:

SELECT * FROM tbl WHERE {postgres containskey(tbl.data, 'abc')}

I chose containskey based on the meaning of ? in the hstore documentation. Similar suggestions for ?& : containsallkeys) and for ?| : containsanykey. For consistency you could consider to do this for the other hstore operators as well.

You could also decide to only escape the question mark itself. For example escape with {postgres '?'} or {postgres qm} (qm for question mark). I do think readability is less than my function-escape suggestion:

SELECT * FROM tbl WHERE tbl.data {postgres '?'} 'abc'

Updated based on new proposal for JDBC v.Next (4.4?)

The next JDBC specification (likely numbered 4.4), will probably add an explicit JDBC escape to entirely escape fragments of a query, with the express intent to be able to escape question marks for database systems that don't natively use question marks for parameter markers and need to support other uses of question marks.

The proposed syntax would be {\ <thing-to escape> \} (which is already supported by the Oracle JDBC driver as a non-standard escape). With this syntax, a parameter marker could then be escaped with {\?\} (in a string literal: {\\?\\}), or escape a larger fragment for improved readability.

See also SQL 2016 MATCH RECOGNIZE JDBC parameter Marker / Escape Characters and earlier discussion on the jdbc-spec-discuss mailing list.

I don't see anything in the JDBC specification that would allow ? to be escaped. Pretty much all it says about ? is:

Parameter markers, represented by “?” in the SQL string, are used to specify input values to the statement that may vary at runtime. ^[1]

and later...

Parameter ordinals, which are integers passed to the approriate setter method, refer to the parameter markers ("?") in the statement, starting at one. ^[2]

And it only defines escape syntax for a small set of features, none of which look like they could be applied to ?:

JDBC defines escape syntax for the following:

• scalar functions
• date and time literals
• outer joins
• calling stored procedures
• escape characters for LIKE clauses ^[3]

Overall, it doesn't seem like the JDBC specification has very "strict" language (compared to, for example, some W3C specification documents which use must and should a lot), so I don't know whether a driver which allowed ? to be escaped would be technically non-compliant, but it would probably be not-so-compatible.

It doesn't even look like even the Postgres driver would allow it, as the method in the driver which actually parses SQL statements for ? doesn't check for any escape characters.

<sup>1. JDBC 4.1 Specification, Section 13.2 — The PreparedStatement Interface
2. JDBC 4.1 Specification, Section 13.3.2 — Setting Parameters
3. JDBC 4.1 Specification, Section 13.4 — Escape Syntax</sup>