MySQL Bugs: #57552: some functions cause assertion when given a row constructor argument

Bug #57552	some functions cause assertion when given a row constructor argument
Submitted:	19 Oct 2010 7:19	Modified:	30 Jan 2023 12:29
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S6 (Debug Builds)
Version:	5.0.92-debug,5.1.53-debug, 5.5.8-debug, 5.6.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	interval, like escape

Description:
release binary gives error message:
mysql> select interval(1,0,1,(1,1));
ERROR 1241 (21000): Operand should contain 1 column(s)

debug binary asserts:

Version: '5.5.6-rc-debug'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
Assertion failed: 0, file ..\..\mysql-5.5.6-rc\sql\item_row.cc, line 63

 my_sigabrt_handler()[my_thr_init.c:521]
 raise()[winsig.c:597]
 abort()[abort.c:78]
 _wassert()[assert.c:163]
 Item_row::illegal_method_call()[item_row.cc:63]
 Item_row::val_real()[item_row.h:48]
 Item_func_interval::val_int()[item_cmpfunc.cc:2118]
 Item::send()[item.cc:5864]
 Protocol::send_result_set_row()[protocol.cc:866]
 select_send::send_data()[sql_class.cc:1769]
 JOIN::exec()[sql_select.cc:1847]
 mysql_select()[sql_select.cc:2543]
 handle_select()[sql_select.cc:296]
 execute_sqlcom_select()[sql_parse.cc:4565]
 mysql_execute_command()[sql_parse.cc:2175]
 mysql_parse()[sql_parse.cc:5594]
 dispatch_command()[sql_parse.cc:1142]
 do_command()[sql_parse.cc:811]
 do_handle_one_connection()[sql_connect.cc:1191]
 handle_one_connection()[sql_connect.cc:1131]
 pthread_start()[my_winthread.c:62]
 _callthreadstartex()[threadex.c:348]
 _threadstartex()[threadex.c:331]
 BaseThreadStart()

How to repeat:
#on debug build, try some of these queries:

select interval(1,0,1,(1,1));

Suggested fix:
while not devastating by itself, it does mean interval() function must be excluded from any further tests.

Verified on Ubuntu:

openxs@ubuntu:~/dbs/5.5$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  select interval(1,0,1,(1,1));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 101019 10:39:53 mysqld_safe Number of processes running now: 0
101019 10:39:53 mysqld_safe mysqld restarted

mysql> exit
Bye
openxs@ubuntu:~/dbs/5.5$ tail -70 var/ubuntu.err 
101019 10:39:22 InnoDB 1.1.2 started; log sequence number 1595675
101019 10:39:23 [Note] Event Scheduler: Loaded 0 events
101019 10:39:23 [Note] /home/openxs/dbs/5.5/libexec/mysqld: ready for connections.
Version: '5.5.7-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld: item_row.cc:63: void Item_row::illegal_method_call(const char*): Assertion `0' failed.
101019 10:39:53 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337925 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xa5caf00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0xa91c135c thread_stack 0x30000
/home/openxs/dbs/5.5/libexec/mysqld(my_print_stacktrace+0x26)[0x863a838]
/home/openxs/dbs/5.5/libexec/mysqld(handle_segfault+0x2dd)[0x827be54]
[0xce4400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182)[0xe07a82]
/lib/tls/i686/cmov/libc.so.6(__assert_fail+0xf8)[0xdfd718]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN8Item_row10fix_fieldsEP3THDPP4Item+0x0)[0x8234600]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN8Item_row8val_realEv+0x19)[0x8234db1]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN18Item_func_interval7val_intEv+0x395)[0x81f419f]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN4Item4sendEP8ProtocolP6String+0x1ab)[0x81bcc85]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN8Protocol19send_result_set_rowEP4ListI4ItemE+0xda)[0x8270f30]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN11select_send9send_dataER4ListI4ItemE+0xc3)[0x8263093]
/home/openxs/dbs/5.5/libexec/mysqld(_ZN4JOIN4execEv+0x3d2)[0x8300380]
/home/openxs/dbs/5.5/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x2fd)[0x8302704]
/home/openxs/dbs/5.5/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1c2)[0x82fb6f8]
/home/openxs/dbs/5.5/libexec/mysqld[0x829501f]
/home/openxs/dbs/5.5/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x9d0)[0x828d668]
/home/openxs/dbs/5.5/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x1d6)[0x8296ea5]
/home/openxs/dbs/5.5/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x9ce)[0x828b10f]
/home/openxs/dbs/5.5/libexec/mysqld(_Z10do_commandP3THD+0x242)[0x828a547]
/home/openxs/dbs/5.5/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x199)[0x82887b1]
/home/openxs/dbs/5.5/libexec/mysqld(handle_one_connection+0x28)[0x8288611]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e)[0xb0d96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e)[0xea7a4e]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xa5f18a0 = select interval(1,0,1,(1,1))
thd->thread_id=1
thd->killed=NOT_KILLED
...

