The "NEW" table in a trigger sometimes contains column and sometimes it does not

Question

I've been trying to create an INSERT trigger and in a simple trigger body I can access the "NEW" table just fine.

But in an example where the "NEW" table is used deeper into the body it no longer finds the column I need.

I can find the column "NEW.nr_legitimatie" in:

CREATE DEFINER=`root`@`localhost` 
TRIGGER `calculMedie` 
AFTER INSERT ON `note` 
FOR EACH ROW 
INSERT INTO medii (MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3)
VALUES (new.nr_legitimatie, 1, 2, 3, 4)

I can no longer find the column "NEW.nr_legitimatie" in:

CREATE DEFINER=`root`@`localhost` 
TRIGGER `calculMedie` 
AFTER INSERT ON `note` 
FOR EACH ROW 
INSERT INTO medii (MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3)
WITH date AS (
    WITH medii_pe_coloane AS (
        SELECT medie1.nr_legitimatie, 
            AVG(medie1.maxim) as medie_an_1, 
            AVG(medie2.maxim) as medie_an_2, 
            AVG(medie3.maxim) as medie_an_3
        FROM 
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 1 AND nr_legitimatie = new.nr_legitimatie
        GROUP BY nr_legitimatie, disciplina
        ) as medie1,
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 2 AND nr_legitimatie = new.nr_legitimatie
        GROUP BY nr_legitimatie, disciplina
        ) as medie2,
        (
        SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
        FROM note 
        WHERE an_studiu = 3 AND nr_legitimatie = new.nr_legitimatie
        GROUP BY nr_legitimatie, disciplina
        ) as medie3
    ),
    medii_union AS (
        SELECT medii_pe_coloane.medie_an_1 as medie from medii_pe_coloane
        UNION ALL
        SELECT medii_pe_coloane.medie_an_2 as medie from medii_pe_coloane
        UNION ALL
        SELECT medii_pe_coloane.medie_an_3 as medie from medii_pe_coloane
    ),
    medie_generala AS (
        SELECT AVG(medii_union.medie) as medie FROM medii_union
    )
        
    
    SELECT medii_pe_coloane.*, medie_generala.medie from medii_pe_coloane, medie_generala
)
SELECT nr_legitimatie, medie, medie_an_1, medie_an_2, medie_an_3
FROM date

I can not wrap my head around what could possibly happen. It's not overwritten. Maybe it's outside it's scope? How can I possibly use it in the WHERE clause?

The error is:

#1054 - Unknown column 'nr_legitimatie' in 'NEW'

To test it I insert a new row in note table which 100% contains the "nr_legitimatie" column.

MariaDB 10.4

I used the exact same trigger but hardcoded value "12345" instead of `new.nr_legitimatie` and it runs it just fine — Cristian Benescu, Nov 15 '21 at 22:22

score 1 · Accepted Answer · edited Aug 01 '23 at 11:06

Perhaps you can't refer to NEW three times in the same trigger?

If so, I'd rewrite your query, which looks to have logical errors in it anyway.

This is my (untested) suggested query:

INSERT INTO
  medii (
    MEDII.nr_legitimatie, MEDII.medie_generala, medii.medie_an1, medii.medie_an2, medii.medie_an3
  )
WITH
  max_an_studiu AS
(
    SELECT nr_legitimatie, disciplina, an_studiu, MAX(nota) AS maxim 
      FROM note 
     WHERE an_studiu IN (1, 2, 3)
       AND nr_legitimatie = (SELECT nr_legitimatie FROM new)
  GROUP BY nr_legitimatie, disciplina, an_studiu
),
  medie AS
(
  SELECT
    nr_legitimatie,
    an_studiu,
    AVG(maxim) AS medie
  FROM
    max_an_studiu
  GROUP BY
    nr_legitimatie,
    an_studiu
)
SELECT
  nr_legitimatie,
  AVG(medie)                                AS medie,
  MAX(CASE WHEN an_studiu=1 THEN medie END) AS medie_an_1,
  MAX(CASE WHEN an_studiu=2 THEN medie END) AS medie_an_2,
  MAX(CASE WHEN an_studiu=3 THEN medie END) AS medie_an_3
FROM
  medie
GROUP BY
  nr_legitimatie

Thank you so much, this works great. I only changed `(SELECT nr_legitimatie FROM new)` into `new.nr_legitimatie` in your solution and it works! — Cristian Benescu, Nov 16 '21 at 10:40

score 0 · Answer 2 · answered Nov 16 '21 at 01:00

This is a MariaDB bug (see especially this comment with a reproducible example close to your problem) with a misleading error message.

Your bug occurs in a trigger when you reuse a CTE that uses new.

In your case, you are using medii_pe_coloane (that uses new) 3 times in medii_union and once in the final select of date. This triggers the bug.

You can use new itself as often as you want (in as many CTEs as you want) though. So a workaround would be to redefine the CTE several times, e.g. something like

WITH medii_pe_coloane AS ( <query using "new"> ),
     medii_pe_coloane1 AS ( <same query using "new"> ),
     medii_pe_coloane2 AS ( <same query using "new"> ),
     medii_pe_coloane3 AS ( <same query using "new"> ),
...
medii_union AS (
           select medii_pe_coloane1.medie_an_1 ... from medii_pe_coloane1 
 union all select medii_pe_coloane2.medie_an_2 ... from medii_pe_coloane2
 union all select medii_pe_coloane3.medie_an_3 ... from medii_pe_coloane3)
... 
SELECT ... from medii_pe_coloane, medie_generala

Makes your code a bit repetitive, but should work as a general workaround. Alternatively, you may be able to rewrite and/or simplify your query otherwise (see e.g. MatBailies answer).

The "NEW" table in a trigger sometimes contains column and sometimes it does not

2 Answers2