Postgres Pro
Downloads
Home   >   mailing lists

Thread: Simple queries with JDBC escaped scalar functions result in exceptions

Simple queries with JDBC escaped scalar functions result in exceptions

From
Adam Rauch
Date:
I upgraded to postgresql-9.4.1209.jar and fixed all the places in our code that used JDBC escaped scalar functions (e.g., {fn now()}) inside function definitions. However, we continued to see the telltale exception (org.postgresql.util.PSQLException: ERROR: syntax error at or near "{") when executing straightforward SELECT queries that included scalar functions. I tracked this down to comments; a simple repro is to execute the following query via JDBC:
SELECT /* $ */ {fn curdate()}
The original comments included many characters, but the dollar sign seems to be the culprit. I think this indicates a problem with the parser that's attempting to detect string literals.

Thanks,
Adam

Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Dave Cramer
Date:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 


On 27 August 2016 at 00:31, Adam Rauch <adam@labkey.com> wrote:
I upgraded to postgresql-9.4.1209.jar and fixed all the places in our code that used JDBC escaped scalar functions (e.g., {fn now()}) inside function definitions. However, we continued to see the telltale exception (org.postgresql.util.PSQLException: ERROR: syntax error at or near "{") when executing straightforward SELECT queries that included scalar functions. I tracked this down to comments; a simple repro is to execute the following query via JDBC:
SELECT /* $ */ {fn curdate()}
The original comments included many characters, but the dollar sign seems to be the culprit. I think this indicates a problem with the parser that's attempting to detect string literals.

Thanks,
Adam


Re: Simple queries with JDBC escaped scalar functions resultin exceptions

From
Dave Cramer
Date:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 


On 27 August 2016 at 00:31, Adam Rauch <adam@labkey.com> wrote:
I upgraded to postgresql-9.4.1209.jar and fixed all the places in our code that used JDBC escaped scalar functions (e.g., {fn now()}) inside function definitions. However, we continued to see the telltale exception (org.postgresql.util.PSQLException: ERROR: syntax error at or near "{") when executing straightforward SELECT queries that included scalar functions. I tracked this down to comments; a simple repro is to execute the following query via JDBC:
SELECT /* $ */ {fn curdate()}
The original comments included many characters, but the dollar sign seems to be the culprit. I think this indicates a problem with the parser that's attempting to detect string literals.

Thanks,
Adam


Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Andreas Joseph Krogh
Date:
På søndag 28. august 2016 kl. 03:18:42, skrev Dave Cramer <pg@fastcrypt.com>:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 
 
I think the universial answer here is "Because you can". Many use comments in SQL as a way to both understand what's going on better (ie. when the SQL is generated by code) and to make logging easier. There's no reason why a driver should not cope with valid comments.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Adam Rauch
Date:

In our development and test environments (but not production), our SQL generation engine inserts comments at strategic places in the queries it constructs, e.g., tagging a GUID with the real-world name of the object it represents or providing details about the requests from higher layers in the system that caused the query to be produced. When a query results in an exception, incorrect results, or poor performance, those comments help us better understand the query so we can fix or optimize it. Of course, this is legal and has been supported by the driver for many years.

The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

SELECT /* " */ {fn curdate()};
SELECT /* $ */ {fn curdate()};
SELECT /* ' */ {fn curdate()};
SELECT /* {D */ {fn curdate()};
SELECT /* {E */ {fn curdate()};
SELECT /* {F */ {fn curdate()};
SELECT /* {O */ {fn curdate()};
SELECT /* {T */ {fn curdate()};
SELECT /* {d */ {fn curdate()};
SELECT /* {e */ {fn curdate()};
SELECT /* {f */ {fn curdate()};
SELECT /* {o */ {fn curdate()};
SELECT /* {t */ {fn curdate()};

Thanks,
Adam
On 8/28/2016 6:43 AM, Andreas Joseph Krogh wrote:

På søndag 28. august 2016 kl. 03:18:42, skrev Dave Cramer <pg@fastcrypt.com>:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 
 
I think the universial answer here is "Because you can". Many use comments in SQL as a way to both understand what's going on better (ie. when the SQL is generated by code) and to make logging easier. There's no reason why a driver should not cope with valid comments.
 


Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Vladimir Sitnikov
Date:
>The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

Thanks for the analysis.


The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Andreas Joseph Krogh
Date:
På søndag 28. august 2016 kl. 03:18:42, skrev Dave Cramer <pg@fastcrypt.com>:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 
 
I think the universial answer here is "Because you can". Many use comments in SQL as a way to both understand what's going on better (ie. when the SQL is generated by code) and to make logging easier. There's no reason why a driver should not cope with valid comments.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
Adam Rauch
Date:

Thanks, Vladimir! Local build of 9.4.1210-SNAPSHOT works great; all my legal test queries execute successfully now.

Just FYI, the new driver threw java.lang.ArrayIndexOutOfBoundsException with an illegal query: SELECT /* /* */ 1

java.lang.ArrayIndexOutOfBoundsException
    at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:597)
    at java.lang.StringBuilder.append(StringBuilder.java:190)
    at org.postgresql.core.Parser.parseSql(Parser.java:1037)
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:974)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19)
    at org.postgresql.util.LruCache.borrow(LruCache.java:118)
    at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:261)
    at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:146)
    at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:91)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1259)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1625)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:418)

I'm not suggesting this needs to be addressed... unbalanced comments will of course be rejected by the server anyway.

Thanks,
Adam
On 8/28/2016 8:00 AM, Vladimir Sitnikov wrote:

