DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
Issue Details (XML | Word | Printable)

Type: Bug Bug
Status: Closed Closed
Resolution: Cannot Reproduce
Priority: Major Major
Assignee: Unassigned
Reporter: Michael Brown
Votes: 0
Watchers: 1

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

Query by date when field mapped as varchar fails

Created: 12/Oct/09 02:51 PM   Updated: 26/Dec/09 12:56 PM   Resolved: 09/Dec/09 08:39 PM
Component/s: Queries
Affects Version/s: 2.0.0.m2
Fix Version/s: None

File Attachments: 1. Text File date_tests.patch (5 kB)
2. Text File date_tests2.patch (4 kB)

Environment: Win7, MySQL 5, Java 6

Datastore: MySQL

 Description  « Hide
Doing a query by date, where the date field is persisted as a varchar, fails to find the row. Looking at the logs I can see that values are inserted correctly, but it is searching in the wrong timezone, and formatting the date incorrectly. This leads me to believe that the query is not using the specified mapping to translate query parameters.

I am attempting to investigate this right now, but what would really help are a few pointers in the right direction.
A patch to the tests in SVN to demonstrate this issue will also be attached in a moment.

Below I also attach logged sql statements, that demonstrate the mis-match.
INSERT INTO g_pc_98mbjzp8_datecont (g_ia_sequence,g_ia_frozen,g_at_ovvtecif_date,g_at_2zj4at3w_identity,g_ia_version) VALUES (<2>,<false>,<'2009-08-09 14:17:01.000'>,<'9dc4f500-85bc-4fa2-a413-561b5759a60d'>,<1>)
SELECT 'com.assethouse.goya.test.bugs.bug166.DateContainer' AS JPOXMETADATA,this.g_ia_version,this.g_at_ovvtecif_date,this.g_at_2zj4at3w_identity,this.g_ia_frozen,this.g_ia_sequence FROM g_pc_98mbjzp8_datecont this WHERE this.g_at_ovvtecif_date = <2009-08-09 15:17:01.0>

Sort Order: Ascending order - Click to sort in descending order
Michael Brown added a comment - 12/Oct/09 02:52 PM
Tests as promised.

Andy Jefferson added a comment - 12/Oct/09 04:27 PM
You seem to have some "JPOXMETADATA" in your query. This is not in DataNucleus codebase. You don't say if using "JDOQL" or "JDOQL2" implementation; JDOQL is in a package called "legacy" for a reason - JDOQL2 is the future so use that

Andy Jefferson added a comment - 13/Oct/09 10:04 AM
Tests are in SVN now. Thx. JDOQL2 in SVN trunk passes also.

Suggest that the parameter is being mapped with a DateMapping with datastore mapping of DateRDBMSMapping (or similar) so is using JDBC.setDate() on the param, instead of setString. As already said, legacy JDOQL will not be fixed (by me) for anything now; if you want to do that then fine but I've no time for providing input on it.

Michael Brown added a comment - 15/Oct/09 10:45 AM
I understand that DN must move on, so I am trying to investigate this issue in the JDOQL2 parser, but am finding it difficult.

Presently I am looking at QueryToSQLMapper, which I think deals with the specifics of mapping to the database from query values. In method I see that processParameterExpression seems to be trying to discover the appropriate JavaTypeMapping for each occurance of a parameter. However, what I can't seem to find is a connection between that logic and the JavaTypeMapping of the field on the other side of the expression. Would I be correct in saying that parameters are being handled in isolation?..

Additionally, wrt jdoql2, I have modified the tests to use it. However, I am curious, will this become the default QL implementation at some point?

Andy Jefferson added a comment - 15/Oct/09 10:59 AM
1. JDOQL2 works on your tests.

2. When it parses a ParameterExpression it will create a XXXLiteral (with "isParameter" set to true). This will give it a JavaTypeMapping for the java type being represented ... but with the default DatastoreMapping for that type - it has no better information at that point so just moves forward with that for now).

3. When it gets to processing the "eq" operation there is a left and right expression (the only place where you know both sides, hence know that the parameter needs to be treated in a particular way). Left is the PrimaryExpression for the Date, and right is the XXXLiteral for the parameter. At this point it checks the underlying DatastoreMapping for both sides and makes the parameter side consistent (in your case becomes a VarcharRDBMSMapping instead of TimestampRDBMSMapping, or something). Hence it plugs in the param value as a String form and it works.

4. Obviously "legacy" code will be deactivated at some point ... that's why it's in its own package. Deletion will be invoked at some point too, ... since I don't utilise SUNs retarded "deprecation" (but never remove) policy.

Michael Brown added a comment - 19/Oct/09 01:21 PM
I think I may be on the cusp of understanding the problem, but more on that later when I unerstand more.

What concerns me is that I have a user seeing this problem, I can re-produce it in the SVN head of DN, but you don't experience it.

I'd like to compare our environments, just to get to the bottom of this.
  I am testing on
    Ubuntu 9.04 i686
    Java 1.6.0_13
I build using Maven2, using the script

Are there any major differences with your test environment?

Michael Brown added a comment - 19/Oct/09 01:42 PM
Should have said, Timezone also. I'm in GMT+1 at the moment.

Michael Brown added a comment - 19/Oct/09 03:28 PM
Scratch that. I'll update shortly.

Andy Jefferson added a comment - 09/Dec/09 08:39 PM
No further comment was made so closing