From 7eb5ae07e1207e9eaaf347802407969d9af43c0a Mon Sep 17 00:00:00 2001 From: Andrew Bloomgarden Date: Tue, 2 Feb 2016 15:33:54 -0800 Subject: [PATCH] Fix group by max when using < or <= Given a table like: CREATE TABLE t1 (a INT, b INT, KEY(a,b)); If you create a group by query that uses max(b) and can be optimized to only hit the index, it's possible for the optimizer to look for the wrong data in the index and then end up returning no data. Specifically, with a query like: SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a; and a row (1, 2000) in the table, the max(b) found internally will be 2000, but since that doesn't actually satisfy the WHERE clause it's stripped out from the final result. The same is true for <=. As far as I can tell the code that the patch changes dates back to at least 2000, but I'm not clear if the bug dates back that long since I can't easily tell how it was used then. --- mysql-test/r/group_min_max.result | 148 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/group_min_max.test | 67 +++++++++++++++++ sql/opt_range.cc | 12 ++-- 3 files changed, 222 insertions(+), 5 deletions(-) diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index be47cfa..8e059ab 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -3762,3 +3762,151 @@ SELECT 1 FROM t WHERE b IN ('') GROUP BY b ; 1 DROP TABLE t; # End of test#18486293. +# Bug#80244 - INCORRECT EVALUATION OF INDEX FOR < and <= WITH MIN/MAX AND ROUNDING/TRUNCATION +# +# +CREATE TABLE t1 (a INT, b INT, KEY(a,b)); +INSERT INTO t1 VALUES (1,1000), (1,1001), (1,2000), (1,3000), (1,3002); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < 1999.5)) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a; +a max(b) +1 1001 +SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.499 GROUP BY a; +a max(b) +1 1001 +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.5 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` <= 1999.5)) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.5 GROUP BY a; +a max(b) +1 1001 +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.499 GROUP BY a; +a max(b) +1 1001 +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.5 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` between 0 and 1999.5)) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.5 GROUP BY a; +a max(b) +1 1001 +SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.499 GROUP BY a; +a max(b) +1 1001 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.499 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` > 2000.499)) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.499 GROUP BY a; +a min(b) +1 3000 +SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.5 GROUP BY a; +a min(b) +1 3000 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.499 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` >= 2000.499)) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.499 GROUP BY a; +a min(b) +1 3000 +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.5 GROUP BY a; +a min(b) +1 3000 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.499 and 10000 GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 10 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` between 2000.499 and 10000)) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.499 and 10000 GROUP BY a; +a min(b) +1 3000 +SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.5 and 10000 GROUP BY a; +a min(b) +1 3000 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b DATETIME, KEY(a, b)); +INSERT INTO t1 VALUES (1,'2010-01-01 00:01:00'), (1,'2011-01-01 00:01:00'), (1,'2012-01-01 00:01:00'), (1,'2013-01-01 00:01:00'), (1,'2014-01-01 00:01:00'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.5' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` < '2012-01-01 00:00:59.5')) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.5' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.499' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.5' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` <= '2012-01-01 00:00:59.5')) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.5' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.499' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.5' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,max(`test`.`t1`.`b`) AS `max(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.5')) group by `test`.`t1`.`a` +SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.5' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.499' GROUP BY a; +a max(b) +1 2011-01-01 00:01:00 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.499' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` > '2012-01-01 00:01:00.499')) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.499' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.5' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.499' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` >= '2012-01-01 00:01:00.499')) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.499' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.5' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.499' and '2020-01-01 00:00:00' GROUP BY a; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 NULL range a a 11 NULL 1 100.00 Using where; Using index for group-by +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,min(`test`.`t1`.`b`) AS `min(b)` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`b` between '2012-01-01 00:01:00.499' and '2020-01-01 00:00:00')) group by `test`.`t1`.`a` +SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.499' and '2020-01-01 00:00:00' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.5' and '2020-01-01 00:00:00' GROUP BY a; +a min(b) +1 2013-01-01 00:01:00 +DROP TABLE t1; +# End of test#80244 diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test index aac187a..6e7092a 100644 --- a/mysql-test/t/group_min_max.test +++ b/mysql-test/t/group_min_max.test @@ -1429,3 +1429,70 @@ SELECT 1 FROM t WHERE b IN ('') GROUP BY b ; DROP TABLE t; --echo # End of test#18486293. +--echo # Bug#80244 - INCORRECT EVALUATION OF INDEX FOR < and <= WITH MIN/MAX AND ROUNDING/TRUNCATION +--echo # +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY(a,b)); +INSERT INTO t1 VALUES (1,1000), (1,1001), (1,2000), (1,3000), (1,3002); + +ANALYZE TABLE t1; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b < 1999.499 GROUP BY a; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= 1999.499 GROUP BY a; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.5 GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b between 0 and 1999.499 GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.499 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.499 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b > 2000.5 GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.499 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.499 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= 2000.5 GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.499 and 10000 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.499 and 10000 GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b between 2000.5 and 10000 GROUP BY a; + +DROP TABLE t1; + +CREATE TABLE t1 (a INT, b DATETIME, KEY(a, b)); +INSERT INTO t1 VALUES (1,'2010-01-01 00:01:00'), (1,'2011-01-01 00:01:00'), (1,'2012-01-01 00:01:00'), (1,'2013-01-01 00:01:00'), (1,'2014-01-01 00:01:00'); + +ANALYZE TABLE t1; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b < '2012-01-01 00:00:59.499' GROUP BY a; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b <= '2012-01-01 00:00:59.499' GROUP BY a; + +EXPLAIN SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.5' GROUP BY a; +SELECT a, max(b) FROM t1 WHERE a = 1 and b between '1990-01-01 00:00:00' and '2012-01-01 00:00:59.499' GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.499' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.499' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b > '2012-01-01 00:01:00.5' GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.499' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.499' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b >= '2012-01-01 00:01:00.5' GROUP BY a; + +EXPLAIN SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.499' and '2020-01-01 00:00:00' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.499' and '2020-01-01 00:00:00' GROUP BY a; +SELECT a, min(b) FROM t1 WHERE a = 1 and b between '2012-01-01 00:01:00.5' and '2020-01-01 00:00:00' GROUP BY a; + +DROP TABLE t1; + +--echo # End of test#80244 diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 68d62b6..372893e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7545,12 +7545,14 @@ get_mm_leaf(RANGE_OPT_PARAM *param, Item *conf_func, Field *field, switch (type) { case Item_func::LT_FUNC: - /* Don't use open ranges for partial key_segments */ - if ((!(key_part->flag & HA_PART_KEY_SEG)) && - stored_field_cmp_to_item(param->thd, field, value) == 0) - tree->max_flag=NEAR_MAX; - /* fall through */ case Item_func::LE_FUNC: + /* Don't use open ranges for partial key_segments */ + if (!(key_part->flag & HA_PART_KEY_SEG)) { + int comparison = stored_field_cmp_to_item(param->thd, field, value); + if ((type == Item_func::LT_FUNC && comparison >= 0) || + (type == Item_func::LE_FUNC && comparison > 0)) + tree->max_flag=NEAR_MAX; + } if (!maybe_null) tree->min_flag=NO_MIN_RANGE; /* From start */ else -- 2.3.0