Date: 2017-10-14 21:22:25 +0200
From: @mlkersten
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @njnes

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

Comment 25707

Date: 2017-10-14 21:22:25 +0200
From: @mlkersten

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

Using the mini database over mergetables triggered an assertion error.
Beware, no statistics has been computed over the tables.

(gdb) where
0 0x00007f677149769b in raise () from /lib64/libc.so.6
1 0x00007f67714994a0 in abort () from /lib64/libc.so.6
2 0x00007f677148fd5a in __assert_fail_base () from /lib64/libc.so.6
3 0x00007f677148fdd2 in __assert_fail () from /lib64/libc.so.6
4 0x00007f676c0186ce in sql_ref_dec (r=0x7f67389a4fc0) at /export/scratch1/home/mk/default//package/sql/common/sql_mem.c:29
5 0x00007f676bf8497e in rel_destroy (rel=0x7f67389a4fc0) at /export/scratch1/home/mk/default//package/sql/server/rel_rel.c:77
6 0x00007f676bfaaeb1 in rel_merge_table_rewrite (changes=0x7f6748c9a8b8, sql=0x7f67381263f0, rel=0x7f67389a4fc0)
at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:7900
7 0x00007f676bfaf3d9 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a4fc0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8819
8 0x00007f676bfaf4fc in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389beb90, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8841
9 0x00007f676bfaf4d5 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389becc0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8840
10 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389bee60, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
11 0x00007f676bfaf4fc in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389bf990, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8841
12 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389db9d0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
13 0x00007f676bfaf4fc in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a5aa0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8841
14 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389dc800, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
15 0x00007f676bfaf4d5 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a75a0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8840
16 0x00007f676bfaf4fc in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a7890, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8841
17 0x00007f676bfaf4fc in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389c22b0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8841
18 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389cf8c0, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
19 0x00007f676bfaf4d5 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389c1f60, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8840
20 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389bc720, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
21 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a8580, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
22 0x00007f676bfaf528 in rewrite_topdown (sql=0x7f67381263f0, rel=0x7f67389a8f80, rewriter=0x7f676bfa9ed3 <rel_merge_table_rewrite>,
has_changes=0x7f6748c9a8b8) at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:8848
23 0x00007f676bfafee3 in _rel_optimizer (sql=0x7f67381263f0, rel=0x7f67389a8f80, level=0)
at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:9025
24 0x00007f676bfaffd9 in rel_optimizer (sql=0x7f67381263f0, rel=0x7f67389a8f80)
at /export/scratch1/home/mk/default//package/sql/server/rel_optimizer.c:9043
25 0x00007f676be90b35 in sql_symbol2relation (c=0x7f67381263f0, sym=0x7f673899ba30)
at /export/scratch1/home/mk/default//package/sql/backends/monet5/sql.c:121
26 0x00007f676beaf96c in SQLparser (c=0x7f676d852338) at /export/scratch1/home

Reproducible: Always

Steps to Reproduce:

select
ref_7.length as c0,
sample_1.type as c1,
ref_7.length as c2,
sample_8.function_type_id as c3
from
sys.columns as sample_1
inner join bam.rg as ref_4
left join sys.keys as ref_6
on (ref_4.pi = ref_6.id )
right join sys.table_types as sample_7
left join sys.function_types as sample_8
left join sys.r as sample_9
on (sample_9.y is not NULL)
on (sample_7.table_type_name = sample_8.function_type_name )
left join sys.r as sample_10
right join sys.netcdf_dims as ref_7
on ((false)
or (true))
on (sample_10.z is not NULL)
right join sys._tables as sample_12
left join bam.rg as ref_8
on (sample_12.id = ref_8.pi )
on (sample_10.y = ref_8.pi )
on (ref_4.cn = sample_9.z )
on (sample_1.type_scale = ref_4.pi )
where sample_10.y is NULL
limit 14;

Comment 25708

Date: 2017-10-14 21:24:47 +0200
From: @mlkersten

A variation on the same issue

select
sample_8.dimpos as c0,
ref_4.x as c1,
ref_2.y as c2
from
sys.r3 as ref_2
left join tmp._tables as sample_6
on (ref_2.y = sample_6.id )
left join sys._tables as sample_7
right join sys.netcdf_vardim as sample_8
left join sys.querylog_catalog as ref_3
on (sample_8.dimpos = ref_3.mal )
on (true)
left join sys.s1 as ref_4
on (sample_8.dimpos = ref_4.x )
on ((sample_7.id is NULL)
or (sample_8.var_id is not NULL))
left join sys.optimizers as ref_12
inner join bam.sq as ref_13
on (ref_12.def = ref_13.sn )
inner join sys.keywords as ref_14
on (ref_13.sp is not NULL)
on ((sample_6.commit_action is NULL)
or (ref_14.keyword is not NULL))
where ref_3.optimize is NULL
limit 86;

Comment 25715

Date: 2017-10-14 23:51:05 +0200
From: @njnes

fixed reference bug in split_outerjoin optimizer