Hi,
I'm testing h2-1.3.146.jar beta release, because I'm needing the
nested join feature.
During my tests, I find it's still not working for some of the queries
I'm trying to run. Follows a sample query that triggers the error, and
the error below:
-- tables:
DROP TABLE IF EXISTS PRJ_OBS_UNITS;
CREATE TABLE PRJ_OBS_UNITS
(
ID INTEGER NOT NULL,
TYPE_ID INTEGER NOT NULL,
UNIQUE_NAME VARCHAR(240) NULL,
PARENT_ID INTEGER NULL,
NAME VARCHAR(1440) NULL
);
DROP TABLE IF EXISTS CMN_CAPTIONS_NLS;
CREATE TABLE CMN_CAPTIONS_NLS
(
ID INTEGER NOT NULL,
TABLE_NAME VARCHAR(120) NOT NULL,
PK_ID INTEGER NOT NULL,
LANGUAGE_CODE VARCHAR(120) NOT NULL,
NAME VARCHAR(4000) NOT NULL
);
DROP TABLE IF EXISTS CMN_LOOKUP_TYPES;
CREATE TABLE CMN_LOOKUP_TYPES
(
ID INTEGER NOT NULL,
LOOKUP_TYPE VARCHAR(1020) NOT NULL
);
-- query:
select
units.id
from
prj_obs_units units
left outer join cmn_captions_nls valsObsTipoFase
join cmn_lookup_types ltObsTipoFase
on ltObsTipoFase.id = valsObsTipoFase.pk_id
on valsObsTipoFase.table_name = 'CMN_LOOKUP_TYPES'
left outer join cmn_captions_nls valsObsFase
join cmn_lookup_types ltObsFase
on valsObsFase.pk_id = ltObsFase.id
on valsObsFase.table_name = 'CMN_LOOKUP_TYPES'
where
units.id = 5003736
--- error:
org.h2.jdbc.JdbcSQLException: Column "VALSOBSFASE.PK_ID" not found;
SQL statement:
select units.id
from
prj_obs_units units
left outer join
cmn_captions_nls valsObsTipoFase
join cmn_lookup_types ltObsTipoFase
on ltObsTipoFase.id =
valsObsTipoFase.pk_id
on valsObsTipoFase.table_name =
'CMN_LOOKUP_TYPES'
left outer join cmn_captions_nls
valsObsFase
join cmn_lookup_types ltObsFase
on
valsObsFase.pk_id = ltObsFase.id
on valsObsFase.table_name =
'CMN_LOOKUP_TYPES' where
units.id = 5003736 [42122-146]
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:
126)
at org.h2.expression.Comparison.optimize(Comparison.java:154)
at org.h2.command.dml.Select.setEvaluatableRecursive(Select.java:915)
at org.h2.command.dml.Select.preparePlan(Select.java:894)
at org.h2.command.dml.Select.prepare(Select.java:797)
at org.h2.command.Parser.prepare(Parser.java:201)
at org.h2.command.Parser.prepareCommand(Parser.java:213)
at org.h2.engine.Session.prepareLocal(Session.java:423)
at org.h2.engine.Session.prepareCommand(Session.java:373)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1056)
at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:71)
at br.com.r4j.commons.sql.SQLProcessor.processQuery(SQLProcessor.java:
178)
at
br.com.agila.petr.wf.AprovacaoEngine.calculaAprovadores(AprovacaoEngine.java:
336)
at
br.com.agila.petr.wf.TestAprovacaoEngine.doTest(TestAprovacaoEngine.java:
326)
....
That's it. If you need some more info, I can provide.
I'm trying to peek at the code to see if I can fix myself, but I
wasn't able to work it out so far...
Should I post it as a bug even if it's in beta release? Maybe someone
is still working on that.
thanks!
Roberto Barra
--
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.
