LIMIT clause in the query has no effect when searching by id

[GoogleCodeExporter]

What steps will reproduce the problem?
Search for an item by its id, including a LIMIT clause with a non zero skip:
>>> client.search("SELECT i FROM Investigation i WHERE i.id = 22179 LIMIT 1,10")

What is the expected output? What do you see instead?
Expect the empty list in any case, since search by id by itself may at most 
yield one result, skipping the first by the LIMIT clause should yield none.  
The actual result was the one investigation having the given id.  The LIMIT 
clause is ignored.

Original issue reported on code.google.com by rolf.kr...@helmholtz-berlin.de on 11 Dec 2014 at 1:23

[GoogleCodeExporter]

This one is strange. I see nothing obviously wrong in the code. I will write a 
little test to add to the large set of tests I currently have for LIMIT. I 
can't do this immediately as my test ICAT is busy doing other things.

Original comment by dr.s.m.f...@gmail.com on 11 Dec 2014 at 3:28

[GoogleCodeExporter]

Wrong behaviour confirmed by test

Original comment by dr.s.m.f...@gmail.com on 15 Dec 2014 at 10:15

• Changed state: Accepted

[GoogleCodeExporter]

This seems to be an eclipselink bug. With a similar query but restricting on 
the name instead of the id the generated SQL includes a limit clause but in the 
case of the id no such limit is generated. If you replace i.id = 22179 by i.id 
in (22179) it works. It appears to be that selecting with equality on the 
primary key is treated as a special case. I will see how it behaves with ORACLE 
- the generated SQL must be different as  ORACLE does not have a LIMIT keyword. 
I could detect this special case in the code - I will bring it up at this 
week's meeting.

Original comment by dr.s.m.f...@gmail.com on 15 Dec 2014 at 11:37

• Changed state: Started

[GoogleCodeExporter]

The meeting decided that it should be fixed.

Original comment by dr.s.m.f...@gmail.com on 18 Dec 2014 at 5:37