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

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

    XMLWordPrintable

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 Assertion `typeid(*copy) == typeid(*this)' failed in Item_func_or_sum::do_build_clone

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

          Activity

            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.