search

lbehnke / h2database

Automatically exported from code.google.com/p/h2database
0 stars 0 forks source link

Reference to outer table in doubly nested IN clause won't compile #67

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Try running 

select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1)); 

What is the expected output? 
A single row, single column result set.

What do you see instead?
Column A.X not found; SQL statement:
select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1)) [42122-107] 42S22/42122 (Help)
org.h2.jdbc.JdbcSQLException: Column A.X not found; SQL statement:
select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1)) [42122-107]
    at org.h2.message.Message.getSQLException(Message.java:103)
    at org.h2.message.Message.getSQLException(Message.java:114)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:130)
    at org.h2.expression.Comparison.optimize(Comparison.java:133)
    at org.h2.command.dml.Select.prepare(Select.java:729)
    at org.h2.command.Parser.prepare(Parser.java:203)
    at org.h2.engine.Session.prepare(Session.java:391)
    at org.h2.engine.Session.prepare(Session.java:378)
    at org.h2.table.TableView.recompileQuery(TableView.java:65)
    at org.h2.table.TableView.initColumnsAndTables(TableView.java:78)
    at org.h2.table.TableView.<init>(TableView.java:55)
    at org.h2.table.TableView.createTempView(TableView.java:350)
    at
org.h2.expression.ConditionInSelect.optimizeInJoin(ConditionInSelect.java:143)
    at org.h2.command.dml.Select.prepare(Select.java:731)
    at org.h2.expression.ConditionInSelect.optimize(ConditionInSelect.java:92)
    at org.h2.command.dml.Select.prepare(Select.java:729)
    at org.h2.command.Parser.prepareCommand(Parser.java:233)
    at org.h2.engine.Session.prepareLocal(Session.java:406)
    at org.h2.engine.Session.prepareCommand(Session.java:367)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1048)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
    at org.h2.server.web.WebThread.getResult(WebThread.java:1723)
    at org.h2.server.web.WebThread.query(WebThread.java:1286)
    at org.h2.server.web.WebThread.process(WebThread.java:444)
    at org.h2.server.web.WebThread.processRequest(WebThread.java:186)
    at org.h2.server.web.WebThread.process(WebThread.java:241)
    at org.h2.server.web.WebThread.run(WebThread.java:196) 

What version of the product are you using? On what operating system, file
system, and virtual machine?
H2 1.1.107.  Windows XP Professional x64 Edition, Version 2003, NTFS file
system, Java HotSpot(TM) 64-Bit Server VM 1.6.0_10-b33.

Do you know a workaround?
Disable the optimization for IN(SELECT...) queries. To do that, set the
system property h2.optimizeInJoin to false (or using version 1.0.79).

How important/urgent is the problem for you?
We'd like to move to the newer version of H2 for its improved performance.

In your view, is this a defect or a feature request?
Defect.

Please provide any additional information below.

Original issue reported on code.google.com by wesona...@gmail.com on 25 Feb 2009 at 5:46

GoogleCodeExporter commented 9 years ago

Original comment by thomas.t...@gmail.com on 27 Feb 2009 at 5:41

GoogleCodeExporter commented 9 years ago 
Should work with release 1.1.108.
Please re-open if not.

Original comment by thomas.t...@gmail.com on 1 Mar 2009 at 10:07

GoogleCodeExporter commented 9 years ago 
This version does not appear to solve our problem.  Running 

 SELECT COUNT(*)
   FROM
  (SELECT cp.ID
     FROM cc_checkportion cp
    WHERE cp.Retired             =0
  AND cp.FixedTransactionAmount IS NOT NULL
  AND cp.FixedTransactionAmount <>cp.FixedClaimAmount
  AND (SELECT MIN(cl.Currency)
       FROM cc_claim cl,
      cc_check ch
      WHERE ch.PortionID=cp.ID
    AND ch.ClaimID      =cl.ID) IN
    (SELECT t.Currency
       FROM cc_transaction t,
      cc_check c            ,
      cc_checkgroup cg
      WHERE t.CheckID=c.ID
    AND c.GroupID    =cg.ID
    AND cg.ID       IN
      (SELECT c1.GroupID FROM cc_check c1 WHERE c1.PortionID=cp.ID
      )
    )
  ) a

yields

Column CP.ID not found; SQL statement:
SELECT COUNT(*)
   FROM
  (SELECT cp.ID
     FROM cc_checkportion cp
    WHERE cp.Retired             =0
  AND cp.FixedTransactionAmount IS NOT NULL
  AND cp.FixedTransactionAmount <>cp.FixedClaimAmount
  AND (SELECT MIN(cl.Currency)
       FROM cc_claim cl,
      cc_check ch
      WHERE ch.PortionID=cp.ID
    AND ch.ClaimID      =cl.ID) IN
    (SELECT t.Currency
       FROM cc_transaction t,
      cc_check c            ,
      cc_checkgroup cg
      WHERE t.CheckID=c.ID
    AND c.GroupID    =cg.ID
    AND cg.ID       IN
      (SELECT c1.GroupID FROM cc_check c1 WHERE c1.PortionID=cp.ID
      )
    )
  ) a [42122-108] 42S22/42122 (Help)

