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

Bug #112983	Assertion Failure in /mysql-8.0.34/sql/sql_base.cc:7731
Submitted:	7 Nov 2023 8:11	Modified:	7 Nov 2023 12:07
Reporter:	xin wen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S6 (Debug Builds)
Version:	8.0.34, 8.0.35	OS:	Ubuntu (20.04)
Assigned to:		CPU Architecture:	Any

Description:
Run these queries:

CREATE TABLE x ( x VARCHAR ( 1 ) ) ;
INSERT INTO x ( x ) VALUES ( 'x' ) , ( NULL ) , ( 'x' ) , ( 1 IN ( SELECT 1 ) ) ;
WITH x AS ( SELECT 1.000000 AS x , 1 FROM x ) SELECT ( ( x = 1 AND x = 1 ) OR x = 1 ) AS x , x % ( x NOT IN ( x ) AND x IN ( SELECT x FROM ( SELECT 1 FROM x WHERE x != x ORDER BY ( SELECT x ORDER BY 1 ) , CASE WHEN x >= 1 THEN ( - x ) ELSE x IN ( SELECT x WHERE x = x OR ( ( WITH x ( x ) AS ( SELECT x FROM x GROUP BY x , 1.000000 , 1.000000 , 1 , 'x' , 'x' , 1 , 'x' , 'x' , 'x' , TRUE , 'x' , 'x' , 'x' , 'x' , 'x' , 'x' , 'x' , 'x' , 'x' ) SELECT ( SELECT DISTINCT x FROM x WHERE NULL = x ) FROM x WINDOW x AS ( PARTITION BY x ORDER BY x DESC ) ORDER BY ( SELECT x FROM ( SELECT x FROM x UNION SELECT x FROM x GROUP BY x HAVING x * ( SELECT 1 FROM x AS x ) WINDOW x AS ( PARTITION BY x ORDER BY x ) ) AS x ORDER BY 1 ) , x , x ) ) ) END , CASE WHEN x >= 1 THEN ( - x ) ELSE CASE 1.000000 WHEN 1 THEN 'x' WHEN 1 THEN 'x' ELSE 'x' END END % x ) AS x ) ) FROM x ;

Will trigger assertion failure.

GDB info:
#0  0x00007ffff6b1300b in raise () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x00007ffff6af2859 in abort () from /lib/x86_64-linux-gnu/libc.so.6
#2  0x00007ffff6af2729 in ?? () from /lib/x86_64-linux-gnu/libc.so.6
#3  0x00007ffff6b03fd6 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
#4  0x00005555592dfc36 in find_field_in_table_ref (thd=thd@entry=0x6270002cd900, table_list=table_list@entry=0x61b0006572b0, name=name@entry=0x606000d96a90 "x", length=length@entry=1, item_name=<optimized out>, db_name=db_name@entry=0x0, table_name=<optimized out>, ref=<optimized out>, want_privilege=<optimized out>, allow_rowid=<optimized out>, field_index_ptr=<optimized out>, register_tree_change=<optimized out>, actual_table=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_base.cc:7731
#5  0x00005555592e0c26 in find_field_in_tables (thd=thd@entry=0x6270002cd900, item=0x61200031fe70, first_table=first_table@entry=0x61b00065f0b0, last_table=last_table@entry=0x0, ref=ref@entry=0x7fffc6dbbfd0, report_error=report_error@entry=IGNORE_ERRORS, want_privilege=<optimized out>, register_tree_change=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_base.cc:7963
#6  0x000055555956c9e3 in find_order_in_list (thd=thd@entry=0x6270002cd900, ref_item_array=..., tables=tables@entry=0x61b00065f0b0, order=order@entry=0x60d000ac3ee0, fields=fields@entry=0x6190005613f0, is_group_field=<optimized out>, is_window_order=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:4319
#7  0x000055555956deb7 in setup_order (thd=thd@entry=0x6270002cd900, ref_item_array=..., tables=<optimized out>, fields=fields@entry=0x6190005613f0, order=order@entry=0x60d000ac3ee0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:4533
#8  0x000055555958d830 in Query_block::prepare (this=this@entry=0x6190005613b0, thd=thd@entry=0x6270002cd900, insert_field_list=insert_field_list@entry=0x0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:364
#9  0x000055555970ff37 in Query_expression::prepare (this=this@entry=0x61200032df70, thd=thd@entry=0x6270002cd900, sel_result=<optimized out>, insert_field_list=insert_field_list@entry=0x0, added_options=added_options@entry=268435456, removed_options=removed_options@entry=0) at /home/wx/mysql-8.0.34/sql/sql_union.cc:753
#10 0x0000555559e1f1d8 in SubqueryWithResult::prepare (this=0x6080037c9f50, thd=thd@entry=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/item_subselect.cc:2971
#11 0x0000555559e31605 in Item_subselect::fix_fields (this=this@entry=0x61400016c870, thd=thd@entry=0x6270002cd900, ref=ref@entry=0x606000d96498) at /home/wx/mysql-8.0.34/sql/item_subselect.cc:547
#12 0x0000555559e31d02 in Item_in_subselect::fix_fields (this=0x61400016c870, thd=0x6270002cd900, ref=0x606000d96498) at /home/wx/mysql-8.0.34/sql/item_subselect.cc:2525
#13 0x0000555559cc8754 in Item_cond::fix_fields (this=0x613000d2d430, thd=<optimized out>, ref=<optimized out>) at /home/wx/mysql-8.0.34/sql/item_cmpfunc.cc:5522
#14 0x0000555559d5166d in Item_func::fix_func_arg (this=this@entry=0x61200032ddf0, thd=thd@entry=0x6270002cd900, arg=arg@entry=0x61200032dea0) at /home/wx/mysql-8.0.34/sql/item_func.cc:429
#15 0x0000555559d51c48 in Item_func::fix_fields (this=0x61200032ddf0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/item_func.cc:418
#16 0x00005555592cdd7d in setup_fields (thd=thd@entry=0x6270002cd900, want_privilege=<optimized out>, allow_sum_func=allow_sum_func@entry=true, split_sum_funcs=split_sum_funcs@entry=true, column_update=column_update@entry=false, typed_items=typed_items@entry=0x0, fields=<optimized out>, ref_item_array=...) at /home/wx/mysql-8.0.34/sql/sql_base.cc:9016
#17 0x000055555958ccf7 in Query_block::prepare (this=this@entry=0x61900055b9b0, thd=thd@entry=0x6270002cd900, insert_field_list=insert_field_list@entry=0x0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:279
#18 0x00005555595b0c63 in Sql_cmd_select::prepare_inner (this=0x60b0001548e0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:650
#19 0x00005555595cd20b in Sql_cmd_dml::prepare (this=0x60b0001548e0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:566
#20 0x00005555595cda8d in Sql_cmd_dml::execute (this=0x60b0001548e0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:718
#21 0x00005555594bd568 in mysql_execute_command (thd=thd@entry=0x6270002cd900, first_level=first_level@entry=true) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:4719
#22 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002cd900, parser_state=parser_state@entry=0x7fffc6dc0c00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#23 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002cd900, com_data=com_data@entry=0x7fffc6dc2200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#24 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#25 0x000055555989b7be in handle_connection (arg=arg@entry=0x607000325fe0) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#26 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140c60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#27 0x00007ffff7568609 in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#28 0x00007ffff6bef133 in clone () from /lib/x86_64-linux-gnu/libc.so.6

