Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.9
-
None
-
None
Description
SET sql_mode=DEFAULT;
|
PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS SIGNED) AS c";
|
SET sql_mode='no_zero_in_date';
|
EXECUTE stmt;
|
returns
+----------+
|
| c |
|
+----------+
|
| 20010000 |
|
+----------+
|
If I change CAST type from SIGNED to CHAR, the behaviour changes:
SET sql_mode=DEFAULT;
|
PREPARE stmt FROM "SELECT CAST(DATE'2001-00-00' AS CHAR) AS c";
|
SET sql_mode='no_zero_in_date';
|
EXECUTE stmt;
|
returns
+---+
|
| c |
|
+---+
|
| NULL |
|
+---+
|
1 row in set, 1 warning (1.50 sec)
|
It should be fixed to follow the same rules:
either NULL in both cases, or not-NULL in both cases.
Also, notice the second problem: wrong table alignment in the last results.
If I run "mysql --column-type-info" and run the last script that displays a wrong
table, it reports:
Field 1: `c`
|
Catalog: `def`
|
Database: ``
|
Table: ``
|
Org_table: ``
|
Type: VAR_STRING
|
Collation: utf8_general_ci (33)
|
Length: 30
|
Max_length: 0
|
Decimals: 31
|
Flags: NOT_NULL
|
The NOT_NULL flag is not correct.
Alternatively, this script also demonstrates the problem with a wrong NOT_NULL flag:
SET sql_mode=DEFAULT;
|
DROP TABLE IF EXISTS t1;
|
PREPARE stmt FROM "CREATE TABLE t1 AS SELECT CAST(DATE'2001-00-00' AS CHAR) AS c";
|
SET sql_mode='no_zero_in_date';
|
EXECUTE stmt;
|
SHOW COLUMNS FROM t1;
|
displays:
+-------+-------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-------------+------+-----+---------+-------+
|
| c | varchar(10) | NO | | | |
|
+-------+-------------+------+-----+---------+-------+
|
Null=NO is wrong.