Bugs item #1841896, was opened at 2007-11-30 11:00 Message generated for change (Comment added) made by matt_harrison You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1841896&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: Core Group: MonetDB5 5.2 Status: Open Resolution: None Priority: 5 Private: No Submitted By: mharrison (matt_harrison) Assigned to: Nobody/Anonymous (nobody) Summary: LIMIT / OFFSET support still broken Initial Comment: It appears that OFFSET with no LIMIT clause is still broken. I think the last query below should return (3,ed)...(7,fido): sql>select * from query_users ; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | | 4 |wendy | | 5 |laura | | 6 |ralph | | 7 |fido | +---------+----------+ Timer 1.049 msec 7 rows sql>select * from query_users LIMIT 3 OFFSET 2; +---------+----------+ |user_id |user_name | +=========+==========+ | 3 |ed | | 4 |wendy | | 5 |laura | +---------+----------+ Timer 1.378 msec 3 rows sql>select * from query_users LIMIT 3; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | +---------+----------+ Timer 1.142 msec 3 rows sql>select * from query_users OFFSET 2; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | | 4 |wendy | | 5 |laura | | 6 |ralph | | 7 |fido | +---------+----------+ Timer 1.022 msec 7 rows ---------------------------------------------------------------------- >Comment By: mharrison (matt_harrison) Date: 2007-11-30 11:14 Message: Logged In: YES user_id=1164236 Originator: YES Some other interesting queries.... Here's a hack that appears to work (specify a large number instead of -1 or no LIMIT clause): sql>select * from query_users LIMIT 100000 OFFSET 5; +---------+----------+ |user_id |user_name | +=========+==========+ | 6 |ralph | | 7 |fido | +---------+----------+ But if you add in a ORDER BY it fails.... sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more> LIMIT 1000000 OFFSET 5 more>; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | | 4 |wendy | | 5 |laura | | 6 |ralph | | 7 |fido | +---------+----------+ ---------------------------------------------------------------------- Comment By: mharrison (matt_harrison) Date: 2007-11-30 11:07 Message: Logged In: YES user_id=1164236 Originator: YES Some other dbs support LIMIT -1 in this instance.... ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1841896&group_id=56967 ------------------------------------------------------------------------- SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
