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

[fisherab]

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.krahl@helmholtz-berlin.de on 2014-12-11 13:23:43

[fisherab]

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 issue reported on code.google.com by dr.s.m.fisher on 2014-12-11 15:28:29

[fisherab]

Wrong behaviour confirmed by test

Original issue reported on code.google.com by dr.s.m.fisher on 2014-12-15 10:15:16

[fisherab]

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 issue reported on code.google.com by dr.s.m.fisher on 2014-12-15 11:37:17

[fisherab]

The meeting decided that it should be fixed.

Original issue reported on code.google.com by dr.s.m.fisher on 2014-12-18 17:37:10

[fisherab]

(No text was entered with this change)

Original issue reported on code.google.com by dr.s.m.fisher on 2015-05-06 09:23:46

• Labels added: Milestone-4.5

[RKrahl]

It looks like this old bug has reappeared:

>>> client.getApiVersion()
4.8.0
>>> client.search("SELECT o FROM Dataset o WHERE o.id = 616 LIMIT 200,100")
[(dataset){
   createId = "simple/root"
   createTime = 2017-01-03 13:34:08+00:00
   id = 616
   modId = "simple/root"
   modTime = 2017-01-03 13:34:08+00:00
   complete = False
   name = "test_02_simpleupload"
 }]

[fisherab]

I don't remember changing the test that should have detected this. I did throw out a lot of old code because of moving as much work as possible to the database. The difficulty is recognising the query which needs fixing. The simple one listed in the example is easy but it it may be in a subselect clause which then requires considerably more analysis. I will reopen the issue to keep a track of it.