[MDEV-5971] Asymmetry between CAST(DATE'2001-00-00') to INT and TO CHAR in prepared statements Created: 2014-03-28  Updated: 2014-03-31  Resolved: 2014-03-31

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.9
Fix Version/s: 10.0.10

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
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.
Generated at Thu Feb 08 07:08:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.