Table aliases not used in HSQLDB OQL-SQL generator class

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Duplicate
Affects Version/s: 0.9.5
Fix Version/s: 0.9.9 M1
Component/s: JDO
Labels:
None
Environment:
Operating System: All
Platform: PC

Bugzilla Id:
1379
Number of attachments :
0

Description

The OQL-SQL mapping generator for HSQLDB generates illegal SQL statements which
refer to aliases that are not declared. This takes place particularly when
table joins are performed.

I've tracked the root of the problem being method addTables in
HsqlQueryExpression class:

private void addTables(StringBuffer buffer, Hashtable tables, boolean first).

This method adds table names to the SQL statement being generated. Although,
unlike its JDBC corresponding code, it never makes use of aliases in the
provided HashMap:

Enumeration enum = tables.elements();
while ( enum.hasMoreElements() )

{ if ( first ) first = false; else buffer.append( JDBCSyntax.TableSeparator ); buffer.append( _factory.quoteName( (String) enum.nextElement() ) ); }

}

I do not know the internals of SQL generation in Castor well enough to
contribute a full patch+test case bundle. Although I have locally replaced the
above method with the related code from JDBC SQL generation class and it passed
all of my project's test cases (these test cases have been designed to work
with MySQL and HSQLDB). Therefore I believe this patch should solve the bug. I
would suggest the person knowledgeable in HSQLDB generation engine takes a look
at the code before it is incorporated in the CVS, however.

Regards,
Dawid Weiss

Issue Links

relates to

CASTOR-1110 Refactor CTF-JDO tests into subdirectories (part 2)

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Dawid Weiss added a comment - 18/Aug/03 1:33 PM

Created an attachment (id=313)
A proposed patch to HsqlQueryExpression class, hopefully solving the problem of incorrect table aliases.

Show

Dawid Weiss added a comment - 18/Aug/03 1:33 PM Created an attachment (id=313) A proposed patch to HsqlQueryExpression class, hopefully solving the problem of incorrect table aliases.

Hide

Permalink

Martin Fuchs added a comment - 30/Oct/04 7:35 AM

Created an attachment (id=753)
updated patch

Show

Martin Fuchs added a comment - 30/Oct/04 7:35 AM Created an attachment (id=753) updated patch

Hide

Permalink

Martin Fuchs added a comment - 30/Oct/04 7:38 AM

Created an attachment (id=754)
Sorry, please use this smaller patch.

Show

Martin Fuchs added a comment - 30/Oct/04 7:38 AM Created an attachment (id=754) Sorry, please use this smaller patch.

Hide

Permalink

Martin Fuchs added a comment - 30/Oct/04 7:43 AM

Dawid, if you are still around, could you please post one of your test cases?
I could not figure out exactly what mapping/type of OQL queries you used.

The updated patch above includes not only the original patch, but also an
additional fix for the addOuterJoin() function in HsqlQueryExpression. This is
needed to generate correct SQL if one table is used more than once for depended
objects in the JDO mapping.

Show

Martin Fuchs added a comment - 30/Oct/04 7:43 AM Dawid, if you are still around, could you please post one of your test cases? I could not figure out exactly what mapping/type of OQL queries you used. The updated patch above includes not only the original patch, but also an additional fix for the addOuterJoin() function in HsqlQueryExpression. This is needed to generate correct SQL if one table is used more than once for depended objects in the JDO mapping.

Hide

Permalink

Werner Guttmann added a comment - 28/Nov/04 11:23 AM

