Issue Details (XML | Word | Printable)

Key: NUCRDBMS-365
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Marco Schulze
Votes: 0
Watchers: 0
Operations

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

MySQLSyntaxErrorException: Unknown column 'a0_sub.text' in 'where clause'

Created: 15/Apr/10 08:30 PM   Updated: 26/Apr/10 03:39 PM   Resolved: 17/Apr/10 02:29 PM
Component/s: ORM
Affects Version/s: 2.0.3, 2.1.0.m1
Fix Version/s: 2.1.0.m2

Datastore: MySQL
Severity: Development


 Description  « Hide
*** Exception ***
javax.jdo.JDODataStoreException: Exception thrown when executing query
at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:319)
at org.datanucleus.jdo.JDOQuery.executeWithMap(JDOQuery.java:351)
at org.datanucleus.test.Main$QueryDataTransRunnable0.run(Main.java:165)
at org.datanucleus.test.Main.executeInTransaction(Main.java:49)
at org.datanucleus.test.Main.main(Main.java:183)
NestedThrowablesStackTrace:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'a0_sub.text' in 'where clause'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeQuery(ParamLoggingPreparedStatement.java:359)
at org.datanucleus.store.rdbms.SQLController.executeStatementQuery(SQLController.java:457)
at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecuteInternal(JDOQLQuery.java:716)
at org.datanucleus.store.query.Query$1.run(Query.java:1840)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
at java.util.concurrent.FutureTask.run(FutureTask.java:138)
at java.lang.Thread.run(Thread.java:619)

*** Test ***
http://www.nightlabs.de/~marco/datanucleus/2010-04-15.00/


Sort Order: Ascending order - Click to sort in descending order
Marco Schulze added a comment - 15/Apr/10 08:36 PM
Hello Andy,

this time, I didn't post to the forum, because I thought it's somehow redundant and not really necessary. Do you agree?

Thanks a lot for all your quick help! JFire is running pretty completely, already. I'm very optimistic that I can check-in the new DN 2.1.x into our subversion-trunk of JFire 1.1 by the end of this month.

Best regards, Marco :-)

Andy Jefferson added a comment - 16/Apr/10 09:51 AM
The forum is for non-priority free support, and unlikely to get a reply.


