Assertion error in exp_bin (sqlsmith)

[monetdb-team]

Date: 2017-11-02 23:05:36 +0100
From: @mlkersten
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

Last updated: 2017-12-14 14:46:08 +0100

Comment 25840

Date: 2017-11-02 23:05:36 +0100
From: @mlkersten

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

After recompilation this assertion is still triggered.

3 0x00007f6c00f2edd2 in __assert_fail () from /lib64/libc.so.6
4 0x00007f6bfb98d174 in exp_bin (be=0x7f6bc8129b10, e=0x7f6bc82a87a0, left=0x7f6bc8348900, right=0x0, grp=0x0, ext=0x0, cnt=0x0, sel=0x0)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:734
5 0x00007f6bfb995e18 in rel2bin_select (be=0x7f6bc8129b10, rel=0x7f6bc82a7db0, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:2629
6 0x00007f6bfb9a0038 in subrel_bin (be=0x7f6bc8129b10, rel=0x7f6bc82a7db0, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:4840
7 0x00007f6bfb99517d in rel2bin_project (be=0x7f6bc8129b10, rel=0x7f6bc82a7a20, refs=0x7f6bc82f9610, topn=0x0)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:2455
8 0x00007f6bfb9a000a in subrel_bin (be=0x7f6bc8129b10, rel=0x7f6bc82a7a20, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:4836
9 0x00007f6bfb992970 in rel2bin_semijoin (be=0x7f6bc8129b10, rel=0x7f6bc82a7c50, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:1925
10 0x00007f6bfb99ff50 in subrel_bin (be=0x7f6bc8129b10, rel=0x7f6bc82a7c50, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:4820
11 0x00007f6bfb99517d in rel2bin_project (be=0x7f6bc8129b10, rel=0x7f6bc82814e0, refs=0x7f6bc82f9610, topn=0x0)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:2455
12 0x00007f6bfb9a000a in subrel_bin (be=0x7f6bc8129b10, rel=0x7f6bc82814e0, refs=0x7f6bc82f9610)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:4836
13 0x00007f6bfb9a0301 in output_rel_bin (be=0x7f6bc8129b10, rel=0x7f6bc82814e0)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/rel_bin.c:4901
14 0x00007f6bfb9bc104 in sql_relation2stmt (be=0x7f6bc8129b10, r=0x7f6bc82814e0)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/sql_gencode.c:531
15 0x00007f6bfb9bc30d in backend_dumpstmt (be=0x7f6bc8129b10, mb=0x7f6bc812a170,

Reproducible: Always

Steps to Reproduce:

select
subq_0.c0 as c0
from
(select
ref_2.coord_dimension as c0
from
sys.geometry_columns as ref_2
where ref_2.f_table_catalog is not NULL
limit 127) as subq_0
where EXISTS (
select
ref_4.srtext as c0,
ref_4.auth_srid as c1,
37 as c2,
subq_0.c0 as c3,
ref_5.minflt as c4
from
sys.spatial_ref_sys as ref_4
right join sys.tracelog as ref_5
on (ref_4.proj4text = ref_5.clk )
where case when EXISTS (
select
subq_0.c0 as c0,
ref_4.proj4text as c1,
ref_5.thread as c2,
subq_0.c0 as c3,
ref_6.commit_action as c4
from
sys.tables as ref_6
where false) then ref_4.auth_srid else ref_4.auth_srid end
is not NULL);

Comment 25848

Date: 2017-11-03 21:55:12 +0100
From: @mlkersten

Another example of the same problem.
select
cast(coalesce(cast(coalesce(subq_0.c0,
subq_0.c0) as clob),
subq_0.c0) as clob) as c0,
subq_0.c3 as c1,
subq_0.c1 as c2,
subq_0.c2 as c3,
subq_0.c1 as c4,
subq_0.c2 as c5,
subq_0.c1 as c6,
subq_0.c2 as c7,
subq_0.c0 as c8,
subq_0.c2 as c9,
subq_0.c3 as c10
from
(select
ref_0.z as c0,
ref_0.x as c1,
case when false then ref_0.x else ref_0.x end
as c2,
case when EXISTS (
select
ref_0.x as c0,
ref_1.type as c1,
ref_0.y as c2,
ref_0.y as c3,
44 as c4,
ref_0.z as c5,
ref_0.y as c6,
ref_1.id as c7
from
sys.tables as ref_1
where EXISTS (
select

                 ref_0.y as c0, 
                 ref_2.login_id as c1, 
                 ref_2.login_id as c2
               from 
                 sys.user_role as ref_2
               where ref_0.x is NULL)) then ref_0.y else ref_0.y end
        as c3
   from 
     sys.r2 as ref_0
   where ref_0.y is NULL
   limit 72) as subq_0

where case when 86 is NULL then subq_0.c1 else subq_0.c1 end
is NULL;

Comment 25852

Date: 2017-11-08 21:57:45 +0100
From: @njnes

fixed by improved handling of subqueries in the optimizers. Now before any optimizer is run the subquery flags are reset.

Comment 25854

Date: 2017-11-08 21:59:03 +0100
From: MonetDB Mercurial Repository <>

Changeset 0d8fd1c7710c 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=0d8fd1c7710c

Changeset description:

fixed bug #6459
and bug #6456