Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 328768

Summary:	setMaxResults(10) results an empty list on oracle11g database
Product:	z_Archived	Reporter:	veeren <acnt4reg>
Component:	Eclipselink	Assignee:	Nobody - feel free to take it <nobody>
Status:	CLOSED DUPLICATE	QA Contact:
Severity:	major
Priority:	P2	CC:	acnt4reg, christopher.delahunt, marcel.pokrandt, tom.ware
Version:	unspecified
Target Milestone:	---
Hardware:	PC
OS:	All
Whiteboard:

Description veeren

2010-10-26 15:24:11 EDT

Build Identifier: 2.1.1.v20100817-r8050

root cause : it generates an incorrect sql hint statement for oracle database
which results an empty list. 

Wrong: /*+ FIRST_ROWS */ 
Correct: /*+ FIRST_ROWS(n) */ 

DataBase: oracle11g
eclipselinkversion: eclipselink-2.1.1.v20100817-r8050

Reproducible: Always

Steps to Reproduce:
1. query.setFirstResult(0);
2. query.setMaxResults(10);
3. list = query.getResultList();
// fetching the empty list even though records are available.

Comment 1 Marcel Pokrandt

2010-11-11 18:35:03 EST

For me the query is generated the same ...

SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum  FROM (SELECT t1.ID AS a1, ... FROM ZBM t1 LEFT OUTER JOIN MEL t0 ON (t0.ID = t1.MESSAGE_ID) LEFT OUTER JOIN EGS t2 ON (t2.ID = t1.EVENT_ID) LEFT OUTER JOIN BES t3 ON (t3.ID = t1.EVENTDATA_ID)) a WHERE ROWNUM <= ?) WHERE rnum > ?
	bind => [10, 0]

But it works for me 

Eclipselink 2.1.1.v20100817-r805
Oracle 11g (11.2.0.1.0)

Comment 2 Marcel Pokrandt

2010-11-12 01:45:23 EST

But I want to add that he is correct with saying that the hint should be "FIRST_ROWS(n)" or "FIRST_ROWS_n" and not just "FIRST_ROWS"

http://www.dba-oracle.com/art_orafaq_oracle_first_rows_sql_optimization.htm

Comment 3 Tom Ware

2010-11-12 08:19:30 EST

Setting target and priority.  See the following page for the meanings of these fields:

http://wiki.eclipse.org/EclipseLink/Development/Bugs/Guidelines

Comment 4 Chris Delahunt

2011-02-28 11:02:41 EST

I am not sure wether to set this to works for me, as the query does return results (and another poster confirmed as well), or as a duplicate of the enhancement request:
https://bugs.eclipse.org/bugs/show_bug.cgi?id=282682

Please vote for the enhancement if that was what intended.

*** This bug has been marked as a duplicate of bug 282682 ***

Comment 5 Eclipse Webmaster

2022-06-09 10:34:39 EDT

The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink