JDBC bug with limit and offset?
I'm running MonetDB on Ubuntu 10.04, with Monet built from the Oct2012_release http://dev.monetdb.org/hg/MonetDB/rev/3c157ea4be53 tag. I have a client program that connects using the JDBC driver and issues a simple SQL query using limit and offset. I see that: - limit and offset work when using literal parameters in the SQL string - limit works by itself when using a bind parameter - offset works by itself when using a bind parameter - when specifying both limit and offset and using a bind parameter for either or both, the merovingian segfaults with a message like this: database 'click' (2428) was killed by signal SIGSEGV Examples of queries that work: SELECT * FROM the_table LIMIT 5 OFFSET 100; SELECT * FROM the_table LIMIT ?; SELECT * FROM the_table OFFSET ?; Example of queries that don't work: SELECT * FROM the_table LIMIT ? OFFSET ?; SELECT * FROM the_table LIMIT 5 OFFSET ?; SELECT * FROM the_table LIMIT ? OFFSET 100; I've tried passing in the limit and offset as 64 bit integers (long), 32 bit integers (int) and strings, and none of these work. I also tried downgrading to MonetDB Database Server v1.6 (Jul2012-SP2) but had the same issue. Has anyone else run into this? Is this a new bug that I should file? Thanks, Percy Wegmann _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
On 02-11-2012 11:01:02 -0500, Percy Wegmann wrote:
Is this a new bug that I should file?
Yes. Thanks, Fabian _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Thanks, it's filed:
http://bugs.monetdb.org/show_bug.cgi?id=3173
I documented the workaround, which is to not use bind parameters for limit
and offset. The major impact here is that it prevents effective caching of
the compiled query, since each combo of limit/offset ends up looking like a
different query.
Cheers,
Percy
On Fri, Nov 2, 2012 at 11:04 AM, Fabian Groffen
On 02-11-2012 11:01:02 -0500, Percy Wegmann wrote:
Is this a new bug that I should file?
Yes.
Thanks, Fabian
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
On 02-11-2012 11:18:24 -0500, Percy Wegmann wrote:
Thanks, it's filed:
Thanks.
I documented the workaround, which is to not use bind parameters for limit and offset. The major impact here is that it prevents effective caching of the compiled query, since each combo of limit/offset ends up looking like a different query.
Not sure about that. The SQL compiler already factors out constants for queries before it caches them, e.g. select x from t where a > 10 uses the same plan as select x from t where a > 3. Fabian _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Clever! That definitely makes this low priority in my mind. I've updated
the bug accordingly.
Cheers,
Percy
On Fri, Nov 2, 2012 at 11:37 AM, Fabian Groffen
On 02-11-2012 11:18:24 -0500, Percy Wegmann wrote:
Thanks, it's filed:
Thanks.
I documented the workaround, which is to not use bind parameters for limit and offset. The major impact here is that it prevents effective caching of the compiled query, since each combo of limit/offset ends up looking like a different query.
Not sure about that. The SQL compiler already factors out constants for queries before it caches them, e.g. select x from t where a > 10 uses the same plan as select x from t where a > 3.
Fabian
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- Percy Wegmann +1 512 637 8500 ext 148 _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
On 02-11-2012 12:37:06 -0500, Percy Wegmann wrote:
Clever! That definitely makes this low priority in my mind. I've updated the bug accordingly.
It's a crash, so performance or not, it should be fixed. Fabian _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Fabian Groffen -
Percy Wegmann