Response to PREPARE emtpy if query contains a LIMIT

[monetdb-team]

Date: 2013-10-08 09:10:05 +0200
From: @hannesmuehleisen
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-12-03 13:59:33 +0100

Comment 19252

Date: 2013-10-08 09:10:05 +0200
From: @hannesmuehleisen

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.37 Safari/537.36
Build Identifier:

The response to the PREPARE ... statement usually contains the schema of the response to the prepared query. However, if the query contains a LIMIT, the response is empty.

Reproducible: Always

Steps to Reproduce:

1. PREPARE SELECT * FROM tables LIMIT 42;

Actual Results:

sql>prepare select * from tables limit 42;
execute prepared statement using: EXEC 4(...)
+------+--------+-------+--------+-------+--------+
| type | digits | scale | schema | table | column |
+======+========+=======+========+=======+========+
+------+--------+-------+--------+-------+--------+
0 tuples (2.089ms)

Expected Results:

+----------+--------+-------+--------+--------+---------------+
| type | digits | scale | schema | table | column |
+==========+========+=======+========+========+===============+
| int | 32 | 0 | | tables | id |
| varchar | 1024 | 0 | | tables | name |
| int | 32 | 0 | | tables | schema_id |
| varchar | 2048 | 0 | | tables | query |
| smallint | 16 | 0 | | tables | type |
| boolean | 1 | 0 | | tables | system |
| smallint | 16 | 0 | | tables | commit_action |
| boolean | 1 | 0 | | tables | readonly |
| tinyint | 8 | 0 | | tables | temporary |
+----------+--------+-------+--------+--------+---------------+
9 tuples (2.035ms)

Comment 19253

Date: 2013-10-08 09:11:58 +0200
From: @hannesmuehleisen

Maybe also allow ORDER BY/LIMIT OFFSET in subqueries for compatibility reasons as this is fixed.

Comment 19257

Date: 2013-10-08 16:15:41 +0200
From: @sjoerdmullender

The problem lies in mvc_export_prepare which checks whether the query being prepared is an op_project, and if so generates the table with information about the result set.
In the case of a select with limit, the query is an op_topn, about which the code knows nothing.
I guess there are also other possibilities.

Comment 19258

Date: 2013-10-09 09:33:32 +0200
From: @njnes

We won't support order by/limit offset in subqueries, only in inserts. This isn't likely to change. Sofar no examples exists to why this would be needed.

Comment 19259

Date: 2013-10-09 09:47:04 +0200
From: @njnes

fixed by properly handling the topn case in the mvc_export_prepare code. The exec-ute code allready handled this case.

Comment 19261

Date: 2013-10-09 11:51:16 +0200
From: MonetDB Mercurial Repository <>

Changeset 46ab29b898c2 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=46ab29b898c2

Changeset description:

fixed bug #3382 (added test to Bug #2552, which are very related)

Comment 19379

Date: 2013-12-03 13:59:33 +0100
From: @sjoerdmullender

Feb2013-SP6 has been released.