another testcase:
select '' like '' escape (1,4);

Bug #76468 marked as duplicate of this

Please add testcase from bug 76468 as MTR testcase also

Here is another one for 5.7, 5.8 :

----
drop table if exists t1;
create table `t1` (`a` int,`b` int generated always as ((a,a)) stored not null) engine=innodb;
insert into t1 set a = 1;
----

Version: '5.7.8-rc-debug'  MySQL Community Server (GPL)
Assertion failed: 0, file ..\..\sql\item_row.cc, line 87
mysqld-debug.exe!my_sigabrt_handler()[my_thr_init.c:366]
mysqld-debug.exe!raise()[winsig.c:594]
mysqld-debug.exe!abort()[abort.c:82]
mysqld-debug.exe!_wassert()[assert.c:156]
mysqld-debug.exe!Item_row::illegal_method_call()[item_row.cc:87]
mysqld-debug.exe!Item_row::val_int()[item_row.h:85]
mysqld-debug.exe!Item::save_in_field()[item.cc:6484]
mysqld-debug.exe!update_generated_write_fields()[table.cc:7181]
mysqld-debug.exe!fill_record()[sql_base.cc:9073]
mysqld-debug.exe!fill_record_n_invoke_before_triggers()[sql_base.cc:9272]
mysqld-debug.exe!Sql_cmd_insert::mysql_insert()[sql_insert.cc:626]
mysqld-debug.exe!Sql_cmd_insert::execute()[sql_insert.cc:3014]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:3222]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5201]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1283]
mysqld-debug.exe!do_command()[sql_parse.cc:851]
mysqld-debug.exe!handle_connection()[connection_handler_per_thread.cc:299]
mysqld-debug.exe!pfs_spawn_thread()[pfs.cc:2149]
mysqld-debug.exe!win_thread_start()[my_thread.c:37]
mysqld-debug.exe!_callthreadstartex()[threadex.c:376]
mysqld-debug.exe!_threadstartex()[threadex.c:359]

Did somebody fix this?  Current trunk doesn't crash:

mysql> SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0));
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0));
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql>
mysql> select interval(1,0,1,(1,1));
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql>
mysql> select interval(1,0,1,(1,1));
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table `t1` (`a` int,`b` int generated always as ((a,a)) stored not null) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 set a = 1;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql>

I have the same question... 
https://bugs.launchpad.net/percona-server/+bug/1506697

Please add 5.6

mysql> SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0));
ERROR 2013 (HY000): Lost connection to MySQL server during query

