Runaway SQL optimizer in too many nested operators

[monetdb-team]

Date: 2017-04-23 08:18:16 +0200
From: @mlkersten
To: SQL devs <>
Version: 11.25.15 (Dec2016-SP3)
CC: @njnes

Last updated: 2017-07-17 16:07:25 +0200

Comment 25261

Date: 2017-04-23 08:18:16 +0200
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:52.0) Gecko/20100101 Firefox/52.0
Build Identifier:

Create a table over which we run a small complex query.
Then wrapping the same text in a function causes the
the system to complain about 'too many nested operators'.

Looks like an infinite loop in rel_logical_value()

Test file added

0 sql_error (sql=0x7f1e3c108250, error_code=10, format=0x7f1e6c99cd90 "SELECT: too many nested operators")
at /export/scratch1/mk/Dec2016//package/sql/server/sql_parser.y:6023
1 0x00007f1e6c8bbb63 in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:1870
2 0x00007f1e6c8c8a00 in rel_value_exp2 (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=..., is_last=0x7f1e52e05dac)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4461
3 0x00007f1e6c8c8ab2 in rel_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=...)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4476
4 0x00007f1e6c8bdcee in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:2284
5 0x00007f1e6c8c8a00 in rel_value_exp2 (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=..., is_last=0x7f1e52e0615c)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4461
6 0x00007f1e6c8c8ab2 in rel_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=...)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4476
7 0x00007f1e6c8bdcee in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:2284
8 0x00007f1e6c8c8a00 in rel_value_exp2 (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=..., is_last=0x7f1e52e0650c)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4461
9 0x00007f1e6c8c8ab2 in rel_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=...)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4476
10 0x00007f1e6c8bdcee in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:2284
11 0x00007f1e6c8c8a00 in rel_value_exp2 (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=..., is_last=0x7f1e52e068bc)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4461
12 0x00007f1e6c8c8ab2 in rel_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=...)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4476
13 0x00007f1e6c8bdcee in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:2284
14 0x00007f1e6c8c8a00 in rel_value_exp2 (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=..., is_last=0x7f1e52e06c6c)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4461
15 0x00007f1e6c8c8ab2 in rel_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, se=0x7f1e3c0dbc30, f=2, ek=...)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:4476
16 0x00007f1e6c8bdcee in rel_logical_value_exp (sql=0x7f1e3c108250, rel=0x7f1e53001588, sc=0x7f1e3c0dbc30, f=2)
at /export/scratch1/mk/Dec2016//package/sql/server/rel_select.c:2284

Reproducible: Always

Comment 25262

Date: 2017-04-23 08:21:26 +0200
From: MonetDB Mercurial Repository <>

Changeset 3f6e864df6b4 made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #6292

Comment 25296

Date: 2017-05-11 15:26:21 +0200
From: MonetDB Mercurial Repository <>

Changeset 9bb37272aa9f made by Martin van Dinther martin.van.dinther@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Add missing nestedoperators.Bug-6292.stable.* files for Bug #6292

Comment 25375

Date: 2017-06-07 15:09:09 +0200
From: MonetDB Mercurial Repository <>

Changeset 16a4efa7b3dc 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=16a4efa7b3dc

Changeset description:

fixed nested operators bug #6292 (ie allow with in function creation)

Comment 25376

Date: 2017-06-07 15:09:45 +0200
From: @njnes

added missing handling of with in function creation