How to repeat:
Run the queries above.

Hello xin wen,

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

regards,
Umesh

-- release - not affected

 ./mtr --nocheck-testcases bug112983
Logging: ./mtr  --nocheck-testcases bug112983
MySQL Version 8.0.35
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.35/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE x ( x INT ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
UPDATE x SET x = 1 WHERE FALSE ;
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
WITH x ( x ) AS ( SELECT ( 1.000000 + 1 = x % 1 OR - 1 >= x IS NOT NULL ) AND x % 1 = 1 AND x IS NOT NULL FROM x ) SELECT x , x FROM x WHERE x BETWEEN ( WITH x AS ( WITH x AS ( SELECT 1 ORDER BY CASE WHEN x >= 1 THEN ( - x ) ELSE x IN ( SELECT x WHERE x IN ( SELECT 1 WHERE x = x OR ( x = 1 AND x = 1 ) ) AND x NOT IN ( ( SELECT x ( x ( x ) ) FROM x ) ) OR ( x = 1 AND x = 1 ) ) END , x + x ) SELECT 1 ) SELECT x AS x FROM x AS x GROUP BY x HAVING ( NOT ( 'x' = 'x' AND ( ( SELECT 1 AS x WHERE ( WITH x AS ( SELECT * FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE x IN ( 1 , ( 'x' , TRUE ) NOT IN ( SELECT x , x FROM x ) ) ) SELECT 1 IN ( SELECT x FROM x AS x GROUP BY ( SELECT 1 GROUP BY x HAVING x < 'x' WINDOW x AS ( PARTITION BY ( SELECT x WHERE TRUE = x ORDER BY x IN ( 1 , ( SELECT x FROM x ORDER BY ( ( 1.000000 ) ^ 1.000000 AND ( SELECT 1 FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE 1 IN ( SELECT 1 UNION SELECT 1 ) ) ) DESC LIMIT 1 OFFSET 1 ) , 1 , 1 ) DESC ) ORDER BY x DESC , x ASC ) ) ) FROM x WHERE ( x = 1 ) OR ( x = 1 ) OR ( x BETWEEN 1 AND 1 ) OR ( x = 1 ) ) ) = ( SELECT 1 AS x WHERE ( SELECT x AS x WHERE 1 > x GROUP BY x , 1.000000 HAVING x > 1 OR ( ( x < 1 OR x > 1 ) AND x % 1 = 1 AND x IS NOT NULL ) OR ( x = 1 AND x = 1 ) AND x = x ) ) OR x = x ) ) ) ) AND 1 ;
x       x
Warnings:
Warning 1292    Truncated incorrect DOUBLE value: 'x'
[ 50%] main.bug112983                            [ pass ]     96
[100%] shutdown_report                           [ pass ]

-- debug build - affected

./mtr --nocheck-testcases bug112983 --debug-server
Logging: ./mtr  --nocheck-testcases bug112983 --debug-server
MySQL Version 8.0.35
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.35/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE x ( x INT ) ;
INSERT INTO x ( x ) VALUES ( 1 ) ;
UPDATE x SET x = 1 WHERE FALSE ;
INSERT INTO x ( x ) VALUES ( 1 ) , ( 1 ) ;
WITH x ( x ) AS ( SELECT ( 1.000000 + 1 = x % 1 OR - 1 >= x IS NOT NULL ) AND x % 1 = 1 AND x IS NOT NULL FROM x ) SELECT x , x FROM x WHERE x BETWEEN ( WITH x AS ( WITH x AS ( SELECT 1 ORDER BY CASE WHEN x >= 1 THEN ( - x ) ELSE x IN ( SELECT x WHERE x IN ( SELECT 1 WHERE x = x OR ( x = 1 AND x = 1 ) ) AND x NOT IN ( ( SELECT x ( x ( x ) ) FROM x ) ) OR ( x = 1 AND x = 1 ) ) END , x + x ) SELECT 1 ) SELECT x AS x FROM x AS x GROUP BY x HAVING ( NOT ( 'x' = 'x' AND ( ( SELECT 1 AS x WHERE ( WITH x AS ( SELECT * FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE x IN ( 1 , ( 'x' , TRUE ) NOT IN ( SELECT x , x FROM x ) ) ) SELECT 1 IN ( SELECT x FROM x AS x GROUP BY ( SELECT 1 GROUP BY x HAVING x < 'x' WINDOW x AS ( PARTITION BY ( SELECT x WHERE TRUE = x ORDER BY x IN ( 1 , ( SELECT x FROM x ORDER BY ( ( 1.000000 ) ^ 1.000000 AND ( SELECT 1 FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE 1 IN ( SELECT 1 UNION SELECT 1 ) ) ) DESC LIMIT 1 OFFSET 1 ) , 1 , 1 ) DESC ) ORDER BY x DESC , x ASC ) ) ) FROM x WHERE ( x = 1 ) OR ( x = 1 ) OR ( x BETWEEN 1 AND 1 ) OR ( x = 1 ) ) ) = ( SELECT 1 AS x WHERE ( SELECT x AS x WHERE 1 > x GROUP BY x , 1.000000 HAVING x > 1 OR ( ( x < 1 OR x > 1 ) AND x % 1 = 1 AND x IS NOT NULL ) OR ( x = 1 AND x = 1 ) AND x = x ) ) OR x = x ) ) ) ) AND 1 ;
[ 50%] main.bug112983                            [ fail ]
        Test ended at 2023-11-07 13:06:47

CURRENT_TEST: main.bug112983
mysqltest: At line 6: Query 'WITH x ( x ) AS ( SELECT ( 1.000000 + 1 = x % 1 OR - 1 >= x IS NOT NULL ) AND x % 1 = 1 AND x IS NOT NULL FROM x ) SELECT x , x FROM x WHERE x BETWEEN ( WITH x AS ( WITH x AS ( SELECT 1 ORDER BY CASE WHEN x >= 1 THEN ( - x ) ELSE x IN ( SELECT x WHERE x IN ( SELECT 1 WHERE x = x OR ( x = 1 AND x = 1 ) ) AND x NOT IN ( ( SELECT x ( x ( x ) ) FROM x ) ) OR ( x = 1 AND x = 1 ) ) END , x + x ) SELECT 1 ) SELECT x AS x FROM x AS x GROUP BY x HAVING ( NOT ( 'x' = 'x' AND ( ( SELECT 1 AS x WHERE ( WITH x AS ( SELECT * FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE x IN ( 1 , ( 'x' , TRUE ) NOT IN ( SELECT x , x FROM x ) ) ) SELECT 1 IN ( SELECT x FROM x AS x GROUP BY ( SELECT 1 GROUP BY x HAVING x < 'x' WINDOW x AS ( PARTITION BY ( SELECT x WHERE TRUE = x ORDER BY x IN ( 1 , ( SELECT x FROM x ORDER BY ( ( 1.000000 ) ^ 1.000000 AND ( SELECT 1 FROM ( SELECT x FROM x UNION SELECT x FROM x ) AS x WHERE 1 IN ( SELECT 1 UNION SELECT 1 ) ) ) DESC LIMIT 1 OFFSET 1 ) , 1 , 1 ) DESC ) ORDER BY x DESC , x ASC ) ) ) FROM x WHERE ( x = 1 ) OR ( x = 1 ) OR ( x BETWEEN 1 AND 1 ) OR ( x = 1 ) ) ) = ( SELECT 1 AS x WHERE ( SELECT x AS x WHERE 1 > x GROUP BY x , 1.000000 HAVING x > 1 OR ( ( x < 1 OR x > 1 ) AND x % 1 = 1 AND x IS NOT NULL ) OR ( x = 1 AND x = 1 ) AND x = x ) ) OR x = x ) ) ) ) AND 1 ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query