Original comment by wesona...@gmail.com on 13 Mar 2009 at 9:08

GoogleCodeExporter commented 9 years ago 
The test query also still fails:

select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1));
Column A.X not found; SQL statement:
select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1)) [42122-108] 42S22/42122 (Help)
org.h2.jdbc.JdbcSQLException: Column A.X not found; SQL statement:
select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1)) [42122-108]
    at org.h2.message.Message.getSQLException(Message.java:107)
    at org.h2.message.Message.getSQLException(Message.java:118)
    at org.h2.message.Message.getSQLException(Message.java:77)
    at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:130)
    at org.h2.expression.Comparison.optimize(Comparison.java:133)
    at org.h2.command.dml.Select.prepare(Select.java:729)
    at org.h2.command.Parser.prepare(Parser.java:203)
    at org.h2.engine.Session.prepare(Session.java:393)
    at org.h2.engine.Session.prepare(Session.java:380)
    at org.h2.table.TableView.recompileQuery(TableView.java:65)
    at org.h2.table.TableView.initColumnsAndTables(TableView.java:78)
    at org.h2.table.TableView.<init>(TableView.java:55)
    at org.h2.table.TableView.createTempView(TableView.java:350)
    at org.h2.expression.ConditionInSelect.optimizeInJoin(ConditionInSelect.java:143)
    at org.h2.command.dml.Select.prepare(Select.java:731)
    at org.h2.expression.ConditionInSelect.optimize(ConditionInSelect.java:92)
    at org.h2.command.dml.Select.prepare(Select.java:729)
    at org.h2.command.Parser.prepareCommand(Parser.java:233)
    at org.h2.engine.Session.prepareLocal(Session.java:408)
    at org.h2.engine.Session.prepareCommand(Session.java:369)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1045)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:153)
    at org.h2.server.web.WebThread.getResult(WebThread.java:1723)
    at org.h2.server.web.WebThread.query(WebThread.java:1286)
    at org.h2.server.web.WebThread.process(WebThread.java:444)
    at org.h2.server.web.WebThread.processRequest(WebThread.java:186)
    at org.h2.server.web.WebThread.process(WebThread.java:241)
    at org.h2.server.web.WebThread.run(WebThread.java:196)

Original comment by wesona...@gmail.com on 13 Mar 2009 at 9:09

GoogleCodeExporter commented 9 years ago 
select * from settings:

SELECT * FROM INFORMATION_SCHEMA.SETTINGS;
NAME    VALUE  
CREATE_BUILD    108
CLUSTER ''
LOCK_MODE   3
DEFAULT_TABLE_TYPE  0
DB_CLOSE_DELAY  -1
WRITE_DELAY 500
DEFAULT_LOCK_TIMEOUT    2000
CACHE_SIZE  131072
info.BUILD_ID   108
info.VERSION_MAJOR  1
info.VERSION_MINOR  1
info.VERSION    1.1.108 (2009-02-28)
property.java.runtime.version   1.6.0_10-b33
property.java.vm.name   Java HotSpot(TM) 64-Bit Server VM
property.java.vendor    Sun Microsystems Inc.
property.os.name    Windows 2003
property.os.arch    amd64
property.os.version 5.2
property.sun.os.patch.level Service Pack 2
property.file.separator \
property.path.separator ;
property.line.separator 
property.user.country   US
property.user.language  en
property.user.variant    
property.file.encoding  Cp1252
EXCLUSIVE   FALSE
MODE    REGULAR
MULTI_THREADED  0
MVCC    FALSE
QUERY_TIMEOUT   0
LOB_FILES_IN_DIRECTORIES    true
h2.allowBigDecimalExtensions    false
h2.baseDir  
=
null
h2.check    true
h2.check2   false
h2.clientTraceDirectory trace.db/
h2.collatorCacheSize    32000
h2.defaultMaxMemoryUndo 50000
h2.lobFilesInDirectories    true
h2.lobFilesPerDirectory 256
h2.logAllErrors false
h2.logAllErrorsFile h2errors.txt
h2.maxFileRetry 16
h2.maxQueryTimeout  0
h2.lobCloseBetweenReads false
h2.objectCache  true
h2.objectCacheSize  1024
h2.objectCacheMaxPerElementSize 4096
h2.optimizeIn   true
h2.optimizeInJoin   true
h2.optimizeMinMax   true
h2.optimizeSubqueryCache    true
h2.overflowExceptions   true
h2.recompileAlways  false
h2.redoBufferSize   262144
h2.runFinalize  true
h2.scriptDirectory   
h2.serverCachedObjects  64
h2.serverResultSetFetchSize 100
h2.sortNullsHigh    false
CACHE_TYPE  LRU
info.FILE_DISK_WRITE    0
info.FILE_DISK_READ 6200
info.FILE_INDEX_WRITE   48
info.FILE_INDEX_READ    3360
info.CACHE_DATA_MAX_SIZE    33554432
info.CACHE_DATA_SIZE    674288
info.CACHE_INDEX_MAX_SIZE   4194304
info.CACHE_INDEX_SIZE   121344
(70 rows, 16 ms)

