Issue Details (XML | Word | Printable)

Key: NUCRDBMS-280
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Richard Sang
Votes: 0
Watchers: 0
Operations

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

JPA Query.setMaxResults and Query.setFirstResult not working as expected for Oracle

Created: 27/Oct/09 09:07 PM   Updated: 26/Dec/09 12:57 PM   Resolved: 28/Oct/09 08:27 PM
Component/s: Queries
Affects Version/s: 2.0.0.m3
Fix Version/s: 2.0.0.m4

File Attachments: 1. Zip Archive example.zip (2 kB)

Environment: Windows XP SP3, JDK 1.5, Oracle 10g

Datastore: Oracle
Severity: Development


 Description  « Hide
JPA Query functions setMaxResults and setFirstResult are not working as expected for Oracle, no problem with HSQLDB.

Example:

        Query query = entityManager.createQuery("select s from Person s order by s.id");
        query.setMaxResults(10);
        query.setFirstResult(50);
        List list = query.getResultList();
        assertEquals(list.size(), 10);

The query is returning total 60 (50+10) results, not 10 as expected.

Log info is as following:
[DEBUG] Datastore.Native SELECT 'test.models.Person' AS NUCLEUS_TYPE,S.ID AS NUCORDER0,S.FIRST_NAME,S.LAST_NAME FROM PERSON S ORDER BY NUCORDER0


Below is Person entity:
@Entity
public class Person implements Serializable {

    @Id
    private long id;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Column(name="first_name")
    private String firstName;

    @Column(name="last_name")
    private String lastName;

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }


    public String toString() {
        return lastName + ", " + firstName;
    }
}


Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 27/Oct/09 09:19 PM
The docs define the testcase format that is accepted. Please define a testcase that reproduces this meeting that format, ... since JPA works perfectly well with the JPA TCK and there is nothing datastore specific in the SQL (as you can confirm yourself).

Richard Sang added a comment - 28/Oct/09 04:32 PM
Sorry, I did not look at the instruction carefully.

Attached is the example to show the error.

As I said, this issue is only observed on Oracle; There is no problem with HSQLDB. I saw the native log message with HSQLDB is different from the one with Oracle:

HSQLDB: [DEBUG] Datastore.Native SELECT LIMIT 50 10 'test.models.Person' AS NUCLEUS_TYPE,S.FIRST_NAME,S.ID AS NUCORDER0,S.LAST_NAME FROM PERSON S ORDER BY NUCORDER0

"LIMIT 50 10" is not generated for Oracle.

Thanks,
Richard

Andy Jefferson added a comment - 28/Oct/09 08:27 PM
SVN trunk reinstates the applyRangeChecks() method to legacy JPQL.
JPQL2 was not affected.