Martin, as David does not seem to be replying any more .. 8-(, can you please
get your head around a complete test case, as I'd love to get this in for 0.9.6,
if still possible. It does not need to be part of the CTF suite yet, i.e. a
plain JUnit test case would do for the time being.

Show

Werner Guttmann added a comment - 28/Nov/04 11:23 AM Martin, as David does not seem to be replying any more .. 8-(, can you please get your head around a complete test case, as I'd love to get this in for 0.9.6, if still possible. It does not need to be part of the CTF suite yet, i.e. a plain JUnit test case would do for the time being.

Hide

Permalink

Martin Fuchs added a comment - 28/Nov/04 11:39 AM

IIRC the problem was, I could not reproduce the problem, Dawid reported,
because I didn't know, what type of query he used. I could only find another
problem, which resulted in the "additional fix for the addOuterJoin() function"
I describes above. I will try to find my test case I used for this...

Show

Martin Fuchs added a comment - 28/Nov/04 11:39 AM IIRC the problem was, I could not reproduce the problem, Dawid reported, because I didn't know, what type of query he used. I could only find another problem, which resulted in the "additional fix for the addOuterJoin() function" I describes above. I will try to find my test case I used for this...

Hide

Permalink

Martin Fuchs added a comment - 28/Nov/04 2:19 PM

The following test case uses this data table:

CREATE TABLE A(NR_A INTEGER);

INSERT INTO A VALUES(1);
INSERT INTO A VALUES(2);
INSERT INTO A VALUES(3);
INSERT INTO A VALUES(4);
INSERT INTO A VALUES(5);
INSERT INTO A VALUES(6);
INSERT INTO A VALUES(7);
INSERT INTO A VALUES(8);

COMMIT;

When running the test with the current Castor version, this exception is thrown:

org.exolab.castor.jdo.PersistenceException: Nested error:
java.sql.SQLException: Table not found: A_F0 in statement [SELECT
A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] while
executing SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON
(A.NR_A=A_f0.NR_A): Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A
FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)]
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute
(SQLEngine.java:1788)
at org.exolab.castor.persist.TransactionContext.query
(TransactionContext.java:742)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:474)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:426)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:418)
at test1379.Test1379.run(Test1379.java:66)
at test1379.Test1379.main(Test1379.java:22)
Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT
A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute
(SQLEngine.java:1762)
... 6 more
Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT
A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute
(SQLEngine.java:1762)
at org.exolab.castor.persist.TransactionContext.query
(TransactionContext.java:742)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:474)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:426)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:418)
at test1379.Test1379.run(Test1379.java:66)
at test1379.Test1379.main(Test1379.java:22)
Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT
A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)]
at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute
(SQLEngine.java:1762)
at org.exolab.castor.persist.TransactionContext.query
(TransactionContext.java:742)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:474)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:426)
at org.exolab.castor.jdo.engine.OQLQueryImpl.execute
(OQLQueryImpl.java:418)
at test1379.Test1379.run(Test1379.java:66)
at test1379.Test1379.main(Test1379.java:22)

After applying the patch, the test runs successfull and prints:

A.NR_A=1
A.NR_A=2
A.NR_A=3
A.NR_A=4
A.NR_A=5
A.NR_A=6
A.NR_A=7
A.NR_A=8

Please don't look too tight into the test - it is of no practical use and uses
a 1:n mapping from A to A.

Show

Martin Fuchs added a comment - 28/Nov/04 2:19 PM The following test case uses this data table: CREATE TABLE A(NR_A INTEGER); INSERT INTO A VALUES(1); INSERT INTO A VALUES(2); INSERT INTO A VALUES(3); INSERT INTO A VALUES(4); INSERT INTO A VALUES(5); INSERT INTO A VALUES(6); INSERT INTO A VALUES(7); INSERT INTO A VALUES(8); COMMIT; When running the test with the current Castor version, this exception is thrown: org.exolab.castor.jdo.PersistenceException: Nested error: java.sql.SQLException: Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] while executing SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A): Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute (SQLEngine.java:1788) at org.exolab.castor.persist.TransactionContext.query (TransactionContext.java:742) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:474) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:426) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:418) at test1379.Test1379.run(Test1379.java:66) at test1379.Test1379.main(Test1379.java:22) Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute (SQLEngine.java:1762) ... 6 more Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute (SQLEngine.java:1762) at org.exolab.castor.persist.TransactionContext.query (TransactionContext.java:742) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:474) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:426) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:418) at test1379.Test1379.run(Test1379.java:66) at test1379.Test1379.main(Test1379.java:22) Caused by: java.sql.SQLException: Table not found: A_F0 in statement [SELECT A.NR_A,A_f0.NR_A FROM A LEFT OUTER JOIN A_f0 ON (A.NR_A=A_f0.NR_A)] at org.hsqldb.jdbc.jdbcUtil.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.exolab.castor.jdo.engine.SQLEngine$SQLQuery.execute (SQLEngine.java:1762) at org.exolab.castor.persist.TransactionContext.query (TransactionContext.java:742) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:474) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:426) at org.exolab.castor.jdo.engine.OQLQueryImpl.execute (OQLQueryImpl.java:418) at test1379.Test1379.run(Test1379.java:66) at test1379.Test1379.main(Test1379.java:22) After applying the patch, the test runs successfull and prints: A.NR_A=1 A.NR_A=2 A.NR_A=3 A.NR_A=4 A.NR_A=5 A.NR_A=6 A.NR_A=7 A.NR_A=8 Please don't look too tight into the test - it is of no practical use and uses a 1:n mapping from A to A.

