Sqlitelogictest crash in complex SELECT query with IN operator

[monetdb-team]

Date: 2017-10-18 10:32:31 +0200
From: @PedroTadim
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2017-10-26 14:01:41 +0200

Comment 25751

Date: 2017-10-18 10:32:31 +0200
From: @PedroTadim

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

Mserver gets an assertion error while optimizing in the SQL layer a complex SELECT query with comparisons, AND, OR, BETWEEN and IN operators.

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);
2. SELECT pk FROM tab0 WHERE ((col1 <= 601.41 AND (((col0 IS NULL AND (col3 > 768 OR col3 > 89)) AND ((col0 BETWEEN 996 AND 960 OR col4 < 664.34 AND col1 >= 445.23 AND ((col3 > 823 AND ((col3 >= 663)))) AND col0 > 883 AND col4 >= 247.43 OR (col0 = 115))) AND (((col0 > 941 AND col1 > 559.36)) AND (col4 IS NULL OR col0 > 583 AND (col3 > 927) OR col1 > 616.82 OR col0 < 35 OR (col0 > 688 AND (((col0 < 595 AND (col4 > 228.67) OR (col0 = 651) AND (((col1 = 509.82))) AND ((((col0 > 416))) OR col1 > 84.53 AND col1 < 285.58 AND col0 IS NULL AND col1 < 672.99 OR col3 = 682 AND col0 < 708) AND ((((col0 IS NULL))) OR col0 IS NULL OR col3 < 153 AND (col0 BETWEEN 36 AND 310 AND (col1 = 774.43) OR col4 < 876.56 AND col3 < 539 AND (col0 BETWEEN 177 AND 119) AND col3 < 820 OR col3 >= 529 AND col3 >= 268 OR (col3 > 749) AND col3 BETWEEN 511 AND 711 AND (((col3 >= 337) OR col0 IS NULL AND col4 > 965.36 AND ((col3 < 227) AND (col4 < 99.43 AND col3 > 263 OR col3 > 830 AND ((((((col1 < 984.99)))) AND ((col0 > 236 OR col0 > 475 AND col0 >= 150 AND (col3 >= 78) AND (((col4 > 135.83))))))) OR col0 <= 331 AND col1 >= 757.56 OR (col0 < 776) OR ((col1 >= 625.78)) AND col0 IS NULL OR (col3 >= 758 OR (col0 < 140)) AND col3 IS NULL)) AND ((col0 < 107 OR (col3 < 647))) AND (col4 <= 35.83) AND ((((col3 > 959))) OR (col3 >= 425 OR col3 <= 824 AND col1 > 223.73 AND (col4 >= 408.86 AND col4 > 641.19 OR col4 <= 813.85 OR (col0 IS NULL)) AND (((col4 > 587.71))) AND (((col0 > 214 AND col3 > 316 AND ((col0 > 212) AND (col3 >= 606 AND col0 <= 213 OR col3 IS NULL AND col0 <= 529 AND ((col0 = 74))) OR ((((col3 BETWEEN 45 AND 763) OR col4 > 137.10))) AND (col1 < 931.44 AND col3 > 344) AND (col3 > 290 OR col4 < 663.75 AND col4 <= 216.96 AND col3 < 982) AND col3 > 240 AND col1 >= 153.46 OR (col0 IN (540,371,358) AND col0 BETWEEN 299 AND 738) AND col0 < 694 AND ((col3 < 378 AND col3 < 506 AND col0 IS NULL) AND col1 < 145.44) OR (col0 > 687 AND col0 < 667) OR (col0 = 412 OR (((((col0 < 136) AND col3 < 47)))) AND (col3 >= 720)) OR (col3 = 806 AND ((col3 > 988) AND ((((col1 < 786.49)) OR col3 = 752) AND col0 < 740))) AND col0 = 203 OR col0 <= 262 OR ((col3 <= 964 AND col1 < 173.79)) OR col0 > 763 AND (col3 >= 187) OR col0 > 767 AND (col1 <= 872.2) AND col0 = 326 OR (col4 = 57.9) OR col0 > 930 OR col1 < 773.31 AND (col0 >= 394) AND (col0 > 926) AND (col0 IN (305,760,124,899) OR col1 IN (71.18,407.37) AND (((col0 < 533) OR (((col4 > 241.99))) AND col1 > 746.98)))) AND col4 < 802.32 AND ((col0 > 531)) AND col0 < 58) OR col0 IS NULL)) AND col0 < 775))) AND ((col0 < 647 AND col4 <= 907.73) OR (col0 = 99))) AND (col3 > 388 AND (((col0 < 109 AND col0 = 535 AND col3 < 959 OR ((col4 >= 617.70)))))) OR ((((col0 > 95) AND (((col0 < 247 OR col3 > 685 OR (col1 > 477.89 AND col1 > 239.93 OR col0 IS NULL OR col0 >= 641 OR col1 >= 893.3 AND (col3 = 72 AND col0 > 666 AND col3 > 17) AND (col4 BETWEEN 259.28 AND 405.64) AND col3 <= 829 OR col3 IS NULL AND (col1 < 228.68)))))) OR col0 >= 729 OR col0 < 431) AND ((col0 IS NULL))) AND col3 IN (779,539,505,633,380) AND (col3 > 895) AND col1 < 604.14 OR (col0 > 626 AND ((col0 IS NULL AND (((((col4 <= 777.85) AND col0 > 388) OR col0 >= 30))) OR (col0 IS NULL) OR col0 < 692 OR col0 <= 368 OR col3 > 698 OR col3 > 349 AND (((col0 < 303)) OR (col0 = 191) AND col1 IN (108.43) AND (col4 < 816.73)))) OR col0 < 45) OR col0 <= 900 OR col0 < 218 OR (col1 > 472.9 AND (col3 > 800) OR (col3 IN (918)) AND (col3 = 813) OR col1 IS NULL OR ((col1 <= 997.17) AND ((col1 > 808.22 AND (col1 > 539.58 OR col3 IS NULL)) AND (((col4 < 173.56)))))) AND col1 < 91.27 OR col0 > 696)) OR (col3 >= 50) AND (((col0 < 609))) AND col3 < 295)) AND col0 < 400 AND col3 > 267) OR col3 > 157 AND col3 <= 642 OR (col0 = 209) AND col0 > 921 AND col1 > 587.17 OR col0 < 950) AND col0 > 805 OR col3 >= 829 AND ((((col3 >= 214)))) OR ((col0 > 744 AND col1 < 102.6 AND ((col3 <= 779)) AND (((((col3 < 876))))) AND col3 >= 707) AND (col3 < 265)))))) AND (col1 < 985.78) OR col4 BETWEEN 314.64 AND 482.24));

