[Commits] Rev 3181: Fix bug MDEV-641 in file:///home/tsk/mprog/src/5.1/

timour at askmonty.org timour at askmonty.org
Fri Feb 1 00:09:51 EET 2013


At file:///home/tsk/mprog/src/5.1/

------------------------------------------------------------
revno: 3181
revision-id: timour at askmonty.org-20130131220936-2kpzy2si5seimpaa
parent: sergii at pisem.net-20130125132946-wpbhodtnxikdhko3
fixes bug(s): https://mariadb.atlassian.net/browse/MDEV-641
committer: timour at askmonty.org
branch nick: 5.1
timestamp: Fri 2013-02-01 00:09:36 +0200
message:
  Fix bug MDEV-641
  
  Analysis:
  Range analysis discoveres that the query can be executed via loose index scan for GROUP BY.
  Later, GROUP BY analysis fails to confirm that the GROUP operation can be computed via an
  index because there is no logic to handle duplicate field references in the GROUP clause.
  As a result the optimizer produces an inconsistent plan. It constructs a temporary table,
  but on the other hand the group fields are not set to point there.
      
  Solution:
  Make loose scan analysis work in sync with order by analysis. In the case of duplicate
  columns loose scan will not be applicable. This limitation will be lifted in 10.0 by
  removing duplicate columns.
-------------- next part --------------
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-11-21 17:13:14 +0000
+++ b/mysql-test/r/group_by.result	2013-01-31 22:09:36 +0000
@@ -1940,4 +1940,248 @@ Warning	1292	Truncated incorrect INTEGER
 Warning 1292    Truncated incorrect INTEGER value: 'jxW<'
 DROP TABLE t1;
 SET SQL_BIG_TABLES=0;
