You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Marc Logemann <li...@logemann.org> on 2011/03/02 16:43:28 UTC
same jpa query generates different SQLs
Hi,
i am totally astonished:
I am using this query:
public List<Order> findByDate(Date date) {
TypedQuery<Order> query = getEntityManager().
createQuery("select o FROM Order o where o.createdYmd = ?1 order by o.id", Order.class);
query.setParameter(1, date, TemporalType.DATE);
return query.getResultList();
}
This query is called by a service class which is scheduled ever 30 seconds. See the caller:
// get yesterdays Date
Date today = new Date();
Calendar calendar = Calendar.getInstance();
calendar.setTime(today);
calendar.add(Calendar.DATE, -1);
Date yesterday = calendar.getTime();
List<Order> list = orderDao.findByDate(yesterday);
Now, on the second run, the query parameter is of type timestamp with full time specified, giving me 0 records of course. First query returns records because there TemporalType seems to work. See log.
FIRST RUN ->
[DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`, t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created, t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid, t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype, t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created, t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype, t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname, t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox, t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption, t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city, t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender, t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street, t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner, t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby, t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid = t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype IN (?)) ORDER BY t0.ordernr ASC [params=(Date) 2011-03-01, (int) 2]
SECOND RUN ->
[DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955> executing prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`, t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created, t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid, t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype, t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created, t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype, t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname, t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox, t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption, t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city, t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender, t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street, t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner, t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby, t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid = t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype IN (?)) ORDER BY t0.ordernr ASC [params=(Timestamp) 2011-03-01 16:37:00.001, (int) 2]
I have completely no clue what to do now ;-) Thanks for input.
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Re: same jpa query generates different SQLs
Posted by Marc Logemann <li...@logemann.org>.
Hi,
the issue is: https://issues.apache.org/jira/browse/OPENJPA-1955
Seems nobody cared so far ;-) Feel free to vote up (if this is possible).
Greetings to Fabian Lange btw. Just noticed you both work for the same company. He knows me from Nokia-Siemens back then.
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Am 07.04.2011 um 14:52 schrieb Tobias Trelle:
>
> Marc Logemann wrote:
>>
>> created an issue for it. Thanks for your ideas to make the cache
>> disablement query based....
>>
>
> Dear Marc,
>
> can you please provide a link to this issue? I'm facing the same problem
> with OpenJPA 2.0.1 und DB2 V9:
>
> I have a query based on two Date parameters (amongst others). The first time
> the query is executed, it runs fine and uses SQL type DATE:
>
>
> ...
> WHERE ((t0.A = ? OR t0.B = ? OR t0.C= ?) AND t0.Z >= ? AND t0.Z <= ?)
> [params=(int) 41140, (int) 41140, (int) 41140, (Date) 2010-08-01, (Date)
> 2010-12-31]
>
>
> The second execution looks like this ...
>
>
> ...
> WHERE ((t0.A= ? OR t0.B= ? OR t0.C= ?) AND t0.Z >= ? AND t0.Z <= ?)
> [params=(int) 41140, (int) 41140, (int) 41140, (Timestamp) 2010-08-01
> 14:47:05.812, (Timestamp) 2010-12-31 14:47:05.812]
>
>
> ... and fails with
>
> DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007
>
> because the DB2 column is of type DATE.
>
> Will we run into performance issues if we disable the QuerySQLCache? Our
> persistence unit uses only two named queries.
>
> Cheers,
> Tobias
>
>
>
> --
> View this message in context: http://openjpa.208410.n2.nabble.com/same-jpa-query-generates-different-SQLs-tp6081417p6249868.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: same jpa query generates different SQLs
Posted by Tobias Trelle <to...@codecentric.de>.
Marc Logemann wrote:
>
> created an issue for it. Thanks for your ideas to make the cache
> disablement query based....
>
Dear Marc,
can you please provide a link to this issue? I'm facing the same problem
with OpenJPA 2.0.1 und DB2 V9:
I have a query based on two Date parameters (amongst others). The first time
the query is executed, it runs fine and uses SQL type DATE:
...
WHERE ((t0.A = ? OR t0.B = ? OR t0.C= ?) AND t0.Z >= ? AND t0.Z <= ?)
[params=(int) 41140, (int) 41140, (int) 41140, (Date) 2010-08-01, (Date)
2010-12-31]
The second execution looks like this ...
...
WHERE ((t0.A= ? OR t0.B= ? OR t0.C= ?) AND t0.Z >= ? AND t0.Z <= ?)
[params=(int) 41140, (int) 41140, (int) 41140, (Timestamp) 2010-08-01
14:47:05.812, (Timestamp) 2010-12-31 14:47:05.812]
... and fails with
DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007
because the DB2 column is of type DATE.
Will we run into performance issues if we disable the QuerySQLCache? Our
persistence unit uses only two named queries.
Cheers,
Tobias
--
View this message in context: http://openjpa.208410.n2.nabble.com/same-jpa-query-generates-different-SQLs-tp6081417p6249868.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: same jpa query generates different SQLs
Posted by Marc Logemann <li...@logemann.org>.
Hi,
created an issue for it. Thanks for your ideas to make the cache disablement query based....
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Am 02.03.2011 um 19:10 schrieb Jeremy Bauer:
> Marc,
>
> I'm glad that worked for you. You could actually just exclude that query
> for the time being, that way you still get caching benefits for your other
> statements. To exclude the query you can either set the query hint via
> query.setHint("openjpa.hint.IgnorePreparedQuery", true) on the query or
> exclude it via persistence property:
>
> <property name="openjpa.jdbc.QuerySQLCache"
> value="true(excludes='select o FROM Order o where o.createdYmd = ?1
> order by o.id')"/>
>
> You can get more specifics on cache exclusion in this section of the OpenJPA
> manual[1].
>
> That should get you by for the time being, but please open a JIRA for this
> problem so that it gets fixed.
>
> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/ref_guide_cache_querysql.html
>
> -Jeremy
>
> On Wed, Mar 2, 2011 at 10:51 AM, Marc Logemann <li...@logemann.org> wrote:
>
>> Hi Jeremy,
>>
>> thanks for your instant feedback. I directly tried your suggestion and now
>> it works. So there is a bug in the cache implementation with regard to Type
>> Mappings.
>> And its not a DB2 thingy because i am using MySQL ;-)
>>
>> if you need more infos in the error case, i am willing to help if you
>> want.... I would like to see this solved because disabling the cache means
>> decreasing the performance somehow right?
>>
>> ---
>> regards
>> Marc Logemann
>> http://www.logemann.org
>> http://www.logentis.de
>>
>>
>>
>>
>> Am 02.03.2011 um 17:31 schrieb Jeremy Bauer:
>>
>>> Hi Marc,
>>>
>>> I'm wondering if this isn't caused by an inconsistent parameter type
>> mapping
>>> as a result of using the QuerySQLCache. I saw a similar issue a few weeks
>>> ago, but it was oddly specific to DB2 on zOS. Please try:
>>>
>>> <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
>>>
>>> -Jeremy
>>>
>>> On Wed, Mar 2, 2011 at 9:43 AM, Marc Logemann <li...@logemann.org> wrote:
>>>
>>>> Hi,
>>>>
>>>> i am totally astonished:
>>>>
>>>> I am using this query:
>>>>
>>>> public List<Order> findByDate(Date date) {
>>>>
>>>> TypedQuery<Order> query = getEntityManager().
>>>> createQuery("select o FROM Order o where o.createdYmd = ?1
>>>> order by o.id", Order.class);
>>>>
>>>> query.setParameter(1, date, TemporalType.DATE);
>>>> return query.getResultList();
>>>> }
>>>>
>>>> This query is called by a service class which is scheduled ever 30
>> seconds.
>>>> See the caller:
>>>>
>>>> // get yesterdays Date
>>>> Date today = new Date();
>>>> Calendar calendar = Calendar.getInstance();
>>>> calendar.setTime(today);
>>>> calendar.add(Calendar.DATE, -1);
>>>> Date yesterday = calendar.getTime();
>>>>
>>>> List<Order> list = orderDao.findByDate(yesterday);
>>>>
>>>>
>>>> Now, on the second run, the query parameter is of type timestamp with
>> full
>>>> time specified, giving me 0 records of course. First query returns
>> records
>>>> because there TemporalType seems to work. See log.
>>>>
>>>> FIRST RUN ->
>>>>
>>>> [DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing
>>>> prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid,
>> t1.`_version`,
>>>> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
>>>> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
>>>> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
>>>> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
>>>> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`,
>> t3.addresstype,
>>>> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
>>>> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip,
>> t3.postofficebox,
>>>> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
>>>> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
>>>> t4.company, t4.country, t4.department, t4.email, t4.firstname,
>> t4.gender,
>>>> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox,
>> t4.street,
>>>> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
>>>> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed,
>> t0.printedby,
>>>> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid =
>> t1.oid
>>>> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
>> address
>>>> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
>>>> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
>> t3.jpatype
>>>> IN (?)) ORDER BY t0.ordernr ASC [params=(Date) 2011-03-01, (int) 2]
>>>>
>>>> SECOND RUN ->
>>>>
>>>> [DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955>
>> executing
>>>> prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
>>>> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
>>>> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
>>>> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
>>>> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
>>>> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`,
>> t3.addresstype,
>>>> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
>>>> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip,
>> t3.postofficebox,
>>>> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
>>>> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
>>>> t4.company, t4.country, t4.department, t4.email, t4.firstname,
>> t4.gender,
>>>> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox,
>> t4.street,
>>>> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
>>>> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed,
>> t0.printedby,
>>>> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid =
>> t1.oid
>>>> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
>> address
>>>> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
>>>> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
>> t3.jpatype
>>>> IN (?)) ORDER BY t0.ordernr ASC [params=(Timestamp) 2011-03-01
>> 16:37:00.001,
>>>> (int) 2]
>>>>
>>>> I have completely no clue what to do now ;-) Thanks for input.
>>>>
>>>>
>>>>
>>>> ---
>>>> regards
>>>> Marc Logemann
>>>> http://www.logemann.org
>>>> http://www.logentis.de
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
Re: same jpa query generates different SQLs
Posted by Jeremy Bauer <te...@gmail.com>.
Marc,
I'm glad that worked for you. You could actually just exclude that query
for the time being, that way you still get caching benefits for your other
statements. To exclude the query you can either set the query hint via
query.setHint("openjpa.hint.IgnorePreparedQuery", true) on the query or
exclude it via persistence property:
<property name="openjpa.jdbc.QuerySQLCache"
value="true(excludes='select o FROM Order o where o.createdYmd = ?1
order by o.id')"/>
You can get more specifics on cache exclusion in this section of the OpenJPA
manual[1].
That should get you by for the time being, but please open a JIRA for this
problem so that it gets fixed.
[1]
http://openjpa.apache.org/builds/latest/docs/manual/ref_guide_cache_querysql.html
-Jeremy
On Wed, Mar 2, 2011 at 10:51 AM, Marc Logemann <li...@logemann.org> wrote:
> Hi Jeremy,
>
> thanks for your instant feedback. I directly tried your suggestion and now
> it works. So there is a bug in the cache implementation with regard to Type
> Mappings.
> And its not a DB2 thingy because i am using MySQL ;-)
>
> if you need more infos in the error case, i am willing to help if you
> want.... I would like to see this solved because disabling the cache means
> decreasing the performance somehow right?
>
> ---
> regards
> Marc Logemann
> http://www.logemann.org
> http://www.logentis.de
>
>
>
>
> Am 02.03.2011 um 17:31 schrieb Jeremy Bauer:
>
> > Hi Marc,
> >
> > I'm wondering if this isn't caused by an inconsistent parameter type
> mapping
> > as a result of using the QuerySQLCache. I saw a similar issue a few weeks
> > ago, but it was oddly specific to DB2 on zOS. Please try:
> >
> > <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
> >
> > -Jeremy
> >
> > On Wed, Mar 2, 2011 at 9:43 AM, Marc Logemann <li...@logemann.org> wrote:
> >
> >> Hi,
> >>
> >> i am totally astonished:
> >>
> >> I am using this query:
> >>
> >> public List<Order> findByDate(Date date) {
> >>
> >> TypedQuery<Order> query = getEntityManager().
> >> createQuery("select o FROM Order o where o.createdYmd = ?1
> >> order by o.id", Order.class);
> >>
> >> query.setParameter(1, date, TemporalType.DATE);
> >> return query.getResultList();
> >> }
> >>
> >> This query is called by a service class which is scheduled ever 30
> seconds.
> >> See the caller:
> >>
> >> // get yesterdays Date
> >> Date today = new Date();
> >> Calendar calendar = Calendar.getInstance();
> >> calendar.setTime(today);
> >> calendar.add(Calendar.DATE, -1);
> >> Date yesterday = calendar.getTime();
> >>
> >> List<Order> list = orderDao.findByDate(yesterday);
> >>
> >>
> >> Now, on the second run, the query parameter is of type timestamp with
> full
> >> time specified, giving me 0 records of course. First query returns
> records
> >> because there TemporalType seems to work. See log.
> >>
> >> FIRST RUN ->
> >>
> >> [DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing
> >> prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid,
> t1.`_version`,
> >> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
> >> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
> >> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
> >> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
> >> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`,
> t3.addresstype,
> >> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
> >> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip,
> t3.postofficebox,
> >> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
> >> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
> >> t4.company, t4.country, t4.department, t4.email, t4.firstname,
> t4.gender,
> >> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox,
> t4.street,
> >> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
> >> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed,
> t0.printedby,
> >> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid =
> t1.oid
> >> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
> address
> >> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> >> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
> t3.jpatype
> >> IN (?)) ORDER BY t0.ordernr ASC [params=(Date) 2011-03-01, (int) 2]
> >>
> >> SECOND RUN ->
> >>
> >> [DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955>
> executing
> >> prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
> >> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
> >> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
> >> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
> >> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
> >> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`,
> t3.addresstype,
> >> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
> >> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip,
> t3.postofficebox,
> >> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
> >> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
> >> t4.company, t4.country, t4.department, t4.email, t4.firstname,
> t4.gender,
> >> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox,
> t4.street,
> >> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
> >> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed,
> t0.printedby,
> >> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid =
> t1.oid
> >> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN
> address
> >> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> >> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR
> t3.jpatype
> >> IN (?)) ORDER BY t0.ordernr ASC [params=(Timestamp) 2011-03-01
> 16:37:00.001,
> >> (int) 2]
> >>
> >> I have completely no clue what to do now ;-) Thanks for input.
> >>
> >>
> >>
> >> ---
> >> regards
> >> Marc Logemann
> >> http://www.logemann.org
> >> http://www.logentis.de
> >>
> >>
> >>
> >>
> >>
>
>
Re: same jpa query generates different SQLs
Posted by Marc Logemann <li...@logemann.org>.
Hi Jeremy,
thanks for your instant feedback. I directly tried your suggestion and now it works. So there is a bug in the cache implementation with regard to Type Mappings.
And its not a DB2 thingy because i am using MySQL ;-)
if you need more infos in the error case, i am willing to help if you want.... I would like to see this solved because disabling the cache means decreasing the performance somehow right?
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Am 02.03.2011 um 17:31 schrieb Jeremy Bauer:
> Hi Marc,
>
> I'm wondering if this isn't caused by an inconsistent parameter type mapping
> as a result of using the QuerySQLCache. I saw a similar issue a few weeks
> ago, but it was oddly specific to DB2 on zOS. Please try:
>
> <property name="openjpa.jdbc.QuerySQLCache" value="false"/>
>
> -Jeremy
>
> On Wed, Mar 2, 2011 at 9:43 AM, Marc Logemann <li...@logemann.org> wrote:
>
>> Hi,
>>
>> i am totally astonished:
>>
>> I am using this query:
>>
>> public List<Order> findByDate(Date date) {
>>
>> TypedQuery<Order> query = getEntityManager().
>> createQuery("select o FROM Order o where o.createdYmd = ?1
>> order by o.id", Order.class);
>>
>> query.setParameter(1, date, TemporalType.DATE);
>> return query.getResultList();
>> }
>>
>> This query is called by a service class which is scheduled ever 30 seconds.
>> See the caller:
>>
>> // get yesterdays Date
>> Date today = new Date();
>> Calendar calendar = Calendar.getInstance();
>> calendar.setTime(today);
>> calendar.add(Calendar.DATE, -1);
>> Date yesterday = calendar.getTime();
>>
>> List<Order> list = orderDao.findByDate(yesterday);
>>
>>
>> Now, on the second run, the query parameter is of type timestamp with full
>> time specified, giving me 0 records of course. First query returns records
>> because there TemporalType seems to work. See log.
>>
>> FIRST RUN ->
>>
>> [DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing
>> prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
>> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
>> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
>> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
>> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
>> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype,
>> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
>> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox,
>> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
>> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
>> t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender,
>> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street,
>> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
>> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby,
>> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid
>> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address
>> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
>> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype
>> IN (?)) ORDER BY t0.ordernr ASC [params=(Date) 2011-03-01, (int) 2]
>>
>> SECOND RUN ->
>>
>> [DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955> executing
>> prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
>> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
>> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
>> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
>> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
>> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype,
>> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
>> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox,
>> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
>> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
>> t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender,
>> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street,
>> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
>> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby,
>> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid
>> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address
>> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
>> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype
>> IN (?)) ORDER BY t0.ordernr ASC [params=(Timestamp) 2011-03-01 16:37:00.001,
>> (int) 2]
>>
>> I have completely no clue what to do now ;-) Thanks for input.
>>
>>
>>
>> ---
>> regards
>> Marc Logemann
>> http://www.logemann.org
>> http://www.logentis.de
>>
>>
>>
>>
>>
Re: same jpa query generates different SQLs
Posted by Jeremy Bauer <te...@gmail.com>.
Hi Marc,
I'm wondering if this isn't caused by an inconsistent parameter type mapping
as a result of using the QuerySQLCache. I saw a similar issue a few weeks
ago, but it was oddly specific to DB2 on zOS. Please try:
<property name="openjpa.jdbc.QuerySQLCache" value="false"/>
-Jeremy
On Wed, Mar 2, 2011 at 9:43 AM, Marc Logemann <li...@logemann.org> wrote:
> Hi,
>
> i am totally astonished:
>
> I am using this query:
>
> public List<Order> findByDate(Date date) {
>
> TypedQuery<Order> query = getEntityManager().
> createQuery("select o FROM Order o where o.createdYmd = ?1
> order by o.id", Order.class);
>
> query.setParameter(1, date, TemporalType.DATE);
> return query.getResultList();
> }
>
> This query is called by a service class which is scheduled ever 30 seconds.
> See the caller:
>
> // get yesterdays Date
> Date today = new Date();
> Calendar calendar = Calendar.getInstance();
> calendar.setTime(today);
> calendar.add(Calendar.DATE, -1);
> Date yesterday = calendar.getTime();
>
> List<Order> list = orderDao.findByDate(yesterday);
>
>
> Now, on the second run, the query parameter is of type timestamp with full
> time specified, giving me 0 records of course. First query returns records
> because there TemporalType seems to work. See log.
>
> FIRST RUN ->
>
> [DEBUG myScheduler-3 16:36:30] | <t 941878577, conn 650647154> executing
> prepstmnt 1488869003 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype,
> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox,
> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
> t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender,
> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street,
> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby,
> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid
> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address
> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype
> IN (?)) ORDER BY t0.ordernr ASC [params=(Date) 2011-03-01, (int) 2]
>
> SECOND RUN ->
>
> [DEBUG myScheduler-2 16:37:00] | <t 1835085919, conn 1503676955> executing
> prepstmnt 154018541 SELECT t0.oid, t0.`_version`, t1.oid, t1.`_version`,
> t1.cleared, t1.id, t1.lastused, t2.oid, t2.`_version`, t2.created,
> t2.createdymd, t2.custnr, t2.greenoption, t2.ordernr, t2.invaddress_oid,
> t2.iscardowner, t2.cost, t2.currency, t2.pricing, t2.paymenttype,
> t2.printed, t2.printedby, t2.totalprice, t1.boxtype, t0.created,
> t0.createdymd, t0.custnr, t3.oid, t3.jpatype, t3.`_version`, t3.addresstype,
> t3.city, t3.company, t3.country, t3.department, t3.email, t3.firstname,
> t3.gender, t3.lastname, t3.middlename, t3.phone, t3.zip, t3.postofficebox,
> t3.street, t3.housenr, t3.title, t3.deliverymode, t0.greenoption,
> t0.ordernr, t4.oid, t4.jpatype, t4.`_version`, t4.addresstype, t4.city,
> t4.company, t4.country, t4.department, t4.email, t4.firstname, t4.gender,
> t4.lastname, t4.middlename, t4.phone, t4.zip, t4.postofficebox, t4.street,
> t4.housenr, t4.title, t4.deliverymode, t4.order_oid, t0.iscardowner,
> t0.cost, t0.currency, t0.pricing, t0.paymenttype, t0.printed, t0.printedby,
> t0.totalprice FROM orders t0 LEFT OUTER JOIN boxes t1 ON t0.box_oid = t1.oid
> LEFT OUTER JOIN address t3 ON t0.oid = t3.order_oid LEFT OUTER JOIN address
> t4 ON t0.invaddress_oid = t4.oid LEFT OUTER JOIN orders t2 ON t1.oid =
> t2.box_oid WHERE (t0.createdymd = ?) AND (t3.jpatype IS NULL OR t3.jpatype
> IN (?)) ORDER BY t0.ordernr ASC [params=(Timestamp) 2011-03-01 16:37:00.001,
> (int) 2]
>
> I have completely no clue what to do now ;-) Thanks for input.
>
>
>
> ---
> regards
> Marc Logemann
> http://www.logemann.org
> http://www.logentis.de
>
>
>
>
>