Original comment by wesona...@gmail.com on 13 Mar 2009 at 9:10

GoogleCodeExporter commented 9 years ago 
Version 1.1.109 does not yet fix this problem completely.  The small test query 
now
works ("select 1 from dual a where 1 in(select 1 from dual b where 1 in(select
1 from dual c where a.x=1))"), but this one does not:

SELECT cp.ID
     FROM cc_checkportion cp
    WHERE cp.Retired             =0
  AND cp.FixedTransactionAmount IS NOT NULL
  AND cp.FixedTransactionAmount <>cp.FixedClaimAmount
  AND (SELECT MIN(cl.Currency)
       FROM cc_claim cl,
      cc_check ch
      WHERE ch.PortionID=cp.ID
    AND ch.ClaimID      =cl.ID) IN
    (SELECT t.Currency
       FROM cc_transaction t,
      cc_check c            ,
      cc_checkgroup cg
      WHERE t.CheckID=c.ID
    AND c.GroupID    =cg.ID
    AND cg.ID       IN
      (SELECT c1.GroupID FROM cc_check c1 WHERE c1.PortionID=cp.ID
      )
    )

Database in zip format attached.

I don't know if you see comments on closed issues, so I'll put a message in the 
forum.

Original comment by wesona...@gmail.com on 16 Mar 2009 at 10:30

Attachments:

GoogleCodeExporter commented 9 years ago 
Hi,

> if you see comments on closed issues

Yes I do.

> this one does not [work]

What do you mean with does not work? Do you get an exception, if yes which one, 
or is
the result not what you expect, in which case I need the data as well (the data 
you
sent seems to be empty).

Original comment by thomas.t...@gmail.com on 17 Mar 2009 at 3:10

GoogleCodeExporter commented 9 years ago 
Unzip database to a directory, say c:\temp.  Open the H2 command console with 
no id
or password.  Issue the query.  Receive:
Column CP.ID not found; SQL statement:
SELECT cp.ID
     FROM cc_checkportion cp
    WHERE cp.Retired             =0
  AND cp.FixedTransactionAmount IS NOT NULL
  AND cp.FixedTransactionAmount <>cp.FixedClaimAmount
  AND (SELECT MIN(cl.Currency)
       FROM cc_claim cl,
      cc_check ch
      WHERE ch.PortionID=cp.ID
    AND ch.ClaimID      =cl.ID) IN
    (SELECT t.Currency
       FROM cc_transaction t,
      cc_check c            ,
      cc_checkgroup cg
      WHERE t.CheckID=c.ID
    AND c.GroupID    =cg.ID
    AND cg.ID       IN
      (SELECT c1.GroupID FROM cc_check c1 WHERE c1.PortionID=cp.ID
      )
    ) [42122-109] 42S22/42122 (Help)

Original comment by wesona...@gmail.com on 17 Mar 2009 at 6:34

GoogleCodeExporter commented 9 years ago 
Hi,

Thanks for your help! It's weird, it works on one of my computers but not on the
other... I will investigate.

Original comment by thomas.t...@gmail.com on 17 Mar 2009 at 7:44

GoogleCodeExporter commented 9 years ago 
Hi,

Now I understand why it worked on one of my computers: I disabled the 
optimization
for IN(SELECT ..) on that machine...

I found the problem now, it will be fixed in the next release. If you want to 
try it
out before the release, you could check out the current source code and compile 
H2
yourself - the correction is committed. I also (finally) added a test case.

Thanks a lot for your help! And sorry that I didn't fix it before.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 18 Mar 2009 at 7:39

GoogleCodeExporter commented 9 years ago 
I'll check it out with the next release.  Thanks, Thomas.

Original comment by wesona...@gmail.com on 18 Mar 2009 at 10:03

GoogleCodeExporter commented 9 years ago 
This should be fixed with todays release (1.1.110).

Original comment by thomas.t...@gmail.com on 3 Apr 2009 at 3:58

GoogleCodeExporter commented 9 years ago 
It fixed the query that broke before, and I've put this version into our unit 
test
system so all of tests will run on it.  I'll report if anything else comes up. 
Thanks, Thomas.

Original comment by wesona...@gmail.com on 3 Apr 2009 at 4:52