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

Computed default for INVISIBLE column is ignored in INSERT

Details

    Description

      When a table has an INVISIBLE column with a computed default, said default is only used in an INSERT if said column is not specified, but other columns are. Best illustrated with an example:

      MariaDB> CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, c2 INTEGER NOT NULL DEFAULT (c1 + 1) INVISIBLE);
      		 
      MariaDB> INSERT INTO t1 VALUES(1),(2);
      		 
      MariaDB> INSERT INTO t1(c1) VALUES(3),(4);
      		 
      MariaDB> INSERT INTO t1 VALUES(5),(6);
      		 
      MariaDB [test]> SELECT c1, c2 FROM t1;
      		 
      +----+----+
      		 
      | c1 | c2 |
      		 
      +----+----+
      		 
      |  1 |  0 |
      		 
      |  2 |  0 |
      		 
      |  3 |  4 |
      		 
      |  4 |  5 |
      		 
      |  5 |  0 |
      		 
      |  6 |  0 |
      		 
      +----+----+

      As can be seen, when doing an INSERT without specifying the columns, 0 is inserted and the defined DEFAULT is ignored. If an INSERT specifying which columns to insert though, the DEFAULT is computed appropriately.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15085 Invisible Column Non-constant Default value results wrong values

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment -

            This is the same as Mdev-15085, which was fixed and the test was added (https://github.com/MariaDB/server/commit/2d73b58101), but then smth went wrong in https://github.com/MariaDB/server/commit/537f8594a60a1e09d6da0933b55764e0f8abbf5c merge commit and the bug is still reproducible on 10.3-10.5

            alice Alice Sherepa added a comment - This is the same as Mdev-15085, which was fixed and the test was added ( https://github.com/MariaDB/server/commit/2d73b58101 ), but then smth went wrong in https://github.com/MariaDB/server/commit/537f8594a60a1e09d6da0933b55764e0f8abbf5c merge commit and the bug is still reproducible on 10.3-10.5
            midenok Aleksey Midenkov added a comment -

            Please review bb-10.3-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.3-midenok
            sanja Oleksandr Byelkin added a comment - - edited 

            c70fc55bd7599cee91fe271ac7fb1f89ef79aa3e
            		 
             
            		 
            Autor: Aleksey Midenkov <midenok@gmail.com>  2021-09-29 13:45:29
            		 
            Eintragender: Aleksey Midenkov <midenok@gmail.com>  2021-09-29 13:49:32
            		 
            Eltern: e1288a66bee5ab20f59086e4b0c58a451a658539 (MDEV-22660 SIGSEGV on adding system versioning and modifying system column)
            		 
            Zweig: remotes/origin/bb-10.3-midenok, remotes/origin/bb-10.3-midenok-MDEV-24176
            		 
            Folgt auf: mariadb-10.3.31
            		 
            Vorgänger von: 
             
            		 
                MDEV-25891 Computed default for INVISIBLE column is ignored in INSERT
            		 
                
                There are two fill_record() functions (8343 and 8618). First one is
            		 
                used when there are some explicit values, the second one is used for
            		 
                all implicit values. First one does update_default_fields(), the
            		 
                second one did not. Added update_default_fields() call to the implicit
            		 
                version of fill_record().

            sanja Oleksandr Byelkin added a comment - - edited c70fc55bd7599cee91fe271ac7fb1f89ef79aa3e   Autor: Aleksey Midenkov <midenok@gmail.com> 2021-09-29 13:45:29 Eintragender: Aleksey Midenkov <midenok@gmail.com> 2021-09-29 13:49:32 Eltern: e1288a66bee5ab20f59086e4b0c58a451a658539 (MDEV-22660 SIGSEGV on adding system versioning and modifying system column) Zweig: remotes/origin/bb-10.3-midenok, remotes/origin/bb-10.3-midenok-MDEV-24176 Folgt auf: mariadb-10.3.31 Vorgänger von:   MDEV-25891 Computed default for INVISIBLE column is ignored in INSERT There are two fill_record() functions (8343 and 8618). First one is used when there are some explicit values, the second one is used for all implicit values. First one does update_default_fields(), the second one did not. Added update_default_fields() call to the implicit version of fill_record().
            sanja Oleksandr Byelkin added a comment -

            OK to push!

            sanja Oleksandr Byelkin added a comment - OK to push!

            People

              midenok Aleksey Midenkov
              karlsson Anders Karlsson
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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