Menu

Error when selecting from a joined table

Help
2014-10-27
2014-10-28
  • 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

    Marco Amadei - 2014-10-27

    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

    Mark Strein - 2014-10-28

    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

    Mark Strein - 2014-10-28

    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

    Marco Amadei - 2014-10-28

    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
  • 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

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.