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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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];
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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....
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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.
Last edit: Marco Amadei 2014-10-28
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
The error appears to happen when distinctly selecting keys or anything in the join criteria.
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];
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....
Marco,
I would also like to add, that the query provided by Mr. Horton works correctly when run against an SQL server database.
Mark....
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.
Last edit: Marco Amadei 2014-10-28
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