*thread bump* Heh, sorry bout that.. I happen to be really liking 1.3.155, everything is faster, more stable..
I tested a long time against 1.3.154 and didn't like the results. spurious lock errors in MULTI_THREADED=1, some indexes gets a bit slower for no apparent reason, some 'between' bugs.. I really really want to put 1.3.155 for production, replacing our current 1.2.147, just this one problem happened to be a show stopper... will be glad if a patch is possible.. Thank you. evan On Jun 14, 10:10 am, Evan <[email protected]> wrote: > Good day, > > Very sorry, I was sure the query failed the other day. > > Well I think I found it: subquery. > > create table tableOne(id int auto_increment primary key, val > char(5)); > create table tableTwo(a_id int not null, val char(5)); > create index on tableTwo(a_id); > insert into tableOne (val) values('1'), ('2'), (null); > insert into tableTwo values(1,'val1'), (2,'val2'), (5,'val3'); > > /*below fails with > Column "TABLETWO.A_ID" not found; SQL statement: > CREATE FORCE VIEW PUBLIC._66 AS > SELECT > A.ID > FROM PUBLIC.TABLEONE A > WHERE TABLETWO.A_ID IS NULL [42122-155] 42S22/42122 > */ > > SELECT * FROM ( > SELECT A.* > FROM tableOne AS A > LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID > WHERE tableTwo.A_ID Is Null > ); > > --below executed ok > SELECT * FROM ( > SELECT A.* > FROM ( > tableOne AS A > LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID > ) > WHERE tableTwo.A_ID Is Null > ); > > drop table tableOne; > drop table tableTwo; > > Going back from work yesterday > thinking why most other queries work.. > at least the ones used in what i've been tested. > > Thankfully I don't use subqueries that much ^^ > > Hope this helps Thomas. > good day. > > On Jun 13, 10:37 pm, Thomas Mueller <[email protected]> > wrote: > > > > > Hi, > > > I can't reproduce the problem with the query you provided. Please post > > a complete, reproducible test case. A reproducible test case > > *includes* all the required "CREATE TABLE" statements and if necessary > > all the data. This includes the problem described by Rami and it > > includes the problem described by Evan. My test case: > > > drop all objects; > > create table tableOne(id int); > > create table tableTwo(a_id int); > > insert into tableOne values(1), (2), (null); > > insert into tableTwo values(1), (3), (null); > > > SELECT A.* FROM tableOne AS A > > LEFT OUTER JOIN tableTwo ON A.ID = tableTwo.A_ID > > WHERE tableTwo.A_ID Is Null; > > > 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.
