[MonetDB-users] offset support?
Is there support for OFFSET in queries?
The syntax doesn't throw an error, but the expected behavior isn't correct...
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.820 msec 7 rows sql> Timer 0.294 msec 0 rows sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more> LIMIT 3 OFFSET 2; +---------+----------+ |user_id |user_name | +=========+==========+ | 1 |john | | 2 |jack | | 3 |ed | +---------+----------+
-matt
On Nov 26, 2007 10:47 AM, Fabian Groffen Fabian.Groffen@cwi.nl wrote:
On 26-11-2007 10:40:53 -0700, m h wrote:
Is there support for OFFSET in queries?
It used to be there. Have you tried select bla offset 2 limit 3 instead already?
You mean this?
sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more>OFFSET 2 LIMIT 3 ; !syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION in: "select query_users.user_id, query_users.user_name !from query_users order by query_users.user_id !offset 2 limit" Timer 892.177 msec 0 rows
On Nov 26, 2007 10:51 AM, m h sesquile@gmail.com wrote:
On Nov 26, 2007 10:47 AM, Fabian Groffen Fabian.Groffen@cwi.nl wrote:
On 26-11-2007 10:40:53 -0700, m h wrote:
Is there support for OFFSET in queries?
It used to be there. Have you tried select bla offset 2 limit 3 instead already?
You mean this?
sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more>OFFSET 2 LIMIT 3 ; !syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION in: "select query_users.user_id, query_users.user_name !from query_users order by query_users.user_id !offset 2 limit" Timer 892.177 msec 0 rows
It appears that the "ORDER BY" is throwing things off. Bug?
On Tue, Nov 27, 2007 at 11:52:23AM -0700, m h wrote:
On Nov 26, 2007 10:51 AM, m h sesquile@gmail.com wrote:
On Nov 26, 2007 10:47 AM, Fabian Groffen Fabian.Groffen@cwi.nl wrote:
On 26-11-2007 10:40:53 -0700, m h wrote:
Is there support for OFFSET in queries?
It used to be there. Have you tried select bla offset 2 limit 3 instead already?
You mean this?
sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more>OFFSET 2 LIMIT 3 ; !syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION in: "select query_users.user_id, query_users.user_name !from query_users order by query_users.user_id !offset 2 limit" Timer 892.177 msec 0 rows
It appears that the "ORDER BY" is throwing things off. Bug?
This bug is fixed now. Both the case with and with out order by gave wrong results. It needed some work in the optimizer which was push 'renumber' statements down the tree which doesn't work with offsets.
(only available from cvs tho).
Niels
This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Nov 27, 2007 2:33 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Tue, Nov 27, 2007 at 11:52:23AM -0700, m h wrote:
On Nov 26, 2007 10:51 AM, m h sesquile@gmail.com wrote:
On Nov 26, 2007 10:47 AM, Fabian Groffen Fabian.Groffen@cwi.nl wrote:
On 26-11-2007 10:40:53 -0700, m h wrote:
Is there support for OFFSET in queries?
It used to be there. Have you tried select bla offset 2 limit 3 instead already?
You mean this?
sql>SELECT query_users.user_id, query_users.user_name more>FROM query_users ORDER BY query_users.user_id more>OFFSET 2 LIMIT 3 ; !syntax error, unexpected LIMIT, expecting INTERSECT or EXCEPT or UNION in: "select query_users.user_id, query_users.user_name !from query_users order by query_users.user_id !offset 2 limit" Timer 892.177 msec 0 rows
It appears that the "ORDER BY" is throwing things off. Bug?
This bug is fixed now. Both the case with and with out order by gave wrong results. It needed some work in the optimizer which was push 'renumber' statements down the tree which doesn't work with offsets.
(only available from cvs tho).
WOW!
That was quick.
Thanks much
On Nov 27, 2007 2:40 PM, m h sesquile@gmail.com wrote:
It appears that the "ORDER BY" is throwing things off. Bug?
This bug is fixed now. Both the case with and with out order by gave wrong results. It needed some work in the optimizer which was push 'renumber' statements down the tree which doesn't work with offsets.
(only available from cvs tho).
Still has a bug.
OFFSET by itself (without a LIMIT clause) doesn't work correctly. The second query here should only 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 | +---------+----------+
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 | +---------+----------+
Have opened bug 1841896
On Fri, Nov 30, 2007 at 12:02:23PM -0700, m h wrote:
On Nov 27, 2007 2:40 PM, m h sesquile@gmail.com wrote:
It appears that the "ORDER BY" is throwing things off. Bug?
This bug is fixed now. Both the case with and with out order by gave wrong results. It needed some work in the optimizer which was push 'renumber' statements down the tree which doesn't work with offsets.
(only available from cvs tho).
Still has a bug.
OFFSET by itself (without a LIMIT clause) doesn't work correctly. The second query here should only 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 | +---------+----------+
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 | +---------+----------+
Have opened bug 1841896
This one is fixed now. The offset handling without limit was missing.
Niels
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-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Sat, Dec 01, 2007 at 09:56:00PM -0700, m h wrote:
This one is fixed now. The offset handling without limit was missing.
Stable (today Dec 1st 9pm MST) didn't build for me.
Indeed (now it should).
So I tried Current. I'm not sure if changes are pushed to current as well, but this is still broken in currnet.
Yes but wit a delay. And indeed these aren't pushed to current yet.
Niels
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-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Dec 1, 2007 11:56 PM, Niels Nes Niels.Nes@cwi.nl wrote:
On Sat, Dec 01, 2007 at 09:56:00PM -0700, m h wrote:
This one is fixed now. The offset handling without limit was missing.
Stable (today Dec 1st 9pm MST) didn't build for me.
Indeed (now it should).
So I tried Current. I'm not sure if changes are pushed to current as well, but this is still broken in currnet.
Yes but wit a delay. And indeed these aren't pushed to current yet.
OFFSET with ORDER BY and with no LIMIT still fails on todays stable build.
participants (3)
-
Fabian Groffen -
m h -
Niels Nes