MySQL Bugs: #112475: Assertion Failure in /mysql-8.0.34/sql/sql

Bug #112475	Assertion Failure in /mysql-8.0.34/sql/sql_select.cc:2303
Submitted:	26 Sep 2023 9:43	Modified:	26 Sep 2023 9:44
Reporter:	xin wen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.34	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	Any

Description:
Run these queries:

CREATE TABLE t0 REPLACE AS SELECT -89 AS c14 ORDER BY c14 ;
INSERT INTO t0 VALUES ( DEFAULT ) , ( DEFAULT ) ;
ALTER TABLE t0 RENAME TO t1 ;
INSERT INTO t1 VALUES ( 12 ) , ( 121 ) ;
SELECT t1 . c14 AS c25 FROM ( SELECT t1 . c14 AS c31 FROM ( SELECT CONVERT ( c14 = RAND ( ) IN ( SIGN ( -54 ) & RAND ( ) LIKE CASE -47 WHEN TRUE THEN -94 WHEN -16 THEN 107 ELSE -44 END , -6351076902139315214 , -68 ) , BINARY ) AS c58 FROM t1 ) AS t2 JOIN t1 ON + EXISTS ( SELECT t1 . c14 AS c33 FROM t1 WHERE c58 = ASCII ( -97 ) << t1 . c14 + -119 GROUP BY c14 , c14 ) , t1 AS t3 JOIN t1 AS t4 ON REVERSE ( t3 . c14 ) ) AS t5 JOIN t1 ON t5 . c31 = t5 . c31 ;

