Hi, I would need a complete, reproducible test case, including "create table" and data.
Is this the exact statement that caused the exception? I would be interested to understand what the query does and how it was written (handwritten or generated?). Regards, Thomas On Tuesday, October 13, 2015, Chandra DhulipalaV <[email protected]> wrote: > 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] > <javascript:_e(%7B%7D,'cvml','h2-database%[email protected]');> > . > To post to this group, send email to [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- 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.