>The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

Thanks for the analysis.


The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Re: Simple queries with JDBC escaped scalar functions result in exceptions

From
danap
Date:
Dave Cramer wrote:
> Looks like the JDBC mini parser is having troubles with that.

> Why do you have comments in your sql ?
>
> Dave Cramer
>

MyJSQLView SQL schema dump. All databases that my application supports
comments, and is documented.

--
-- MyJSQLView SQL Dump
-- Version: 7.07
-- WebSite: http://myjsqlview.com
--
-- Host: 127.0.0.1
-- Generated On: 2016.05.08 AD at 02:11:28 MDT
-- SQL version: PostgreSQL 9.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."procedure_files"
--

DROP TABLE IF EXISTS "public"."procedure_files";
CREATE TABLE "public"."procedure_files" (
     "id" serial NOT NULL,
     "procedure_id" integer DEFAULT NULL,
     "file_name" varchar(null) DEFAULT NULL,
     "file_oid" oid DEFAULT NULL
);

Re: Simple queries with JDBC escaped scalar functionsresult in exceptions

From
Adam Rauch
Date:

In our development and test environments (but not production), our SQL generation engine inserts comments at strategic places in the queries it constructs, e.g., tagging a GUID with the real-world name of the object it represents or providing details about the requests from higher layers in the system that caused the query to be produced. When a query results in an exception, incorrect results, or poor performance, those comments help us better understand the query so we can fix or optimize it. Of course, this is legal and has been supported by the driver for many years.

The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

SELECT /* " */ {fn curdate()};
SELECT /* $ */ {fn curdate()};
SELECT /* ' */ {fn curdate()};
SELECT /* {D */ {fn curdate()};
SELECT /* {E */ {fn curdate()};
SELECT /* {F */ {fn curdate()};
SELECT /* {O */ {fn curdate()};
SELECT /* {T */ {fn curdate()};
SELECT /* {d */ {fn curdate()};
SELECT /* {e */ {fn curdate()};
SELECT /* {f */ {fn curdate()};
SELECT /* {o */ {fn curdate()};
SELECT /* {t */ {fn curdate()};

Thanks,
Adam
On 8/28/2016 6:43 AM, Andreas Joseph Krogh wrote:

På søndag 28. august 2016 kl. 03:18:42, skrev Dave Cramer <pg@fastcrypt.com>:
Looks like the JDBC mini parser is having troubles with that. Why do you have comments in your sql ? 
 
I think the universial answer here is "Because you can". Many use comments in SQL as a way to both understand what's going on better (ie. when the SQL is generated by code) and to make logging easier. There's no reason why a driver should not cope with valid comments.
 


Re: Simple queries with JDBC escaped scalar functions resultin exceptions

From
Vladimir Sitnikov
Date:
>The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

Thanks for the analysis.


The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Re: Simple queries with JDBC escaped scalar functions resultin exceptions

From
Adam Rauch
Date:

Thanks, Vladimir! Local build of 9.4.1210-SNAPSHOT works great; all my legal test queries execute successfully now.

Just FYI, the new driver threw java.lang.ArrayIndexOutOfBoundsException with an illegal query: SELECT /* /* */ 1

java.lang.ArrayIndexOutOfBoundsException
    at java.lang.AbstractStringBuilder.append(AbstractStringBuilder.java:597)
    at java.lang.StringBuilder.append(StringBuilder.java:190)
    at org.postgresql.core.Parser.parseSql(Parser.java:1037)
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:974)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43)
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:19)
    at org.postgresql.util.LruCache.borrow(LruCache.java:118)
    at org.postgresql.core.QueryExecutorBase.borrowQuery(QueryExecutorBase.java:261)
    at org.postgresql.jdbc.PgConnection.borrowQuery(PgConnection.java:146)
    at org.postgresql.jdbc.PgPreparedStatement.<init>(PgPreparedStatement.java:91)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1259)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1625)
    at org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:418)

I'm not suggesting this needs to be addressed... unbalanced comments will of course be rejected by the server anyway.

Thanks,
Adam
On 8/28/2016 8:00 AM, Vladimir Sitnikov wrote:

>The dollar sign ($) is the only case we came across in our test environments, but it looks like a single quote (') or double quote (") in a comment also trips up the parser. As do the two-character combinations {D, {E, {F, {O, and {T... and their lowercase equivalents. A full set of test queries that fail when executed via postgresql-9.4.1209.jar:

Thanks for the analysis.


The fix is available in 9.4.1210-SNAPSHOT

Vladimir

Re: Simple queries with JDBC escaped scalar functions resultin exceptions

From
danap
Date:
Dave Cramer wrote:
> Looks like the JDBC mini parser is having troubles with that.

> Why do you have comments in your sql ?
>
> Dave Cramer
>

MyJSQLView SQL schema dump. All databases that my application supports
comments, and is documented.

--
-- MyJSQLView SQL Dump
-- Version: 7.07
-- WebSite: http://myjsqlview.com
--
-- Host: 127.0.0.1
-- Generated On: 2016.05.08 AD at 02:11:28 MDT
-- SQL version: PostgreSQL 9.4.1
-- Database: key_tables
--

-- ------------------------------------------

--
-- Table structure for table "public"."procedure_files"
--

DROP TABLE IF EXISTS "public"."procedure_files";
CREATE TABLE "public"."procedure_files" (
     "id" serial NOT NULL,
     "procedure_id" integer DEFAULT NULL,
     "file_name" varchar(null) DEFAULT NULL,
     "file_oid" oid DEFAULT NULL
);