MySQL Bugs: #115269: "INSERT SET column=column" inserts zero date

Bug #115269	"INSERT SET column=column" inserts zero date
Submitted:	10 Jun 12:53	Modified:	10 Jun 13:33
Reporter:	Guilhem Bichot	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
used the latest master from github

commit 824e2b4064053f7daf17d7f3f84b7a3ed92e5fb4 (HEAD -> trunk, tag: mysql-cluster-8.3.0, tag: mysql-8.3.0, origin/trunk)
Author: Tor Didriksen <tor.didriksen@oracle.com>
Date:   Mon Nov 20 12:31:51 2023 +0100

    Bug#36027494 Add mtr test for my_print_stacktrace

Tried this:

CREATE TABLE t0 (c0 int PRIMARY KEY);
INSERT INTO t0 (c0) VALUES (123);
select * from  t0; # returns 123, ok
INSERT INTO t0 SET t0.c0 = t0.c0;
select * from  t0; # returns 123 and 0

Inserting zero is absurd. If there is no meaning to having t0.c0 on the right side of '=' then the query should fail with an error.
Even worse: in how-to-repeat we can see that, with a DATE column, it inserts a zero DATE, in contradiction with:
- the fact that NO_ZERO_DATE is in SQL_MODE
- the fact that an equivalent "INSERT INTO t0 SET t0.c0 = '0000-00-00';"
is properly rejected with "ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'c0' at row 1"

How to repeat:
Each time, the value t0.c0 on the right side of the equality is seen as 0:

# INT column
CREATE TABLE t0 (c0 int PRIMARY KEY);
INSERT INTO t0 (c0) VALUES (123);
select * from  t0;
INSERT INTO t0 SET t0.c0 = t0.c0; # inserts 0
select * from  t0;
delete from t0;

# trying with +1
INSERT INTO t0 (c0) VALUES (123);
select * from  t0;
INSERT INTO t0 SET t0.c0 = t0.c0+1; # inserts 1
select * from  t0;
drop table t0;

# DATE column
CREATE TABLE t0 (c0 DATE PRIMARY KEY);
INSERT INTO t0 (c0) VALUES (DATE '2024-1-1');
select * from  t0;
INSERT INTO t0 SET t0.c0 = t0.c0; # inserts 0000-00-00
select * from  t0;
drop table t0;

Suggested fix:
return an error ?

Bonjour mom cher ami, Guilhem,

We have repeated the behaviour with both 8.0 and 8.4.

They are the same and your report is correct.

This is a bug in DML, indeed.

Thank you for your contribution.

Hi Guilhem,

I think I will consider this not a bug, although it is indeed weird behavior.
Consider this:

CREATE TABLE t0(a INTEGER NOT NULL, b INTEGER NOT NULL);
INSERT INTO t0 SET a=555, b=a;
SELECT * FROM t0;
DROP TABLE t0;

We first insert a value in t0.a, then use the same value as a default value for t0.b. But if t0.a had not been assigned a value yet, we resort to the implicit default value for the column which is zero.

This is kind of in compliance with the documentation chapter "Data Type Default Values", by using implicit default values, although one could consider to disallow this practice when strict mode is enabled. Unfortunately, the implicit default value for a DATE column is the generally invalid value "0000-00-00". Inserting this value is indeed an error.

It would thus be a change in behavior to analyze for as yet not assigned column default values and reject them.

As for many other things in MySQL, this is beyond the SQL standard.
This is how the feature is documented:

An expression expr can refer to any column that was set earlier in a value list.

For example, you can do this because the value for col2 refers to col1, which has previously been assigned:

  INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

But notice that this rule is actually violated here, as the column to be assigned references itself, and thus has not been given a value. So, yes, I do agree this is a bug.