SQL optimizer enters loop and goes into stack overflow

[monetdb-team]

Date: 2019-04-26 13:50:53 +0200
From: @swingbit
To: SQL devs <>
Version: 11.31.13 (Aug2018-SP2)
CC: @njnes

Last updated: 2019-09-02 16:05:28 +0200

Comment 26975

Date: 2019-04-26 13:50:53 +0200
From: @swingbit

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

The script in attachment contains a partial DB schema and a query. No data.

It makes MonetDB (Aug2018-SP2) crash.

According to gdb the SQL optimizer calls function flatten_dep in an endless loop, eventually crashing:

...
...
32663 0x00007f7311917ff6 in flatten_dep (nrefs=0x7f72e4334170, refs=0x7f72e4331660, nr=0, deps=0x7f72e4334150 "", n=5) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6361
32664 0x00007f7311917ff6 in flatten_dep (nrefs=0x7f72e4334170, refs=0x7f72e4331660, nr=3, deps=0x7f72e4334150 "", n=5) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6361
32665 0x00007f7311917ff6 in flatten_dep (nrefs=0x7f72e4334170, refs=0x7f72e4331660, nr=0, deps=0x7f72e4334150 "", n=5) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6361
32666 0x00007f7311917ff6 in flatten_dep (nrefs=0x7f72e4334170, refs=0x7f72e4331660, nr=3, deps=0x7f72e4334150 "", n=5) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6361
32667 0x00007f7311917ff6 in flatten_dep (nrefs=0x7f72e4334170, refs=0x7f72e4331660, nr=0, deps=0x7f72e4334150 "", n=5) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6361
32668 0x00007f731191812a in flatten_dep_graph (sql=0x7f72e4006bb0, deps=0x7f72e4334150 "", refs=0x7f72e4331660) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6377
32669 0x00007f7311918290 in rel_dependencies (sql=0x7f72e4006bb0, refs=0x7f72e4331660) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6395
32670 0x00007f7311918cc7 in rel_dce (sql=0x7f72e4006bb0, rel=0x7f72e41f47a0) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:6661
32671 0x00007f7311924b61 in optimize_rel (sql=0x7f72e4006bb0, rel=0x7f72e41f47a0, g_changes=0x7f72ed09c890, level=0, value_based_opt=1) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:9372
32672 0x00007f7311924df4 in optimize (sql=0x7f72e4006bb0, rel=0x7f72e41f47a0, value_based_opt=1) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:9445
32673 0x00007f7311924e6b in rel_optimizer (sql=0x7f72e4006bb0, rel=0x7f72e41f47a0, value_based_opt=1) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/server/rel_optimizer.c:9453
32674 0x00007f73117eee0c in sql_symbol2relation (c=0x7f72e4006bb0, sym=0x7f72e4390df0) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/backends/monet5/sql.c:122
32675 0x00007f731180fe56 in SQLparser (c=0x7f73121d7368) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/sql/backends/monet5/sql_scenario.c:1202
32676 0x00007f7320d6a7a8 in runPhase (c=0x7f73121d7368, phase=1) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/mal/mal_scenario.c:510
32677 0x00007f7320d6a8b1 in runScenarioBody (c=0x7f73121d7368, once=0) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/mal/mal_scenario.c:532
32678 0x00007f7320d6ab7f in runScenario (c=0x7f73121d7368, once=0) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/mal/mal_scenario.c:569
32679 0x00007f7320d6c950 in MSserveClient (dummy=0x7f73121d7368) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/mal/mal_session.c:519
32680 0x00007f7320d6c412 in MSscheduleClient (command=0x7f72e4009170 '\333' <repeats 160 times>, "\300", challenge=0x7f72ed09cd2b "9ITGaphTOc", fin=0x7f72e400b2a0, fout=0x7f72e8002e50, protocol=PROTOCOL_9, blocksize=8190, compute_column_widths=0) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/mal/mal_session.c:397
32681 0x00007f7320e0ec00 in doChallenge (data=0x7f72e8000b30) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/monetdb5/modules/mal/mal_mapi.c:279
32682 0x00007f7320bcaa54 in thread_starter (arg=0x2626100) at /home/roberto/MonetDBServer/MonetDB.Spinque_Aug2018/src/gdk/gdk_system.c:492
32683 0x00007f731fdcb58e in start_thread () from /lib64/libpthread.so.0
32684 0x00007f731fcf8683 in clone () from /lib64/libc.so.6

Reproducible: Always

$ mserver5 --version
MonetDB 5 server v11.31.14 (64-bit, 128-bit integers)
This is an unreleased version
Copyright (c) 1993 - July 2008 CWI
Copyright (c) August 2008 - 2019 MonetDB B.V., all rights reserved
Visit https://www.monetdb.org/ for further information
Found 15.5GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.43 2019-02-23 (compiled with 8.43)
openssl: OpenSSL 1.1.1b FIPS 26 Feb 2019 (compiled with OpenSSL 1.1.1b FIPS 26 Feb 2019)
libxml2: 2.9.8 (compiled with 2.9.8)
Compiled by: roberto@photon.hq.spinque.com (x86_64-pc-linux-gnu)
Compilation: gcc -g3 -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wundef -Wformat=2 -Wformat-overflow=1 -Wno-format-truncation -Wno-format-nonliteral -Wno-cast-function-type -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wduplicated-cond -Wduplicated-branches -Wrestrict -Wnested-externs -Wno-char-subscripts -Wunreachable-code
Linking : /usr/bin/ld -m elf_x86_64 -Wl,-Bsymbolic-functions

Comment 26976

Date: 2019-04-26 13:53:00 +0200
From: @swingbit

Created attachment 617
SQL script to execute on empty DB

Attached file: bug6703.sql (application/sql, 7527 bytes)
Description: SQL script to execute on empty DB

Comment 26977

Date: 2019-05-01 12:45:57 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=c6c91400555f

Changeset description:

fixed bug #6703, ie only order the dependent referenced subqueries once

Comment 26978

Date: 2019-05-01 12:50:21 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=f90ce3b09681

Changeset description:

add test for bug #6703

Comment 26979

Date: 2019-05-01 12:51:57 +0200
From: @njnes

fixed in the april branch. Dependent subqueries are order only once now.