From c40de1df363da0bc04118b4957c94fd52afe20b0 Mon Sep 17 00:00:00 2001
From: Alexander Barkov <bar@mariadb.org>
Date: Thu, 22 Aug 2013 15:20:27 +0400
Subject:  MDEV-4804 Date comparing false result

---
 mysql-test/r/type_date.result | 38 ++++++++++++++++++++++++++++++++++++++
 mysql-test/t/type_date.test   | 22 ++++++++++++++++++++++
 2 files changed, 60 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index da38f8d9ef8..e452b3255ae 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -307,5 +307,43 @@ NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '2022-00-00 00:00:00'
 #
+# MDEV-4804 Date comparing false result 
+#
+SET @h0="20111107";
+SET @h1="0";
+SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00');
+SELECT
+COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d,
+COALESCE(DATE(@h1),DATE(NOW())) AS h1d,
+COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1;
+h0d	h1d	compare_h0_gt_h1
+2011-11-07	2013-08-19	0
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+SELECT
+DATE('20011107'),
+DATE('0'),
+COALESCE(DATE('0'),CURRENT_DATE) AS d1,
+DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp;
+DATE('20011107')	DATE('0')	d1	cmp
+2001-11-07	NULL	2013-08-19	0
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+SELECT
+DATE('20011107'),
+DATE('0'),
+IFNULL(DATE('0'),CURRENT_DATE) AS d1,
+DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp;
+DATE('20011107')	DATE('0')	d1	cmp
+2001-11-07	NULL	2013-08-19	0
+Warnings:
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+Warning	1292	Incorrect datetime value: '0'
+SET @@timestamp=DEFAULT;
+#
 # End of 5.3 tests
 #
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 57b545f004b..8a47d324406 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -287,6 +287,28 @@ drop table t1;
 --echo #
 SELECT CONVERT_TZ(GREATEST(DATE('2021-00-00'),DATE('2022-00-00')),'+00:00','+7:5');
 
+--echo #
+--echo # MDEV-4804 Date comparing false result 
+--echo #
+SET @h0="20111107";
+SET @h1="0";
+SET @@timestamp=UNIX_TIMESTAMP('2013-08-19 20:30:00');
+SELECT
+  COALESCE(DATE(@h0),DATE("1901-01-01")) AS h0d,
+  COALESCE(DATE(@h1),DATE(NOW())) AS h1d,
+  COALESCE(DATE(@h0),DATE("1901-01-01"))>COALESCE(DATE(@h1),DATE(NOW())) AS compare_h0_gt_h1;
+SELECT
+  DATE('20011107'),
+  DATE('0'),
+  COALESCE(DATE('0'),CURRENT_DATE) AS d1,
+  DATE('20011107')>COALESCE(DATE('0'),CURRENT_DATE) AS cmp;
+SELECT
+  DATE('20011107'),
+  DATE('0'),
+  IFNULL(DATE('0'),CURRENT_DATE) AS d1,
+  DATE('20011107')>IFNULL(DATE('0'),CURRENT_DATE) AS cmp;
+SET @@timestamp=DEFAULT;
+
 --echo #
 --echo # End of 5.3 tests
 --echo #
-- 
cgit v1.2.1