Bug #119453 DEFAULT(column) returns wrong result when column is to the right of left join

• Submitted: 24 Nov 14:12
• Reporter: Guilhem Bichot
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.4
• OS: Any
• CPU Architecture: Any

[24 Nov 14:12] Guilhem Bichot

Description:
CREATE TABLE t0 (
  c1 tinyint DEFAULT NULL
       )  engine=innodb;
insert into t0 values(0),(1);
 CREATE TABLE t98 (
  c3 decimal(10,0) NOT NULL DEFAULT '1317787993'
       ) engine=innodb;

SELECT DEFAULT(t98.c3) FROM t0 LEFT JOIN t98 ON TRUE ;

the result is NULL and NULL. This is not consistent with the doc:
https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_default

It is specific of columns declared NOT NULL; if we instead do:

alter table t98 modify  c3 decimal(10,0)  DEFAULT '1317787993';

then
SELECT DEFAULT(t98.c3) FROM t0 LEFT JOIN t98 ON TRUE ;
returns 1317787993 and 1317787993 as expected.

How to repeat:
CREATE TABLE t0 (
  c1 tinyint DEFAULT NULL
       )  engine=innodb;
insert into t0 values(0),(1);
 CREATE TABLE t98 (
  c3 decimal(10,0) NOT NULL DEFAULT '1317787993'
       ) engine=innodb;

SELECT DEFAULT(t98.c3) FROM t0 LEFT JOIN t98 ON TRUE ;

Suggested fix:
Item_default_value inherits from Item_field. So it re-uses Item_field::is_null(). When the column is not nullable, Item_field::is_null() returns the value of table->has_null_row(). Here we have a null-complemented row, so is_null() returns true and we get a NULL.
If the column is nullable, Item_field::is_null() reads the "null bytes" of the Field's record, and that record is share->default_values (see Item_default_value::fix_fields), which is not influenced by null-complementing so the result is correct.