Why do you have all of those "on 1=1" statements? That's not valid SQL.
HK wrote: > Hello, > > > I'll write an error message. > > Exception in thread "main" org.h2.jdbc.JdbcSQLException: Syntax error > in SQL statement "SELECT > GC.GCL_SID1 AS GCL_SID1, > GC.GCL_SID2 AS GCL_SID2, > GC.GCL_SID3 AS GCL_SID3, > GC.GCL_SID4 AS GCL_SID4, > GC.GCL_SID5 AS GCL_SID5, > GI1.GRP_NAME AS GNAME1, > GI2.GRP_NAME AS GNAME2, > GI3.GRP_NAME AS GNAME3, > GI4.GRP_NAME AS GNAME4, > GI5.GRP_NAME AS GNAME5 > FROM ( > ( > ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON[*] 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5 > ON GC.GCL_SID5 = GI5.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > INNER JOIN ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > INNER JOIN PUBLIC.TEST_GROUP_CLASS GC > ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5 > ON GC.GCL_SID5 = GI5.GRP_SID"; expected "AS, RIGHT, LEFT, FULL, > INNER, JOIN, CROSS, NATURAL, )"; SQL statement: > CREATE FORCE VIEW PUBLIC._0 AS > SELECT > GC.GCL_SID1 AS GCL_SID1, > GC.GCL_SID2 AS GCL_SID2, > GC.GCL_SID3 AS GCL_SID3, > GC.GCL_SID4 AS GCL_SID4, > GC.GCL_SID5 AS GCL_SID5, > GI1.GRP_NAME AS GNAME1, > GI2.GRP_NAME AS GNAME2, > GI3.GRP_NAME AS GNAME3, > GI4.GRP_NAME AS GNAME4, > GI5.GRP_NAME AS GNAME5 > FROM ( > ( > ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5 > ON GC.GCL_SID5 = GI5.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > ) ON 1=1 > INNER JOIN ( > ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > ) ON 1=1 > INNER JOIN ( > PUBLIC.TEST_GROUP_CLASS GC > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > ) ON 1=1 > INNER JOIN PUBLIC.TEST_GROUP_CLASS GC > ON 1=1 > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI1 > ON GC.GCL_SID1 = GI1.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI2 > ON GC.GCL_SID2 = GI2.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI3 > ON GC.GCL_SID3 = GI3.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI4 > ON GC.GCL_SID4 = GI4.GRP_SID > LEFT OUTER JOIN PUBLIC.TEST_GROUPM GI5 > ON GC.GCL_SID5 = GI5.GRP_SID [42001-154] > at org.h2.message.DbException.getJdbcSQLException(DbException.java: > 327) > at org.h2.message.DbException.get(DbException.java:167) > at org.h2.message.DbException.getSyntaxError(DbException.java:192) > at org.h2.command.Parser.getSyntaxError(Parser.java:487) > at org.h2.command.Parser.read(Parser.java:2762) > at org.h2.command.Parser.readTableFilter(Parser.java:1033) > at org.h2.command.Parser.readTableFilter(Parser.java:1026) > at org.h2.command.Parser.readTableFilter(Parser.java:1026) > at org.h2.command.Parser.readTableFilter(Parser.java:1026) > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1669) > at org.h2.command.Parser.parseSelectSimple(Parser.java:1776) > at org.h2.command.Parser.parseSelectSub(Parser.java:1663) > at org.h2.command.Parser.parseSelectUnion(Parser.java:1508) > at org.h2.command.Parser.parseSelect(Parser.java:1496) > at org.h2.command.Parser.parsePrepared(Parser.java:401) > at org.h2.command.Parser.parse(Parser.java:275) > at org.h2.command.Parser.parse(Parser.java:251) > at org.h2.command.Parser.prepare(Parser.java:201) > at org.h2.engine.Session.prepare(Session.java:399) > at org.h2.engine.Session.prepare(Session.java:386) > at org.h2.table.TableView.compileViewQuery(TableView.java:95) > at org.h2.table.TableView.initColumnsAndTables(TableView.java:139) > at org.h2.table.TableView.init(TableView.java:91) > at org.h2.table.TableView.<init>(TableView.java:59) > at org.h2.table.TableView.createTempView(TableView.java:416) > at org.h2.command.Parser.readTableFilter(Parser.java:1022) > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1669) > at org.h2.command.Parser.parseSelectSimple(Parser.java:1776) > at org.h2.command.Parser.parseSelectSub(Parser.java:1663) > at org.h2.command.Parser.parseSelectUnion(Parser.java:1508) > at org.h2.command.Parser.parseSelect(Parser.java:1496) > at org.h2.command.Parser.parsePrepared(Parser.java:401) > at org.h2.command.Parser.parse(Parser.java:275) > at org.h2.command.Parser.parse(Parser.java:247) > at org.h2.command.Parser.prepare(Parser.java:201) > at org.h2.command.Parser.prepareCommand(Parser.java:214) > at org.h2.engine.Session.prepareLocal(Session.java:426) > at org.h2.engine.Session.prepareCommand(Session.java:374) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java: > 1088) > at > org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java: > 71) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java: > 241) > at H2_13Test.main(H2_13Test.java:25) > > > > On 5ζ12ζ₯, εεΎ5:30, Noel Grandin <[email protected]> wrote: >> what error message are you getting? >> >> >> >> >> >> >> >> HK wrote: >>> Hello, >>> I was using the h2 1.2, an update to 1.3. >>> 1.2 worked fine in SQL there. >>> But an error in 1.3. >>> create table TEST_GROUP_CLASS >>> ( >>> GCL_SID1 integer not null, >>> GCL_SID2 integer not null, >>> GCL_SID3 integer not null, >>> GCL_SID4 integer not null, >>> GCL_SID5 integer not null, >>> primary key (GCL_SID1, >>> GCL_SID2, >>> GCL_SID3, >>> GCL_SID4, >>> GCL_SID5 >>> ) >>> ) ; >>> create table TEST_GROUPM >>> ( >>> GRP_SID integer not null, >>> GRP_NAME varchar(20), >>> primary key (GRP_SID) >>> ) ; >>> This error occurs in SQL >>> select count(*) from >>> ( >>> select >>> GC.GCL_SID1 as GCL_SID1, >>> GC.GCL_SID2 as GCL_SID2, >>> GC.GCL_SID3 as GCL_SID3, >>> GC.GCL_SID4 as GCL_SID4, >>> GC.GCL_SID5 as GCL_SID5, >>> GI1.GRP_NAME as GNAME1, >>> GI2.GRP_NAME as GNAME2, >>> GI3.GRP_NAME as GNAME3, >>> GI4.GRP_NAME as GNAME4, >>> GI5.GRP_NAME as GNAME5 >>> from >>> ((((( >>> TEST_GROUP_CLASS GC >>> left join TEST_GROUPM GI1 on GC.GCL_SID1 = GI1.GRP_SID) >>> left join TEST_GROUPM GI2 on GC.GCL_SID2 = GI2.GRP_SID) >>> left join TEST_GROUPM GI3 on GC.GCL_SID3 = GI3.GRP_SID) >>> left join TEST_GROUPM GI4 on GC.GCL_SID4 = GI4.GRP_SID) >>> left join TEST_GROUPM GI5 on GC.GCL_SID5 = GI5.GRP_SID) >>> ) >>> Is this a bug? >>> I'll make simple SQL successfully. >>> select >>> GC.GCL_SID1 as GCL_SID1, >>> GC.GCL_SID2 as GCL_SID2, >>> GC.GCL_SID3 as GCL_SID3, >>> GC.GCL_SID4 as GCL_SID4, >>> GC.GCL_SID5 as GCL_SID5, >>> GI1.GRP_NAME as GNAME1, >>> GI2.GRP_NAME as GNAME2, >>> GI3.GRP_NAME as GNAME3, >>> GI4.GRP_NAME as GNAME4, >>> GI5.GRP_NAME as GNAME5 >>> from >>> ((((( >>> TEST_GROUP_CLASS GC >>> left join TEST_GROUPM GI1 on GC.GCL_SID1 = GI1.GRP_SID) >>> left join TEST_GROUPM GI2 on GC.GCL_SID2 = GI2.GRP_SID) >>> left join TEST_GROUPM GI3 on GC.GCL_SID3 = GI3.GRP_SID) >>> left join TEST_GROUPM GI4 on GC.GCL_SID4 = GI4.GRP_SID) >>> left join TEST_GROUPM GI5 on GC.GCL_SID5 = GI5.GRP_SID) >>> Thanks -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
