db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Bouschen (JIRA)" <j...@apache.org>
Subject [jira] Updated: (JDO-244) JPOX generates illegal SQL for having clauses using COUNT.
Date Sun, 18 Dec 2005 21:24:36 GMT
     [ http://issues.apache.org/jira/browse/JDO-244?page=all ]

Michael Bouschen updated JDO-244:
---------------------------------

    Attachment: JDO-244.patch

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.


> JPOX generates illegal SQL for having clauses using COUNT.
> ----------------------------------------------------------
>
>          Key: JDO-244
>          URL: http://issues.apache.org/jira/browse/JDO-244
>      Project: JDO
>         Type: Bug
>   Components: tck20
>     Reporter: Michael Watzek
>     Assignee: Erik Bengtson
>  Attachments: JDO-244.patch
>
> 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)

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Mime
View raw message