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.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
