Left joins don't work when used as subquery in form clause

[GoogleCodeExporter]

H2 1.3.155

SQL to reproduce:

create table a (id int primary key, col1 varchar(255));
create table b (id int primary key, col2 varchar(255));
select count(*) from (select a.col1, b.col2 from a left join b on a.id=b.id);

last select goes to:

Column "B.COL2" not found; SQL statement:
CREATE FORCE VIEW PUBLIC._0 AS
SELECT
    A.COL1,
    B.COL2
FROM PUBLIC.A [42122-155]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:134)
    at org.h2.command.dml.Select.prepare(Select.java:794)
    at org.h2.command.Parser.prepare(Parser.java:202)
    at org.h2.engine.Session.prepare(Session.java:400)
    at org.h2.engine.Session.prepare(Session.java:387)
    at org.h2.table.TableView.compileViewQuery(TableView.java:95)
    at org.h2.table.TableView.initColumnsAndTables(TableView.java:139) 

I also tried this with 1.3.154 and it works fine. If I change "left join" to 
"inner join" it works as well.

Original issue reported on code.google.com by ymak...@gmail.com on 28 May 2011 at 5:08

[GoogleCodeExporter]

I don't understand why this problem was not detected so far... It will be fixed 
in the next release. Thanks a lot for your help!

Original comment by thomas.t...@gmail.com on 2 Jun 2011 at 3:10

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.3.157

Original comment by thomas.t...@gmail.com on 25 Jun 2011 at 1:10

• Changed state: Fixed