+bt
#0  0x00007f8e85a79741 in __pthread_kill (threadid=<optimized out>, signo=6) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1  0x0000000000a9bd32 in my_write_core (sig=6) at /sda/mysql-5.6.30_dbg/mysys/stacktrace.c:422
#2  0x000000000072bbc0 in handle_fatal_signal (sig=6) at /sda/mysql-5.6.30_dbg/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007f8e8401a5f7 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#5  0x00007f8e8401bce8 in __GI_abort () at abort.c:90
#6  0x00007f8e84013566 in __assert_fail_base (fmt=0x7f8e84163228 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0xf35121 "0", file=file@entry=0xf351e8 "/sda/mysql-5.6.30_dbg/sql/item_row.cc", line=line@entry=87, function=function@entry=0xf35360 <Item_row::illegal_method_call(char const*)::__PRETTY_FUNCTION__> "void Item_row::illegal_method_call(const char*)") at assert.c:92
#7  0x00007f8e84013612 in __GI___assert_fail (assertion=0xf35121 "0", file=0xf351e8 "/sda/mysql-5.6.30_dbg/sql/item_row.cc", line=87, function=0xf35360 <Item_row::illegal_method_call(char const*)::__PRETTY_FUNCTION__> "void Item_row::illegal_method_call(const char*)") at assert.c:101
#8  0x000000000092ff7f in Item_row::illegal_method_call (this=0x7f8dc68253c0, method=0xf35185 "val_str") at /sda/mysql-5.6.30_dbg/sql/item_row.cc:87
#9  0x0000000000930b95 in Item_row::val_str (this=0x7f8dc68253c0) at /sda/mysql-5.6.30_dbg/sql/item_row.h:58
#10 0x00000000006888e4 in Item_func_like::fix_fields (this=0x7f8dc6825480, thd=0x7f8e822a8800, ref=0x7f8dc6825650) at /sda/mysql-5.6.30_dbg/sql/item_cmpfunc.cc:5343
#11 0x000000000077682f in setup_fields (thd=0x7f8e822a8800, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7f8dc6825990, allow_sum_func=true) at /sda/mysql-5.6.30_dbg/sql/sql_base.cc:8348
#12 0x00000000007ff894 in JOIN::prepare (this=0x7f8dc6825748, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, select_lex_arg=0x7f8e822aacd0, unit_arg=0x7f8e822aa688) at /sda/mysql-5.6.30_dbg/sql/sql_resolver.cc:168
#13 0x0000000000807319 in mysql_prepare_select (thd=0x7f8e822a8800, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, select_options=2147748608, result=0x7f8dc6825720, unit=0x7f8e822aa688, select_lex=0x7f8e822aacd0, free_join=0x7f8e86083347) at /sda/mysql-5.6.30_dbg/sql/sql_select.cc:1055
#14 0x00000000008075e7 in mysql_select (thd=0x7f8e822a8800, tables=0x0, wild_num=0, fields=..., conds=0x0, order=0x7f8e822aae98, group=0x7f8e822aadd0, having=0x0, select_options=2147748608, result=0x7f8dc6825720, unit=0x7f8e822aa688, select_lex=0x7f8e822aacd0) at /sda/mysql-5.6.30_dbg/sql/sql_select.cc:1178
#15 0x000000000080581f in handle_select (thd=0x7f8e822a8800, result=0x7f8dc6825720, setup_tables_done_option=0) at /sda/mysql-5.6.30_dbg/sql/sql_select.cc:110
#16 0x00000000007df8da in execute_sqlcom_select (thd=0x7f8e822a8800, all_tables=0x0) at /sda/mysql-5.6.30_dbg/sql/sql_parse.cc:5133
#17 0x00000000007d86cf in mysql_execute_command (thd=0x7f8e822a8800) at /sda/mysql-5.6.30_dbg/sql/sql_parse.cc:2655
#18 0x00000000007e2460 in mysql_parse (thd=0x7f8e822a8800, rawbuf=0x7f8dc6825010 "SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0))", length=38, parser_state=0x7f8e860845a0) at /sda/mysql-5.6.30_dbg/sql/sql_parse.cc:6385
#19 0x00000000007d5649 in dispatch_command (command=COM_QUERY, thd=0x7f8e822a8800, packet=0x7f8dd85c0181 "SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0))", packet_length=38) at /sda/mysql-5.6.30_dbg/sql/sql_parse.cc:1339
#20 0x00000000007d469c in do_command (thd=0x7f8e822a8800) at /sda/mysql-5.6.30_dbg/sql/sql_parse.cc:1036
#21 0x000000000079bf91 in do_handle_one_connection (thd_arg=0x7f8e822a8800) at /sda/mysql-5.6.30_dbg/sql/sql_connect.cc:982
#22 0x000000000079ba7a in handle_one_connection (arg=0x7f8e822a8800) at /sda/mysql-5.6.30_dbg/sql/sql_connect.cc:898
#23 0x0000000000df92d1 in pfs_spawn_thread (arg=0x7f8e6879bae0) at /sda/mysql-5.6.30_dbg/storage/perfschema/pfs.cc:1860
#24 0x00007f8e85a74dc5 in start_thread (arg=0x7f8e86085700) at pthread_create.c:308
#25 0x00007f8e840db21d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:113

