Details

    Description

      171016 19:22:54 [ERROR] mysqld got signal 11 ;
      Thread pointer: 0x7fbe940008c8
      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 = 0x7fbf9ac6ed70 thread_stack 0x40000
      (my_addr_resolve failure: fork)
      /usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x562e645ba80e]
      /usr/sbin/mysqld(handle_fatal_signal+0x30d) [0x562e640495cd]
      /lib64/libpthread.so.0(+0xf5e0) [0x7fcb411805e0]
      /usr/sbin/mysqld(Arg_comparator::set_cmp_func(Item_func_or_sum*, Item**, Item**)+0x3d) [0x562e64080a9d]
      /usr/sbin/mysqld(Arg_comparator::set_cmp_func_for_row_arguments()+0x1af) [0x562e640812ef]
      /usr/sbin/mysqld(Arg_comparator::set_cmp_func(Item_func_or_sum*, Item**, Item**)+0x8a) [0x562e64080aea]
      /usr/sbin/mysqld(Item_func::setup_args_and_comparator(THD*, Arg_comparator*)+0x61) [0x562e64080b61]
      /usr/sbin/mysqld(Item_func::fix_fields(THD*, Item**)+0x152) [0x562e640aeb52]
      /usr/sbin/mysqld(Item_cond::fix_fields(THD*, Item**)+0x108) [0x562e6407c158]
      /usr/sbin/mysqld(Item_cond::fix_fields(THD*, Item**)+0x108) [0x562e6407c158]
      /usr/sbin/mysqld(sp_prepare_func_item(THD*, Item**, unsigned int)+0x94) [0x562e63e0b9d4]
      /usr/sbin/mysqld(sp_instr_jump_if_not::exec_core(THD*, unsigned int*)+0x1e) [0x562e63e0ba0e]
      /usr/sbin/mysqld(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*)+0x325) [0x562e63e113a5]
      /usr/sbin/mysqld(sp_head::execute(THD*, bool)+0x7ee) [0x562e63e0d37e]
      /usr/sbin/mysqld(sp_head::execute_function(THD*, Item**, unsigned int, Field*)+0x67f) [0x562e63e0ed1f]
      /usr/sbin/mysqld(Item_func_sp::execute_impl(THD*)+0xfd) [0x562e640bb2ed]
      /usr/sbin/mysqld(Item_func_sp::execute()+0x2c) [0x562e640bb3fc]
      /usr/sbin/mysqld(Item_func_sp::val_str(String*)+0x58) [0x562e640c4768]
      /usr/sbin/mysqld(Type_handler::Item_send_str(Item*, Protocol*, st_value*) const+0x1c) [0x562e63faf86c]
      /usr/sbin/mysqld(Protocol::send_result_set_row(List<Item>*)+0x13f) [0x562e63df0e5f]
      /usr/sbin/mysqld(select_send::send_data(List<Item>&)+0x53) [0x562e63e58a63]
      /usr/sbin/mysqld(JOIN::exec_inner()+0xaa4) [0x562e63ee3e94]
      /usr/sbin/mysqld(JOIN::exec()+0x33) [0x562e63ee4043]
      /usr/sbin/mysqld(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*)+0x11a) [0x562e63ee419a]
      /usr/sbin/mysqld(handle_select(THD*, LEX*, select_result*, unsigned long)+0x15e) [0x562e63ee4bfe]
      /usr/sbin/mysqld(+0x475855) [0x562e63db0855]
      /usr/sbin/mysqld(mysql_execute_command(THD*)+0x6933) [0x562e63e93a63]
      /usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool)+0x392) [0x562e63e969a2]
      /usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool)+0x295d) [0x562e63e9a41d]
      /usr/sbin/mysqld(do_command(THD*)+0x149) [0x562e63e9b149]
      /usr/sbin/mysqld(do_handle_one_connection(CONNECT*)+0x1aa) [0x562e63f5f29a]
      /usr/sbin/mysqld(handle_one_connection+0x3d) [0x562e63f5f3bd]
      /lib64/libpthread.so.0(+0x7e25) [0x7fcb41178e25]
      /lib64/libc.so.6(clone+0x6d) [0x7fcb3f7cb34d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7fbe9400fd40): select function_name()
      Connection ID (thread ID): 26257
      Status: NOT_KILLED
      

      After that, it crashes one after another (two times more) and we can see this in error log.

      stack_bottom = 0x7ff76c0fcd70 thread_stack 0x40000
      *** buffer overflow detected ***: /usr/sbin/mysqld terminated
      

      That function details and error logs are attached with support ticket.

      Attachments

        Issue Links

          Activity

            --source include/have_innodb.inc
             
            SET sql_mode='ORACLE';
             
            CREATE TABLE t (c VARCHAR(16), KEY(c)) ENGINE=InnoDB;
            INSERT INTO t VALUES ('foo');
             
            --delimiter $$
            CREATE FUNCTION f() RETURN VARCHAR(16)
            IS
              v VARCHAR2(16);
            BEGIN
              FOR v IN ( SELECT DISTINCT c FROM t )
              LOOP
                IF (v = 'bar') THEN
                  SELECT 1 INTO @a;
                END IF;
              END LOOP;
              RETURN 'qux';
            END $$
            --delimiter ;
             
            SELECT f();
             
            # Cleanup
            DROP FUNCTION f;
            DROP TABLE t;
            

            10.3 5b3da95bf362

            #3  <signal handler called>
            #4  0x00005594f477e654 in Arg_comparator::set_cmp_func (this=0x7fbda0044cc0, owner_arg=0x7fbda012d648, a1=0x7fbda0044230, a2=0x0) at /data/src/10.3/sql/item_cmpfunc.cc:486
            #5  0x00005594f4505d45 in Arg_comparator::set_cmp_func (this=0x7fbda0044cc0, owner_arg=0x7fbda012d648, a1=0x7fbda0044230, a2=0x0, set_null_arg=true) at /data/src/10.3/sql/item_cmpfunc.h:100
            #6  0x00005594f477e9f0 in Arg_comparator::set_cmp_func_for_row_arguments (this=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:517
            #7  0x00005594f477ea8a in Arg_comparator::set_cmp_func_row (this=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:528
            #8  0x00005594f464ceb8 in Type_handler_row::set_comparator_func (this=0x5594f5bb2f40 <type_handler_row>, cmp=0x7fbda012d708) at /data/src/10.3/sql/sql_type.cc:2540
            #9  0x00005594f477e731 in Arg_comparator::set_cmp_func (this=0x7fbda012d708, owner_arg=0x7fbda012d648, a1=0x7fbda012d6d8, a2=0x7fbda012d6e0) at /data/src/10.3/sql/item_cmpfunc.cc:494
            #10 0x00005594f4505d45 in Arg_comparator::set_cmp_func (this=0x7fbda012d708, owner_arg=0x7fbda012d648, a1=0x7fbda012d6d8, a2=0x7fbda012d6e0, set_null_arg=true) at /data/src/10.3/sql/item_cmpfunc.h:100
            #11 0x00005594f477e56c in Item_func::setup_args_and_comparator (this=0x7fbda012d648, thd=0x7fbda0000b00, cmp=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:448
            #12 0x00005594f477e5dd in Item_bool_rowready_func2::fix_length_and_dec (this=0x7fbda012d648) at /data/src/10.3/sql/item_cmpfunc.cc:462
            #13 0x00005594f47bad48 in Item_func::fix_fields (this=0x7fbda012d648, thd=0x7fbda0000b00, ref=0x7fbda012d800) at /data/src/10.3/sql/item_func.cc:370
            #14 0x00005594f48c46cd in sp_prepare_func_item (thd=0x7fbda0000b00, it_addr=0x7fbda012d800, cols=1) at /data/src/10.3/sql/sp_head.cc:354
            #15 0x00005594f48cdaf6 in sp_instr_jump_if_not::exec_core (this=0x7fbda012d7b0, thd=0x7fbda0000b00, nextp=0x7fbde0761054) at /data/src/10.3/sql/sp_head.cc:3697
            #16 0x00005594f48cc0da in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fbda012d808, thd=0x7fbda0000b00, nextp=0x7fbde0761054, open_tables=true, instr=0x7fbda012d7b0) at /data/src/10.3/sql/sp_head.cc:3109
            #17 0x00005594f48cda93 in sp_instr_jump_if_not::execute (this=0x7fbda012d7b0, thd=0x7fbda0000b00, nextp=0x7fbde0761054) at /data/src/10.3/sql/sp_head.cc:3687
            #18 0x00005594f48c6997 in sp_head::execute (this=0x7fbda0129b48, thd=0x7fbda0000b00, merge_da_on_success=true) at /data/src/10.3/sql/sp_head.cc:1200
            #19 0x00005594f48c826a in sp_head::execute_function (this=0x7fbda0129b48, thd=0x7fbda0000b00, argp=0x0, argcount=0, return_value_fld=0x7fbda0016fa0) at /data/src/10.3/sql/sp_head.cc:1808
            #20 0x00005594f47d0810 in Item_func_sp::execute_impl (this=0x7fbda0014d28, thd=0x7fbda0000b00) at /data/src/10.3/sql/item_func.cc:6493
            #21 0x00005594f47d05a6 in Item_func_sp::execute (this=0x7fbda0014d28) at /data/src/10.3/sql/item_func.cc:6426
            #22 0x00005594f47d65fe in Item_func_sp::val_str (this=0x7fbda0014d28, str=0x7fbde0761a70) at /data/src/10.3/sql/item_func.h:2825
            #23 0x00005594f4651bd3 in Type_handler::Item_send_str (this=0x5594f5bb3010 <type_handler_varchar>, item=0x7fbda0014d28, protocol=0x7fbda0001098, buf=0x7fbde0761a40) at /data/src/10.3/sql/sql_type.cc:5136
            #24 0x00005594f465554a in Type_handler_string_result::Item_send (this=0x5594f5bb3010 <type_handler_varchar>, item=0x7fbda0014d28, protocol=0x7fbda0001098, buf=0x7fbde0761a40) at /data/src/10.3/sql/sql_type.h:1668
            #25 0x00005594f43a54e8 in Item::send (this=0x7fbda0014d28, protocol=0x7fbda0001098, buffer=0x7fbde0761a40) at /data/src/10.3/sql/item.h:772
            #26 0x00005594f43a0d46 in Protocol::send_result_set_row (this=0x7fbda0001098, row_items=0x7fbda0005070) at /data/src/10.3/sql/protocol.cc:985
            #27 0x00005594f4427f9e in select_send::send_data (this=0x7fbda00169d8, items=...) at /data/src/10.3/sql/sql_class.cc:2927
            #28 0x00005594f44c61c6 in JOIN::exec_inner (this=0x7fbda00169f8) at /data/src/10.3/sql/sql_select.cc:3498
            #29 0x00005594f44c5c24 in JOIN::exec (this=0x7fbda00169f8) at /data/src/10.3/sql/sql_select.cc:3417
            #30 0x00005594f44c6e05 in mysql_select (thd=0x7fbda0000b00, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbda00169d8, unit=0x7fbda0004818, select_lex=0x7fbda0004f50) at /data/src/10.3/sql/sql_select.cc:3817
            #31 0x00005594f44baf2e in handle_select (thd=0x7fbda0000b00, lex=0x7fbda0004750, result=0x7fbda00169d8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
            #32 0x00005594f448632b in execute_sqlcom_select (thd=0x7fbda0000b00, all_tables=0x0) at /data/src/10.3/sql/sql_parse.cc:6477
            #33 0x00005594f447cb00 in mysql_execute_command (thd=0x7fbda0000b00) at /data/src/10.3/sql/sql_parse.cc:3740
            #34 0x00005594f4489c5e in mysql_parse (thd=0x7fbda0000b00, rawbuf=0x7fbda0014a98 "SELECT f()", length=10, parser_state=0x7fbde0763610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7933
            #35 0x00005594f447763e in dispatch_command (command=COM_QUERY, thd=0x7fbda0000b00, packet=0x7fbda011dfa1 "", packet_length=10, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1828
            #36 0x00005594f447605f in do_command (thd=0x7fbda0000b00) at /data/src/10.3/sql/sql_parse.cc:1370
            #37 0x00005594f45cde2a in do_handle_one_connection (connect=0x5594f6e46ad0) at /data/src/10.3/sql/sql_connect.cc:1418
            #38 0x00005594f45cdbb7 in handle_one_connection (arg=0x5594f6e46ad0) at /data/src/10.3/sql/sql_connect.cc:1324
            #39 0x00005594f4a4f0a8 in pfs_spawn_thread (arg=0x5594f6e63930) at /data/src/10.3/storage/perfschema/pfs.cc:1863
            #40 0x00007fbdf1876494 in start_thread (arg=0x7fbde0764700) at pthread_create.c:333
            #41 0x00007fbdefc5c93f in clone () from /lib/x86_64-linux-gnu/libc.so.6
            

            elenst Elena Stepanova added a comment - --source include/have_innodb.inc   SET sql_mode= 'ORACLE' ;   CREATE TABLE t (c VARCHAR (16), KEY (c)) ENGINE=InnoDB; INSERT INTO t VALUES ( 'foo' );   --delimiter $$ CREATE FUNCTION f() RETURN VARCHAR (16) IS v VARCHAR2(16); BEGIN FOR v IN ( SELECT DISTINCT c FROM t ) LOOP IF (v = 'bar' ) THEN SELECT 1 INTO @a; END IF ; END LOOP; RETURN 'qux' ; END $$ --delimiter ;   SELECT f();   # Cleanup DROP FUNCTION f; DROP TABLE t; 10.3 5b3da95bf362 #3 <signal handler called> #4 0x00005594f477e654 in Arg_comparator::set_cmp_func (this=0x7fbda0044cc0, owner_arg=0x7fbda012d648, a1=0x7fbda0044230, a2=0x0) at /data/src/10.3/sql/item_cmpfunc.cc:486 #5 0x00005594f4505d45 in Arg_comparator::set_cmp_func (this=0x7fbda0044cc0, owner_arg=0x7fbda012d648, a1=0x7fbda0044230, a2=0x0, set_null_arg=true) at /data/src/10.3/sql/item_cmpfunc.h:100 #6 0x00005594f477e9f0 in Arg_comparator::set_cmp_func_for_row_arguments (this=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:517 #7 0x00005594f477ea8a in Arg_comparator::set_cmp_func_row (this=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:528 #8 0x00005594f464ceb8 in Type_handler_row::set_comparator_func (this=0x5594f5bb2f40 <type_handler_row>, cmp=0x7fbda012d708) at /data/src/10.3/sql/sql_type.cc:2540 #9 0x00005594f477e731 in Arg_comparator::set_cmp_func (this=0x7fbda012d708, owner_arg=0x7fbda012d648, a1=0x7fbda012d6d8, a2=0x7fbda012d6e0) at /data/src/10.3/sql/item_cmpfunc.cc:494 #10 0x00005594f4505d45 in Arg_comparator::set_cmp_func (this=0x7fbda012d708, owner_arg=0x7fbda012d648, a1=0x7fbda012d6d8, a2=0x7fbda012d6e0, set_null_arg=true) at /data/src/10.3/sql/item_cmpfunc.h:100 #11 0x00005594f477e56c in Item_func::setup_args_and_comparator (this=0x7fbda012d648, thd=0x7fbda0000b00, cmp=0x7fbda012d708) at /data/src/10.3/sql/item_cmpfunc.cc:448 #12 0x00005594f477e5dd in Item_bool_rowready_func2::fix_length_and_dec (this=0x7fbda012d648) at /data/src/10.3/sql/item_cmpfunc.cc:462 #13 0x00005594f47bad48 in Item_func::fix_fields (this=0x7fbda012d648, thd=0x7fbda0000b00, ref=0x7fbda012d800) at /data/src/10.3/sql/item_func.cc:370 #14 0x00005594f48c46cd in sp_prepare_func_item (thd=0x7fbda0000b00, it_addr=0x7fbda012d800, cols=1) at /data/src/10.3/sql/sp_head.cc:354 #15 0x00005594f48cdaf6 in sp_instr_jump_if_not::exec_core (this=0x7fbda012d7b0, thd=0x7fbda0000b00, nextp=0x7fbde0761054) at /data/src/10.3/sql/sp_head.cc:3697 #16 0x00005594f48cc0da in sp_lex_keeper::reset_lex_and_exec_core (this=0x7fbda012d808, thd=0x7fbda0000b00, nextp=0x7fbde0761054, open_tables=true, instr=0x7fbda012d7b0) at /data/src/10.3/sql/sp_head.cc:3109 #17 0x00005594f48cda93 in sp_instr_jump_if_not::execute (this=0x7fbda012d7b0, thd=0x7fbda0000b00, nextp=0x7fbde0761054) at /data/src/10.3/sql/sp_head.cc:3687 #18 0x00005594f48c6997 in sp_head::execute (this=0x7fbda0129b48, thd=0x7fbda0000b00, merge_da_on_success=true) at /data/src/10.3/sql/sp_head.cc:1200 #19 0x00005594f48c826a in sp_head::execute_function (this=0x7fbda0129b48, thd=0x7fbda0000b00, argp=0x0, argcount=0, return_value_fld=0x7fbda0016fa0) at /data/src/10.3/sql/sp_head.cc:1808 #20 0x00005594f47d0810 in Item_func_sp::execute_impl (this=0x7fbda0014d28, thd=0x7fbda0000b00) at /data/src/10.3/sql/item_func.cc:6493 #21 0x00005594f47d05a6 in Item_func_sp::execute (this=0x7fbda0014d28) at /data/src/10.3/sql/item_func.cc:6426 #22 0x00005594f47d65fe in Item_func_sp::val_str (this=0x7fbda0014d28, str=0x7fbde0761a70) at /data/src/10.3/sql/item_func.h:2825 #23 0x00005594f4651bd3 in Type_handler::Item_send_str (this=0x5594f5bb3010 <type_handler_varchar>, item=0x7fbda0014d28, protocol=0x7fbda0001098, buf=0x7fbde0761a40) at /data/src/10.3/sql/sql_type.cc:5136 #24 0x00005594f465554a in Type_handler_string_result::Item_send (this=0x5594f5bb3010 <type_handler_varchar>, item=0x7fbda0014d28, protocol=0x7fbda0001098, buf=0x7fbde0761a40) at /data/src/10.3/sql/sql_type.h:1668 #25 0x00005594f43a54e8 in Item::send (this=0x7fbda0014d28, protocol=0x7fbda0001098, buffer=0x7fbde0761a40) at /data/src/10.3/sql/item.h:772 #26 0x00005594f43a0d46 in Protocol::send_result_set_row (this=0x7fbda0001098, row_items=0x7fbda0005070) at /data/src/10.3/sql/protocol.cc:985 #27 0x00005594f4427f9e in select_send::send_data (this=0x7fbda00169d8, items=...) at /data/src/10.3/sql/sql_class.cc:2927 #28 0x00005594f44c61c6 in JOIN::exec_inner (this=0x7fbda00169f8) at /data/src/10.3/sql/sql_select.cc:3498 #29 0x00005594f44c5c24 in JOIN::exec (this=0x7fbda00169f8) at /data/src/10.3/sql/sql_select.cc:3417 #30 0x00005594f44c6e05 in mysql_select (thd=0x7fbda0000b00, tables=0x0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fbda00169d8, unit=0x7fbda0004818, select_lex=0x7fbda0004f50) at /data/src/10.3/sql/sql_select.cc:3817 #31 0x00005594f44baf2e in handle_select (thd=0x7fbda0000b00, lex=0x7fbda0004750, result=0x7fbda00169d8, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378 #32 0x00005594f448632b in execute_sqlcom_select (thd=0x7fbda0000b00, all_tables=0x0) at /data/src/10.3/sql/sql_parse.cc:6477 #33 0x00005594f447cb00 in mysql_execute_command (thd=0x7fbda0000b00) at /data/src/10.3/sql/sql_parse.cc:3740 #34 0x00005594f4489c5e in mysql_parse (thd=0x7fbda0000b00, rawbuf=0x7fbda0014a98 "SELECT f()", length=10, parser_state=0x7fbde0763610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7933 #35 0x00005594f447763e in dispatch_command (command=COM_QUERY, thd=0x7fbda0000b00, packet=0x7fbda011dfa1 "", packet_length=10, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1828 #36 0x00005594f447605f in do_command (thd=0x7fbda0000b00) at /data/src/10.3/sql/sql_parse.cc:1370 #37 0x00005594f45cde2a in do_handle_one_connection (connect=0x5594f6e46ad0) at /data/src/10.3/sql/sql_connect.cc:1418 #38 0x00005594f45cdbb7 in handle_one_connection (arg=0x5594f6e46ad0) at /data/src/10.3/sql/sql_connect.cc:1324 #39 0x00005594f4a4f0a8 in pfs_spawn_thread (arg=0x5594f6e63930) at /data/src/10.3/storage/perfschema/pfs.cc:1863 #40 0x00007fbdf1876494 in start_thread (arg=0x7fbde0764700) at pthread_create.c:333 #41 0x00007fbdefc5c93f in clone () from /lib/x86_64-linux-gnu/libc.so.6

            The same crash is repeatable:

            • With a MyISAM table
            • With an explicit %ROWTYPE declaration instead of an explicit declaration in FOR.

            SET sql_mode='ORACLE';
            CREATE OR REPLACE TABLE t1 (c VARCHAR(16), KEY(c)) ENGINE=MyISAM;
            INSERT INTO t1 VALUES ('foo');
            DELIMITER $$
            CREATE OR REPLACE FUNCTION f1() RETURN VARCHAR(16)
            IS
              v t1%ROWTYPE;
            BEGIN
              IF v = 'bar' THEN
                NULL;
              END IF;
              RETURN 'qux';
            END $$
            DELIMITER ;
            SELECT f1();
            

            Note, the table t1 must have one column only. With more than one column in the table, it returns an error as expected:

            ERROR 1241 (21000): Operand should contain 2 column(s
            

            bar Alexander Barkov added a comment - The same crash is repeatable: With a MyISAM table With an explicit %ROWTYPE declaration instead of an explicit declaration in FOR . SET sql_mode= 'ORACLE' ; CREATE OR REPLACE TABLE t1 (c VARCHAR (16), KEY (c)) ENGINE=MyISAM; INSERT INTO t1 VALUES ( 'foo' ); DELIMITER $$ CREATE OR REPLACE FUNCTION f1() RETURN VARCHAR (16) IS v t1%ROWTYPE; BEGIN IF v = 'bar' THEN NULL ; END IF ; RETURN 'qux' ; END $$ DELIMITER ; SELECT f1(); Note, the table t1 must have one column only. With more than one column in the table, it returns an error as expected: ERROR 1241 (21000): Operand should contain 2 column(s
            bar Alexander Barkov added a comment - - edited

            The same problem is repeatable with an ROW declaration with an explicit field list:

            SET sql_mode='ORACLE';
            DELIMITER $$
            CREATE OR REPLACE FUNCTION f1() RETURN VARCHAR(16)
            IS
              v ROW(a INT);
            BEGIN
              IF v = 'bar' THEN
                NULL;
              END IF;
              RETURN 'qux';
            END $$
            DELIMITER ;
            SELECT f1();
            

            With sql_mode=DEFAULT it also crashes:

            SET sql_mode=DEFAULT;
            DELIMITER $$
            CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR(16)
            BEGIN
              DECLARE v ROW(a INT);
              IF v = 'bar' THEN
                RETURN 'eq';
              END IF;
              RETURN 'ne';
            END $$
            DELIMITER ;
            SELECT f1();
            

            bar Alexander Barkov added a comment - - edited The same problem is repeatable with an ROW declaration with an explicit field list: SET sql_mode= 'ORACLE' ; DELIMITER $$ CREATE OR REPLACE FUNCTION f1() RETURN VARCHAR (16) IS v ROW(a INT ); BEGIN IF v = 'bar' THEN NULL ; END IF ; RETURN 'qux' ; END $$ DELIMITER ; SELECT f1(); With sql_mode=DEFAULT it also crashes: SET sql_mode= DEFAULT ; DELIMITER $$ CREATE OR REPLACE FUNCTION f1() RETURNS VARCHAR (16) BEGIN DECLARE v ROW(a INT ); IF v = 'bar' THEN RETURN 'eq' ; END IF ; RETURN 'ne' ; END $$ DELIMITER ; SELECT f1();
            bar Alexander Barkov added a comment - - edited

            This script erroneously returns a result, it should return an error instead,
            as it mixes ROW and scalar expressions in the IN operator:

            SET sql_mode=DEFAULT;
            DELIMITER $$
            BEGIN NOT ATOMIC
              DECLARE v ROW(a INT);
              SELECT v IN ('a','b');
            END $$
            DELIMITER ;
            

            +----------------+
            | v IN ('a','b') |
            +----------------+
            |           NULL |
            +----------------+
            

            Note, if I slightly rewrite the script, it correctly returns an error:

            SET sql_mode=DEFAULT;
            DELIMITER $$
            BEGIN NOT ATOMIC
              DECLARE v ROW(a INT);
              SELECT 'a' IN (v,'b');
            END $$
            DELIMITER ;
            

            ERROR 1241 (21000): Operand should contain 1 column(s)
            

            But the error text is misleading.

            bar Alexander Barkov added a comment - - edited This script erroneously returns a result, it should return an error instead, as it mixes ROW and scalar expressions in the IN operator: SET sql_mode= DEFAULT ; DELIMITER $$ BEGIN NOT ATOMIC DECLARE v ROW(a INT ); SELECT v IN ( 'a' , 'b' ); END $$ DELIMITER ; +----------------+ | v IN ('a','b') | +----------------+ | NULL | +----------------+ Note, if I slightly rewrite the script, it correctly returns an error: SET sql_mode= DEFAULT ; DELIMITER $$ BEGIN NOT ATOMIC DECLARE v ROW(a INT ); SELECT 'a' IN (v, 'b' ); END $$ DELIMITER ; ERROR 1241 (21000): Operand should contain 1 column(s) But the error text is misleading.

            MariaDB should be fixed to report errors on attempts to compare a ROW and a scalar expressions.

            A similar script in Oracle returns an error.

            DROP TABLE t1;
            CREATE TABLE t1 (c VARCHAR(16));
            INSERT INTO t1 VALUES ('foo');
             
            DROP FUNCTION f1;
            CREATE FUNCTION f1 RETURN VARCHAR
            IS
              v VARCHAR2(16);
            BEGIN
              FOR v IN ( SELECT DISTINCT c FROM t1 )
              LOOP
                IF (v = 'bar') THEN
                  RETURN 'eq';
                END IF;
              END LOOP;
              RETURN 'qux';
            END;
            /
            SHOW ERRORS;
            

            LINE/COL ERROR
            -------- -----------------------------------------------------------------
            7/5	 PL/SQL: Statement ignored
            7/11	 PLS-00306: wrong number or types of arguments in call to '='
            

            bar Alexander Barkov added a comment - MariaDB should be fixed to report errors on attempts to compare a ROW and a scalar expressions. A similar script in Oracle returns an error. DROP TABLE t1; CREATE TABLE t1 (c VARCHAR (16)); INSERT INTO t1 VALUES ( 'foo' );   DROP FUNCTION f1; CREATE FUNCTION f1 RETURN VARCHAR IS v VARCHAR2(16); BEGIN FOR v IN ( SELECT DISTINCT c FROM t1 ) LOOP IF (v = 'bar' ) THEN RETURN 'eq' ; END IF ; END LOOP; RETURN 'qux' ; END ; / SHOW ERRORS; LINE/COL ERROR -------- ----------------------------------------------------------------- 7/5 PL/SQL: Statement ignored 7/11 PLS-00306: wrong number or types of arguments in call to '='

            People

              bar Alexander Barkov
              niljoshi Nilnandan Joshi
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.