Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Description
If CREATE TABLE .. AS uses a string function, e.g. REPEAT (but not only it), NULL-ability and default value of the resulting column depends on the strict mode.
MariaDB [test]> set sql_mode=''; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> create table t2 as select repeat('foo',1) as a; |
Query OK, 1 row affected (0.34 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table t2; |
+-------+---------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+---------------------------------------------------------------------------------------------------------+ |
| t2 | CREATE TABLE `t2` ( |
`a` varchar(3) CHARACTER SET utf8 NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+---------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
MariaDB [test]> set sql_mode='STRICT_ALL_TABLES'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> create table t4 as select repeat('foo',1) as a; |
Query OK, 1 row affected (0.38 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table t4; |
+-------+-------------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+-------------------------------------------------------------------------------------------------------------+ |
| t4 | CREATE TABLE `t4` ( |
`a` varchar(3) CHARACTER SET utf8 DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+-------------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
It doesn't happen if a constant value is used.
MariaDB [test]> set sql_mode=''; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> create table t1 as select 'foo' as a; |
Query OK, 1 row affected (0.34 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table t1; |
+-------+---------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+---------------------------------------------------------------------------------------------------------+ |
| t1 | CREATE TABLE `t1` ( |
`a` varchar(3) CHARACTER SET utf8 NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+---------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
MariaDB [test]> set sql_mode='STRICT_ALL_TABLES'; |
Query OK, 0 rows affected (0.00 sec) |
|
|
MariaDB [test]> create table t3 as select 'foo' as a; |
Query OK, 1 row affected (0.46 sec)
|
Records: 1 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> show create table t3; |
+-------+---------------------------------------------------------------------------------------------------------+ |
| Table | Create Table | |
+-------+---------------------------------------------------------------------------------------------------------+ |
| t3 | CREATE TABLE `t3` ( |
`a` varchar(3) CHARACTER SET utf8 NOT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | |
+-------+---------------------------------------------------------------------------------------------------------+ |
1 row in set (0.00 sec) |
Same in MySQL 5.7.
In 5.5 the behavior is similar, only the NOT NULL column also gets a default value ''.
Attachments
Issue Links
- is blocked by
-
MDEV-19362 New STRICT flags for sql_mode
-
- Open
-
- relates to
-
-
- Closed
-