Bug #108790 Invisible column's default values is invalid if not specify other columns

• Submitted: 15 Oct 2022 16:46
• Modified: 17 Oct 2022 6:55
• Reporter: Cheng Zhou
• Status: Verified
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.30
• OS: Any
• CPU Architecture: Any

[15 Oct 2022 16:46] Cheng Zhou

Description:
Invisible column's default values is invalid if we do not specify other columns which will be insert values in INSERT statement.

How to repeat:
mysql> create table t1(a int, b timestamp invisible default now());
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select a,b from t1;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t1(a) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select a,b from t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | NULL                |
|    2 | 2022-10-16 00:55:39 |
+------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into t1() values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select a,b from t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | NULL                |
|    2 | 2022-10-16 00:55:39 |
|    3 | NULL                |
+------+---------------------+
3 rows in set (0.00 sec)

Suggested fix:
Invisible column's default value should take effect even if we don't specify other columns to be inserted.

[15 Oct 2022 18:21] Cheng Zhou

in function:
bool Sql_cmd_insert_values::execute_inner(THD *thd) {
   ....
  /*
    We have three alternative syntax rules for the INSERT statement:
    1) "INSERT (columns) VALUES ...", so non-listed columns need a default
    2) "INSERT VALUES (), ..." so all columns need a default;
    note that "VALUES (),(expr_1, ..., expr_n)" is not allowed, so checking
    emptiness of the first row is enough
    3) "INSERT VALUES (expr_1, ...), ..." so no defaults are needed; even if
    expr_i is "DEFAULT" (in which case the column is set by
    Item_default_value::save_in_field_inner()).
  */
  const bool manage_defaults = column_count > 0 ||  // 1)
                               value_count == 0;    // 2)
  ....
}
--------------------------------------------------------------------

Fail to take into account invisible columns when set manage_defaults.

[17 Oct 2022 6:55] MySQL Verification Team

Hello Cheng Zhou,

Thank you for the report and test case.
Verified as described.

regards,
Umesh