[MDEV-32474] INSERT .. DEFAULT broken when when columns are out of order Created: 2023-10-13  Updated: 2023-10-13

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.4, 10.5, 10.6, 10.10, 10.11, 11.0, 11.1, 11.2, 11.3
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Zach Musgrave Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 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.

Repro:

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]

MySQL has the same bug, which they deny is a bug:

https://bugs.mysql.com/bug.php?id=112708

I wrote a blog post about why I think that's the wrong call here:

https://www.dolthub.com/blog/2023-10-13-fixing-mysql-bugs-in-dolt/

 Comments   
Comment by Sergei Golubchik [ 2023-10-13 ]

commenting on your blog and MySQL's bug resolution
This behavior is not part of SQL Standard. At least SQL:2016 does not allow column references in DEFAULT, this was MariaDB extension implemented first in MariaDB-10.2.1, in 2016 and later implemented in MySQL too. The standard says (2016, part 2, section 11.4 <column definition>):

<column definition> ::=
 
  <column name> [ <data type or domain name> ]
 
      [ <default clause> | <identity column specification> | <generation clause>

and

<default clause> ::=
 
  DEFAULT <default option>
 
 
 
<default option> ::=
 
    <literal>
 
  | <datetime value function>
 
  | USER
 
  | CURRENT_USER
 
  | CURRENT_ROLE
 
  | SESSION_USER
 
  | SYSTEM_USER
 
  | CURRENT_CATALOG
 
  | CURRENT_SCHEMA
 
  | CURRENT_PATH
 
  | <implicitly typed value specification>

<datetime value function> is CURRENT_TIMESTAMP and alikes, and <implicitly typed value specification> is NULL and empty array or multiset. That's all, no expressions with column references.

Comment by Sergei Golubchik [ 2023-10-13 ]

Currently it works pretty much as

INSERT INTO t1 (b,a) values (a+1, 3);

and in line with MySQL's left-to-right evaluation it does what it does. Consider, that you can do

INSERT INTO t1 (b,a) values (a, b);

and neither MariaDB nor MySQL will not scream at you for this. Left-to-right allows to resolve all that. As helpful as comparing an integer and a string, isn't it?

Technically it's possible not to do left-to-right in INSERT, MariaDB has a special sql_mode for that. But then we need to figure out what to do with all those cases of circular dependencies like above, how to detect them, what the result should be, etc.

Comment by Zach Musgrave [ 2023-10-13 ]

Very interesting context, thank you for following up!

Comment by Zach Musgrave [ 2023-10-13 ]

Considering that you're kind of off-the-map with respect to the SQL standard, you guys could take this in several directions, eager to see what you think.

From a developer perspective, I would naively think the following three statements should be equivalent:

insert into t1(a) values (3);
insert into t1(a,b) values (3,DEFAULT);
insert into t1(b,a) values (DEFAULT,3);

The fact that these produce different results is confusing in my opinion, although I better understand why the third behaves differently now.

Generated at Thu Feb 08 10:31:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.