1 files changed, 81 insertions, 0 deletions
diff --git a/mysql-test/main/type_datetime.test b/mysql-test/main/type_datetime.test
index eaba3640140..cecdda593d0 100644
--- a/mysql-test/main/type_datetime.test
+++ b/mysql-test/main/type_datetime.test
@@ -859,3 +859,84 @@ DROP TABLE t1;
 --echo #
 --echo # End of 10.3 tests
 --echo #
+
+
+--echo #
+--echo # Start of 10.4 tests
+--echo #
+
+--echo #
+--echo # MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters
+--echo #
+
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-02 00:00:00'),('2001-01-03 00:00:00');
+--echo # Equal values
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP'2001:01:01 00:00:00',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:00',a);
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)' USING TIMESTAMP'2001-01-01 00:00:00';
+
+--echo # Not equal values
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP'2001:01:01 00:00:00',a)<=>COALESCE(TIMESTAMP'2001-01-01 00:00:01',a);
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:01';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)<=>COALESCE(?,a)' USING TIMESTAMP'2001-01-01 00:00:01';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIMESTAMP''2001-01-01 00:00:00'',a)' USING TIMESTAMP'2001-01-01 00:00:01';
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same
+--echo #
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'),('2001-01-01 00:00:02');
+
+--echo Equal values
+SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a));
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a));
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.0';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.0';
+
+--echo Values with different formats
+SELECT LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a)),LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a)) FROM t1;
+SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a));
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.0',a))<=>LENGTH(COALESCE(TIMESTAMP'2001-01-01 00:00:00.00',a));
+EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.00';
+EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(COALESCE(TIME''00:00:00.0'',a))<=>LENGTH(COALESCE(?,a))' USING TIMESTAMP'2001-01-01 00:00:00.00';
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning
+--echo #
+
+CREATE TABLE t1 (b BIT(20));
+CREATE TABLE t2 (t DATETIME);
+INSERT IGNORE INTO t1 VALUES (b'000001001100000');
+INSERT INTO t2 SELECT * FROM t1;
+DROP TABLE t1, t2;
+
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 SELECT CAST(20010101 AS UNSIGNED);
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-17563 Different results using table or view when comparing values of time type 
+--echo #
+
+CREATE TABLE t1 (pk int, x1 datetime, x2 varchar(1));
+INSERT INTO t1 VALUES (17,'2001-01-01 09:16:37','');
+INSERT INTO t1 VALUES (18,'2001-01-01 09:16:37','k');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT pk FROM t1 WHERE x1 >x2;
+SELECT pk FROM v1 WHERE x1 >x2;
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.4 tests
+--echo #