Date: 2012-11-20 17:24:36 +0100
From: @skinkie
To: SQL devs <>
Version: 11.13.3 (Oct2012)
CC: @njnes

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

Comment 17966

Date: 2012-11-20 17:24:36 +0100
From: @skinkie

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

mserver5: rel_optimizer.c:5596: _rel_optimizer: Assertion `0' failed.
Aborted

Reproducible: Always

Steps to Reproduce:

I am trying the following query;

update kvk set anbi = (select begindatum from sys.anbi_kvk, anbi_intern where anbi_kvk.anbi = anbi_intern.id and kvk.kvks = anbi_kvk.kvks and anbi_intern.einddatum is null) where kvk.kvks in (select kvks from anbi_kvk);

This results in;

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

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7ffff0bd7700 (LWP 4376)]
0x00007ffff3f63015 in raise () from /lib64/libc.so.6
(gdb) bt
0 0x00007ffff3f63015 in raise () from /lib64/libc.so.6
1 0x00007ffff3f6448b in abort () from /lib64/libc.so.6
2 0x00007ffff3f5c15e in ?? () from /lib64/libc.so.6
3 0x00007ffff3f5c202 in __assert_fail () from /lib64/libc.so.6
4 0x00007ffff16f1348 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=11) at rel_optimizer.c:5596
5 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=11) at rel_optimizer.c:5599
6 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=10) at rel_optimizer.c:5599
7 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=9) at rel_optimizer.c:5599
8 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=8) at rel_optimizer.c:5599
9 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=7) at rel_optimizer.c:5599
10 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=6) at rel_optimizer.c:5599
11 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=5) at rel_optimizer.c:5599
12 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=4) at rel_optimizer.c:5599
13 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=3) at rel_optimizer.c:5599
14 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=2) at rel_optimizer.c:5599
15 0x00007ffff16f1375 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=1) at rel_optimizer.c:5599
16 0x00007ffff16f13d3 in rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0) at rel_optimizer.c:5608
17 0x00007ffff163008d in sql_symbol2relation (c=0x7fffe4005470, sym=0x7fffe40ffc20) at sql.mx:1679
18 0x00007ffff1616c6d in SQLparser (c=0x7ffff3106338) at sql_scenario.c:1560
19 0x00007ffff6faf5ac in runPhase (c=0x7ffff3106338, phase=1) at mal_scenario.c:522
20 0x00007ffff6faf707 in runScenarioBody (c=0x7ffff3106338) at mal_scenario.c:564
21 0x00007ffff6faf9b6 in runScenario (c=0x7ffff3106338) at mal_scenario.c:601
22 0x00007ffff6fb0a5a in MSserveClient (dummy=0x7ffff3106338) at mal_session.c:430
23 0x00007ffff42dcea7 in start_thread () from /lib64/libpthread.so.0
24 0x00007ffff401614d in clone () from /lib64/libc.so.6
(gdb) up
1 0x00007ffff3f6448b in abort () from /lib64/libc.so.6
(gdb) up
2 0x00007ffff3f5c15e in ?? () from /lib64/libc.so.6
(gdb) up
3 0x00007ffff3f5c202 in __assert_fail () from /lib64/libc.so.6
(gdb) up
4 0x00007ffff16f1348 in _rel_optimizer (sql=0x7fffe4005470, rel=0x7fffe4107ad0, level=11) at rel_optimizer.c:5596
5596 assert(0);
(gdb) list
5591 }
5592
5593 rel = rewrite(sql, rel, &rel_merge_table_rewrite, &changes);
5594
5595 if (changes && level > 10)
5596 assert(0);
5597
5598 if (changes)
5599 return _rel_optimizer(sql, rel, ++level);
5600
(gdb) print changes
$1 = 1
(gdb) print level
$2 = 11

Expected Results:

When I loose the 'where' part of the update. The query finishes and the result is as expected.

sql>update kvk set anbi = (select begindatum from sys.anbi_kvk, anbi_intern where anbi_kvk.anbi = anbi_intern.id and kvk.kvks = anbi_kvk.kvks and anbi_intern.einddatum is null);
2416276 affected rows (2.5s)

MonetDB 5 server v11.13.6 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2012 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 23.5GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.31 2012-07-06 (compiled with 8.31)
openssl: OpenSSL 1.0.1c 10 May 2012 (compiled with OpenSSL 1.0.1c 10 May 2012)
libxml2: 2.8.0 (compiled with 2.8.0)
Compiled by: root@openstate-one (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -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 -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 17991

Date: 2012-11-24 13:33:58 +0100
From: @njnes

somehow the optimizer gets into an every changing query plan loop. After 10 rounds we bail out the hard way. Could you send the ddl statements such that we can debug this problem

Comment 17992

Date: 2012-11-24 14:13:16 +0100
From: @skinkie

Created attachment 159
This directly crashes the server.

Attached file: naarniels.sql (text/plain, 1216 bytes)
Description: This directly crashes the server.

Comment 17993

Date: 2012-11-24 15:03:23 +0100
From: @skinkie

Maybe another suggestion, as you point out "bailing out the hard way" is there a way to just disable the optimiser, print a message in the server and go on without any optimiser?

Comment 17996

Date: 2012-11-24 18:45:13 +0100
From: @njnes

added test recursive_optimizer.Bug-3191.sql
Solved the optimizers introduce empty selects. When pushing these down the optimizers get into an endless recursion. This was fixed by no longer pushing down empty selects.

Comment 17997

Date: 2012-11-24 18:48:30 +0100
From: @njnes

Changeset 4c00145df76f 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=4c00145df76f

Changeset description:

fixed bug #3191, problem with endless recursive optimizer calls.
Solved by not pushing down empty selects.

Comment 18086

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

Changeset 89a9ebc92eda 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=89a9ebc92eda

Changeset description:

added tests for Bugs 3193, 3179 and 3172

Fixed bug #3191, we now allow the order by column expressions to refer to
the lower level projection. This is very limited support voor order by with
expressions. Aliases is still preferred.

Fixed bug #3179 (or feature request). We now rewrite batstr.*like + subselect into
*likeselect. Also some cleanup of pushselect optimizer and mergetable related
helper functions in opt_support.c

Fixed bug #3172. We still don't allow multi row input in table functions. But
we now don't crash on it anymore. Still work on mal.multiplex needed (requires
multiple outputs)!

Comment 18367

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

Oct2012-SP3 has been released.