Issue Details (XML | Word | Printable)

Key: NUCRDBMS-521
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Andy Jefferson
Reporter: KC Berg
Votes: 0
Watchers: 0
Operations

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

Support for subquery variables in the result and order

Created: 20/Apr/11 04:23 PM   Updated: 09/May/11 09:59 AM   Resolved: 21/Apr/11 08:14 AM
Component/s: Queries
Affects Version/s: 3.0.0.m3
Fix Version/s: 3.0.0.m4

File Attachments: 1. Text File dn-core-query.expression.OrderExpression.patch (0.5 kB)
2. Text File dn-store.rdbms-query.store.rdbms.query.QueryToSQLMapper.patch (0.6 kB)
3. Java Source File SubQueryResultTest.java (5 kB)


Forum Thread URL: http://www.datanucleus.org/servlet/forum/viewthread_thread,6631
Datastore: PostgreSQL
Severity: Proof of Concept


 Description  « Hide
I was unable to use the returning variable of a subquery in the result or ordering clauses of a query without getting an error. Attempting to use the variable in either the result or order clause would cause a null pointer in compile process of QueryToSQLMapper.

After reading through the documentation I didn't see that using a subquery variable in the result or ordering clause is even supported, which is why I'm creating this issue as an improvement.

I've create two patches one for supporting the use of a subquery variable in the result and the other for using the variable in the ordering. I have no idea if I did these patches the "right way" but they both seem to work great in my environment.

The result patch was a modification to the org/datanucleus/store/rdbms/query/QueryToSQLMapper.java in the store.rdbms project.

The ordering patch was a modification to org/datanucleus/query/expression/OrderExpression.java in the core project.

Again I'm not sure if these will work in all cases but none of the unit tests in the two projects failed if that means anything.



Sort Order: Ascending order - Click to sort in descending order
KC Berg added a comment - 20/Apr/11 04:24 PM
fix for using subquery variables in result clause which was throwing a null pointer.

KC Berg added a comment - 20/Apr/11 04:25 PM
fix for using subquery variable in ordering clause which was throwing a null pointer.

Andy Jefferson added a comment - 20/Apr/11 04:56 PM
Thx. what about a testcase that demonstrates such things?

KC Berg added a comment - 20/Apr/11 05:21 PM
I had a feeling you were going to ask that :)

I have a unit test in my development environment that confirmed the behavior I desired. I'm currently checking out, and learning, the DN end-to-end test suite so I can convert the test case for my environment to fit into the DN test suite. I'll attach it to this bug once complete.

Thanks

KC Berg added a comment - 20/Apr/11 11:39 PM
Here is a test to demonstrate the use of a subquery's result, via a variable, in the outer query's result. This test also demonstrates using the same subquery result variable in the order clause.

In my checked out version I placed this test in test.jdo.rdbms/src/test/org/datanucleus/tests/SubQueryResultTest.java

Please let me know if this test is adequate as the test is pretty simplistic but I believe it demonstrates the functionality.

Andy Jefferson added a comment - 21/Apr/11 08:14 AM
Thx for the patches, applied to SVN trunk. Note that some persistence APIs don't explicitly allow subqueries in result clauses.

Test adapted to fit "test.jdo.application", into JDOQLSubqueryTest since there is nothing RDBMS-specific in it