Shane, re: [5 Sep 2015 6:25] you may have been using opt build?

5.7.12-debug does not crash

-- 5.6.30 debug build affected

(gdb) bt
#0  0x00007fe9939f3771 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000ab4723 in my_write_core (sig=6) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/mysys/stacktrace.c:422
#2  0x0000000000717862 in handle_fatal_signal (sig=6) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/signal_handler.cc:230
#3  <signal handler called>
#4  0x00007fe9925f95d7 in raise () from /lib64/libc.so.6
#5  0x00007fe9925facc8 in abort () from /lib64/libc.so.6
#6  0x00007fe9925f2546 in __assert_fail_base () from /lib64/libc.so.6
#7  0x00007fe9925f25f2 in __assert_fail () from /lib64/libc.so.6
#8  0x0000000000930d01 in Item_row::illegal_method_call (this=0x7fe934005378, method=0xfa9d74 "val_str") at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/item_row.cc:87
#9  0x0000000000931486 in Item_row::val_str (this=0x7fe934005378) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/item_row.h:58
#10 0x0000000000663ecb in Item_func_like::fix_fields (this=0x7fe934005438, thd=0x22c9360, ref=0x7fe934005608)
    at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/item_cmpfunc.cc:5343
#11 0x000000000075af97 in setup_fields (thd=0x22c9360, ref_pointer_array=..., fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x7fe934005948, allow_sum_func=true)
    at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_base.cc:8348
#12 0x00000000007f4a4d in JOIN::prepare (this=0x7fe934005700, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, select_lex_arg=0x22cb830,
    unit_arg=0x22cb1e8) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_resolver.cc:168
#13 0x000000000080567d in mysql_prepare_select (thd=0x22c9360, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, select_options=2147748608,
    result=0x7fe9340056d8, unit=0x22cb1e8, select_lex=0x22cb830, free_join=0x7fe9692e8c5b) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_select.cc:1055
#14 0x00000000008057d2 in mysql_select (thd=0x22c9360, tables=0x0, wild_num=0, fields=..., conds=0x0, order=0x22cb9f8, group=0x22cb930, having=0x0, select_options=2147748608,
    result=0x7fe9340056d8, unit=0x22cb1e8, select_lex=0x22cb830) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_select.cc:1178
#15 0x0000000000805afd in handle_select (thd=0x22c9360, result=0x7fe9340056d8, setup_tables_done_option=0)
    at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_select.cc:110
#16 0x00000000007cb828 in execute_sqlcom_select (thd=0x22c9360, all_tables=0x0) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_parse.cc:5133
#17 0x00000000007ccd53 in mysql_execute_command (thd=0x22c9360) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_parse.cc:2655
#18 0x00000000007d4362 in mysql_parse (thd=0x22c9360, rawbuf=0x7fe934004fd0 "SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0))", length=38, parser_state=0x7fe9692ea700)
    at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_parse.cc:6385
#19 0x00000000007d5f86 in dispatch_command (command=COM_QUERY, thd=0x22c9360, packet=0x2aa1ff1 "SELECT('a%b' LIKE 'ax%b' ESCAPE (0,0))", packet_length=38)
    at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_parse.cc:1339
#20 0x00000000007d7812 in do_command (thd=0x22c9360) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_parse.cc:1036
#21 0x000000000078c341 in do_handle_one_connection (thd_arg=0x22c9360) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_connect.cc:982
#22 0x000000000078c407 in handle_one_connection (arg=0x22c9360) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/sql/sql_connect.cc:898
#23 0x0000000000dab9ca in pfs_spawn_thread (arg=0x2a83c90) at /export/home/pb2/build/sb_0-18153926-1457023138.3/mysql-5.6.30/storage/perfschema/pfs.cc:1860
#24 0x00007fe9939eedf5 in start_thread () from /lib64/libpthread.so.0
#25 0x00007fe9926ba60d in clone () from /lib64/libc.so.6
(gdb)

Documented fix as follows in the MySQL 8.0.33 changelog:

    CREATE EVENT and ALTER EVENT assumed that all values passed to
    them other than in a DO clause resolved as scalars without
    actually checking the values. This led to assertions when any
    such values actually rows.

    We now perform extra checks for the number of columns when
    resolving such items, and report an error when an item produces
    a row and not a scalar value.

Closed.