Wrong SQL is generated with a little bit complex logic containing 'OR' condition

[andyjefferson]

I've encountered a bug. This only happens if the query is a little bit complex.
The abstract pattern of the query is like:

predicateA && (predicateB || predicateC)

Where predicateB is a 'contains' statement, and predicateC is combination of three additional conditions. If I query for predicateA && predicateB && predicateC, it works.
test-datanucleus.zip
Please check the attached test case.

I have extracted the problem following the same data structure with my real project.

The problem I faced is the SQL generated from JDO is incorrect.

I tried to replicate the same scenario in the test case, but in the test case, it raises another problems.

1. If running 'mvn clean compile test' specified in the template you provided, the tables generated in the database seem different from the second way below

2. Generate with my ant script (running datanucleus_schema_tool in my attached 'mybuild.xml')

My expectation of how the generated tables look like should be the same as the second way. We use ant script to generate the tables in our production.

Because I defined some fields as type of ArrayList and also specified in jdo file, it doesn't generate the join table with method 1).

Anyway, this seems also a problem.

When running the test, it gives the following error:

Exception thrown when executing query : SELECT DISTINCT 'mydomain.model.Watch' AS NUCLEUS_TYPE,A0.KEY,A0.NAME FROM WATCH A0 CROSS JOIN STRATEGY VAR_VARTOPSTRATEGY WHERE A0.COMPANY_MANUFACTURE_KEY_EID = ? AND ((EXISTS (SELECT 1 FROM STRATEGY A0_SUB WHERE A0_SUB.PRIMARYSTRATEGIES_KEY_OWN = A0.KEY AND A0_SUB.KEY = ?)) OR (EXISTS (SELECT 1 FROM STRATEGY A0_SUB INNER JOIN STRATEGY B0_SUB ON A0_SUB.KEY = B0_SUB.KEY WHERE A0_SUB.SECONDARYSTRATEGIES_KEY_OWN = A0.KEY) AND EXISTS (SELECT 1 FROM STRATEGY A0_SUB WHERE A0_SUB.DOWNLINEMARKETSTRATEGIES_KEY_OWN = VAR_VARTOPSTRATEGY.KEY AND A0_SUB.KEY = B0_SUB.KEY) AND VAR_VARTOPSTRATEGY.KEY = ?))

I believe my data structure is clearly defined.

Sort Order: Ascending order - Click to sort in descending order

All
Comments
Change History
Activity Stream

[ Permalink | Edit | Delete | « Hide ]
Ray added a comment - 20/Jul/15 04:32 PM
Please check out the test case.

[ Permalink | Edit | Delete | « Hide ]
Andy Jefferson added a comment - 05/Aug/15 09:59 AM
Attached is your testcase using the DataNucleus template.
Moved the package.xxx file to src/main/resources (where all non-source have to be with Maven).
Changed the package.xxx file to be an ORM file since that will override what is in all annotations.
Added a persistence property.
Includes the log file obtained when running it "mvn clean test".

All passes.

[ Permalink | Edit | Delete | « Hide ]
Andy Jefferson added a comment - 05/Aug/15 10:02 AM
Can't see any issue.
If you don't get join tables with then you look in the log and work out why ... some metadata not overriding the higher level, so put it in ORM (which is where it should be anyway IMHO).
If you start up a PMF and want all tables to be known about then you either use an auto-start, or persistence.xml and specify the persistence property datanucleus.persistenceUnitLoadClasses as per the docs.

I see no exception from the query.

[ Permalink | Edit | Delete | « Hide ]
Ray added a comment - 05/Aug/15 02:54 PM
I see your changes and also you change the version to 4.0.0-release

I just download your modified test case and tried to run it, it throws exception (no javax.jdo.xxx).

I added a dependency

org.datanucleus javax.jdo 3.2.0-m1

But when I run "mvn clean test" it still throws exceptions:

Nested Throwables StackTrace:
java.lang.NullPointerException
at org.datanucleus.api.jdo.metadata.JDOAnnotationReader.processMemberAnnotations(JDOAnnotationReader.java:1083)
at org.datanucleus.metadata.annotations.AbstractAnnotationReader.getMetaDataForClass(AbstractAnnotationReader.java:225)
at org.datanucleus.metadata.annotations.AnnotationManagerImpl.getMetaDataForClass(AnnotationManagerImpl.java:167)
at org.datanucleus.metadata.MetaDataManagerImpl.loadAnnotationsForClass(MetaDataManagerImpl.java:2793)
at org.datanucleus.metadata.MetaDataManagerImpl.loadPersistenceUnit(MetaDataManagerImpl.java:1075)
at org.datanucleus.enhancer.DataNucleusEnhancer.getFileMetadataForInput(DataNucleusEnhancer.java:782)
at org.datanucleus.enhancer.DataNucleusEnhancer.enhance(DataNucleusEnhancer.java:500)
at org.datanucleus.enhancer.DataNucleusEnhancer.main(DataNucleusEnhancer.java:1152)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.datanucleus.maven.AbstractDataNucleusMojo.executeInJvm(AbstractDataNucleusMojo.java:331)
at org.datanucleus.maven.AbstractEnhancerMojo.enhance(AbstractEnhancerMojo.java:281)
at org.datanucleus.maven.AbstractEnhancerMojo.executeDataNucleusTool(AbstractEnhancerMojo.java:81)

You said you run the test and saw no errors. Did you see the log

">> Watch result set = 2" ?

[andyjefferson]

SELECT FROM A
WHERE a.b == :param1
&&
(
a.coll1.contains(:param2)
||
(
a.coll2.contains(var1)
&&
:param2.coll3.contains(var1)
)
)

Specifically the problem with this query is firstly that it uses OR, which means that any use of "contains" has to use "EXISTS (subquery)" currently. This in itself is not a major problem, it's just a problem dependent on what the
element of this contains is needed for.

In your case you want to link the element of one of these "contains" to one of the other "contains". Since we are using "EXISTS(subquery)" we cannot do that correctly (SQL doesn't work like that, whilst Java does). If you had wanted to link the "var1" to some fixed constraint then that would have been ok, and would fit within the subquery
of the EXISTS. BUT you want to link outside of the EXISTS.

Workarounds

1. Split the query into 2 and run individually and sum the results
   SELECT FROM A WHERE a.b == :param1 && a.coll1.contains(:param2)
   SELECT FROM A WHERE a.b == :param1 && a.coll2.contains(var1) && :param2.coll3.contains(var1)
2. Don't pass in your second parameter as it is now, and instead generate the single query based on the values that are in
   "myMainStrategy.downlineMarketStrategies". So you then would have a query like
   SELECT FROM A
   WHERE a.b == :param1
   &&
   (
   a.coll1.contains(:param2)
   ||
   (
   a.coll2.contains(:param3a)
   ||
   a.coll2.contains(:param3b)
   ||
   a.coll2.contains(:param3c)
   ... etc
   )
   )

Or just propose what SQL you think could be generated to do it in one query with the current input.