+#
+# MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause
+# Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 
+#               WITH GROUP BY ON DUPLICATED FIELDS
+#
+CREATE TABLE t1(
+col1 int, 
+UNIQUE INDEX idx (col1));
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
+(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
+EXPLAIN SELECT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      index   NULL    idx     5       NULL    20      Using index; Using temporary; Using filesort
+FLUSH STATUS;
+SELECT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+field1  field2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+Variable_name   Value
+Sort_scan       1
+EXPLAIN SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      index   NULL    idx     5       NULL    20      Using index; Using filesort
+FLUSH STATUS;
+SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM t1 GROUP BY field1, field2;;
+field1  field2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+Variable_name   Value
+Sort_scan       1
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM v1 
+GROUP BY field1, field2;
+field1  field2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2
+FROM t1 as tbl1, t1 as tbl2 
+GROUP BY field1, field2 
+LIMIT 3;
+field1  field2
+1       1
+1       2
+1       3
+explain
+select col1 f1, col1 f2 from t1 order by f2, f1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      index   NULL    idx     5       NULL    20      Using index; Using filesort
+select col1 f1, col1 f2 from t1 order by f2, f1;
+f1      f2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+explain
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      range   NULL    idx     5       NULL    7       Using index for group-by; Using temporary; Using filesort
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+f1      f2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+explain
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t1      index   NULL    idx     5       NULL    20      Using index; Using temporary; Using filesort
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+f1      f2
+1       1
+2       2
+3       3
+4       4
+5       5
+6       6
+7       7
+8       8
+9       9
+10      10
+11      11
+12      12
+13      13
+14      14
+15      15
+16      16
+17      17
+18      18
+19      19
+20      20
+CREATE TABLE t2(
+col1 int, 
+col2 int, 
+UNIQUE INDEX idx (col1, col2));
+INSERT INTO t2(col1, col2) VALUES
+(1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11),
+(11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1);
+explain
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t2      index   NULL    idx     10      NULL    20      Using index; Using temporary; Using filesort
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+f1      f2      f3
+1       20      1
+2       19      2
+3       18      3
+4       17      4
+5       16      5
+6       15      6
+7       14      7
+8       13      8
+9       12      9
+10      11      10
+11      10      11
+12      9       12
+13      8       13
+14      7       14
+15      6       15
+16      5       16
+17      4       17
+18      3       18
+19      2       19
+20      1       20
+explain
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       SIMPLE  t2      index   NULL    idx     10      NULL    20      Using index; Using filesort
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+f1      f2      f3
+1       20      1
+2       19      2
+3       18      3
+4       17      4
+5       16      5
+6       15      6
+7       14      7
+8       13      8
+9       12      9
+10      11      10
+11      10      11
+12      9       12
+13      8       13
+14      7       14
+15      6       15
+16      5       16
+17      4       17
+18      3       18
+19      2       19
+20      1       20
+DROP VIEW v1;
+DROP TABLE t1, t2;
 # End of 5.1 tests

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2011-11-21 17:13:14 +0000
+++ b/mysql-test/t/group_by.test	2013-01-31 22:09:36 +0000
@@ -1315,4 +1315,78 @@ SELECT 1 FROM t1 GROUP BY SUBSTRING(SYSD
 DROP TABLE t1;
 SET SQL_BIG_TABLES=0;
 
+--echo #
+--echo # MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause
+--echo # Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 
+--echo #               WITH GROUP BY ON DUPLICATED FIELDS
+--echo #
+
+CREATE TABLE t1(
+ col1 int, 
+ UNIQUE INDEX idx (col1));
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
+   (11),(12),(13),(14),(15),(16),(17),(18),(19),(20);
+
+let $query0=SELECT col1 AS field1, col1 AS field2
+            FROM t1 GROUP BY field1, field2;
+
+# Needs to be range to exercise bug
+--eval EXPLAIN $query0;
+FLUSH STATUS;
+--eval $query0;
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+
+let $query=SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+           FROM t1 GROUP BY field1, field2;
+
+# Needs to be range to exercise bug
+--eval EXPLAIN $query;
+FLUSH STATUS;
+--eval $query;
+SHOW SESSION STATUS LIKE 'Sort_scan%';
+
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT SQL_BIG_RESULT col1 AS field1, col1 AS field2
+FROM v1 
+GROUP BY field1, field2; 
+
+SELECT SQL_BIG_RESULT tbl1.col1 AS field1, tbl2.col1 AS field2
+FROM t1 as tbl1, t1 as tbl2 
+GROUP BY field1, field2 
+LIMIT 3;
+
+explain
+select col1 f1, col1 f2 from t1 order by f2, f1;
+select col1 f1, col1 f2 from t1 order by f2, f1;
+
+explain
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+select col1 f1, col1 f2 from t1 group by f2 order by f2, f1;
+
+explain
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+select col1 f1, col1 f2 from t1 group by f1, f2 order by f2, f1;
+
+CREATE TABLE t2(
+ col1 int, 
+ col2 int, 
+ UNIQUE INDEX idx (col1, col2));
+
+INSERT INTO t2(col1, col2) VALUES
+   (1,20),(2,19),(3,18),(4,17),(5,16),(6,15),(7,14),(8,13),(9,12),(10,11),
+   (11,10),(12,9),(13,8),(14,7),(15,6),(16,5),(17,4),(18,3),(19,2),(20,1);
+
+explain
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+select col1 f1, col2 f2, col1 f3 from t2 group by f1, f2, f3;
+
+explain
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+select col1 f1, col2 f2, col1 f3 from t2 order by f1, f2, f3;
+
+DROP VIEW v1;
+DROP TABLE t1, t2;
+
 --echo # End of 5.1 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2013-01-25 13:29:46 +0000
+++ b/sql/opt_range.cc	2013-01-31 22:09:36 +0000
@@ -9481,6 +9481,13 @@ get_best_group_min_max(PARAM *param, SEL
         else
           goto next_index;
       }
+      /*
+        This function is called on the precondition that the index is covering.
+        Therefore if the GROUP BY list contains more elements than the index,
+        these are duplicates. The GROUP BY list cannot be a prefix of the index.
+      */
+      if (cur_part == end_part && tmp_group)
+        goto next_index;
     }
     /*
       Check (GA2) if this is a DISTINCT query.



More information about the commits mailing list