[MDEV-5904] NULLIF does not preserve data type Created: 2014-03-19  Updated: 2022-09-08

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.36, 10.0.9
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

This script:

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a DATE, b DATE);
 
CREATE TABLE t2 AS SELECT COALESCE(a,b), NULLIF(a,b) FROM t1; 
SHOW COLUMNS FROM t2;

creates these columns:

+---------------+-------------+------+-----+---------+-------+
 
| Field         | Type        | Null | Key | Default | Extra |
 
+---------------+-------------+------+-----+---------+-------+
 
| COALESCE(a,b) | date        | YES  |     | NULL    |       |
 
| NULLIF(a,b)   | varchar(10) | YES  |     | NULL    |       |
 
+---------------+-------------+------+-----+---------+-------+

NULLIF erroneously created a VARCHAR column.

Notice, on the contrary, COALESCE correctly created a DATE column.
Generated at Thu Feb 08 07:07:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.