To provide a clear bug posting (so people don't have to download your testcase just to see what the problem is), the query is

SELECT DISTINCT this
FROM org.datanucleus.test.Person
WHERE
    dataFields.contains(dataField0) &&
    (
        (
            (
                dataField0.structBlockOrganisationID == structBlockOrganisationID0_0 &&
                dataField0.structBlockID == structBlockID0_0 && dataField0.structFieldOrganisationID == structFieldOrganisationID0_0 &&
                dataField0.structFieldID == structFieldID0_0
            )
            ||
            (
                dataField0.structBlockOrganisationID == structBlockOrganisationID0_1 &&
                dataField0.structBlockID == structBlockID0_1 &&
                dataField0.structFieldOrganisationID == structFieldOrganisationID0_1 &&
                dataField0.structFieldID == structFieldID0_1
            )
            ||
            (
                dataField0.structBlockOrganisationID == structBlockOrganisationID0_2 &&
                dataField0.structBlockID == structBlockID0_2 &&
                dataField0.structFieldOrganisationID == structFieldOrganisationID0_2 &&
                dataField0.structFieldID == structFieldID0_2
            )
        )
        &&
        dataField0.text.toLowerCase().indexOf(searchText0.toLowerCase()) >= 0
    )
VARIABLES
    org.datanucleus.test.TextDataField dataField0
PARAMETERS
    java.lang.String structBlockOrganisationID0_0,
    java.lang.String structBlockID0_0,
    java.lang.String structFieldOrganisationID0_0,
    java.lang.String structFieldID0_0,
    java.lang.String structBlockOrganisationID0_1,
    java.lang.String structBlockID0_1,
    java.lang.String structFieldOrganisationID0_1,
    java.lang.String structFieldID0_1,
    java.lang.String structBlockOrganisationID0_2,
    java.lang.String structBlockID0_2,
    java.lang.String structFieldOrganisationID0_2,
    java.lang.String structFieldID0_2,
    java.lang.String searchText0
Note that I've stripped out some random variable queryProp, which was 'bound' via "queryProp == this", which seems kinda pointless(to me). Current code will always do a CROSS JOIN for that, and do you want that ?

You could then post the "QueryCompilation" from the log, but since your query here is so long I couldn't be bothered.

PS, SVN trunk doesn't care if you've embedded random escape codes in your query and works fine.


The SQL then generated is

SELECT DISTINCT A1.ORGANISATIONID,A1.PROPERTYSETID,A1.DISCRIMINATOR
FROM PERSON A0
INNER JOIN PROPERTYSET A1 ON A0.ORGANISATIONID = A1.ORGANISATIONID AND A0.PROPERTYSETID = A1.PROPERTYSETID WHERE A1.DISCRIMINATOR = 'org.datanucleus.test.Person'
AND EXISTS (
    SELECT 1 FROM TEXTDATAFIELD A0_SUB
    INNER JOIN PROPERTYSET_DATAFIELDS B0_SUB ON A0_SUB.DATABLOCKID = B0_SUB.DATABLOCKID_EID AND A0_SUB.ORGANISATIONID = B0_SUB.ORGANISATIONID_EID AND A0_SUB.PROPERTYSETID = B0_SUB.PROPERTYSETID_EID AND A0_SUB.STRUCTBLOCKID = B0_SUB.STRUCTBLOCKID_EID AND A0_SUB.STRUCTBLOCKORGANISATIONID = B0_SUB.STRUCTBLOCKORGANISATIONID_EID AND A0_SUB.STRUCTFIELDID = B0_SUB.STRUCTFIELDID_EID AND A0_SUB.STRUCTFIELDORGANISATIONID = B0_SUB.STRUCTFIELDORGANISATIONID_EID
    INNER JOIN DATAFIELD A1_SUB ON A0_SUB.DATABLOCKID = A1_SUB.DATABLOCKID AND A0_SUB.ORGANISATIONID = A1_SUB.ORGANISATIONID AND A0_SUB.PROPERTYSETID = A1_SUB.PROPERTYSETID AND A0_SUB.STRUCTBLOCKID = A1_SUB.STRUCTBLOCKID AND A0_SUB.STRUCTBLOCKORGANISATIONID = A1_SUB.STRUCTBLOCKORGANISATIONID AND A0_SUB.STRUCTFIELDID = A1_SUB.STRUCTFIELDID AND A0_SUB.STRUCTFIELDORGANISATIONID = A1_SUB.STRUCTFIELDORGANISATIONID
    WHERE B0_SUB.ORGANISATIONID_OID = A1.ORGANISATIONID
    AND B0_SUB.PROPERTYSETID_OID = A1.PROPERTYSETID
    AND (A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ? OR A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ? OR A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ?)
    )
AND (LOCATE('ni',LOWER(A0_SUB."TEXT")) - 1) >= 0

So,
1. It chooses EXISTS for the contains, because there are still some TODOs to improve the logic so that it works out that the variable for the contained element is the only thing used in OR and NOT clauses so it could have chosen INNER JOIN in this case.
2. Ok, it's decided to have an EXISTS, but then puts the final clause in the main query, likely due to the combination of methods, and needs to check on the statement in use.
3. The use of ORs needs to have parentheses.

So likely will have to wait til 2.1 release for those things. Swap the query to run using JDOQL-Legacy and you can move forward, so we know exactly which queries don't run with JDOQL2; no point in waiting for one to be fixed before finding the next one.

Andy Jefferson added a comment - 16/Apr/10 11:09 AM
Actually 2) above is fixed in http://www.jpox.org/servlet/jira/browse/NUCRDBMS-366 so the query does actually run. Whether the results are correct are a different issue

Marco Schulze added a comment - 16/Apr/10 11:31 AM
Hi Andy,

thanks a lot for your quick reply!

> Note that I've stripped out some random variable queryProp, which was 'bound' via
> "queryProp == this", which seems kinda pointless(to me). Current code will always
> do a CROSS JOIN for that, and do you want that ?

