[MDEV-25891] Computed default for INVISIBLE column is ignored in INSERT Created: 2021-06-10  Updated: 2021-10-11  Resolved: 2021-10-11

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.4.14, 10.5.9, 10.6.1, 10.3, 10.4, 10.5
Fix Version/s: 10.3.32, 10.4.22, 10.5.13, 10.6.5

Type: Bug Priority: Critical
Reporter: Anders Karlsson Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux CentOS 7.9

Issue Links:
Relates
relates to MDEV-15085 Invisible Column Non-constant Default... Closed

 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.

 Comments   
Comment by Alice Sherepa [ 2021-06-10 ]

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

Comment by Aleksey Midenkov [ 2021-09-29 ]

Please review bb-10.3-midenok

Comment by Oleksandr Byelkin [ 2021-10-01 ] 

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().

Comment by Oleksandr Byelkin [ 2021-10-01 ]

OK to push!

Generated at Thu Feb 08 09:41:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.