|
I start ``mysql --column-type-info test'' and run the following script:
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
|
SELECT
|
LEAST('0000-00-00',DATE'2001-01-01') AS c1,
|
LEAST('0001-00-01',DATE'2001-01-01') AS c2;
|
It returns the following metadata and results::
Field 1: `c1`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: DATE
|
Collation: binary (63)
|
Length: 10
|
Max_length: 0
|
Decimals: 0
|
Flags: NOT_NULL BINARY
|
|
Field 2: `c2`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: DATE
|
Collation: binary (63)
|
Length: 10
|
Max_length: 0
|
Decimals: 0
|
Flags: NOT_NULL BINARY
|
+----+----+
|
| c1 | c2 |
|
+----+----+
|
| NULL | NULL |
|
+----+----+
|
1 row in set, 4 warnings (0.00 sec)
|
Notice, metadata says the columns are NOT_NULL, but the result is NULL for both columns. Additionally, the result table is not well-formatted.
Now if I run this script:
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT
|
LEAST('0000-00-00',DATE'2001-01-01') AS c1,
|
LEAST('0001-00-01',DATE'2001-01-01') AS c2;
|
SELECT * FROM t1;
|
+------------+------------+
|
| c1 | c2 |
|
+------------+------------+
|
| 0000-00-00 | 0000-00-00 |
|
+------------+------------+
|
it wrote two zero dates into the table instead of two NULLs.
SHOW tells that columns were created with the "NOT NULL" attribute:
+-------+-------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` date NOT NULL,
|
`c2` date NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
Looks wrong. Columns should be NULL-able.
The same problem is repeatable with a mixture of integer and date:
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
|
SELECT
|
LEAST(0,DATE'2001-01-01') AS c1,
|
LEAST(20010001,DATE'2001-01-01') AS c2;
|
+----+----+
|
| c1 | c2 |
|
+----+----+
|
| NULL | NULL |
|
+----+----+
|
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT
|
LEAST(0,DATE'2001-01-01') AS c1,
|
LEAST(20010001,DATE'2001-01-01') AS c2;
|
SELECT * FROM t1;
|
+------------+------------+
|
| c1 | c2 |
|
+------------+------------+
|
| 0000-00-00 | 0000-00-00 | <-- expect NULLs here
|
+------------+------------+
|
+-------+-------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` date NOT NULL,
|
`c2` date NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------+
|
Expect NULL-able columns.
The same problem is repeatable with a mixture of integer and time:
SET sql_mode='';
|
SELECT LEAST(999,TIME'10:20:30') AS c1;
|
+----+
|
| c1 |
|
+----+
|
| NULL |
|
+----+
|
SET sql_mode='';
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 AS SELECT LEAST(999,TIME'10:20:30') AS c1;
|
SELECT * FROM t1;
|
+----------+
|
| c1 |
|
+----------+
|
| 00:00:00 |
|
+----------+
|
Expect NULL.
+-------+---------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` time NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------+
|
Expect a NULL-able column.
|