Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.1
-
None
Description
Consider the following CREATE statement:
CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT, c2 INT NULL DEFAULT NULL, PRIMARY KEY(c1,c2)) ENGINE=MyISAM; |
Sonce c2 is a part of a PK, it cannot really be nullable. Earlier versions of MariaDB and MySQL would silently convert it into a not-nullable column with default value '0':
Table Create Table
|
t1 CREATE TABLE `t1` (
|
`c1` int(11) NOT NULL AUTO_INCREMENT,
|
`c2` int(11) NOT NULL DEFAULT '0',
|
PRIMARY KEY (`c1`,`c2`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
MySQL 5.7 is stricter in this regard, and refuses to execute the CREATE statement:
'CREATE TABLE t1(c1 INT NOT NULL AUTO_INCREMENT, c2 INT NULL DEFAULT NULL, PRIMARY KEY(c1,c2)) ENGINE=InnoDB' failed: 1171: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
|
At least it's understandable and clearly explained.
MariaDB 10.1, however, converts the column to not-nullable, but does not set a default value. So, farther attempt to insert into the table without providing explicit values for c2 fails with a very confusing warning (or error, if strict mode is enabled):
INSERT INTO t1() VALUES();
|
Warnings:
|
Warning 1364 Field 'c2' doesn't have a default value
|
I don't know what the intentional behavior was, but it surely could not be this?
The change appeared in 10.1 tree with this commit:
commit 6b20342
|
Author: Monty <monty@mariadb.org>
|
Date: Tue Aug 18 00:42:08 2015 +0300
|
 |
Ensure that fields declared with NOT NULL doesn't have DEFAULT values if not
|
|
In original code, sometimes one got an automatic DEFAULT value in some cases
|
|
For example:
|
create table t1 (a int primary key) - No default
|
create table t2 (a int, primary key(a)) - DEFAULT 0
|
create table t1 SELECT .... - Default for all fields, even if t
|
ALTER TABLE ... MODIFY could sometimes add an unexpected DEFAULT value.
|
|
The patch is quite big because we had some many test cases that used
|
CREATE ... SELECT or CREATE ... (...PRIMARY KEY(xxx)) which doesn't have an
|
|
Other things:
|
- Removed warnings from InnoDB when waiting from semaphore (got this when te
|