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

Bug #112510	Assertion Failure in /mysql-8.0.34/sql/sql_const_folding.cc:275
Submitted:	26 Sep 2023 12:15	Modified:	29 Sep 2023 13:54
Reporter:	xin wen	Email Updates:
Status:	Closed	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 ( c3 INT , c60 INT ) ;
INSERT INTO t0 VALUES ( 84 , 18 ) , ( -106 , -59 ) ;
ALTER TABLE t0 ADD COLUMN c34 INT AFTER c3 ;
INSERT INTO t0 ( c3 , c60 ) VALUES ( ASIN ( 1157445836312102258 ) * RAND ( ) = 6 IS NOT TRUE , 54 ) , ( -73 , -96 ) ;
SELECT t0 . c34 AS c20 FROM ( SELECT POW ( -32 , -47 ) AS c53 FROM t0 ) AS t1 JOIN t0 ON t1 . c53 = t0 . c3 ;

Will trigger assertion failure:
mysqld: /home/wx/mysql-8.0.34/sql/sql_const_folding.cc:275: bool analyze_int_field_constant(THD*, Item_field*, Item**, Item_func::Functype, bool, Range_placement*, bool*): Assertion `my_decimal_cmp(&n, &dec) == 0' 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=0x55555e0a91e0 "my_decimal_cmp(&n, &dec) == 0", file=0x55555e0a9b88 "/home/wx/mysql-8.0.34/sql/sql_const_folding.cc", line=275, function=<optimized out>) at assert.c:92
#3  0x00007ffff6b03fd6 in __GI___assert_fail (assertion=assertion@entry=0x55555e0a91e0 "my_decimal_cmp(&n, &dec) == 0", file=file@entry=0x55555e0a9b88 "/home/wx/mysql-8.0.34/sql/sql_const_folding.cc", line=line@entry=275, function=function@entry=0x55555e0a9100 "bool analyze_int_field_constant(THD*, Item_field*, Item**, Item_func::Functype, bool, Range_placement*, bool*)") at assert.c:101
#4  0x000055555a1a0546 in analyze_int_field_constant (thd=thd@entry=0x6270002bf900, f=f@entry=0x613000e1caf0, const_val=const_val@entry=0x6150003ebfd8, ft=ft@entry=Item_func::EQ_FUNC, left_has_field=left_has_field@entry=false, place=place@entry=0x7fffc7064a50, discount_equal=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_const_folding.cc:279
#5  0x000055555a1a45d9 in analyze_field_constant (thd=thd@entry=0x6270002bf900, f=0x613000e1caf0, const_val=const_val@entry=0x6150003ebfd8, func=func@entry=0x6150003ebf30, left_has_field=left_has_field@entry=false, place=place@entry=0x7fffc7064a50, discount_equal=0x7fffc7064a30, negative=0x7fffc7064a40) at /home/wx/mysql-8.0.34/sql/sql_const_folding.cc:1010
#6  0x000055555a1a5a45 in fold_condition (thd=thd@entry=0x6270002bf900, cond=cond@entry=0x6150003ebf30, retcond=retcond@entry=0x619000543ba0, cond_value=cond_value@entry=0x61900053c844, manifest_result=manifest_result@entry=false) at /home/wx/mysql-8.0.34/sql/sql_const_folding.cc:1386
#7  0x000055555946127a in fold_condition_exec (thd=thd@entry=0x6270002bf900, cond=cond@entry=0x6150003ebf30, retcond=retcond@entry=0x619000543ba0, cond_value=cond_value@entry=0x61900053c844) at /home/wx/mysql-8.0.34/sql/sql_optimizer.cc:10352
#8  0x0000555559460acc in remove_eq_conds (thd=thd@entry=0x6270002bf900, cond=<optimized out>, retcond=retcond@entry=0x619000543ba0, cond_value=cond_value@entry=0x61900053c844) at /home/wx/mysql-8.0.34/sql/sql_optimizer.cc:10498
#9  0x0000555559488252 in optimize_cond (thd=<optimized out>, cond=cond@entry=0x619000543ba0, cond_equal=cond_equal@entry=0x619000543bc0, join_list=<optimized out>, cond_value=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_optimizer.cc:10326
#10 0x00005555594928f4 in JOIN::optimize (this=0x6190005438b0, finalize_access_paths=finalize_access_paths@entry=true) at /home/wx/mysql-8.0.34/sql/sql_optimizer.cc:472
#11 0x00005555595b28b0 in Query_block::optimize (this=this@entry=0x61900053c5b0, thd=thd@entry=0x6270002bf900, finalize_access_paths=finalize_access_paths@entry=true) at /home/wx/mysql-8.0.34/sql/sql_select.cc:2013
#12 0x0000555559714de9 in Query_expression::optimize (this=this@entry=0x6120002ee670, 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
#13 0x00005555595b1ee9 in Sql_cmd_dml::execute_inner (this=0x60b000158140, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:1007
#14 0x00005555595ce209 in Sql_cmd_dml::execute (this=0x60b000158140, thd=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_select.cc:793
#15 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
#16 0x00005555594c0bfe in dispatch_sql_command (thd=0x6270002bf900, parser_state=parser_state@entry=0x7fffc7068c00) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:5368
#17 0x00005555594c38e2 in dispatch_command (thd=thd@entry=0x6270002bf900, com_data=com_data@entry=0x7fffc706a200, command=<optimized out>) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:2054
#18 0x00005555594c7392 in do_command (thd=thd@entry=0x6270002bf900) at /home/wx/mysql-8.0.34/sql/sql_parse.cc:1439
#19 0x000055555989b7be in handle_connection (arg=arg@entry=0x603000169990) at /home/wx/mysql-8.0.34/sql/conn_handler/connection_handler_per_thread.cc:302
#20 0x000055555cc724e9 in pfs_spawn_thread (arg=0x614000140a60) at /home/wx/mysql-8.0.34/storage/perfschema/pfs.cc:3042
#21 0x00007ffff7568609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#22 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

- debug is affected
 ./mtr --nocheck-testcases bug112510 --debug-server
Logging: ./mtr  --nocheck-testcases bug112510 --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 ( c3 INT , c60 INT ) ;
INSERT INTO t0 VALUES ( 84 , 18 ) , ( -106 , -59 ) ;
ALTER TABLE t0 ADD COLUMN c34 INT AFTER c3 ;
INSERT INTO t0 ( c3 , c60 ) VALUES ( ASIN ( 1157445836312102258 ) * RAND ( ) = 6 IS NOT TRUE , 54 ) , ( -73 , -96 ) ;
SELECT t0 . c34 AS c20 FROM ( SELECT POW ( -32 , -47 ) AS c53 FROM t0 ) AS t1 JOIN t0 ON t1 . c53 = t0 . c3 ;
[ 50%] main.bug112510                            [ fail ]
        Test ended at 2023-09-26 14:17:30

CURRENT_TEST: main.bug112510
mysqltest: At line 5: Query 'SELECT t0 . c34 AS c20 FROM ( SELECT POW ( -32 , -47 ) AS c53 FROM t0 ) AS t1 JOIN t0 ON t1 . c53 = t0 . c3 ' failed.
ERROR 2013 (HY000): Lost connection to MySQL server during query

-bt

#0  0x00007fd035e05aa1 in pthread_kill () from /lib64/libpthread.so.0
#1  0x000000000402c28e in my_write_core(int) ()
#2  0x00000000032d235e in handle_fatal_signal ()
#3  <signal handler called>
#4  0x00007fd034150387 in raise () from /lib64/libc.so.6
#5  0x00007fd034151a78 in abort () from /lib64/libc.so.6
#6  0x00007fd0341491a6 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fd034149252 in __assert_fail () from /lib64/libc.so.6
#8  0x000000000360dddb in analyze_int_field_constant(THD*, Item_field*, Item**, Item_func::Functype, bool, Range_placement*, bool*) ()
#9  0x000000000360f5cc in analyze_field_constant(THD*, Item_field*, Item**, Item_func*, bool, Range_placement*, bool*, bool*) ()
#10 0x000000000360fe80 in fold_condition(THD*, Item*, Item**, Item::cond_result*, bool) ()
#11 0x000000000313e9b4 in fold_condition_exec(THD*, Item*, Item**, Item::cond_result*) ()
#12 0x000000000313e713 in remove_eq_conds(THD*, Item*, Item**, Item::cond_result*) [clone .localalias] ()
#13 0x000000000314c938 in optimize_cond(THD*, Item**, COND_EQUAL**, mem_root_deque<Table_ref*>*, Item::cond_result*) ()
#14 0x000000000314ffee in JOIN::optimize(bool) ()
#15 0x00000000031b755f in Query_block::optimize(THD*, bool) ()
#16 0x000000000322d695 in Query_expression::optimize(THD*, TABLE*, bool, bool) ()
#17 0x00000000031b7187 in Sql_cmd_dml::execute_inner(THD*) ()
#18 0x00000000031c0beb in Sql_cmd_dml::execute(THD*) ()
#19 0x0000000003161263 in mysql_execute_command(THD*, bool) ()
#20 0x00000000031626a2 in dispatch_sql_command(THD*, Parser_state*) ()
#21 0x0000000003163cd5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
#22 0x000000000316596c in do_command(THD*) ()
#23 0x00000000032c437c in handle_connection ()
#24 0x00000000047e1af5 in pfs_spawn_thread ()
#25 0x00007fd035e00ea5 in start_thread () from /lib64/libpthread.so.0
#26 0x00007fd034218b2d in clone () from /lib64/libc.so.6

Posted by developer:
 

commit 32a87561747aa56ab2d6165d3f7db2314f977cae
Author: Dag Wanvik <dag.wanvik@oracle.com>
Date:   Tue May 30 13:09:51 2023 +0200

    Bug#35200367 WHERE condition assertion error
    
    Wrong assertion: we can have loss of precision/truncation error when
    converting float to decimal even though the result is not 0.0, as
    evidenced by the repro:  05687.3E-84
    
    Fix: remove the assertion, and add test for the repro case.