Hi Rami, Thomas, I can confirm this as well.
I am thinking of upgrading our 1.2.147 installation to 1.3.155, and confronted with this problem. >From what I see, this is ONLY affecting LEFT JOINs. inner joins is fine. my use case: SELECT A.* FROM tableOne AS A LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID WHERE tableTwo.A_ID Is Null above query fails. as Rami pointed out, SELECT A.* FROM ( tableOne AS A LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID ) WHERE tableTwo.A_ID Is Null works. I know the right thing to do is to use EXCEPT for my use case, but for the amount of query needed to be converted and tested this is not feasible at this time. hope my observation can help. thanks for all the hard work, evan On Jun 10, 7:37 pm, Rami <[email protected]> wrote: > Here is a real life query that fails because > the FROM of the inner select does not have parentheses. > The error message makes clear that only the first table in the failing > FROM clause is used (and the rest are dropped). > I am pretty sure that this query worked before 155 without the parentheses. > Adding the parentheses is no problem but just wanted to let you know about > the incompatibility. > > Hope this helps. > > SELECT > *, > DISPLAY_NAME AS TITLE, > TYPE || ': ' || DISPLAY_NAME AS FULLTITLE > FROM ( > SELECT > PN.PNID, > PN.NAME, > PN.DISPLAY_NAME, > PN.PARENT_ID, > PN.CAPTION, > PN.DESCRIPTION, > PN.NOTES, > PN.IMAGE_ID, > PN.SERIES_ID, > PN.PATH, > PN.PUBLISH_ON_WEB, > PN.PRODUCTION_UNIT_ID, > PN.DEFAULT_RECORDING_TEMPLATE_PNID, > PN.DEFAULT_TRANSLATION_TEMPLATE_PNID, > > SPN.DISPLAY_NAME AS SERIES_NAME, > SPPN.DISPLAY_NAME AS INHERITED_SERIES_NAME, > > PG.DEFAULT_DURATION_5MIN AS DEFINED_DEFAULT_DURATION_5MIN, > PN.DEFAULT_DURATION_5MIN AS DEFAULT_DURATION_5MIN, > PPN.DEFAULT_DURATION_5MIN AS INHERITED_DEFAULT_DURATION_5MIN, > > PG.IS_SERIES, > > C.CATEGORY_ID, > C.NAME AS CATEGORY_NAME, > PC.NAME AS INHERITED_CATEGORY_NAME, > C.COLOR, > PC.COLOR INHERITED_COLOR, > > CASE > WHEN CPG.CHILD_COUNT IS NULL > THEN 0 > ELSE CPG.CHILD_COUNT > END AS CHILD_COUNT, > > CASE > WHEN PG.IS_SERIES THEN 'SARJA' > ELSE 'KANSIO' > END AS TYPE > > FROM > EXODUS_VIEW.PROGRAM_NODES PN > LEFT JOIN EXODUS_VIEW.PROGRAM_NODES SPN ON PN.SERIES_ID = SPN.PNID > LEFT JOIN EXODUS_VIEW.PROGRAM_NODES PPN ON PN.PARENT_ID = PPN.PNID > LEFT JOIN EXODUS_VIEW.PROGRAM_NODES SPPN ON PPN.SERIES_ID = SPPN.PNID > > JOIN EXODUS.PROGRAM_GROUP PG ON PN.PNID = PG.PNID > > LEFT JOIN EXODUS.CATEGORY C ON PN.CATEGORY_ID = C.CATEGORY_ID > LEFT JOIN EXODUS.CATEGORY PC ON PPN.CATEGORY_ID = PC.CATEGORY_ID > > LEFT JOIN > ( > SELECT PARENT_ID, COUNT(PNID) CHILD_COUNT > FROM EXODUS_VIEW.PROGRAM_NODES > GROUP BY PARENT_ID > ) AS CPG > ON PG.PNID = CPG.PARENT_ID > > ); > Column "SPN.DISPLAY_NAME" not found; SQL statement: > CREATE FORCE VIEW PUBLIC._4478 AS > SELECT > PN.PNID, > PN.NAME, > PN.DISPLAY_NAME, > PN.PARENT_ID, > PN.CAPTION, > PN.DESCRIPTION, > PN.NOTES, > PN.IMAGE_ID, > PN.SERIES_ID, > PN.PATH, > PN.PUBLISH_ON_WEB, > PN.PRODUCTION_UNIT_ID, > PN.DEFAULT_RECORDING_TEMPLATE_PNID, > PN.DEFAULT_TRANSLATION_TEMPLATE_PNID, > SPN.DISPLAY_NAME AS SERIES_NAME, > SPPN.DISPLAY_NAME AS INHERITED_SERIES_NAME, > PG.DEFAULT_DURATION_5MIN AS DEFINED_DEFAULT_DURATION_5MIN, > PN.DEFAULT_DURATION_5MIN AS DEFAULT_DURATION_5MIN, > PPN.DEFAULT_DURATION_5MIN AS INHERITED_DEFAULT_DURATION_5MIN, > PG.IS_SERIES, > C.CATEGORY_ID, > C.NAME AS CATEGORY_NAME, > PC.NAME AS INHERITED_CATEGORY_NAME, > C.COLOR, > PC.COLOR AS INHERITED_COLOR, > CASEWHEN((CPG.CHILD_COUNT IS NULL), 0, CPG.CHILD_COUNT) AS CHILD_COUNT, > CASEWHEN(PG.IS_SERIES, 'SARJA', 'KANSIO') AS TYPE > FROM EXODUS_VIEW.PROGRAM_NODES PN [42122-155] > <http://localhost:8082/query.do?jsessionid=53d257f0d764319b73dc85d4990...> > 42S22/42122 > > On 06/09/2011 08:17 PM, Thomas Mueller wrote: > > > > > Hi, > > > That's strange, it should work. Most of the test cases don't use > > parentheses. Could you post a complete example? The one you gave > > works: > > > drop all objects; > > create table a(x int); > > create table b(whatever int, foo int); > > create table c(bar int); > > SELECT * > > FROM > > A > > LEFT JOIN B ON A.X = B.WHATEVER > > JOIN C ON B.FOO = C.BAR > > WHERE 1=0; > > > Regards, > > Thomas -- 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.
