Обсуждение: State of the support for the hstore ? operator

Hello,

I just stumbled upon this (using postgresql-9.4-1205.jdbc42.jar)

  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select count(*) from product where attributes ? 'location_id'");

results in

   org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

"product.attributes" is a hstore column:

I recall a discussion about this problem a while ago, but I can't find it any more.

While I certainly would expect a problem when using a PreparedStatement I am surprised
that a "plain" executeQuerys() also tries to do parameter replacement.

I have found the suggestion to use exist(attributes, 'location_id') but that is not
an option as that does not use the index on the column.

So what is the state of this? Do we have a way of escaping the ? operator?

I couldn't find anything in the documentation.

Regards
Thomas

Thomas Kellerer wrote:
> I just stumbled upon this (using postgresql-9.4-1205.jdbc42.jar)
> 
>   Statement stmt = con.createStatement();
>   ResultSet rs = stmt.executeQuery("select count(*) from product where attributes ? 'location_id'");
> 
> results in
> 
>    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
> 
> "product.attributes" is a hstore column:
> 
> I recall a discussion about this problem a while ago, but I can't find it any more.
> 
> While I certainly would expect a problem when using a PreparedStatement I am surprised
> that a "plain" executeQuerys() also tries to do parameter replacement.
> 
> I have found the suggestion to use exist(attributes, 'location_id') but that is not
> an option as that does not use the index on the column.
> 
> So what is the state of this? Do we have a way of escaping the ? operator?
> 
> I couldn't find anything in the documentation.

I looked into the source, and you can escape the question mark by doubling it.

But I agree that it is strange that the JDBC driver substitutes question marks
with positional parameters in that case.

I think that is a bug in org/postgresql/core/Parser: Even if "withParameters"
is false, it parses question marks.

I'll try to come up with a patch.

Yours,
Laurenz Albe

Albe Laurenz schrieb am 18.11.2015 um 11:35:
>> I just stumbled upon this (using postgresql-9.4-1205.jdbc42.jar)
>>
>>   Statement stmt = con.createStatement();
>>   ResultSet rs = stmt.executeQuery("select count(*) from product where attributes ? 'location_id'");
>>
>> results in
>>
>>    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
>>
> I looked into the source, and you can escape the question mark by doubling it.

Ah, right, thanks.

I knew there was something available - I tried \? and "?" but didn't think of ??

Thomas

Thomas Kellerer wrote:
> Albe Laurenz schrieb am 18.11.2015 um 11:35:
>>> I just stumbled upon this (using postgresql-9.4-1205.jdbc42.jar)
>>>
>>>   Statement stmt = con.createStatement();
>>>   ResultSet rs = stmt.executeQuery("select count(*) from product where attributes ? 'location_id'");
>>>
>>> results in
>>>
>>>    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
>>>
>> I looked into the source, and you can escape the question mark by doubling it.
> 
> Ah, right, thanks.
> 
> I knew there was something available - I tried \? and "?" but didn't think of ??

I see that it is mentioned in the documentation source (since Dec 2014, commit
31c5fcf9d620eb8403ec03682d8f90210da827f3), but I can't find it in the documentation
available on the web site.

Has the documentation on the web site been built recently?

I tried to build the documentation myself with "ant -lib lib doc", but it
failed with

java.io.FileNotFoundException: /path/to/pgjdbc/doc/${docbook.stylesheet} (No such file or directory)

Am I missing something?

Yours,
Laurenz Albe

Laurenz Albe wrote:
> Thomas Kellerer wrote:
>> I just stumbled upon this (using postgresql-9.4-1205.jdbc42.jar)
>>
>>   Statement stmt = con.createStatement();
>>   ResultSet rs = stmt.executeQuery("select count(*) from product where attributes ? 'location_id'");
>>
>> results in
>>
>>    org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

> I think that is a bug in org/postgresql/core/Parser: Even if "withParameters"
> is false, it parses question marks.
> 
> I'll try to come up with a patch.

Here:
https://github.com/pgjdbc/pgjdbc/pull/427

Yours,
Laurenz Albe