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:
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.