Re: Problems with question marks in operators (JDBC, ECPG, ...)

Bruno Harbulot <bruno(at)distributedmatter(dot)net> wrote:
> On Fri, May 15, 2015 at 9:41 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, May 15, 2015 at 4:38 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:

>>> Well our solution was to use ?? but that does mean we have to
>>> do some extra parsing which in a perfect world wouldn't be
>>> necessary.

It seems like maybe we missed a trick when we dealt with this; the
Java Specification (the language spec, not the API spec) seems to
say that curly braces should be used for this sort of thing. So
The Java Way would seem to be to have used {?} or {question_mark}
or some such as our product-specific way of dealing with this.
That probably would reduce the JDBC parsing overhead, since it
must look for curly braces for the standard escapes, anyway (like
a date literal being {d '2015-05-15'}).

That would be kinda ugly, since if you wanted to use the ?||
operator you would need to write that in your prepared statement as
{?}||. That seems only moderately more confusing than the current
need to write it as ??||, though.

But the opportunity to do that up-front was missed and, besides, we
have other connectors to worry about.

>> So what about strings quoted with '' or $$ or $something$ - how
>> would you handle those?
>
> I hadn't realised that the JDBC driver allowed the ? operator to
> be escaped as ??. It seems to work indeed (at least with version
> 9.4-1201 of the JDBC driver).
>
> $$....?$$ also works. I guess the JDBC drivers tries to parse
> literals first and escapes them accordingly.

Yeah; regardless of what escape is used, the JDBC driver still
needs to deal with finding literals and treating them differently.

> That said, I'd still suggest providing new operators and
> deprecating the ones containing a question mark if possible.
> (There are 8 distinct operator names like this: "?-", "?&", "?",
> "?#", "?||", "?-|", "?|" and "<?>".)

That would lower the burden on every connector to do something
about this.

> I think it would be nicer to have a single mechanism that can be
> used consistently across multiple languages (?? doesn't work for
> ECPG, for example), considering that ? as a placeholder seems
> quite common.

I don't know how practical it would be for all connectors to use
the same escape syntax. They all need to have some way to do it if
they want to allow the operators containing a question mark to be
used, but if we're going to allow it in SQL operators it may be
more sane to allow each connector to figure out what is the best
escape.

I lean toward deprecating those operators in favor of ones without
the problem character, and some years down the line dropping the
old (deprecated) operators.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company