Issue Details (XML | Word | Printable)

Key: NUCRDBMS-568
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Unassigned
Reporter: Daniel Baldes
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Count query produced without DISTINCT, resulting in wrong count

Created: 29/Nov/11 05:40 PM   Updated: 04/Apr/12 05:30 PM   Resolved: 04/Apr/12 05:29 PM
Component/s: Queries
Affects Version/s: 3.0.3
Fix Version/s: 3.1.0.m2

File Attachments: 1. Zip Archive testcase-count.zip (3 kB)


Datastore: HSQL, Oracle


 Description  « Hide
Somehow NUCRDBMS-390 seems to have resurfaced. The attached test case demonstrates the same wrong behavior with DN 3.0.3.

I am trying to count results of a query with a join:

filter: "elements.contains(vElement) && vElement.otherProperty == 'test'"
result: "count(this)"

The count query is translated to:
 
 SELECT COUNT(A0.A_ID) FROM A A0 INNER JOIN B B0 ON A0.A_ID = B0.A_A_ID_OID WHERE B0.OTHERPROPERTY = 'test';

but it should be COUNT(DISTINCT A0.A_ID).

The object query is correctly translated to:

 SELECT DISTINCT 'org.datanucleus.test.A' AS NUCLEUS_TYPE,A0.A_ID FROM A A0 INNER JOIN B B0 ON A0.A_ID = B0.A_A_ID_OID WHERE B0.OTHERPROPERTY = 'test'

The test case uses hsqldb but I get the same problem with Oracle.

(test case follows)

Daniel Baldes added a comment - 29/Nov/11 05:40 PM
Attached test case.

Daniel Baldes made changes - 29/Nov/11 05:40 PM
Field Original Value New Value
Attachment testcase-count.zip [ 11531 ]
Andy Jefferson added a comment - 29/Nov/11 07:20 PM
NUCRDBMS-390 is closed, worked then, and works now. It is also nothing to do with this, since it is to do with size() and that executing a COUNT() query to get the size of results. Your case OTOH does nothing of the sort.

In your case you can easily set the result clause as

COUNT(DISTINCT this)

which is, after all, why the JDOQL BNF allows it.

Daniel Baldes added a comment - 30/Nov/11 09:00 AM
Well, the specification reads "count(<expression>): the count of the number of instances of the expression is returned". While this is not overly precise, one could argue that, as JDO guarantees to not return multiple instances of the same object, my expression in the test case returns only one instance (not two as counted). Furthermore, the result of count(this) has no useful meaning otherwise, IMHO. Therefore, I think that "this" actually should imply "distinct" (of course that might be different for other count(expressions).)

But thanks for the hint, I'll use distinct.

Andy Jefferson added a comment - 04/Apr/12 05:29 PM
SVN trunk addresses the specific case of "COUNT(this)" and there being a join to a different inheritance tree (hence causing non-distinct results).

Andy Jefferson made changes - 04/Apr/12 05:29 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.1.0.m2 [ 11509 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 04/Apr/12 05:30 PM
Status Resolved [ 5 ] Closed [ 6 ]