Date: 2012-11-02 17:17:18 +0100
From: Percy Wegmann <>
To: @njnes
Version: 11.13.3 (Oct2012)
CC: bugs-sql, @njnes, s.idreos

Last updated: 2013-01-22 09:29:18 +0100

Comment 17866

Date: 2012-11-02 17:17:18 +0100
From: Percy Wegmann <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.4 (KHTML, like Gecko) Chrome/22.0.1229.94 Safari/537.4
Build Identifier:

I'm running MonetDB on Ubuntu 10.04, with Monet built from the Oct2012_release 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.

Reproducible: Always

Steps to Reproduce:

Issue a query like this:

Issue a query like any of the following:

SELECT * FROM the_table LIMIT ? OFFSET ?;
SELECT * FROM the_table LIMIT 5 OFFSET ?;
SELECT * FROM the_table LIMIT ? OFFSET 100;

Actual Results:

the merovingian segfaults with a message like this:

database 'click' (2428) was killed by signal SIGSEGV

Expected Results:

It should have executed the query with the appropriate LIMIT and OFFSET set

The workaround for this bug is to not use bind parameters for limit and offset clauses in queries. The downside to this is that differ limit/offset combinations result in different query strings, which I presume prevents Monet from effectively caching what is really just 1 query.

Comment 17867

Date: 2012-11-02 18:36:29 +0100
From: Percy Wegmann <>

Sounds like the inability to use bind parameters may not be a performance issue.

Per Fabian:

"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."

Comment 18034

Date: 2012-11-27 11:38:42 +0100
From: Stratos Idreos <<s.idreos>>

In order to use bind variables and possibly prepare statements such as you avoid optimization cost in future queries you may use the PREPARE statement as follows:

PREPARE SELECT * FROM the_table LIMIT ? OFFSET ?;

monetdb will reply:
execute prepared statement using: EXEC Z(...)

where Z is a numeric id for this statement

you can later execute this prepared statement using
EXEC Z(variable1,variable2);

Comment 18059

Date: 2012-11-27 13:35:34 +0100
From: Percy Wegmann <>

This doesn't work. I tried on the Oct 2012 release and the Oct 2012 SP1 release.

Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012-SP1)
Database: MonetDB v11.13.5 (Oct2012-SP1), 'mapi:monetdb://percy-sandbox:50000/click'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE test_table(id BIGINT);
operation successful (29.824ms)
sql>PREPARE SELECT * FROM test_table LIMIT ? OFFSET ?;
Connection terminated

From merovingian.log:

2012-11-27 06:29:35 MSG merovingian[13398]: database 'click' (13403) was killed by signal SIGSEGV

GDB Output:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x7f524a0fa700 (LWP 13750)]
0x00007f524adbc9dd in ?? () from /usr/lib/monetdb5/lib_sql.so
(gdb) where
0 0x00007f524adbc9dd in ?? () from /usr/lib/monetdb5/lib_sql.so
1 0x00007f524adbe4a8 in ?? () from /usr/lib/monetdb5/lib_sql.so
2 0x00007f524adbbaf5 in ?? () from /usr/lib/monetdb5/lib_sql.so
3 0x00007f524adc50af in ?? () from /usr/lib/monetdb5/lib_sql.so
4 0x00007f524ad3b5e9 in sql_symbol2relation () from /usr/lib/monetdb5/lib_sql.so
5 0x00007f524ad2a6ed in SQLparser () from /usr/lib/monetdb5/lib_sql.so
6 0x00007f5252d4a50c in runPhase (phase=1, c=0x7f5253758338) at mal_scenario.c:522
7 runScenarioBody (c=0x7f5253758338) at mal_scenario.c:564
8 0x00007f5252d4b29f in runScenario (c=0x7f5253758338) at mal_scenario.c:601
9 0x00007f5252d4b348 in MSserveClient (dummy=0x7f5253758338) at mal_session.c:430
10 0x00007f52522fee9a in start_thread (arg=0x7f524a0fa700) at pthread_create.c:308
11 0x00007f525202bcbd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
12 0x0000000000000000 in ?? ()

Comment 18070

Date: 2012-11-27 14:07:31 +0100
From: @grobian

Yes, this needs a test (and a fix)

Comment 18099

Date: 2012-11-27 14:55:08 +0100
From: @njnes

crashed in the optimizer. Now we handle the limit/offset expressions with more care (ie we don't assume values, but expressions). So we generate an addition expression instead of a direct value addition.

Comment 18103

Date: 2012-11-27 15:03:20 +0100
From: @njnes

Changeset ba23cef882fc made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=ba23cef882fc

Changeset description:

fixed bug #3173

Comment 18383

Date: 2013-01-22 09:29:18 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.