Bug #112708 INSERT .. DEFAULT broken when when columns are out of order
Submitted: 12 Oct 2023 20:21 Modified: 13 Oct 2023 11:11
Reporter: Zach Musgrave Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.34 OS:Windows
Assigned to: CPU Architecture:x86
Tags: defaults

[12 Oct 2023 20:21] Zach Musgrave 
Description:
Defining a column default that refers to a column earlier in the schema doesn't work when using the DEFAULT keyword in insert statements if the set of insert columns is given in a different order than table schema order. See repro steps.

How to repeat:
mysql> CREATE TABLE t1 (a int default 1, b int default (a+1));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 (b,a) values (DEFAULT, 3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    3 |    2 |
+------+------+
1 row in set (0.00 sec)

(Expected result is [3,4])
[12 Oct 2023 20:55] Zach Musgrave 
Confirmed the bug is also present in 8.0.34.
[12 Oct 2023 20:55] Zach Musgrave 
Updating affected version from 8.0.27 to 8.0.34.
[13 Oct 2023 11:11] MySQL Verification Team 
Hi Mr. Musgrave,

Thank you for your bug report.

However, this is not a bug.

If column B has a default that is an expression that depends on the value of column A, then column A must precede the list of columns in the INSERT statement. According to SQL standard, columns are evaluated in the order that they are given.

Not a bug.
[14 Oct 2023 3:42] Paul Smith 
This is definitely a bug. Per the order of the arguments and the spec:

Column a should be populated with 3
Column b should be populated with 4

Please carefully review both the reported issue and expected behavior.
[16 Oct 2023 10:44] MySQL Verification Team 
Hi Mr. Smith,

SQL standard is very precise on these issues.

The order of the evaluation of the expressions in the SELECT list is not guaranteed, the order of  INSERT list  must follow the order provided.

This is described in the standard, chapters 14.7 and 14.11.

Not a bug.
[17 Oct 2023 10:01] MySQL Verification Team 
Hi Mr. Musgrave,

Small correction from us .....

It is actually stipulated in our Manual that column values must be evaluated in the order
they occur in the table definition.

Which still means that this is not a bug, albeit for another reason.