[Commits] 41a12f9: MDEV-8320 Allow index usage for DATE(datetime_column) = const.

Alexey Botchkov holyfoot at askmonty.org
Wed Sep 28 13:50:19 EEST 2016


revision-id: 41a12f990519fb68eaa66ecc6860985471e6ba5a (mariadb-10.1.8-264-g41a12f9)
parent(s): 28f441e36aaaec15ce7d447ef709fad7fbc7cf7d
committer: Alexey Botchkov
timestamp: 2016-09-28 14:48:54 +0400
message:

MDEV-8320 Allow index usage for DATE(datetime_column) = const.

        Test for 'sargable functions' added.

---
 mysql-test/r/sargable_func.result | 64 +++++++++++++++++++++++++++++++++++++++
 mysql-test/t/sargable_func.test   | 41 +++++++++++++++++++++++++
 2 files changed, 105 insertions(+)

diff --git a/mysql-test/r/sargable_func.result b/mysql-test/r/sargable_func.result
new file mode 100644
index 0000000..e49c14b
--- /dev/null
+++ b/mysql-test/r/sargable_func.result
@@ -0,0 +1,64 @@
+drop table if exists t1;
+CREATE TABLE t1 (
+id int(6) NOT NULL,
+d datetime,
+KEY k_id (id),
+KEY k_d (d)
+);
+explain select * from t1 where date(d) < '2000-01-04';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k_d	k_d	6	NULL	12	Using index condition
+select * from t1 where date(d) < '2000-01-04';
+id	d
+1	2000-01-01 05:00:00
+2	2000-01-01 10:00:00
+3	2000-01-01 15:00:00
+4	2000-01-01 20:00:00
+5	2000-01-02 01:00:00
+6	2000-01-02 06:00:00
+7	2000-01-02 11:00:00
+8	2000-01-02 16:00:00
+9	2000-01-02 21:00:00
+10	2000-01-03 02:00:00
+11	2000-01-03 07:00:00
+12	2000-01-03 12:00:00
+13	2000-01-03 17:00:00
+14	2000-01-03 22:00:00
+explain select * from t1 where date(d) <= '2000-01-04';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k_d	k_d	6	NULL	17	Using index condition
+select * from t1 where date(d) <= '2000-01-04';
+id	d
+1	2000-01-01 05:00:00
+2	2000-01-01 10:00:00
+3	2000-01-01 15:00:00
+4	2000-01-01 20:00:00
+5	2000-01-02 01:00:00
+6	2000-01-02 06:00:00
+7	2000-01-02 11:00:00
+8	2000-01-02 16:00:00
+9	2000-01-02 21:00:00
+10	2000-01-03 02:00:00
+11	2000-01-03 07:00:00
+12	2000-01-03 12:00:00
+13	2000-01-03 17:00:00
+14	2000-01-03 22:00:00
+15	2000-01-04 03:00:00
+16	2000-01-04 08:00:00
+17	2000-01-04 13:00:00
+18	2000-01-04 18:00:00
+19	2000-01-04 23:00:00
+explain select * from t1 where date(d) < '2000-01-19';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	k_d	NULL	NULL	NULL	100	Using where
+explain select * from t1 where date(d) = '2000-01-19';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k_d	k_d	6	NULL	6	Using index condition
+select * from t1 where date(d) = '2000-01-19';
+id	d
+87	2000-01-19 03:00:00
+88	2000-01-19 08:00:00
+89	2000-01-19 13:00:00
+90	2000-01-19 18:00:00
+91	2000-01-19 23:00:00
+drop table t1;
diff --git a/mysql-test/t/sargable_func.test b/mysql-test/t/sargable_func.test
new file mode 100644
index 0000000..2422916
--- /dev/null
+++ b/mysql-test/t/sargable_func.test
@@ -0,0 +1,41 @@
+#
+# Testing ORDER BY
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Test old ORDER BY bug
+#
+
+CREATE TABLE t1 (
+  id int(6) NOT NULL,
+  d datetime,
+  KEY k_id (id),
+  KEY k_d (d)
+);
+
+--disable_query_log
+let $cnt=100;
+while ($cnt)
+{  
+  eval insert into t1 values ($cnt, date_add('2000-01-01', INTERVAL $cnt*5 HOUR)); 
+  dec $cnt;
+}
+--enable_query_log
+
+explain select * from t1 where date(d) < '2000-01-04';
+select * from t1 where date(d) < '2000-01-04';
+
+explain select * from t1 where date(d) <= '2000-01-04';
+select * from t1 where date(d) <= '2000-01-04';
+
+explain select * from t1 where date(d) < '2000-01-19';
+
+explain select * from t1 where date(d) = '2000-01-19';
+select * from t1 where date(d) = '2000-01-19';
+
+drop table t1;
+


More information about the commits mailing list