diff options
| author | Alexander Barkov <bar@mariadb.org> | 2015-03-12 11:40:37 +0400 |
|---|---|---|
| committer | Alexander Barkov <bar@mariadb.org> | 2015-03-12 11:40:37 +0400 |
| commit | e91bc2e91d649d4650fc4d8961edc3280d1203fe (patch) | |
| tree | ce7b3e77ea1a132e2f20edb0fd7ea58fe9c9a3ff /mysql-test/r/null.result | |
| parent | 80f03abcca0d902a876d10e6c1dc8c4c6d3b2bfa (diff) | |
| download | mariadb-git-e91bc2e91d649d4650fc4d8961edc3280d1203fe.tar.gz | |
MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END
Diffstat (limited to 'mysql-test/r/null.result')
| -rw-r--r-- | mysql-test/r/null.result | 40 |
1 files changed, 40 insertions, 0 deletions
diff --git a/mysql-test/r/null.result b/mysql-test/r/null.result index d48985822df..93f3ac4ab7b 100644 --- a/mysql-test/r/null.result +++ b/mysql-test/r/null.result @@ -502,6 +502,10 @@ NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0), NULLIF(TIMESTAMP'2001-01-01 00:00:00',1e0), NULLIF(TIMESTAMP'2001-01-01 00:00:00','2001-01-01'), NULLIF(TIMESTAMP'2001-01-01 00:00:00',TIME'00:00:00'); +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1.0' +Warning 1292 Incorrect datetime value: '1' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -518,6 +522,10 @@ NULLIF(DATE'2001-01-01',1.0), NULLIF(DATE'2001-01-01',1e0), NULLIF(DATE'2001-01-01','2001-01-01'), NULLIF(DATE'2001-01-01',TIME'00:00:00'); +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1.0' +Warning 1292 Incorrect datetime value: '1' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -1350,5 +1358,37 @@ t2 CREATE TABLE `t2` ( DROP TABLE t2; DROP TABLE t1; # +# MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END +# +CREATE TABLE t1 (a YEAR); +INSERT INTO t1 VALUES (2010),(2020); +SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +a +2010 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) +SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +a +2010 +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and 1) +DROP TABLE t1; +# Two warnings expected +CREATE TABLE t1 AS SELECT +NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a, +CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL +ELSE TIMESTAMP'2001-01-01 00:00:00' + END AS b; +Warnings: +Warning 1292 Incorrect datetime value: '1' +Warning 1292 Incorrect datetime value: '1' +DROP TABLE t1; +# # End of 10.1 tests # |