Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28629

Null is inserted by INSERT SELECT MAX () to the same table.

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            Takahashi Kazumasa Takahashi
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.