[Commits] Rev 3625: Fix for MDEV-4140 in file:///home/tsk/mprog/src/5.3-md4140/

timour at askmonty.org timour at askmonty.org
Wed Feb 13 11:58:27 EET 2013


At file:///home/tsk/mprog/src/5.3-md4140/

------------------------------------------------------------
revno: 3625
revision-id: timour at askmonty.org-20130213095816-f7kdtzf3o9kvbylv
parent: igor at askmonty.org-20130212194946-azyoaim04r3b8evx
fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-4140
committer: timour at askmonty.org
branch nick: 5.3-md4140
timestamp: Wed 2013-02-13 11:58:16 +0200
message:
  Fix for MDEV-4140
  
  Analysis:
  Range analysis detects that the subquery is expensive and doesn't
  build a range access method. Later, the applicability test for loose
  scan doesn't take that into account, and builds a loose scan method
  without a range scan on the min/max column. As a result loose scan
  fetches the first key in each group, rather than the first key that
  satisfies the condition on the min/max column.
  
  Solution:
  Since there is no SEL_ARG tree to be used for the min/max column,
  it is not possible to use loose scan if the min/max column is compared
  with an expensive scalar subquery. Make the test for loose scan
  applicability to be in sync with the range analysis code by testing if
  the min/max argument is compared with an expensive predicate.
-------------- next part --------------
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2013-02-04 15:35:48 +0000
+++ b/mysql-test/r/group_min_max.result	2013-02-13 09:58:16 +0000
@@ -3243,4 +3243,40 @@ d	4
 f       7
 g       8
 drop table t1;
+#
+# MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
+#
+CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+CREATE TABLE t2 (c int) ;
+INSERT INTO t2 VALUES (0),(1);
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      range   NULL    b       10      NULL    10      Using where; Using index for group-by
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+MIN(a)  b
+1       0
+9       99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      index   NULL    b       10      NULL    9       Using where; Using index
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+MIN(a)  b
+1       0
+9       99
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      index   NULL    b       10      NULL    9       Using where; Using index
+2       SUBQUERY        t2      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       SUBQUERY        t1a     index   NULL    b       10      NULL    9       Using index; Using join buffer (flat, BNL join)
+2       SUBQUERY        t1b     index   NULL    b       10      NULL    9       Using index; Using join buffer (incremental, BNL join)
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+MIN(a)  b
+1       0
+9       99
+drop table t1, t2;
 End of 5.3 tests

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2013-02-04 15:35:48 +0000
+++ b/mysql-test/t/group_min_max.test	2013-02-13 09:58:16 +0000
@@ -1239,4 +1239,26 @@ SELECT b, min(a) FROM t1 WHERE (a > '0'
 
 drop table t1;
 
+--echo #
+--echo # MDEV-4140 Wrong result with GROUP BY + multipart key + MIN/MAX loose scan and a subquery
+--echo #
+
+CREATE TABLE t1 (a int, b int, KEY (b, a)) ;
+INSERT INTO t1 VALUES (0,99),(9,99),(4,0),(7,0),(99,0),(7,0),(8,0),(99,0),(1,0);
+CREATE TABLE t2 (c int) ;
+INSERT INTO t2 VALUES (0),(1);
+
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > 0 GROUP BY b;
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT c FROM t2 WHERE c = 0 ) GROUP BY b;
+# this test is for 5.5 to ensure that the subquery is expensive
+EXPLAIN
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+SELECT MIN(a), b FROM t1 WHERE a > ( SELECT min(c) FROM t2, t1 t1a, t1 t1b WHERE c = 0 ) GROUP BY b;
+
+drop table t1, t2;
+
 --echo End of 5.3 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2013-02-04 15:35:48 +0000
+++ b/sql/opt_range.cc	2013-02-13 09:58:16 +0000
@@ -12236,7 +12236,7 @@ check_group_min_max_predicates(Item *con
                                           &has_min_max, &has_other))
         DBUG_RETURN(FALSE);
     }
-    else if (cur_arg->const_item())
+    else if (cur_arg->const_item() && !cur_arg->is_expensive())
     {
       /*
         For predicates of the form "const OP expr" we also have to check 'expr'



More information about the commits mailing list