Mantis Bugtracker
  

Viewing Issue Advanced Details Jump to Notes ] View Simple ] Issue History ] Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0002821 [Resin] minor always 08-05-08 07:14 08-11-08 09:32
Reporter ferg View Status public  
Assigned To ferg
Priority normal Resolution fixed Platform
Status closed   OS
Projection none   OS Version
ETA none Fixed in Version 3.2.1 Product Version 3.2.0
  Product Build
Summary 0002821: amber: clean query caching sql
Description (rep by Riccardo Cohen)


      javax.persistence.Query hqr=m_manager.createQuery("select h from Adluser h order by h.id");
      log.info("start="+numstart+" cnt="+numcnt);
      hqr.setFirstResult(numstart);
      hqr.setMaxResults(numcnt);
      ret=(List<Adluser>)hqr.getResultList();

I have this :

if start = 0 and count=5 the sql request is

[14:40:49.245] {http--8000-7} start=0 cnt=5
[14:40:49.260] {http--8000-7} adlresintest.1.0:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25,type=1003,concurrency=1007)
[14:40:49.260] {http--8000-7} adlresintest.1.0:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25)
[14:40:49.267] {http--8000-7} adlresintest.1:isClosed() -> false
[14:40:49.267] {http--8000-7} adlresintest.1.1:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid,type=1003,concurrency=1007)
[14:40:49.267] {http--8000-7} adlresintest.1.1:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid)

if start = 0 and count=500

[14:38:01.530] {http--8000-6} start=0 cnt=500
[14:38:01.531] {http--8000-6} adlresintest.0.16:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25,type=1003,concurrency=1007)
[14:38:01.531] {http--8000-6} adlresintest.0.16:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25)
[14:38:01.533] {http--8000-6} adlresintest.0:isClosed() -> false
[14:38:01.535] {http--8000-6} adlresintest.0.17:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25, 475,type=1003,concurrency=1007)
[14:38:01.535] {http--8000-6} adlresintest.0.17:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25, 475)

if start = 22 and count=5 the sql request is

[14:43:36.249] {http--8000-15} start=22 cnt=5
[14:43:36.249] {http--8000-15} adlresintest.2.2:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25,type=1003,concurrency=1007)
[14:43:36.257] {http--8000-15} adlresintest.2.2:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25)
[14:43:36.260] {http--8000-15} adlresintest.2:isClosed() -> false
[14:43:36.260] {http--8000-15} adlresintest.2.3:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25, 2,type=1003,concurrency=1007)
[14:43:36.260] {http--8000-15} adlresintest.2.3:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 25, 2)


This creates 2 requests instead of one if start<25, and the LIMIT clause is not always what needed. Is there any particular reason for that ?

But now if start>25 :

[14:44:43.237] {http--8000-4} start=26 cnt=5
[14:44:43.237] {http--8000-4} adlresintest.3.2:prepareStatement(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 26, 5,type=1003,concurrency=1007)
[14:44:43.238] {http--8000-4} adlresintest.3.2:executeQuery(select h.adlid, h.adlfirstname, h.adlname from adluser h order by h.adlid LIMIT 26, 5)

Here there is only one request and it seems correct (the select works in mysql console), but the resulting list is empty in java.

Steps To Reproduce
Additional Information
Attached Files

- Relationships

- Notes
(0003312)
ferg
08-11-08 09:14

jpa/1434
 

- Issue History
Date Modified Username Field Change
08-05-08 07:14 ferg New Issue
08-11-08 09:14 ferg Note Added: 0003312
08-11-08 09:32 ferg Assigned To  => ferg
08-11-08 09:32 ferg Status new => closed
08-11-08 09:32 ferg Resolution open => fixed
08-11-08 09:32 ferg Fixed in Version  => 3.2.1


Mantis 1.0.0rc3[^]
Copyright © 2000 - 2005 Mantis Group
28 total queries executed.
25 unique queries executed.
Powered by Mantis Bugtracker