[MDEV-32022] ERROR 1054 (42S22): Unknown column 'X' in 'NEW' in trigger Created: 2023-08-27  Updated: 2023-08-27

Status: Open
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.4.26, 10.6.9, 10.9.2, 10.4.32
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Daniel Black Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-24488 Cannot use CTE in trigger definition Confirmed

 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.
Generated at Thu Feb 08 10:28:13 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.