Will trigger assertion failure:
/home/wx/mysql-8.0.34/sql/sql_select.cc:2303: void calc_length_and_keyparts(Key_use*, JOIN_TAB*, uint, table_map, Key_use**, uint*, uint*, table_map*, bool*): Assertion `false' failed.

GDB info:
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff6af2859 in __GI_abort () at abort.c:79
#2  0x00007ffff6af2729 in __assert_fail_base (fmt=0x7ffff6c88588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55555dd85720 "false", file=0x55555dd856c0 "/home/wx/mysql-8.0.34/sql/sql_select.cc", line=2303, function=<optimized out>) at assert.c:92
#3  0x00007ffff6b03fd6 in __GI___assert_fail (assertion=assertion@entry=0x55555dd85720 "false", file=file@entry=0x55555dd856c0 "/home/wx/mysql-8.0.34/sql/sql_select.cc", line=line@entry=2303, function=function@entry=0x55555dd87c60 "void calc_length_and_keyparts(Key_use*, JOIN_TAB*, uint, table_map, Key_use**, uint*, uint*, table_map*, bool*)") at assert.c:101
#4  0x00005555595b2ef8 in calc_length_and_keyparts (keyuse=0x61d0006f0518, keyuse@entry=0x61d0006f04b0, tab=tab@entry=0x6130010f3b30, key=key@entry=0, used_tables=used_tables@entry=6917529027641081921, chosen_keyuses=chosen_keyuses@entry=0x7fffc70750e0, length_out=length_out@entry=0x7fffc7075080, keyparts_out=0x7fffc7075070, dep_map=0x0, maybe_null=0x0) at /home/wx/mysql-8.0.34/sql/sql_select.cc:2303
#5  0x00005555595b8043 in create_ref_for_key (join=join@entry=0x619000ba13b0, j=j@entry=0x6130010f3b30, org_keyuse=org_keyuse@entry=0x61d0006f04b0, used_tables=6917529027641081921) at /home/wx/mysql-8.0.34/sql/sql_select.cc:2438
#6  0x00005555595b8fb5 in JOIN::init_ref_access (this=this@entry=0x619000ba13b0) at /home/wx/mysql-8.0.34/sql/sql_select.cc:2212
#7  0x0000555559494a2c in JOIN::optimize (this=0x619000ba13b0, finalize_access_paths=finalize_access_paths@entry=true) at /home/wx/mysql-8.0.34/sql/sql_optimizer.cc:768
#8  0x00005555595b28b0 in Query_block::optimize (this=this@entry=0x619000b8e7b0, thd=thd@entry=0x6270002bf900, finalize_access_paths=finalize_access_paths@entry=true) at /home/wx/mysql-8.0.34/sql/sql_select.cc:2013
#9  0x0000555559714de9 in Query_expression::optimize (this=this@entry=0x612000408070, thd=thd@entry=0x6270002bf900, materialize_destination=materialize_destination@entry=0x0, create_iterators=create_iterators@entry=true, finalize_access_paths=finalize_access_paths@entry=true) at /home/wx/mysql-8.0.34/sql/sql_union.cc:1006
#10 0x00005555595b1ee9 in Sql_cmd_dml::execute_inner (this=0x60b0001b69c0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:1007
#11 0x00005555595ce209 in Sql_cmd_dml::execute (this=0x60b0001b69c0, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:793
#12 0x00005555594bd568 in mysql_execute_command (thd=thd@entry=0x6270002bf900, first_level=first_level@entry=true) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:4719
#13 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002bf900, parser_state=parser_state@entry=0x7fffc7078c00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#14 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002bf900, com_data=com_data@entry=0x7fffc707a200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#15 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#16 0x000055555989b7be in handle_connection (arg=arg@entry=0x603000169780) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#17 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140a60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#18 0x00007ffff7568609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#19 0x00007ffff6bef133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

How to repeat:
Run the queries above.

Hello xin wen,

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

regards,
Umesh

- release - failes

 ./mtr --nocheck-testcases bug112475
Logging: ./mtr  --nocheck-testcases bug112475
MySQL Version 8.0.34
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 REPLACE AS SELECT -89 AS c14 ORDER BY c14 ;
INSERT INTO t0 VALUES ( DEFAULT ) , ( DEFAULT ) ;
ALTER TABLE t0 RENAME TO t1 ;
INSERT INTO t1 VALUES ( 12 ) , ( 121 ) ;
SELECT t1 . c14 AS c25 FROM ( SELECT t1 . c14 AS c31 FROM ( SELECT CONVERT ( c14 = RAND ( ) IN ( SIGN ( -54 ) & RAND ( ) LIKE CASE -47 WHEN TRUE THEN -94 WHEN -16 THEN 107 ELSE -44 END , -6351076902139315214 , -68 ) , BINARY ) AS c58 FROM t1 ) AS t2 JOIN t1 ON + EXISTS ( SELECT t1 . c14 AS c33 FROM t1 WHERE c58 = ASCII ( -97 ) << t1 . c14 + -119 GROUP BY c14 , c14 ) , t1 AS t3 JOIN t1 AS t4 ON REVERSE ( t3 . c14 ) ) AS t5 JOIN t1 ON t5 . c31 = t5 . c31 ;
[ 50%] main.bug112475                            [ fail ]
        Test ended at 2023-09-26 11:43:52

CURRENT_TEST: main.bug112475
mysqltest: At line 5: Query 'SELECT t1 . c14 AS c25 FROM ( SELECT t1 . c14 AS c31 FROM ( SELECT CONVERT ( c14 = RAND ( ) IN ( SIGN ( -54 ) & RAND ( ) LIKE CASE -47 WHEN TRUE THEN -94 WHEN -16 THEN 107 ELSE -44 END , -6351076902139315214 , -68 ) , BINARY ) AS c58 FROM t1 ) AS t2 JOIN t1 ON + EXISTS ( SELECT t1 . c14 AS c33 FROM t1 WHERE c58 = ASCII ( -97 ) << t1 . c14 + -119 GROUP BY c14 , c14 ) , t1 AS t3 JOIN t1 AS t4 ON REVERSE ( t3 . c14 ) ) AS t5 JOIN t1 ON t5 . c31 = t5 . c31 ' failed.
ERROR 1815 (HY000): Internal error: Key not found
safe_process[27651]: Child process: 27652, exit: 1

 - the logfile can be found in '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var/log/main.bug112475/bug112475.log'

[100%] shutdown_report                           [ pass ]

--debug - asserts

./mtr --nocheck-testcases bug112475 --debug-server
Logging: ./mtr  --nocheck-testcases bug112475 --debug-server
MySQL Version 8.0.34
Checking supported features
 - Binaries are debug compiled
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.34/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE t0 REPLACE AS SELECT -89 AS c14 ORDER BY c14 ;
INSERT INTO t0 VALUES ( DEFAULT ) , ( DEFAULT ) ;
ALTER TABLE t0 RENAME TO t1 ;
INSERT INTO t1 VALUES ( 12 ) , ( 121 ) ;
SELECT t1 . c14 AS c25 FROM ( SELECT t1 . c14 AS c31 FROM ( SELECT CONVERT ( c14 = RAND ( ) IN ( SIGN ( -54 ) & RAND ( ) LIKE CASE -47 WHEN TRUE THEN -94 WHEN -16 THEN 107 ELSE -44 END , -6351076902139315214 , -68 ) , BINARY ) AS c58 FROM t1 ) AS t2 JOIN t1 ON + EXISTS ( SELECT t1 . c14 AS c33 FROM t1 WHERE c58 = ASCII ( -97 ) << t1 . c14 + -119 GROUP BY c14 , c14 ) , t1 AS t3 JOIN t1 AS t4 ON REVERSE ( t3 . c14 ) ) AS t5 JOIN t1 ON t5 . c31 = t5 . c31 ;
[ 50%] main.bug112475                            [ fail ]
        Test ended at 2023-09-26 11:44:27

CURRENT_TEST: main.bug112475
mysqltest: At line 5: Query 'SELECT t1 . c14 AS c25 FROM ( SELECT t1 . c14 AS c31 FROM ( SELECT CONVERT ( c14 = RAND ( ) IN ( SIGN ( -54 ) & RAND ( ) LIKE CASE -47 WHEN TRUE THEN -94 WHEN -16 THEN 107 ELSE -44 END , -6351076902139315214 , -68 ) , BINARY ) AS c58 FROM t1 ) AS t2 JOIN t1 ON + EXISTS ( SELECT t1 . c14 AS c33 FROM t1 WHERE c58 = ASCII ( -97 ) << t1 . c14 + -119 GROUP BY c14 , c14 ) , t1 AS t3 JOIN t1 AS t4 ON REVERSE ( t3 . c14 ) ) AS t5 JOIN t1 ON t5 . c31 = t5 . c31 ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query