Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 10.1
-
Fix Version/s: N/A
-
Component/s: Documentation
-
Labels: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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Ian, could you please find a proper place in the KB to document it? We will have questions in future, it's better to refer to documentation while answering them.
This has now been documented at https://mariadb.com/kb/en/mariadb/multi-part-primary-keys-with-nullable-columns/
I believe our behavior is exactly what SQL standard (2003) says it should be.
SQL-2003, Part II, “Foundation” says:
Explanation: all PRIMARY KEY columns are automatically converted to NOT NULL.
Explanation: there is no concept of “no default value” in the standard, instead a column always has an implicit default value of NULL. On insertion it might fail the NOT NULL constraint though. MariaDB and MySQL instead mark such a column as “not having a default value”. The end result is the same — a value must be specified explicitly or an INSERT will fail.
Thus, I believe, MariaDB behaves in a standard compatible manner — being part of a PRIMARY KEY, the nullable column gets an automatic NOT NULL contraint, on insertion one must specify a value for such a column. MariaDB before 10.1 and MySQL before 5.7 were automatically assigning a default value of 0 — this behavior was non-standard. MySQL 5.7 refuses to convert a nullable column to NOT NULL — this is also against the SQL standard.