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

            Ascending order - Click to sort in descending order
            danblack Daniel Black created issue -
            danblack Daniel Black made changes -
            Field Original Value New Value
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]
            alice Alice Sherepa made changes -
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.6 [ 29515 ]
            alice Alice Sherepa made changes -
            Assignee Igor Babaev [ igor ] Oleksandr Byelkin [ sanja ]
            alice Alice Sherepa made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5.27 [ 29902 ]
            Fix Version/s 10.6.20 [ 29903 ]
            Fix Version/s 10.11.10 [ 29904 ]
            Fix Version/s 11.2.6 [ 29906 ]
            Fix Version/s 11.4.4 [ 29907 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.2 [ 28603 ]
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.6 [ 29515 ]
            Assignee Oleksandr Byelkin [ sanja ] Sergei Golubchik [ serg ]
            Resolution Fixed [ 1 ]
            Status In Review [ 10002 ] Closed [ 6 ]

            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.