MySQL Bugs: #96680: Assertion "!table->const

Bug #96680	Assertion "!table->const_table"
Submitted:	28 Aug 2019 13:39	Modified:	10 Nov 2020 20:48
Reporter:	Hrvoje Matijakovic	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S6 (Debug Builds)
Version:	8.0.17	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
I'm able to reproduce this on debug only. Release build doesn't result in a crash:

gdb:
Core was generated by `/sdc/MS230819-mysql-8.0.17-linux-x86_64-debug/bin/mysqld --no-defaults --basedi'.
Program terminated with signal 6, Aborted.
#0  0x00007f321e0e39d1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
59        val = INTERNAL_SYSCALL (tgkill, err, 3, THREAD_GETMEM (THREAD_SELF, pid),
(gdb) bt
+bt
#0  0x00007f321e0e39d1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
#1  0x0000000004580cc8 in my_write_core (sig=6) at /sdc/MS-8.0.17_dbg/mysys/stacktrace.cc:305
#2  0x00000000032f4d64 in handle_fatal_signal (sig=6) at /sdc/MS-8.0.17_dbg/sql/signal_handler.cc:168
#3  <signal handler called>
#4  0x00007f321c1e62c7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:55
#5  0x00007f321c1e79b8 in __GI_abort () at abort.c:90
#6  0x00007f321c1df0e6 in __assert_fail_base (fmt=0x7f321c33a020 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x50682bc "!table->const_table", file=file@entry=0x5068228 "/sdc/MS-8.0.17_dbg/sql/opt_sum.cc", line=line@entry=484, function=function@entry=0x5068520 <optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)::__PRETTY_FUNCTION__> "bool optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)") at assert.c:92
#7  0x00007f321c1df192 in __GI___assert_fail (assertion=0x50682bc "!table->const_table", file=0x5068228 "/sdc/MS-8.0.17_dbg/sql/opt_sum.cc", line=484, function=0x5068520 <optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)::__PRETTY_FUNCTION__> "bool optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)") at assert.c:101
#8  0x0000000002ff261b in optimize_aggregated_query (thd=0x7f31c2421000, select=0x7f31c2529ca8, all_fields=..., conds=0x0, decision=0x7f321ab8586c) at /sdc/MS-8.0.17_dbg/sql/opt_sum.cc:484
#9  0x00000000030fdfa9 in JOIN::optimize (this=0x7f31c25ecaa8) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:342
#10 0x0000000003193d3d in SELECT_LEX::optimize (this=0x7f31c2529ca8, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:1756
#11 0x0000000003231c3c in SELECT_LEX_UNIT::optimize (this=0x7f31c2529f88, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_union.cc:720
#12 0x00000000035d9c0d in Item_subselect::exec (this=0x7f31c2529300, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:685
#13 0x00000000035d9feb in Item_in_subselect::exec (this=0x7f31c2529300, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:758
#14 0x00000000035dd173 in Item_in_subselect::val_bool_naked (this=0x7f31c2529300) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:1606
#15 0x00000000035130a3 in Item_in_optimizer::val_int (this=0x7f31c25ebbb8) at /sdc/MS-8.0.17_dbg/sql/item_cmpfunc.cc:2147
#16 0x0000000003115cac in make_join_select (join=0x7f31c25ec118, cond=0x7f31c25ebbb8) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:9014
#17 0x00000000030feae8 in JOIN::optimize (this=0x7f31c25ec118) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:506
#18 0x0000000003193d3d in SELECT_LEX::optimize (this=0x7f31c2528098, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:1756
#19 0x000000000319210a in Sql_cmd_dml::execute_inner (this=0x7f31c252ad60, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:886
#20 0x0000000003191866 in Sql_cmd_dml::execute (this=0x7f31c252ad60, thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:700
#21 0x000000000312dc73 in mysql_execute_command (thd=0x7f31c2421000, first_level=true) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:4420
#22 0x00000000031304fe in mysql_parse (thd=0x7f31c2421000, parser_state=0x7f321ab87af0) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:5221
#23 0x0000000003125aa9 in dispatch_command (thd=0x7f31c2421000, com_data=0x7f321ab88b90, command=COM_QUERY) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:1755
#24 0x000000000312400c in do_command (thd=0x7f31c2421000) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:1270
#25 0x00000000032dffcd in handle_connection (arg=0x7f31cb0505c0) at /sdc/MS-8.0.17_dbg/sql/conn_handler/connection_handler_per_thread.cc:302
#26 0x0000000004d28926 in pfs_spawn_thread (arg=0x7f321a069fe0) at /sdc/MS-8.0.17_dbg/storage/perfschema/pfs.cc:2854
#27 0x00007f321e0dedd5 in start_thread (arg=0x7f321ab89700) at pthread_create.c:307
#28 0x00007f321c2ae02d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) quit

How to repeat:
CREATE DATABASE test;
USE test;
CREATE TABLE t2(id1 INT,id2 CHAR (1),id3 INT,value INT,KEY (id1,id2,id3)) collate latin1_bin;
set default_storage_ENGINE=MEMORY;
create table t1(a int,b int,primary key(a));
INSERT INTO t1 VALUES(1,0);
SELECT * FROM t1 WHERE t1.a IN(SELECT MAX(t1.b)FROM t2);

One more testcase:

CREATE DATABASE test;
USE test;
CREATE TABLE t2(c1 TEXT);
set session default_storage_engine='MEMORY';
create TABLE t1(a int,key aa(a),b char,unique key bb(b),c char);
INSERT INTO t1 VALUES(3,2,0),(4,2,0);
SELECT * FROM t1 WHERE t1.a IN(SELECT MAX(t1.b)FROM t2);

gdb:
Core was generated by `/sdc/MS230819-mysql-8.0.17-linux-x86_64-debug/bin/mysqld --no-defaults --basedi'.
Program terminated with signal 6, Aborted.
#0  0x00007f16b49149d1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
59        val = INTERNAL_SYSCALL (tgkill, err, 3, THREAD_GETMEM (THREAD_SELF, pid),
(gdb) bt
+bt
#0  0x00007f16b49149d1 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:59
#1  0x0000000004580cc8 in my_write_core (sig=6) at /sdc/MS-8.0.17_dbg/mysys/stacktrace.cc:305
#2  0x00000000032f4d64 in handle_fatal_signal (sig=6) at /sdc/MS-8.0.17_dbg/sql/signal_handler.cc:168
#3  <signal handler called>
#4  0x00007f16b2a172c7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:55
#5  0x00007f16b2a189b8 in __GI_abort () at abort.c:90
#6  0x00007f16b2a100e6 in __assert_fail_base (fmt=0x7f16b2b6b020 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x50682bc "!table->const_table", file=file@entry=0x5068228 "/sdc/MS-8.0.17_dbg/sql/opt_sum.cc", line=line@entry=484, function=function@entry=0x5068520 <optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)::__PRETTY_FUNCTION__> "bool optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)") at assert.c:92
#7  0x00007f16b2a10192 in __GI___assert_fail (assertion=0x50682bc "!table->const_table", file=0x5068228 "/sdc/MS-8.0.17_dbg/sql/opt_sum.cc", line=484, function=0x5068520 <optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)::__PRETTY_FUNCTION__> "bool optimize_aggregated_query(THD*, SELECT_LEX*, List<Item>&, Item*, aggregate_evaluated*)") at assert.c:101
#8  0x0000000002ff261b in optimize_aggregated_query (thd=0x7f1658c21000, select=0x7f1658d82ca8, all_fields=..., conds=0x0, decision=0x7f16b13b686c) at /sdc/MS-8.0.17_dbg/sql/opt_sum.cc:484
#9  0x00000000030fdfa9 in JOIN::optimize (this=0x7f1658dcf760) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:342
#10 0x0000000003193d3d in SELECT_LEX::optimize (this=0x7f1658d82ca8, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:1756
#11 0x0000000003231c3c in SELECT_LEX_UNIT::optimize (this=0x7f1658d82f88, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_union.cc:720
#12 0x00000000035d9c0d in Item_subselect::exec (this=0x7f1658d82300, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:685
#13 0x00000000035d9feb in Item_in_subselect::exec (this=0x7f1658d82300, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:758
#14 0x00000000035dd173 in Item_in_subselect::val_bool_naked (this=0x7f1658d82300) at /sdc/MS-8.0.17_dbg/sql/item_subselect.cc:1606
#15 0x00000000035130a3 in Item_in_optimizer::val_int (this=0x7f1658dce870) at /sdc/MS-8.0.17_dbg/sql/item_cmpfunc.cc:2147
#16 0x0000000003115cac in make_join_select (join=0x7f1658dcedd0, cond=0x7f1658dce870) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:9014
#17 0x00000000030feae8 in JOIN::optimize (this=0x7f1658dcedd0) at /sdc/MS-8.0.17_dbg/sql/sql_optimizer.cc:506
#18 0x0000000003193d3d in SELECT_LEX::optimize (this=0x7f1658d81098, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:1756
#19 0x000000000319210a in Sql_cmd_dml::execute_inner (this=0x7f1658d83d60, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:886
#20 0x0000000003191866 in Sql_cmd_dml::execute (this=0x7f1658d83d60, thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_select.cc:700
#21 0x000000000312dc73 in mysql_execute_command (thd=0x7f1658c21000, first_level=true) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:4420
#22 0x00000000031304fe in mysql_parse (thd=0x7f1658c21000, parser_state=0x7f16b13b8af0) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:5221
#23 0x0000000003125aa9 in dispatch_command (thd=0x7f1658c21000, com_data=0x7f16b13b9b90, command=COM_QUERY) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:1755
#24 0x000000000312400c in do_command (thd=0x7f1658c21000) at /sdc/MS-8.0.17_dbg/sql/sql_parse.cc:1270
#25 0x00000000032dffcd in handle_connection (arg=0x7f166185a5c0) at /sdc/MS-8.0.17_dbg/sql/conn_handler/connection_handler_per_thread.cc:302
#26 0x0000000004d28926 in pfs_spawn_thread (arg=0x7f16b0869fe0) at /sdc/MS-8.0.17_dbg/storage/perfschema/pfs.cc:2854
#27 0x00007f16b490fdd5 in start_thread (arg=0x7f16b13ba700) at pthread_create.c:307
#28 0x00007f16b2adf02d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) quit

Hello Hrvoje Matijakovic,

Thank you for the report and test case.
Observed that 8.0.17 debug build is affected.

regards,
Umesh

Fixed in MySQL 8.0.23.

Does not affect release build. No changelog entry needed.

Closed.