Bug #75447 INSERT into a view sometimes ignores default values
Submitted: 8 Jan 2015 12:59 Modified: 8 Jan 2015 13:25
Reporter: Roy Lyseng Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6, 5.6.24, 5.7.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Jan 2015 12:59] Roy Lyseng 
Description:
When inserting into a view that is defined over a subset of the columns of the underlying table,
if a column list is not specified, then default values for the omitted columns are not properly inserted.

This seems to be a regression in 5.6.

How to repeat:
CREATE TABLE t1(a INTEGER, b TIMESTAMP DEFAULT NOW());
CREATE VIEW v1 AS
SELECT a FROM t1;
INSERT INTO t1(a) VALUES(1);
INSERT INTO v1 VALUES(2);
INSERT INTO v1(a) VALUES(3);
SELECT * FROM t1;

DROP VIEW v1;
DROP TABLE t1;
[8 Jan 2015 13:25] MySQL Verification Team 
Hello Roy Lyseng,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh
[8 Jan 2015 13:26] MySQL Verification Team 
// 5.6.24 - affected

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.24                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.24-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE t1(a INTEGER, b TIMESTAMP DEFAULT NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS
    -> SELECT a FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(a) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1(a) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2015-01-08 14:05:14 |
|    2 | 0000-00-00 00:00:00 |
|    3 | 2015-01-08 14:05:14 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql>
mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

// 5.7.6 - affected

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE t1(a INTEGER, b TIMESTAMP DEFAULT NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS
    -> SELECT a FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(a) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1(a) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2015-01-08 14:15:29 |
|    2 | 0000-00-00 00:00:00 |
|    3 | 2015-01-08 14:15:29 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql>
mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)
[8 Jan 2015 13:26] MySQL Verification Team 
// 5.5.42 - not affected

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.5.42                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.5.42-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux2.6                                                |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE t1(a INTEGER, b TIMESTAMP DEFAULT NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS
    -> SELECT a FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(a) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1(a) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2015-01-08 14:12:18 |
|    2 | 2015-01-08 14:12:18 |
|    3 | 2015-01-08 14:12:18 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql>
mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)

// 5.1.73 - not affected

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| protocol_version        | 10                           |
| version                 | 5.1.73                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | unknown-linux-gnu            |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE t1(a INTEGER, b TIMESTAMP DEFAULT NOW());
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE VIEW v1 AS
    -> SELECT a FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1(a) VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO v1(a) VALUES(3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+---------------------+
| a    | b                   |
+------+---------------------+
|    1 | 2015-01-08 14:23:33 |
|    2 | 2015-01-08 14:23:33 |
|    3 | 2015-01-08 14:23:33 |
+------+---------------------+
3 rows in set (0.00 sec)

mysql>
mysql> DROP VIEW v1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)