Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32022

ERROR 1054 (42S22): Unknown column 'X' in 'NEW' in trigger

Details

    Description

      From https://stackoverflow.com/questions/76985048/triggers-not-working-as-expected-for-set-columns-in-mariadb:

      Simplified version:

      CREATE TABLE t1 ( m set('sms') NOT NULL);
      		 
      CREATE TABLE t2 (i int);
      		 
      CREATE TABLE u (i int);
      		 
       create or replace trigger t1 AFTER INSERT ON t1 FOR EACH ROW INSERT IGNORE INTO t2 SELECT u.i from u JOIN (SELECT 'sms' AS method) m ON FIND_IN_SET(m.method, NEW.m);
      		 
      insert into t1 values ('sms');
      		 
      ERROR 1054 (42S22): Unknown column 'm' in 'field list'

      Requires the NEW.m reference to be in the JOIN ON criteria.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-34683 Types mismatch when cloning items causes debug assertion

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24488 Cannot use CTE in trigger definition

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35090 (Item_func_current_user) Assertion `typeid(*copy) == typeid(*this)' failed in Item_func_or_sum::do_build_clone

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            alice Alice Sherepa added a comment -

            currently fails on 10.5-11.6 with the assertion `typeid(*copy) == typeid(*this)' 

            mariadbd: /10.5/src/sql/item.h:1677: Item* Item::get_copy(THD*) const: Assertion `typeid(*copy) == typeid(*this)' failed.
            		 
            241022 18:08:20 [ERROR] mysqld got signal 6 ;
            		 
             
            		 
            Server version: 10.5.27-MariaDB-debug-log source revision: 9b3413c71f16a3d738af358e288a0168f4c15827
            		 
             
            		 
            /lib/x86_64-linux-gnu/libc.so.6(+0x22729)[0x7fc4d7471729]
            		 
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fc4d7482fd6]
            		 
            sql/item.h:1679(Item::get_copy(THD*) const)[0x55e764078c7e]
            		 
            sql/item.h:3705(Item_field::do_build_clone(THD*) const)[0x55e764123859]
            		 
            sql/item.h:1691(Item::build_clone(THD*) const)[0x55e76427bb29]
            		 
            sql/item.cc:2731(Item_func_or_sum::do_build_clone(THD*) const)[0x55e764c54dbb]
            		 
            sql/item.h:1691(Item::build_clone(THD*) const)[0x55e76427bb29]
            		 
            sql/item.cc:7798(Item::build_pushable_cond(THD*, bool (Item::*)(unsigned char*), unsigned char*))[0x55e764c7f560]
            		 
            sql/sql_derived.cc:1527(pushdown_cond_for_derived(THD*, Item*, TABLE_LIST*))[0x55e76427a3cf]
            		 
            sql/sql_select.cc:2231(JOIN::optimize_inner())[0x55e764424fb9]
            		 
            sql/sql_select.cc:1765(JOIN::optimize())[0x55e76441fd39]
            		 
            sql/sql_select.cc:4904(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55e76444145d]
            		 
            sql/sql_select.cc:449(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e764410c04]
            		 
            sql/sql_parse.cc:4838(mysql_execute_command(THD*))[0x55e764366694]
            		 
            sql/sp_head.cc:3842(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x55e7640f7620]
            		 
            sql/sp_head.cc:3564(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x55e7640f5b51]
            		 
            sql/sp_head.cc:3745(sp_instr_stmt::execute(THD*, unsigned int*))[0x55e7640f6d6d]
            		 
            sql/sp_head.cc:1442(sp_head::execute(THD*, bool))[0x55e7640e70f7]
            		 
            sql/sp_head.cc:1950(sp_head::execute_trigger(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_grant_info*))[0x55e7640ea5f2]
            		 
            sql/sql_trigger.cc:2296(Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool))[0x55e764657ea7]
            		 
            sql/sql_insert.cc:2243(write_record(THD*, TABLE*, st_copy_info*, select_result*))[0x55e76429e39d]
            		 
            sql/sql_insert.cc:1139(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x55e7642952b1]
            		 
            sql/sql_parse.cc:4650(mysql_execute_command(THD*))[0x55e764364a81]
            		 
            sql/sql_parse.cc:8237(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e76437dae7]
            		 
            sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e76435245a]
            		 
            sql/sql_parse.cc:1375(do_command(THD*))[0x55e76434ed34]
            		 
            sql/sql_connect.cc:1407(do_handle_one_connection(CONNECT*, bool))[0x55e7647c1b86]
            		 
            sql/sql_connect.cc:1321(handle_one_connection)[0x55e7647c16e0]
            		 
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55e76547e7fa]
            		 
            nptl/pthread_create.c:478(start_thread)[0x7fc4d7a33609]
            		 
             
            		 
            Query (0x6250001541a8): INSERT IGNORE INTO t2 SELECT u.i from u JOIN (SELECT 'sms' AS method) m ON FIND_IN_SET(m.method, NEW.m)

            alice Alice Sherepa added a comment - currently fails on 10.5-11.6 with the assertion `typeid(*copy) == typeid(*this)' mariadbd: /10.5/src/sql/item.h:1677: Item* Item::get_copy(THD*) const: Assertion `typeid(*copy) == typeid(*this)' failed. 241022 18:08:20 [ERROR] mysqld got signal 6 ;   Server version: 10.5.27-MariaDB-debug-log source revision: 9b3413c71f16a3d738af358e288a0168f4c15827   /lib/x86_64-linux-gnu/libc.so.6(+0x22729)[0x7fc4d7471729] /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7fc4d7482fd6] sql/item.h:1679(Item::get_copy(THD*) const)[0x55e764078c7e] sql/item.h:3705(Item_field::do_build_clone(THD*) const)[0x55e764123859] sql/item.h:1691(Item::build_clone(THD*) const)[0x55e76427bb29] sql/item.cc:2731(Item_func_or_sum::do_build_clone(THD*) const)[0x55e764c54dbb] sql/item.h:1691(Item::build_clone(THD*) const)[0x55e76427bb29] sql/item.cc:7798(Item::build_pushable_cond(THD*, bool (Item::*)(unsigned char*), unsigned char*))[0x55e764c7f560] sql/sql_derived.cc:1527(pushdown_cond_for_derived(THD*, Item*, TABLE_LIST*))[0x55e76427a3cf] sql/sql_select.cc:2231(JOIN::optimize_inner())[0x55e764424fb9] sql/sql_select.cc:1765(JOIN::optimize())[0x55e76441fd39] sql/sql_select.cc:4904(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x55e76444145d] sql/sql_select.cc:449(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55e764410c04] sql/sql_parse.cc:4838(mysql_execute_command(THD*))[0x55e764366694] sql/sp_head.cc:3842(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x55e7640f7620] sql/sp_head.cc:3564(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x55e7640f5b51] sql/sp_head.cc:3745(sp_instr_stmt::execute(THD*, unsigned int*))[0x55e7640f6d6d] sql/sp_head.cc:1442(sp_head::execute(THD*, bool))[0x55e7640e70f7] sql/sp_head.cc:1950(sp_head::execute_trigger(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, st_grant_info*))[0x55e7640ea5f2] sql/sql_trigger.cc:2296(Table_triggers_list::process_triggers(THD*, trg_event_type, trg_action_time_type, bool))[0x55e764657ea7] sql/sql_insert.cc:2243(write_record(THD*, TABLE*, st_copy_info*, select_result*))[0x55e76429e39d] sql/sql_insert.cc:1139(mysql_insert(THD*, TABLE_LIST*, List<Item>&, List<List<Item> >&, List<Item>&, List<Item>&, enum_duplicates, bool, select_result*))[0x55e7642952b1] sql/sql_parse.cc:4650(mysql_execute_command(THD*))[0x55e764364a81] sql/sql_parse.cc:8237(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55e76437dae7] sql/sql_parse.cc:1894(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55e76435245a] sql/sql_parse.cc:1375(do_command(THD*))[0x55e76434ed34] sql/sql_connect.cc:1407(do_handle_one_connection(CONNECT*, bool))[0x55e7647c1b86] sql/sql_connect.cc:1321(handle_one_connection)[0x55e7647c16e0] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55e76547e7fa] nptl/pthread_create.c:478(start_thread)[0x7fc4d7a33609]   Query (0x6250001541a8): INSERT IGNORE INTO t2 SELECT u.i from u JOIN (SELECT 'sms' AS method) m ON FIND_IN_SET(m.method, NEW.m)

            People

              serg Sergei Golubchik
              danblack Daniel Black
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.