Error when selecting from a joined table

[Robert Horton II]

When performing the following query in the northwind database:
SELECT DISTINCT a.[Order ID], a.[Customer ID], a.[order date], b.[Order ID], b.[Product ID], b.quantity FROM ([Orders] a LEFT JOIN [Order Details] b On a.[Order ID]=b.[Order ID])
I get the following stack trace:
SQL Issue: Failed to select from table user lacks privilege or object not found: ORDER ID
SELECT DISTINCT a.[Order ID], a.[Customer ID], a.[order date], b.[Order ID], b.[Product ID], b.quantity FROM ([Orders] a LEFT JOIN [Order Details] b On a.[Order ID]=b.[Order ID])
net.ucanaccess.jdbc.UcanaccessSQLException: user lacks privilege or object not found: ORDER ID
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:200)
at lib.TRACJavaLibrary.io.Query.execute(Query.java:52)
at lib.TRACJavaLibrary.io.Query.execute(Query.java:36)
at src.application.Test.process(Test.java:38)
at src.application.Test.main(Test.java:44)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ORDER ID
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:197)
... 4 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ORDER ID
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Table.getColumnIndex(Unknown Source)
at org.hsqldb.Table.getColumnIndexes(Unknown Source)
at org.hsqldb.QuerySpecification.setDistinctConditions(Unknown Source)
at org.hsqldb.QuerySpecification.resolveTypesPartTwo(Unknown Source)
at org.hsqldb.QueryExpression.resolve(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more

[Robert Horton II]

The error appears to happen when distinctly selecting keys or anything in the join criteria.

[Marco Amadei]

It's because you're using access label instead of column names.
This is the correct query:
SELECT DISTINCT a.[OrderID], a.[CustomerID], a.[orderdate], b.[OrderID], b.[ProductID], b.quantity FROM [Orders] a LEFT JOIN [Order Details] b On a.[OrderID]=b.[OrderID];

[Mark Strein]

Marco,
The query Mr. Horton provided is correct. It would seem that the underscores in his paste of the trace were somehow removed. So anything in his provided query with the construct "Order ID" should be read as "Order_ID".

Also this error occurs when using inner joins. If we replace the inner join with a where clause the query functions as expected.

Mark....

[Mark Strein]

Marco,
I would also like to add, that the query provided by Mr. Horton works correctly when run against an SQL server database.

Mark....

[Marco Amadei]

Hi Mark,
if you executed the query I reported above you would see it working fine as well.
You're right, there is an issue with the hsqldb engine:
I've noticed it depends on the use of parenthesis around the inner/left join with the DISTINCT clause. In the specific case of Robert those parenthesis weren't needed.
In general is a minor issue because you have always a chance to rewrite a working and efficient query despite it.
I hope it's handled in the next hsqldb versions I will embed with UcanAccess.
Cheers Marco
P.S. Whether you agree with me or not, any feedback is welcome.

[Robert Horton II]

Marco,

The query is dynamically generated by the software we are using. We can't just rewrite it. Hopefully the developers of hsqldb will correct the issue and we can update to that. Thank you for your prompt consideration of the issue.

Robert