Error messages could be improved

[Bernhard Haumacher]

Hi,

currently trying to support H2 in our application framework by running our test suite against H2. From these tests, I got the following error message, which is really not helpful, because  the location of the error is not reported:

org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT ""dt"".""BRANCH"", ""dt"".""IDENTIFIER"", ""dt"".""REV_MAX"", ""dt"".""REV_MIN"", ""dt"".""REV_CREATE"", ""dt"".""PHYSICAL_RESOURCE"", ""dt"".""A1"", ""dt"".""A2"", ""dt"".""B1"", ""dt"".""B2"", ""dt"".""B3"", ""dt"".""F3"", ""dt"".""C1"", ""dt"".""C2"" FROM ""CC"" ""dt"" INNER JOIN ((SELECT DISTINCT ""t0"".""BRANCH"", ""t0"".""IDENTIFIER"", ""t0"".""REV_MAX"" FROM ""CC"" ""t0"" LEFT JOIN ""FLEX_DATA"" ""t1"" ON (""t0"".""BRANCH"" = ""t1"".""BRANCH"") AND (""t0"".""IDENTIFIER"" = ""t1"".""IDENTIFIER"") AND (? <= ""t1"".""REV_MAX"") AND (? >= ""t1"".""REV_MIN"") AND (? = ""t1"".""TYPE"") AND (""t1"".""ATTR"" = ?) WHERE (""t0"".""REV_MAX"" >= ?) AND (""t0"".""REV_MIN"" <= ?) AND (""t0"".""BRANCH"" = ?) AND (""t1"".""VARCHAR_DATA"" = ?) AND NOT ((""t1"".""VARCHAR_DATA"") IS NULL)) ""idt""[*]) ON (""dt"".""BRANCH"" = ""idt"".""BRANCH"") AND (""dt"".""IDENTIFIER"" = ""idt"".""IDENTIFIER"") AND (""dt"".""REV_MAX"" = ""idt"".""REV_MAX"") LIMIT 1"; erwartet "UNION, MINUS, EXCEPT, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, FOR, )"
Syntax error in SQL statement "SELECT ""dt"".""BRANCH"", ""dt"".""IDENTIFIER"", ""dt"".""REV_MAX"", ""dt"".""REV_MIN"", ""dt"".""REV_CREATE"", ""dt"".""PHYSICAL_RESOURCE"", ""dt"".""A1"", ""dt"".""A2"", ""dt"".""B1"", ""dt"".""B2"", ""dt"".""B3"", ""dt"".""F3"", ""dt"".""C1"", ""dt"".""C2"" FROM ""CC"" ""dt"" INNER JOIN ((SELECT DISTINCT ""t0"".""BRANCH"", ""t0"".""IDENTIFIER"", ""t0"".""REV_MAX"" FROM ""CC"" ""t0"" LEFT JOIN ""FLEX_DATA"" ""t1"" ON (""t0"".""BRANCH"" = ""t1"".""BRANCH"") AND (""t0"".""IDENTIFIER"" = ""t1"".""IDENTIFIER"") AND (? <= ""t1"".""REV_MAX"") AND (? >= ""t1"".""REV_MIN"") AND (? = ""t1"".""TYPE"") AND (""t1"".""ATTR"" = ?) WHERE (""t0"".""REV_MAX"" >= ?) AND (""t0"".""REV_MIN"" <= ?) AND (""t0"".""BRANCH"" = ?) AND (""t1"".""VARCHAR_DATA"" = ?) AND NOT ((""t1"".""VARCHAR_DATA"") IS NULL)) ""idt""[*]) ON (""dt"".""BRANCH"" = ""idt"".""BRANCH"") AND (""dt"".""IDENTIFIER"" = ""idt"".""IDENTIFIER"") AND (""dt"".""REV_MAX"" = ""idt"".""REV_MAX"") LIMIT 1"; expected "UNION, MINUS, EXCEPT, INTERSECT, ORDER, OFFSET, FETCH, LIMIT, FOR, )"; SQL statement:
SELECT "dt"."BRANCH", "dt"."IDENTIFIER", "dt"."REV_MAX", "dt"."REV_MIN", "dt"."REV_CREATE", "dt"."PHYSICAL_RESOURCE", "dt"."A1", "dt"."A2", "dt"."B1", "dt"."B2", "dt"."B3", "dt"."F3", "dt"."C1", "dt"."C2" FROM "CC" "dt" INNER JOIN ((SELECT DISTINCT "t0"."BRANCH", "t0"."IDENTIFIER", "t0"."REV_MAX" FROM "CC" "t0" LEFT JOIN "FLEX_DATA" "t1" ON ("t0"."BRANCH" = "t1"."BRANCH") AND ("t0"."IDENTIFIER" = "t1"."IDENTIFIER") AND (? <= "t1"."REV_MAX") AND (? >= "t1"."REV_MIN") AND (? = "t1"."TYPE") AND ("t1"."ATTR" = ?) WHERE ("t0"."REV_MAX" >= ?) AND ("t0"."REV_MIN" <= ?) AND ("t0"."BRANCH" = ?) AND ("t1"."VARCHAR_DATA" = ?) AND NOT (("t1"."VARCHAR_DATA") IS NULL)) "idt") ON ("dt"."BRANCH" = "idt"."BRANCH") AND ("dt"."IDENTIFIER" = "idt"."IDENTIFIER") AND ("dt"."REV_MAX" = "idt"."REV_MAX") LIMIT 1 [42001-190]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.getSyntaxError(DbException.java:205)
    at org.h2.command.Parser.getSyntaxError(Parser.java:524)
    at org.h2.command.Parser.read(Parser.java:3120)
    at org.h2.command.Parser.readTableFilter(Parser.java:1159)
    at org.h2.command.Parser.readJoin(Parser.java:1526)
    at org.h2.command.Parser.parseJoinTableFilter(Parser.java:1879)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1982)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1867)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1688)
    at org.h2.command.Parser.parseSelect(Parser.java:1676)
    at org.h2.command.Parser.parsePrepared(Parser.java:432)
    at org.h2.command.Parser.parse(Parser.java:304)
    at org.h2.command.Parser.parse(Parser.java:280)
    at org.h2.command.Parser.prepareCommand(Parser.java:241)
    at org.h2.engine.Session.prepareLocal(Session.java:460)
    at org.h2.engine.Session.prepareCommand(Session.java:402)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:276)

I finally found out what the problem is - but only by debugging the H2 query parser. The problem was a superfluous parenthesis around the subquery within the join.

Maybe, even the H2 SQL grammar could be somewhat widened to ignore such additional parenteses, since at least MySQL and Oracle parse the statement without problems.

[Noel Grandin]

we do indicate the location of the error - look for the [*] in the error message