Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
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:
SHOW CREATE TABLE t1; |
+-------+-------------------------------------------------------------------------------------------------------+
|
| 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
|
+------------+------------+
|
SHOW CREATE TABLE t1; |
+-------+-------------------------------------------------------------------------------------------------------+
|
| 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.
SHOW CREATE TABLE t1; |
+-------+---------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` time NOT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------+
|
Expect a NULL-able column.
Attachments
Issue Links
- blocks
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP
-
- Closed
-
-
-
- Closed
-