[MDEV-8801] New behavior on creating a multi-part PK with a nullable part does not seem reasonable Created: 2015-09-15  Updated: 2015-09-21  Resolved: 2015-09-21

Status: Closed
Project: MariaDB Server
Component/s: Documentation
Affects Version/s: 10.1
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
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



 Comments   
Comment by Sergei Golubchik [ 2015-09-15 ]

I believe our behavior is exactly what SQL standard (2003) says it should be.

SQL-2003, Part II, “Foundation” says:

11.7 <unique constraint definition>

Syntax Rules

…
5) If the <unique specification> specifies PRIMARY KEY, then for each <column name> in the explicit or implicit <unique column list> for which NOT NULL is not specified, NOT NULL is implicit in the <column definition>.

Explanation: all PRIMARY KEY columns are automatically converted to NOT NULL.

11.5 <default clause>

General Rules

…
3) When a site S is set to its default value,
…
b) If the data descriptor for the site includes a <default option>, then S is set to the value specified by that <default option>.
…
e) Otherwise, S is set to the null value.

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.

Comment by Elena Stepanova [ 2015-09-15 ]

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.

Comment by Ian Gilfillan [ 2015-09-21 ]

This has now been documented at https://mariadb.com/kb/en/mariadb/multi-part-primary-keys-with-nullable-columns/

Generated at Thu Feb 08 07:29:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.