JPOX generates illegal SQL for having clauses using COUNT.

• Key: JDO-244
• Type: Bug
• Status: Resolved
• Resolution: Fixed
• Priority: Major
• Assignee: Erik Bengtson
• Reporter: Michael Watzek
• Component/s: tck2

Description

JPOX generates illegal SQL for the query below. The having clause specifies an aggregate COUNT.

14:22:50,906 (main) DEBUG [org.apache.jdo.tck] - Executing API query: SELECT department, SUM(salary) FROM org.apache.jdo.tck.pc.company.Employee GROUP BY department HAVING COUNT(department.employees) > 0
14:22:51,031 (main) INFO [org.apache.jdo.tck] - Exception during setUp or runtest:
javax.jdo.JDODataStoreException: Error executing JDOQL query "SELECT THIS.DEPARTMENT,SUM(THIS.SALARY) FROM applicationidentity0.PERSONS THIS LEFT OUTER JOIN applicationidentity0.DEPARTMENTS THIS_DEPARTMENT_EMPLOYEES ON THIS.DEPARTMENT = THIS_DEPARTMENT_EMPLOYEES.ID WHERE THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? OR THIS.DISCRIMINATOR = ? GROUP BY THIS.DEPARTMENT HAVING COUNT() > 0" : Syntax error: Encountered ")" at line 1, column 324.
ERROR 42X01: Syntax error: Encountered ")" at line 1, column 324.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
at org.apache.derby.jdbc.Driver30.newEmbedPreparedStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:190)
at org.jpox.store.StatementText.prepareStatement(StatementText.java:199)
at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:678)
at org.jpox.store.query.Query.executeWithMap(Query.java:966)
at org.jpox.store.query.Query.executeWithArray(Query.java:939)
at org.jpox.store.query.Query.execute(Query.java:862)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1151)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1029)
at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:966)
at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:946)
at org.apache.jdo.tck.query.result.Having.testPositive(Having.java:110)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:204)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:120)
at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:95)
.
at org.jpox.store.query.JDOQLQuery.performExecute(JDOQLQuery.java:747)
at org.jpox.store.query.Query.executeWithMap(Query.java:966)
at org.jpox.store.query.Query.executeWithArray(Query.java:939)
at org.jpox.store.query.Query.execute(Query.java:862)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1151)
at org.apache.jdo.tck.query.QueryTest.execute(QueryTest.java:1029)
at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:966)
at org.apache.jdo.tck.query.QueryTest.executeAPIQuery(QueryTest.java:946)
at org.apache.jdo.tck.query.result.Having.testPositive(Having.java:110)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at org.apache.jdo.tck.JDO_Test.runBare(JDO_Test.java:204)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at org.apache.jdo.tck.util.BatchTestRunner.start(BatchTestRunner.java:120)
at org.apache.jdo.tck.util.BatchTestRunner.main(BatchTestRunner.java:95)

Comments

This doesn't appear to be a good query.

"SELECT department, SUM(salary) FROM org.apache.jdo.tck.pc.company.Employee GROUP BY department HAVING COUNT(department.employees) > 0"

You can't COUNT a collection field like employees. Don't you want to do COUNT(department) instead?

[Michael Bouschen]

I agree we should changet the query. The attached patch changes the query to
  SELECT department, SUM(salary) FROM org.apache.jdo.tck.pc.company.Employee
  GROUP BY department HAVING COUNT(department) > 0 ORDER BY department.name ascending
However, the test still fails, because the calculated SUM(salary) is 30000.0 and 45000.0 instead of the expected 45000.0 and 58000.0. I figured out that the projected salary for employees emp3 and emp4 is null which leads to the wrong sum value. Please note, in the database the salary is correct and gets correctly returned when selecting the entire employee instance.

I also change the negative query to
  SELECT department, SUM(salary) FROM org.apache.jdo.tck.pc.company.Employee GROUP BY department HAVING firstname
This should result in an exception, because 'firstname' is not a boolean expression. Currently, JPOX does not catch this error.

[Craig Russell]

I don't think the ordering is needed on the first query. Otherwise, looks good.

[Michael Bouschen]

I checked in the updated patch (revision 358395).

However, the test org.apache.jdo.tck.query.result.Having still fails, because it does not return the expected SUM as described above. I have no idea why a null salary gets returned for two of the employees (which leads to the incorrect SUM value). Ideas?

[Andy Jefferson]

Looked at the SQL being generated ?

[Michael Bouschen]

Good point Andy! Actually the generated SQL is correct.
I know what the problem is: the JDOQL query is not valid. It accesses a field salary that is declared in a subclass of the candidate class. JPOX should catch this, but this is a different problem and I filed a new JIRA issue for this (see JDO-270). The entire inheritance hierarchy is mapped to single table, so the salary column is null for a row that does not represent an instance of the class that declares the salary field.

I changed the query to access a field declared in the candidate class.