Hide

Permalink

Martin Fuchs added a comment - 28/Nov/04 2:22 PM

Created an attachment (id=792)
Test case files

ZIP archiv containing the test files:
A.java
Test1379.java
hsql-conf.xml
mapping.xml

Show

Martin Fuchs added a comment - 28/Nov/04 2:22 PM Created an attachment (id=792) Test case files ZIP archiv containing the test files: A.java Test1379.java hsql-conf.xml mapping.xml

Hide

Permalink

Werner Guttmann added a comment - 29/Nov/04 2:48 AM

Martin, can you please provide me with instructions on how to install and
configure HSQL to be used with Castor?

Show

Werner Guttmann added a comment - 29/Nov/04 2:48 AM Martin, can you please provide me with instructions on how to install and configure HSQL to be used with Castor?

Hide

Permalink

Martin Fuchs added a comment - 29/Nov/04 1:22 PM

OK, I just downloaded the latest archive from
http://sourceforge.net/project/showfiles.php?group_id=23316&package_id=16653&release_id=254279
am will now go through all the necessary steps:

unzip the archive somewhere in an empty directory

launch the demo database by executing the following commands in the
installation directory
(this example is for Windows, adjusting this for Unix/Linux should not be too
difficult.)

mkdir data
cd demo
runserver

open another console window to launch the db manager application:

cd demo
runManager

(I am using a bit adjusted start scripts calling javaw instead of java and
"start" (Windows) / "&" (Unix)
to hide the console window, but the default script/batch file is fine also.)

Now choose the database type "HSQL Database Engine Server" in the connection
dialog.
Click "OK"

Paste the schema script into the edit window and click "Execute".

Now you can work with the database manager and issue any SQL queries you want.

To connect to the running database just register the JDBC driver
"lib/hsqldb.jar" into your classpath,
and configure Castor to use it for example with this config file:

<?xml version="1.0" ?>
<jdo-conf>
<database name="db" engine="hsql" >
<driver url="jdbc:hsqldb:hsql://localhost/" class-name="org.hsqldb.jdbcDriver"/>
<mapping href="mapping.xml" />
</database>
<transaction-demarcation mode="local"/>
</jdo-conf>

You can stop the database at any time just by pressing CTRL+C in the first
console window.

Show

Martin Fuchs added a comment - 29/Nov/04 1:22 PM OK, I just downloaded the latest archive from http://sourceforge.net/project/showfiles.php?group_id=23316&package_id=16653&release_id=254279 am will now go through all the necessary steps: unzip the archive somewhere in an empty directory launch the demo database by executing the following commands in the installation directory (this example is for Windows, adjusting this for Unix/Linux should not be too difficult.) mkdir data cd demo runserver open another console window to launch the db manager application: cd demo runManager (I am using a bit adjusted start scripts calling javaw instead of java and "start" (Windows) / "&" (Unix) to hide the console window, but the default script/batch file is fine also.) Now choose the database type "HSQL Database Engine Server" in the connection dialog. Click "OK" Paste the schema script into the edit window and click "Execute". Now you can work with the database manager and issue any SQL queries you want. To connect to the running database just register the JDBC driver "lib/hsqldb.jar" into your classpath, and configure Castor to use it for example with this config file: <?xml version="1.0" ?> <jdo-conf> <database name="db" engine="hsql" > <driver url="jdbc:hsqldb:hsql://localhost/" class-name="org.hsqldb.jdbcDriver"/> <mapping href="mapping.xml" /> </database> <transaction-demarcation mode="local"/> </jdo-conf> You can stop the database at any time just by pressing CTRL+C in the first console window.

