0002821: amber: clean query caching sql

Viewing Issue Advanced Details [ Jump to Notes ]

[ View Simple ] [ Issue History ] [ Print ]

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

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[^]

28 total queries executed.
25 unique queries executed.