The query was originally generated by a dynamic query engine which allows for different scenarios: Either the candidate class *IS* the PropertySet (or a subclass) or the the candidate class *HAS* a PropertySet (in a 1-1-relationship). Therefore, the whole query works with the alias 'queryProp' and only one single line decides about whether it's 'this == queryProp' or 'this.whateverField == queryProp'. Even more complex relationships would be possible (e.g. 'this.whateverField.contains(queryProp)'), but are not (yet) used by us.

A CROSS JOIN is of course not desirable (due to being very inefficient) and it would be great if DataNucleus could optimize aliases itself.

Of course, we might alternatively try to refactor our code to prevent using such an alias, but you probably know best that the SQL which is created by DN contains many aliases, too; after all, using aliases is a very common design pattern when building dynamic queries.

I will continue testing and let you know if there are any other problems.

Andy Jefferson added a comment - 16/Apr/10 12:12 PM
And with parentheses around the ORs (item 3 above) SVN trunk comes out to

SELECT DISTINCT A1.ORGANISATIONID,A1.PROPERTYSETID,A1.DISCRIMINATOR
FROM PERSON A0
INNER JOIN PROPERTYSET A1 ON A0.ORGANISATIONID = A1.ORGANISATIONID AND A0.PROPERTYSETID = A1.PROPERTYSETID
WHERE A1.DISCRIMINATOR = 'org.datanucleus.test.Person'
AND EXISTS (
    SELECT 1 FROM TEXTDATAFIELD A0_SUB
    INNER JOIN PROPERTYSET_DATAFIELDS B0_SUB ON A0_SUB.DATABLOCKID = B0_SUB.DATABLOCKID_EID AND A0_SUB.ORGANISATIONID = B0_SUB.ORGANISATIONID_EID AND A0_SUB.PROPERTYSETID = B0_SUB.PROPERTYSETID_EID AND A0_SUB.STRUCTBLOCKID = B0_SUB.STRUCTBLOCKID_EID AND A0_SUB.STRUCTBLOCKORGANISATIONID = B0_SUB.STRUCTBLOCKORGANISATIONID_EID AND A0_SUB.STRUCTFIELDID = B0_SUB.STRUCTFIELDID_EID AND A0_SUB.STRUCTFIELDORGANISATIONID = B0_SUB.STRUCTFIELDORGANISATIONID_EID
    INNER JOIN DATAFIELD A1_SUB ON A0_SUB.DATABLOCKID = A1_SUB.DATABLOCKID AND A0_SUB.ORGANISATIONID = A1_SUB.ORGANISATIONID AND A0_SUB.PROPERTYSETID = A1_SUB.PROPERTYSETID AND A0_SUB.STRUCTBLOCKID = A1_SUB.STRUCTBLOCKID AND A0_SUB.STRUCTBLOCKORGANISATIONID = A1_SUB.STRUCTBLOCKORGANISATIONID AND A0_SUB.STRUCTFIELDID = A1_SUB.STRUCTFIELDID AND A0_SUB.STRUCTFIELDORGANISATIO
NID = A1_SUB.STRUCTFIELDORGANISATIONID
    WHERE B0_SUB.ORGANISATIONID_OID = A1.ORGANISATIONID
    AND B0_SUB.PROPERTYSETID_OID = A1.PROPERTYSETID
    AND (((A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ?)
        OR (A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ?))
        OR (A1_SUB.STRUCTBLOCKORGANISATIONID = ? AND A1_SUB.STRUCTBLOCKID = ? AND A1_SUB.STRUCTFIELDORGANISATIONID = ? AND A1_SUB.STRUCTFIELDID = ?))
    AND (LOCATE('ni',LOWER(A0_SUB."TEXT")) - 1) >= 0
)

which probably should give the correct results (although not quite as efficient as INNER JOIN).

And as you see there aren't actually that many aliases in "JDOQL2" queries ;-P (if aliases start with the same letter then they are in the same inheritance tree, so A0, A1 represent fields of an object in the same inheritance tree; similarly A0_SUB, A1_SUB).

Andy Jefferson added a comment - 17/Apr/10 02:29 PM
Query runs fine for me, with SQL as quoted.
Remaining refinements are covered under NUCRDBMS-368 (detect when to use INNER JOIN and when to use EXISTS for this scenario), and NUCRDBMS-369 (optimise out any unnecessary variable when assigned to the candidate, to avoid the CROSS JOIN)