Issue Details (XML | Word | Printable)

Key: NUCRDBMS-554
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
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

Boolean properties not correctly mapped in certain queries

Created: 31/Aug/11 05:40 PM   Updated: 03/Oct/11 07:38 AM   Resolved: 06/Sep/11 03:22 PM
Component/s: None
Affects Version/s: 3.0.1
Fix Version/s: 3.0.2

File Attachments: 1. GZip Archive dn-boolquery-testcase.tar.gz (2 kB)

Environment: oracle


 Description  « Hide
The attached test case contains a class (B) with a boolean property which is mapped to a TINYINT column.

It runs two JDOQL queries. In the first one, the "boolProperty" condition is correctly mapped to the SQL where-clause "BOOLPROP = 1"; in the second one, the condition is incorrectly mapped to just "BOOLPROP".

(Note that this seems to work with HSQLDB as used in the test case, but not with Oracle and likely some other RDBMS; the testcase shall just demonstrate query generation)

Queries:

JDOQL1:
"elements.contains(anElement) && anElement.booleanProperty && anElement.c.bs.isEmpty()"

SQL:
SELECT DISTINCT 'org.datanucleus.test.A' AS NUCLEUS_TYPE,A0.A_ID FROM A A0 INNER JOIN B B0 ON A0.A_ID = B0.ELEMENTS_A_ID_OID LEFT OUTER JOIN "C" C0 ON B0.C_C_ID_OID = C0.C_ID WHERE B0.BOOLPROP = 1 AND ((SELECT COUNT(*) FROM B A0_SUB WHERE A0_SUB.C_C_ID_OID = C0.C_ID) = 0)


JDOQL2:
"elements.contains(anElement) && anElement.booleanProperty && !anElement.c.bs.isEmpty()"

SQL:
SELECT 'org.datanucleus.test.A' AS NUCLEUS_TYPE,A0.A_ID FROM A A0 WHERE EXISTS (SELECT 1 FROM B A0_SUB LEFT OUTER JOIN "C" B0_SUB ON A0_SUB.C_C_ID_OID = B0_SUB.C_ID WHERE A0_SUB.ELEMENTS_A_ID_OID = A0.A_ID AND A0_SUB.BOOLPROP AND NOT ((SELECT COUNT(*) FROM B A0_SUB_SUB WHERE A0_SUB_SUB.C_C_ID_OID = B0_SUB.C_ID) = 0))

The latter SQL query leads to an "ORA-00920: invalid relational operator" with oracle.

(I'm aware that these queries do not make sense, they are just the easiest way to show the problem which arises in some real world queries in my software).



Sort Order: Ascending order - Click to sort in descending order
Daniel Baldes added a comment - 31/Aug/11 05:40 PM
Test case attached.

Andy Jefferson added a comment - 06/Sep/11 03:22 PM
SVN trunk enforces all BooleanExpressions have "closure"