Merging upstream version 1:11.4.2.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 363 insertions, 0 deletions

diff --git a/mysql-test/main/sargable_date_cond.test b/mysql-test/main/sargable_date_cond.test
new file mode 100644
index 00000000..e1fa0817
--- /dev/null
+++ b/mysql-test/main/sargable_date_cond.test
@@ -0,0 +1,363 @@
+--source include/have_partition.inc
+--source include/have_sequence.inc
+
+#
+# MDEV-8320: Allow index usage for DATE(datetime_column) = const
+#
+
+--disable_warnings
+drop table if exists t0,t1,t2,t3;
+--enable_warnings
+
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1(a int);
+insert into t1 select A.a + B.a* 10 from t0 A, t0 B;
+
+create table t2 (pk int primary key, a datetime, b date, key(a), key(b));
+insert into t2
+select
+  A.a*10+B.a,
+  date_add(date_add('2017-01-01', interval A.a*8 day), interval B.a hour),
+  date_add('2017-01-01', interval A.a*7 day)
+from t1 A, t0 B;
+
+analyze table t2;
+
+--echo #
+--echo # "YEAR(datetime_col) CMP year_value", basic checks
+--echo #
+let $q= select count(*) from t2 where year(a) < 2018;
+eval $q;
+--echo # Compare the results, they must be equal:
+select count(*) from t2 where a < '2018-01-01';
+eval explain format=json $q;
+--echo # Check rewrite for a prepared statement:
+execute immediate 
+  "explain format=json select * from t2 where year(a) < ?"
+  using 2018;
+eval prepare stmt from "$q";
+execute stmt;
+execute stmt;
+eval create or replace view v1 as $q;
+select * from v1;
+eval create or replace procedure sp() $q;
+call sp();
+call sp();
+--echo # Prepared statement with a placeholder
+prepare stmt from "select count(*) from t2 where year(a) < ?";
+execute stmt using 2018;
+execute stmt using 2017;
+
+let $q= select count(*) from t2 where year(a) <= 2018;
+eval $q;
+select count(*) from t2 where a < '2019-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where year(a) > 2018;
+eval $q;
+select count(*) from t2 where a > '2018-12-31 23:59:59.999999';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where year(a) >= 2018;
+eval $q;
+select count(*) from t2 where a >= '2018-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where year(a) = 2017;
+eval $q;
+select count(*) from t2 where a >= '2017-01-01' and a < '2018-01-01';
+eval explain format=json $q;
+
+--echo #
+--echo # "YEAR(datetime_col) CMP year_value", reverse argument order
+--echo #
+let $q= select count(*) from t2 where 2017 < year(a);
+eval $q;
+select count(*) from t2 where a >= '2018-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where 2018 <= year(a);
+select count(*) from t2 where a >= '2018-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where 2018 > year(a);
+eval $q;
+select count(*) from t2 where a < '2018-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where 2018 >= year(a);
+select count(*) from t2 where a < '2019-01-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where 2018 =  year(a);
+eval $q;
+select count(*) from t2 where a >= '2018-01-01' and a < '2019-01-01';
+eval explain format=json $q;
+
+--echo #
+--echo # "DATE(datetime_col) CMP date_value", basic checks
+--echo #
+let $q= select count(*) from t2 where date(a) < '2017-06-01';
+eval $q;
+select count(*) from t2 where a < '2017-06-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where date(a) <= '2017-06-03';
+eval $q;
+select count(*) from t2 where a < '2017-06-04';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where date(a) > '2018-06-01';
+eval $q;
+select count(*) from t2 where a >= '2018-06-02';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where date(a) >= '2018-06-01';
+eval $q;
+select count(*) from t2 where a >= '2018-06-01';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where date(a) = '2017-06-02';
+eval $q;
+select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
+eval explain format=json $q;
+
+--echo #
+--echo # "DATE(datetime_col) CMP date_value", reverse order
+--echo #
+let $q= select count(*) from t2 where '2017-06-01' > date(a);
+eval $q;
+select count(*) from t2 where '2017-06-01' > a;
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where '2017-06-03' >= date(a);
+eval $q;
+select count(*) from t2 where '2017-06-03' >= a;
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where '2018-06-01' < date(a);
+eval $q;
+select count(*) from t2 where '2018-06-02' <= a;
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where '2018-06-01' <= date(a);
+eval $q;
+select count(*) from t2 where '2018-06-01' <= a;
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where '2017-06-02' = date(a);
+eval $q;
+select count(*) from t2 where a >= '2017-06-02' and a < '2017-06-03';
+eval explain format=json $q;
+
+--echo # Check rewrite of a more complicated query
+explain format=json select * from t2 as t21 force index(a),
+  t2 as t22 force index(a)
+  where year(t21.a) < 2018 and t21.b > '2017-11-01'
+  and date(t22.a) >= '2017-02-01' and t22.b > '2017-11-01';
+
+--echo #
+--echo # Incorrect const values processing (no rewrite is possible)
+--echo #
+explain format=json select * from t2 where year(a) = -1;
+explain format=json select * from t2 where year(a) > -5;
+explain format=json select * from t2 where year(a) < -1;
+explain format=json select * from t2 where year(a) <= 10000;
+explain format=json select * from t2 where year(a) >= 10020;
+explain format=json select * from t2 where date(a) = '10000-01-01';
+explain format=json select * from t2 where date(a) < '-1-01-01';
+
+--echo #
+--echo # Try DATE function and DATE (not DATETIME) column:
+--echo #
+let $q= select count(*) from t2 where date(b)< '2017-06-03';
+eval $q;
+select count(*) from t2 where b < '2017-06-03';
+eval explain format=json $q;
+
+let $q= select count(*) from t2 where date(b)= '2017-06-04';
+eval $q;
+select count(*) from t2 where b >= '2017-06-04' and b < '2017-06-05';
+eval explain format=json $q;
+
+--echo #
+--echo # Check actual query results
+--echo #
+insert into t2 values (10001,'2006-12-31 23:59:59','2006-12-31');
+insert into t2 values (10002,'2007-01-01 00:00:00','2007-01-01');
+insert into t2 values (10003,'2007-12-31 23:59:59','2007-12-31');
+insert into t2 values (10004,'2008-01-01 00:00:00','2008-01-01');
+
+explain format=json
+select * from t2 force index(b) where year(b)=2007;
+select * from t2 force index(b) where year(b)=2007;
+
+insert into t2 values (10010,'2006-12-31 00:00:00','2006-12-31');
+insert into t2 values (10011,'2006-12-30 23:59:59','2006-12-30');
+
+explain format=json
+select * from t2 force index(a) where date(a)='2006-12-31';
+select * from t2 force index(a) where date(a)='2006-12-31';
+
+--echo #
+--echo # Test the TIMESTAMP column
+--echo #
+create table t3 (a timestamp, b date, key(a));
+--echo # Insert data starting from 2016 since that year had a leap second
+--echo # (https://en.wikipedia.org/wiki/Leap_second)
+set time_zone="UTC"; # To make sure we avoid daylight saving time shifts
+insert into t3
+  select
+    timestampadd(hour, B.a, date_add('2016-01-01', interval A.a*8 day)),
+    date_add('2016-01-01', interval A.a*7 day)
+  from t1 A, t0 B;
+
+--echo # Results of those two queries must be equal:
+let $q= select count(*) from t3 force index(a) where year(a)= 2016;
+eval $q;
+--echo # The result must be the same as this query's:
+select count(*) from t3 force index(a) where a >= '2016-01-01 00:00:00'
+    and a <= '2016-12-31 23:59:59.999999';
+explain format=json
+  select count(*) from t3 force index(a) where year(a)= 2016;
+set time_zone= @@global.time_zone;
+
+--echo #
+--echo # Incorrect const values processing (no rewrite is possible)
+--echo #
+explain format=json select * from t2 where year(a) = -1;
+explain format=json select * from t2 where year(a) > -5;
+explain format=json select * from t2 where year(a) < -1;
+explain format=json select * from t2 where year(a) <= 10000;
+explain format=json select * from t2 where year(a) >= 10020;
+explain format=json select * from t2 where date(a) = '10000-01-01';
+explain format=json select * from t2 where date(a) < '-1-01-01';
+
+--echo #
+--echo # Composite indexes
+--echo #
+create table t4 (a datetime, b int, key(a, b)) ;
+insert into t4 values ('2009-11-29 13:43:32', 2);
+insert into t4 values ('2009-10-15 12:15:11', 2);
+insert into t4 values ('2009-09-01 15:55:00', 3);
+insert into t4 values ('2009-08-23 12:07:47', 4);
+explain format=json select * from t4 where year(a) = 2009;
+explain format=json select * from t4 where year(a) >= 2009;
+explain format=json select * from t4 where year(a) < 2018;
+explain format=json select * from t4 where date(a) = '2009-12-01';
+explain format=json select * from t4 where b = 2 and year(a) = 2009;
+explain format=json select * from t4 where b = 10 and year(a) > 2001;
+explain format=json select * from t4 where b = 2 and date(a) = '2009-11-02';
+
+--echo # Reverse order of fields in the index
+create table t5 (a datetime, b int, c varchar(20), key(b, a));
+insert into t5 values ('2009-11-29 13:43:32', 2, 'abc');
+insert into t5 values ('2009-10-15 12:15:11', 2, 'def');
+insert into t5 values ('2009-09-01 15:55:00', 3, 'gfd');
+insert into t5 values ('2009-08-23 12:07:47', 4, 'xyz');
+explain format=json select * from t5 where year(a) = 2009;
+explain format=json select * from t4 where date(a) = '2009-12-01';
+explain format=json select * from t5 where b = 2 and year(a) = 2009;
+explain format=json select * from t5 where b = 10 and year(a) > 2001;
+explain format=json select * from t5 where b = 3 and date(a) > '2009-09-01';
+
+--echo #
+--echo # No rewrite for a non-indexed column
+--echo #
+create table t6 (a datetime);
+insert into t6 values ('2009-11-29 13:43:32');
+insert into t6 values ('2009-10-15 12:15:11');
+insert into t6 values ('2009-09-01 15:55:00');
+insert into t6 values ('2009-08-23 12:07:47');
+explain format=json select * from t6 where year(a) = 2009;
+explain format=json select * from t6 where date(a) = '2009-12-01';
+
+drop table t0,t1,t2,t3,t4,t5,t6;
+drop view v1;
+drop procedure sp;
+
+--echo #
+--echo # MDEV-30946 Index usage for DATE(datetime_column) = const
+--echo #            does not work for DELETE and UPDATE
+--echo #
+create table t1 (pk int primary key, a datetime, c int, key(a));
+
+insert into t1 (pk,a,c) values (1,'2009-11-29 13:43:32', 2);
+insert into t1 (pk,a,c) values (2,'2009-11-29 03:23:32', 2);
+insert into t1 (pk,a,c) values (3,'2009-10-16 05:56:32', 2);
+insert into t1 (pk,a,c) values (4,'2010-11-29 13:43:32', 2);
+insert into t1 (pk,a,c) values (5,'2010-10-16 05:56:32', 2);
+insert into t1 (pk,a,c) values (6,'2011-11-29 13:43:32', 2);
+insert into t1 (pk,a,c) values (7,'2012-10-16 05:56:32', 2);
+
+--echo # YEAR() conditions, UPDATE
+explain format=json update t1 set c = 0 where year(a) = 2010;
+update t1 set c = 0 where year(a) = 2010;
+select * from t1;
+explain format=json update t1 set c = 1
+  where c < (select count(*) from t1 where year(a) = 2010);
+explain format=json update t1 set c = 0
+  where year(a) = 2010 and c < (select count(*) from t1 where year(a) = 2010);
+
+--echo # Multi-table update
+create table t2 (a int);
+insert into t2 values (4),(5),(6);
+explain format=json update t1, t2 set c = 0
+  where year(t1.a) = 2010 and t1.c = t2.a;
+
+prepare stmt from "update t1 set c = 0 where year(a) = 2010";
+execute stmt;
+execute stmt;
+
+--echo # YEAR() conditions, DELETE
+explain format=json delete from t1 where year(a) = 2010;
+delete from t1 where year(a) = 2010;
+select * from t1;
+explain format=json delete from t1
+  where c < (select count(*) from t1 where year(a) = 2010);
+delete from t1 where c < (select count(*) from t1 where year(a) = 2010);
+
+prepare stmt from "delete from t1 where year(a) = 2009";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # DATE() conditions, UPDATE
+explain format=json update t1 set c = 0 where date(a) = '2010-10-16';
+explain format=json update t1 set c = 0 where date(a) <= '2011-10-16';
+prepare stmt from "update t1 set c = 3 where date(a) = '2011-11-29'";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # DATE() conditions, DELETE
+explain format=json delete from t1 where date(a) = '2010-10-16';
+explain format=json delete from t1 where date(a) <= '2011-10-16';
+prepare stmt from "delete from t1 where date(a) <= '2012-01-01'";
+execute stmt;
+execute stmt;
+select * from t1;
+
+--echo # Test partition pruning
+create table t3 (
+  a datetime,
+  key(a)
+) partition by range(year(a)) (
+partition p0 values less than (2022),
+partition p1 values less than (MAXVALUE)
+);
+
+insert into t3
+select date_add('2020-01-01', interval seq*10 day)
+from seq_1_to_100;
+
+--echo # Must be only "p0" partition
+explain partitions select * from t3 where year(a) = 2020;
+
+explain partitions delete from t3 where year(a) = 2020;
+
+explain partitions update t3 set a = a + 1 where year(a) = 2020;
+
+drop tables t1,t2,t3;