Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Fixed
-
Affects Version/s: 10.0.9
-
Fix Version/s: 10.0.10
-
Component/s: None
-
Labels: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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions