Hi,
There is an intermittent Stack Overflow Error we have seen in an SQL
statement. We are not able to replicate it but thought I would share it if
it is anything that needs a fix.
One of our end users wrote an SQL which is transformed by H2 as below
before execution:
SELECT result FROM (TABLE (result int=
(CASE WHEN (((SELECT COLUMN_ONE FROM TABLE_ONE WHERE UNIQUE_ID=(SELECT
UNIQUE_ID FROM MAIN_TABLE WHERE ID=? AND PARENT_ID=?)) = 2
AND
(SELECT COLUMN_TWO FROM TABLE_ONE WHERE UNIQUE_ID=(SELECT UNIQUE_ID FROM
MAIN_TABLE WHERE ID=? AND PARENT_ID=?)) = 2
AND (SELECT COLUMN_THREE FROM TABLE_ONE WHERE UNIQUE_ID=(SELECT UNIQUE_ID
FROM MAIN_TABLE WHERE ID=? AND PARENT_ID=?)) = 2 AND (SELECT COLUMN_FOUR
FROM TABLE_ONE WHERE UNIQUE_ID=(SELECT UNIQUE_ID FROM MAIN_TABLE WHERE ID=?
AND PARENT_ID=?)) = 2) OR (SELECT SOME_COL FROM MAIN_TABLE WHERE ID=? AND
PARENT_ID=?)=1 OR (((SELECT UNIQUE_ID FROM MAIN_TABLE WHERE ID=? AND
PARENT_ID=?)='' OR (SELECT UNIQUE_ID FROM MAIN_TABLE WHERE ID=? AND
PARENT_ID=?) IS NULL) and (SELECT YES_NO FROM MAIN_TABLE WHERE ID=? AND
PARENT_ID=?)='N'))
AND
((SELECT COUNT(*) FROM CHILD_TABLE a INNER JOIN PARENT_TABLE b ON
a.Parent_ID=b.ID WHERE b.PARENT_TABLE_ID IN (1,2,3) and a.CHILD_TABLE_ID IN
(1,2,3) and a.CP_YN='Y' and b.Parent_ID=(SELECT ID FROM MAIN_TABLE WHERE
ID=? AND PARENT_ID=?))>0)
AND
(NOT (((SELECT MAIN_TABLE_ID FROM MAIN_TABLE WHERE ID=? AND
PARENT_ID=?)=(SELECT CORE_INFO FROM CORE_TABLE)) AND (SELECT CORE_COL FROM
CORE_TABLE)=1) OR (SELECT CORE_COL FROM CORE_TABLE) is NULL)
THEN 1
ELSE 0
END)
))
The error stack trace:
WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 50000,
SQLState: HY000
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper: General error:
"java.lang.StackOverflowError"; SQL statement:... ... ...
Caused by: java.lang.StackOverflowError
at org.h2.command.Parser.getSaveTokenType(Parser.java:3739)
at org.h2.command.Parser.isKeyword(Parser.java:3702)
at org.h2.command.Parser.quoteIdentifier(Parser.java:5928)
at org.h2.table.Column.getSQL(Column.java:228)
at org.h2.index.IndexCondition.getSQL(IndexCondition.java:188)
at org.h2.table.TableFilter.getPlanSQL(TableFilter.java:687)
at org.h2.command.dml.Select.getPlanSQL(Select.java:1052)
at org.h2.expression.Subquery.getSQL(Subquery.java:99)
at org.h2.expression.Comparison.getSQL(Comparison.java:139)
at org.h2.command.dml.Select.getPlanSQL(Select.java:1067)
at org.h2.expression.Subquery.getSQL(Subquery.java:99)
at org.h2.expression.Comparison.getAdditional(Comparison.java:532)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:150)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:130)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
...
...
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
...
...
at org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
...
...
around *1000 *times...
...
at
org.h2.expression.ConditionAndOr.optimize(ConditionAndOr.java:131)
As a solution, we have modified the original SQL by providing appropriate
braces(brackets) in additional places to group the query. We have not seen
this error since then.
Regards
Chandra
--
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 http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