Actual Results:

The server gets an assertion 0 error in a _rel_optimizer call.

Expected Results:

An empty result set.

Assertion error:

mserver5: rel_optimizer.c:9031: _rel_optimizer: Assertion `0' failed.

Backtrace:

0 0x00007ffff490169b in raise () from /lib64/libc.so.6
1 0x00007ffff49034a0 in abort () from /lib64/libc.so.6
2 0x00007ffff48f9d5a in __assert_fail_base () from /lib64/libc.so.6
3 0x00007ffff48f9dd2 in __assert_fail () from /lib64/libc.so.6
4 0x00007fffe8b37329 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=11) at rel_optimizer.c:9031
5 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=11) at rel_optimizer.c:9036
6 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=10) at rel_optimizer.c:9036
7 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=9) at rel_optimizer.c:9036
8 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=8) at rel_optimizer.c:9036
9 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=7) at rel_optimizer.c:9036
10 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=6) at rel_optimizer.c:9036
11 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=5) at rel_optimizer.c:9036
12 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=4) at rel_optimizer.c:9036
13 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=3) at rel_optimizer.c:9036
14 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=2) at rel_optimizer.c:9036
15 0x00007fffe8b37359 in _rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040, level=1) at rel_optimizer.c:9036
16 0x00007fffe8b373ac in rel_optimizer (sql=0x7fffb800ab40, rel=0x7fffb81b4040) at rel_optimizer.c:9044
17 0x00007fffe8a17b85 in sql_symbol2relation (c=0x7fffb800ab40, sym=0x7fffb812d410) at sql.c:121
18 0x00007fffe8a36a35 in SQLparser (c=0x7fffea11d338) at sql_scenario.c:1129
19 0x00007ffff7a14da0 in runPhase (c=0x7fffea11d338, phase=1) at mal_scenario.c:506
20 0x00007ffff7a14ee4 in runScenarioBody (c=0x7fffea11d338) at mal_scenario.c:539
21 0x00007ffff7a15087 in runScenario (c=0x7fffea11d338) at mal_scenario.c:568
22 0x00007ffff7a16c3c in MSserveClient (dummy=0x7fffea11d338) at mal_session.c:473
23 0x00007ffff7a1672c in MSscheduleClient (command=0x7fffb80008d0 "0", challenge=0x7fffcd395deb "riPbNGQg78v", fin=0x7fffb80028f0, fout=0x7fffc4002b70, protocol=PROTOCOL_9, blocksize=8190,
compute_column_widths=0) at mal_session.c:364
24 0x00007ffff7ab9ba6 in doChallenge (data=0x7fffc40008d0) at mal_mapi.c:273
25 0x00007ffff769022a in thread_starter (arg=0x7fffc4004c60) at gdk_system.c:485
26 0x00007ffff4ca336d in start_thread () from /lib64/libpthread.so.0
27 0x00007ffff49dbbbf in clone () from /lib64/libc.so.6

Comment 25752

Date: 2017-10-18 10:34:13 +0200
From: MonetDB Mercurial Repository <>

Changeset 955d928d6427 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #6433

Comment 25754

Date: 2017-10-18 15:13:32 +0200
From: @njnes

fixed by increasing the allowed calls to _rel_optimizer, ie larger queries may require some more optimization rounds.

Comment 25756

Date: 2017-10-18 15:30:15 +0200
From: MonetDB Mercurial Repository <>

Changeset 1d48bbe3e8dc 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=1d48bbe3e8dc

Changeset description:

fixed bug #6432, make sure all finished projects are set_processed
fixed bug #6433, incremented the allowed number of calls to _rel_optimize