Description:
In an INSERT...SELECT statement with the target table having NOT NULL and DEFAULT attributes, NULL values resulting from a calculation do not insert the default value but the calculation result from a previous row. NULL values resulting from a simple column name do insert the default, and the problem did not occur in
3.23.53.
How to repeat:
create temporary table master (
id int(7) unsigned NOT NULL default 0,
active int(1) unsigned NOT NULL default 0,
PRIMARY KEY (id)
) type=heap;
create temporary table res (
id int(7) unsigned NOT NULL default 0,
active int(1) unsigned NOT NULL default 0,
val double(10,2) NOT NULL default 0,
val_e double(10,2) NOT NULL default 0,
PRIMARY KEY (id)
) type=heap;
create temporary table sum (
id int(7) unsigned NOT NULL default 0,
val double(10,2) NOT NULL default 0,
PRIMARY KEY (id)
) type=heap;
insert into master (id, active) values (1, 1), (2, 1);
insert into sum (id, val) values (1, 42);
insert into res
select master.id, master.active, sum.val, master.active * sum.val
from master left join sum on sum.id=master.id;
select * from res;
results in:
+----+--------+-------+-------+
| id | active | val | val_e |
+----+--------+-------+-------+
| 1 | 1 | 42.00 | 42.00 |
| 2 | 1 | 0.00 | 42.00 |
+----+--------+-------+-------+
I would expect val_e(2) = 0.00, because the query returns NULL for this field. Obviously, it has worked for val(2).