Hide

Permalink

Werner Guttmann added a comment - 04/Dec/04 11:40 AM

Thanks, Martin, for the instructions. Very much appreciated ... . I have just
been able to run the CTF test suite against hsql, and everything is fine. Though
it surprised me a bit to see that only one test is (specified and) executed
against hsql.

Show

Werner Guttmann added a comment - 04/Dec/04 11:40 AM Thanks, Martin, for the instructions. Very much appreciated ... . I have just been able to run the CTF test suite against hsql, and everything is fine. Though it surprised me a bit to see that only one test is (specified and) executed against hsql.

Hide

Permalink

Martin Fuchs added a comment - 04/Dec/04 12:03 PM

Well, currently there is only TC62 active for HSQL. It was the first I inserted
for this database while working on the LIMIT/OFFSET issue.
If you look into "hsql.sql" you will notice, there are some sections commented
out, mostly because not all data types or supported by HSQL. So not all test
cases will run on this database. Someone should spend some time, and try to
include waht's possible, and what not.

Show

Martin Fuchs added a comment - 04/Dec/04 12:03 PM Well, currently there is only TC62 active for HSQL. It was the first I inserted for this database while working on the LIMIT/OFFSET issue. If you look into "hsql.sql" you will notice, there are some sections commented out, mostly because not all data types or supported by HSQL. So not all test cases will run on this database. Someone should spend some time, and try to include waht's possible, and what not.

Hide

Permalink

Nick Stuart added a comment - 20/Jan/05 11:11 AM

Created an attachment (id=866)
Quick patch fix

I was having problems applying this patch, it wouldn't work on my source tree
for some reason. So I manually updated the files and create a new patch from a
fresh CVS update.

Show

Nick Stuart added a comment - 20/Jan/05 11:11 AM Created an attachment (id=866) Quick patch fix I was having problems applying this patch, it wouldn't work on my source tree for some reason. So I manually updated the files and create a new patch from a fresh CVS update.

Hide

Permalink

Werner Guttmann added a comment - 21/Jan/05 1:57 AM

Thanks, Nick. Regarding comment 12 from Martin, are you in a position to help us
out with getting as many tests included as possible in src/tests/tests.xml for
HSQL ?

Show

Werner Guttmann added a comment - 21/Jan/05 1:57 AM Thanks, Nick. Regarding comment 12 from Martin, are you in a position to help us out with getting as many tests included as possible in src/tests/tests.xml for HSQL ?

Hide

Permalink

Werner Guttmann added a comment - 17/Feb/05 2:29 PM

Nick, could you please update the patch once more, as there's some 'noise' at
its top ? And I'd really appreciate a reply to my last question, be it yes or no.

Show

Werner Guttmann added a comment - 17/Feb/05 2:29 PM Nick, could you please update the patch once more, as there's some 'noise' at its top ? And I'd really appreciate a reply to my last question, be it yes or no.

Hide

Permalink

Ralf Joachim added a comment - 23/Aug/05 3:13 PM

I resolved that problem while working on ~~CASTOR-1110~~.

Show

Ralf Joachim added a comment - 23/Aug/05 3:13 PM I resolved that problem while working on CASTOR-1110 .

People

Assignee:

Werner Guttmann

Reporter:

Dawid Weiss

Vote (0)

Watch (0)

Dates

Created:

18/Aug/03 1:31 PM

Updated:

28/Aug/05 4:50 PM

Resolved:

23/Aug/05 3:14 PM