[MDEV-28629] Null is inserted by INSERT SELECT MAX () to the same table. Created: 2022-05-20  Updated: 2023-10-02  Resolved: 2023-10-02

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.5.15, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type: Bug Priority: Major
Reporter: Kazumasa Takahashi Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: regression
Environment:

ubuntu 20.04(x86_64) + docker


 Description   

The following syntax fails on 10.5 and above.

CREATE TABLE t1(id1 int, id2 int, PRIMARY KEY (id1,id2)) ENGINE=InnoDB;
 
INSERT t1 VALUES(1, 1);
 
INSERT t1 SELECT id1, MAX(id2)+1 FROM t1 WHERE id1=1;


mysql> CREATE TABLE t1(id1 int, id2 int, PRIMARY KEY (id1,id2));
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> INSERT t1 VALUES(1, 1);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> INSERT t1 SELECT id1, MAX(id2)+1 FROM t1 WHERE id1=1;
 
ERROR 1048 (23000): Column 'id2' cannot be null

I tryed it with the docker image below.

  • mariadb:10.2 -> OK
  • mariadb:10.3 -> OK
  • mariadb:10.4 -> OK
  • mariadb:10.5 -> NG
  • mariadb:10.7 -> NG
  • mariadb:10.8-rc -> NG

I also tried it on MyISAM and it worked fine.

 Comments   
Comment by Kazumasa Takahashi [ 2023-10-02 ]

I tryed it with the docker image below.

  • mariadb:10.7.7->NG
  • mariadb:10.7.8->OK
  • mariadb:10.8.6->NG
  • mariadb:10.8.7->OK
  • mariadb:10.8.8->OK
  • mariadb:10.9.8->OK
  • mariadb:10.11.5->OK
  • mariadb:11.1.2->OK

It looks like this issue has already been fixed.

Comment by Sergei Golubchik [ 2023-10-02 ]

Thanks! Closed.

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