Bug #46179 set new.colname = default(colname) in trigger elicits a crazy error message
Submitted: 14 Jul 2009 18:07 Modified: 31 Dec 2018 3:08
Reporter: Peter Brawley (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.6, 5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[14 Jul 2009 18:07] Peter Brawley 
Description:
Setting NEW.colname=DEFAULT(colname) in a Trigger elicits a crazy error message. 

drop table if exists t;
create table t(i int default 0) engine=myisam;
insert into t values();
insert into t values(null);
select * from t;
drop trigger if exists tinsert;
delimiter |

-- trigger setting i to default(i) 
drop trigger if exists tinsert;
delimiter |
create trigger tinsert before insert on t for each row
begin
  if new.i is null then
    set new.i = default(i);
  end if;
end |
delimiter ;
-- set i=default(i) in trigger elicits 
-- crazy error message "Unknown column 'i' in 'field list'"
select * from t;
insert into t values (null); 
ERROR 1054 (42S22): Unknown column 'i' in 'field list'
-- so does this:
insert into t (i) values (null);
select * from t;

-- update t set i=default(i) works as expected
update t set i=default(i) where i is null;
select * from t;

Nothing in the manual pages for CREATE TRIGGER or for DEFAULT() would lead the user to expect such behaviour. 

UPDATE tblname SET colname=DEFAULT(colname) works as expected.

Setting sql_mode='strict_all_tables' does not affect this anomaly.

How to repeat:
As above
[15 Jul 2009 6:17] Sveta Smirnova 
Thank you for the report.

Verified as described.

Workaround:

if new.i is null then
        select default(i) into @i from t limit 1;
        set new.i = @i;
end if;
[15 Jul 2013 9:04] Hartmut Holzgraefe 
Wouldn't the correct syntax be more like

  set new.i = default(new.i);

instead of just

  set new.i = default(i);

?

Anyway, this just leads to a different error:

  ERROR 1364 (HY000): Field 'i' doesn't have a default value
[15 Jul 2013 16:13] Peter Brawley 
4 years after being verified, the crazy behaviour has not been fixed, there's still nothing in the documentation that would lead a user to expect it, and not even the Rube Goldberg workaround has made it into the manual. What gives?
[29 Dec 2018 1:33] Federico Razzoli 
In version 8, the bug still occurs when INSERT does not specify the columns name (i).
[31 Dec 2018 3:08] Peter Brawley 
Federico Razzoli reports "In version 8, the bug still occurs when INSERT does not specify the columns name (i)." In fact in 5.6, 5.7.24 and 8.0.13 it occurs on inserts with and without a column name. It does not occur on updates.

select version();
drop table if exists t;
create table t(i int default 0);
create trigger tins before insert on t for each row
  set new.i = ifnull( new.i, default(i) );
create trigger tupd before update on t for each row
  set new.i = ifnull( new.i, default(i) );
-- INSERT TRIGGER ELICITS CRAZY ERR MSG "UNKNOWN COLUMN 'I' IN 'FIELD LIST'"
insert into t values(null);
-- DITTO
insert into t(i) values(null);
-- DITTO
insert into t set i=null;
-- NO CRAZY ERR MSG
update t set i=null;

he bug has been present for at least 9.5 years.