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

Bug #112982	Assertion Failure in /mysql-8.0.34/sql/sql_derived.cc:558
Submitted:	7 Nov 2023 8:08	Modified:	14 Nov 2023 19:46
Reporter:	xin wen	Email Updates:
Status:	Closed	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 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 ;

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  0x000055555a1b99cd in copy_field_info (thd=thd@entry=0x6270002cd900, orig_expr=orig_expr@entry=0x61200035b270, cloned_expr=cloned_expr@entry=0x613000d44c70) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:558
#5  0x000055555a1c2be9 in Query_block::clone_expression (this=this@entry=0x61900060a9b0, thd=0x6270002cd900, item=item@entry=0x61200035b270) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:760
#6  0x0000555559c63c8a in Item_field::replace_with_derived_expr (this=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.cc:1069
#7  0x0000555559c2e61b in Item::transform (this=0x613000d44570, transformer=<optimized out>, arg=<optimized out>) at /home/wx/mysql-8.0.34/sql/item.cc:776
#8  0x0000555559d38e38 in Item_func::transform (this=<optimized out>, transformer=&virtual table offset 992, argument=<optimized out>) at /home/wx/mysql-8.0.34/sql/item_func.cc:659
#9  0x0000555559cb82ef in Item_cond::transform (this=this@entry=0x6120003706f0, transformer=(Item *(Item::*)(Item * const, uchar *)) 0x7fffc6ecc820, arg=0x7fffc6eccfe0 "\260@g") at /home/wx/mysql-8.0.34/sql/item_cmpfunc.cc:5749
#10 0x000055555a1ba96f in Condition_pushdown::replace_columns_in_cond (this=this@entry=0x7fffc6ecd320, cond=cond@entry=0x7fffc6ecd348, is_having=is_having@entry=false) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:1395
#11 0x000055555a1c33e5 in Condition_pushdown::make_cond_for_derived (this=this@entry=0x7fffc6ecd320) at /home/wx/mysql-8.0.34/sql/sql_derived.cc:1083
#12 0x0000555559558b5d in Query_block::push_conditions_to_derived_tables (this=0x61900058cfb0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:625
#13 0x0000555559558bc4 in Query_block::push_conditions_to_derived_tables (this=<optimized out>, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:641
#14 0x0000555559558bc4 in Query_block::push_conditions_to_derived_tables (this=<optimized out>, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:641
#15 0x0000555559558bc4 in Query_block::push_conditions_to_derived_tables (this=this@entry=0x61900057b2b0, thd=thd@entry=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:641
#16 0x00005555595781fd in Query_block::apply_local_transforms (this=this@entry=0x61900057b2b0, thd=thd@entry=0x6270002cd900, prune=prune@entry=true) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:830
#17 0x000055555958e6f1 in Query_block::prepare (this=this@entry=0x61900057b2b0, thd=thd@entry=0x6270002cd900, insert_field_list=insert_field_list@entry=0x0) at /home/wx/mysql-8.0.34/sql/sql_resolver.cc:589
#18 0x00005555595b0c63 in Sql_cmd_select::prepare_inner (this=0x60b00015b8f0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:650
#19 0x00005555595cd20b in Sql_cmd_dml::prepare (this=0x60b00015b8f0, thd=0x6270002cd900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:566
#20 0x00005555595cda8d in Sql_cmd_dml::execute (this=0x60b00015b8f0, 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=0x7fffc6ed0c00) 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=0x7fffc6ed2200, 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=0x607000325f00) 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

-
 ./mtr --nocheck-testcases bug112982
Logging: ./mtr  --nocheck-testcases bug112982
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.bug112982                            [ pass ]     95
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------

-
 ./mtr --nocheck-testcases bug112982 --debug-server
Logging: ./mtr  --nocheck-testcases bug112982 --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.bug112982                            [ fail ]
        Test ended at 2023-11-07 12:41:19

CURRENT_TEST: main.bug112982
mysqltest: At line 5: 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

Posted by developer:
 
Not reproducible after the fix for bug#35710183.
No documentation required at this time. Closed.