List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 17 2011 10:09am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3617 to 3618) Bug#11761078
View as plain text  
 3618 Jorgen Loland	2011-11-17
      BUG#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' 
                    WITH GROUP BY ON DUPLICATED FIELDS
      
      If a GROUP BY clause contains duplicates of the same field
      and group range access is used to resolve the query, the optimizer
      incorrectly believed that the range access would not provide 
      the ordered output required to do grouping. Thus, the already
      ordered rows were ordered once more using filesort (which yields
      correct result but is not optimal). end_send() then thought 
      that group range access method had not been used (see 
      is_using_loose_index_scan()) and therefore did not copy 
      non-aggregated fields to the resultset.
      
      The fix is to remove duplicated (and redundant) fields from 
      the GROUP BY and ORDER BY clauses, which in turns makes 
      the optimizer realize that the output of the range access method
      is, in fact, ordered.

    modified:
      mysql-test/r/group_by.result
      mysql-test/suite/opt_trace/r/subquery_no_prot.result
      mysql-test/suite/opt_trace/r/subquery_ps_prot.result
      mysql-test/t/group_by.test
      sql/sql_select.cc
 3617 Jimmy Yang	2011-11-16 [merge]
      Implement WL #5538 InnoDB Full-Text Search Support, merge
      mysql-5.6-labs-innodb-fts into mysql-trunk

    removed:
      storage/innobase/include/srv0conc.h
      storage/innobase/srv/srv0conc.c
    added:
      mysql-test/suite/innodb_fts/
      mysql-test/suite/innodb_fts/r/
      mysql-test/suite/innodb_fts/r/fulltext.result
      mysql-test/suite/innodb_fts/r/fulltext2.result
      mysql-test/suite/innodb_fts/r/fulltext3.result
      mysql-test/suite/innodb_fts/r/fulltext_cache.result
      mysql-test/suite/innodb_fts/r/fulltext_distinct.result
      mysql-test/suite/innodb_fts/r/fulltext_left_join.result
      mysql-test/suite/innodb_fts/r/fulltext_misc.result
      mysql-test/suite/innodb_fts/r/fulltext_multi.result
      mysql-test/suite/innodb_fts/r/fulltext_order_by.result
      mysql-test/suite/innodb_fts/r/fulltext_plugin.result
      mysql-test/suite/innodb_fts/r/fulltext_update.result
      mysql-test/suite/innodb_fts/r/fulltext_var.result
      mysql-test/suite/innodb_fts/r/innodb-fts-basic.result
      mysql-test/suite/innodb_fts/r/innodb-fts-ddl.result
      mysql-test/suite/innodb_fts/r/innodb-fts-fic.result
      mysql-test/suite/innodb_fts/r/innodb-fts-stopword.result
      mysql-test/suite/innodb_fts/r/innodb_fts_large_records.result
      mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
      mysql-test/suite/innodb_fts/r/innodb_fts_misc_1.result
      mysql-test/suite/innodb_fts/r/innodb_fts_mutiple_index.result
      mysql-test/suite/innodb_fts/r/innodb_fts_proximity.result
      mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result
      mysql-test/suite/innodb_fts/t/
      mysql-test/suite/innodb_fts/t/disabled.def
      mysql-test/suite/innodb_fts/t/fulltext.test
      mysql-test/suite/innodb_fts/t/fulltext2.test
      mysql-test/suite/innodb_fts/t/fulltext3.test
      mysql-test/suite/innodb_fts/t/fulltext_cache.test
      mysql-test/suite/innodb_fts/t/fulltext_distinct.test
      mysql-test/suite/innodb_fts/t/fulltext_left_join.test
      mysql-test/suite/innodb_fts/t/fulltext_misc.test
      mysql-test/suite/innodb_fts/t/fulltext_multi.test
      mysql-test/suite/innodb_fts/t/fulltext_order_by.test
      mysql-test/suite/innodb_fts/t/fulltext_plugin-master.opt
      mysql-test/suite/innodb_fts/t/fulltext_plugin.test
      mysql-test/suite/innodb_fts/t/fulltext_update.test
      mysql-test/suite/innodb_fts/t/fulltext_var.test
      mysql-test/suite/innodb_fts/t/innodb-fts-basic.test
      mysql-test/suite/innodb_fts/t/innodb-fts-ddl.test
      mysql-test/suite/innodb_fts/t/innodb-fts-fic.test
      mysql-test/suite/innodb_fts/t/innodb-fts-stopword.test
      mysql-test/suite/innodb_fts/t/innodb_fts_large_records.test
      mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
      mysql-test/suite/innodb_fts/t/innodb_fts_misc_1.test
      mysql-test/suite/innodb_fts/t/innodb_fts_mutiple_index.test
      mysql-test/suite/innodb_fts/t/innodb_fts_proximity.test
      mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test
      storage/innobase/fts/
      storage/innobase/fts/Makefile.query
      storage/innobase/fts/fts0ast.c
      storage/innobase/fts/fts0blex.c
      storage/innobase/fts/fts0blex.l
      storage/innobase/fts/fts0config.c
      storage/innobase/fts/fts0opt.c
      storage/innobase/fts/fts0pars.c
      storage/innobase/fts/fts0pars.y
      storage/innobase/fts/fts0que.c
      storage/innobase/fts/fts0sql.c
      storage/innobase/fts/fts0tlex.c
      storage/innobase/fts/fts0tlex.l
      storage/innobase/fts/make_parser.sh
      storage/innobase/fut/fut0fut.c
      storage/innobase/include/fts0ast.h
      storage/innobase/include/fts0blex.h
      storage/innobase/include/fts0fts.h
      storage/innobase/include/fts0opt.h
      storage/innobase/include/fts0pars.h
      storage/innobase/include/fts0priv.h
      storage/innobase/include/fts0tlex.h
      storage/innobase/include/fts0types.h
      storage/innobase/include/fts0types.ic
      storage/innobase/include/fts0vlc.ic
      storage/innobase/include/fts_uc_data.ic
      storage/innobase/include/row0ftsort.h
      storage/innobase/include/srv0conc.h
      storage/innobase/row/row0ftsort.c
      storage/innobase/srv/srv0conc.c
    renamed:
      storage/innobase/fut/fut0fut.c => storage/innobase/fts/fts0fts.c
    modified:
      include/my_base.h
      mysql-test/collections/default.push
      mysql-test/mysql-test-run.pl
      mysql-test/r/information_schema.result
      mysql-test/r/mysqlshow.result
      mysql-test/suite/innodb/r/innodb.result
      mysql-test/suite/innodb/t/innodb.test
      mysql-test/suite/sys_vars/r/all_vars.result
      mysys/my_handler_errors.h
      sql/handler.cc
      sql/share/errmsg-utf8.txt
      storage/innobase/CMakeLists.txt
      storage/innobase/btr/btr0btr.c
      storage/innobase/btr/btr0cur.c
      storage/innobase/dict/dict0crea.c
      storage/innobase/dict/dict0dict.c
      storage/innobase/dict/dict0load.c
      storage/innobase/dict/dict0mem.c
      storage/innobase/dict/dict0stats.c
      storage/innobase/eval/eval0eval.c
      storage/innobase/handler/ha_innodb.cc
      storage/innobase/handler/ha_innodb.h
      storage/innobase/handler/handler0alter.cc
      storage/innobase/handler/i_s.cc
      storage/innobase/handler/i_s.h
      storage/innobase/include/btr0cur.h
      storage/innobase/include/buf0buf.h
      storage/innobase/include/buf0buf.ic
      storage/innobase/include/data0type.h
      storage/innobase/include/data0type.ic
      storage/innobase/include/db0err.h
      storage/innobase/include/dict0dict.h
      storage/innobase/include/dict0dict.ic
      storage/innobase/include/dict0mem.h
      storage/innobase/include/mach0data.h
      storage/innobase/include/mach0data.ic
      storage/innobase/include/os0sync.h
      storage/innobase/include/pars0grm.h
      storage/innobase/include/pars0pars.h
      storage/innobase/include/pars0sym.h
      storage/innobase/include/que0que.h
      storage/innobase/include/que0que.ic
      storage/innobase/include/rem0cmp.h
      storage/innobase/include/rem0cmp.ic
      storage/innobase/include/rem0rec.h
      storage/innobase/include/row0merge.h
      storage/innobase/include/row0mysql.h
      storage/innobase/include/row0sel.h
      storage/innobase/include/row0upd.h
      storage/innobase/include/row0upd.ic
      storage/innobase/include/srv0srv.h
      storage/innobase/include/srv0start.h
      storage/innobase/include/sync0rw.h
      storage/innobase/include/sync0sync.h
      storage/innobase/include/trx0trx.h
      storage/innobase/include/univ.i
      storage/innobase/include/ut0list.h
      storage/innobase/include/ut0list.ic
      storage/innobase/include/ut0mem.h
      storage/innobase/include/ut0rbt.h
      storage/innobase/include/ut0ut.h
      storage/innobase/include/ut0vec.h
      storage/innobase/include/ut0vec.ic
      storage/innobase/include/ut0wqueue.h
      storage/innobase/lock/lock0lock.c
      storage/innobase/log/log0recv.c
      storage/innobase/os/os0file.c
      storage/innobase/os/os0sync.c
      storage/innobase/pars/lexyy.c
      storage/innobase/pars/pars0grm.c
      storage/innobase/pars/pars0grm.y
      storage/innobase/pars/pars0lex.l
      storage/innobase/pars/pars0opt.c
      storage/innobase/pars/pars0pars.c
      storage/innobase/pars/pars0sym.c
      storage/innobase/rem/rem0cmp.c
      storage/innobase/rem/rem0rec.c
      storage/innobase/row/row0ins.c
      storage/innobase/row/row0merge.c
      storage/innobase/row/row0mysql.c
      storage/innobase/row/row0purge.c
      storage/innobase/row/row0sel.c
      storage/innobase/row/row0uins.c
      storage/innobase/row/row0umod.c
      storage/innobase/row/row0upd.c
      storage/innobase/srv/srv0start.c
      storage/innobase/sync/sync0sync.c
      storage/innobase/trx/trx0purge.c
      storage/innobase/trx/trx0roll.c
      storage/innobase/trx/trx0trx.c
      storage/innobase/ut/ut0list.c
      storage/innobase/ut/ut0mem.c
      storage/innobase/ut/ut0rbt.c
      storage/innobase/ut/ut0ut.c
      storage/innobase/ut/ut0vec.c
      storage/innobase/ut/ut0wqueue.c
      storage/innobase/fts/fts0fts.c
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-11-11 12:36:01 +0000
+++ b/mysql-test/r/group_by.result	2011-11-17 10:09:13 +0000
@@ -2153,3 +2153,54 @@ col2
 8
 
 DROP TABLE t1,t2;
+#
+# 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);
+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	range	NULL	idx	5	NULL	6	Using index for group-by; Using filesort
+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
+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
+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
+DROP VIEW v1;
+DROP TABLE t1;

=== modified file 'mysql-test/suite/opt_trace/r/subquery_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_no_prot.result	2011-11-17 10:09:13 +0000
@@ -1691,11 +1691,12 @@ field4,field5,field6	{
                   "item": "`alias2`.`col_varchar_nokey`"
                 },
                 {
-                  "item": "`alias2`.`col_varchar_nokey`"
+                  "item": "`alias2`.`col_varchar_nokey`",
+                  "duplicate_item": true
                 }
               ] /* items */,
               "resulting_clause_is_simple": false,
-              "resulting_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+              "resulting_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,`alias2`.`col_varchar_nokey`"
             } /* clause_processing */
           },
           {
@@ -1744,12 +1745,6 @@ field4,field5,field6	{
                 "database": "",
                 "table": "",
                 "field": "col_varchar_nokey"
-              },
-              {
-                "direction": "asc",
-                "database": "",
-                "table": "",
-                "field": "col_varchar_nokey"
               }
             ] /* filesort_information */,
             "filesort_priority_queue_optimization": {

=== modified file 'mysql-test/suite/opt_trace/r/subquery_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-11-08 07:51:49 +0000
+++ b/mysql-test/suite/opt_trace/r/subquery_ps_prot.result	2011-11-17 10:09:13 +0000
@@ -1669,11 +1669,12 @@ field4,field5,field6	{
                   "item": "`alias2`.`col_varchar_nokey`"
                 },
                 {
-                  "item": "`alias2`.`col_varchar_nokey`"
+                  "item": "`alias2`.`col_varchar_nokey`",
+                  "duplicate_item": true
                 }
               ] /* items */,
               "resulting_clause_is_simple": false,
-              "resulting_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,`alias2`.`col_varchar_nokey`,`alias2`.`col_varchar_nokey`"
+              "resulting_clause": "`test`.`alias1`.`col_varchar_key`,`test`.`alias1`.`col_date_key`,`alias2`.`col_varchar_nokey`"
             } /* clause_processing */
           },
           {
@@ -1722,12 +1723,6 @@ field4,field5,field6	{
                 "database": "",
                 "table": "",
                 "field": "col_varchar_nokey"
-              },
-              {
-                "direction": "asc",
-                "database": "",
-                "table": "",
-                "field": "col_varchar_nokey"
               }
             ] /* filesort_information */,
             "filesort_priority_queue_optimization": {

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2011-11-11 08:11:38 +0000
+++ b/mysql-test/t/group_by.test	2011-11-17 10:09:13 +0000
@@ -1505,3 +1505,36 @@ let $q_body=t2.col2 FROM t2 JOIN t1 ON t
 
 --echo
 DROP TABLE t1,t2;
+
+--echo #
+--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);
+
+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;
+--eval $query;
+
+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;
+
+DROP VIEW v1;
+DROP TABLE t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-11-11 12:36:01 +0000
+++ b/sql/sql_select.cc	2011-11-17 10:09:13 +0000
@@ -101,6 +101,8 @@ static bool make_join_readinfo(JOIN *joi
 static bool only_eq_ref_tables(JOIN *join, ORDER *order, table_map tables,
                                table_map *cached_eq_ref_tables,
                                table_map *eq_ref_tables);
+static bool duplicate_order(const ORDER *first_order, 
+                            const ORDER *possible_dup);
 static void update_depend_map(JOIN *join);
 static void update_depend_map(JOIN *join, ORDER *order);
 static ORDER *remove_const(JOIN *join,ORDER *first_order,Item *cond,
@@ -13236,6 +13238,45 @@ only_eq_ref_tables(JOIN *join, ORDER *or
   return true;
 }
 
+/**
+  Check if an expression in ORDER BY or GROUP BY is a duplicate of a
+  preceding expression.
+
+  @param  first_order   the first expression in the ORDER BY or
+                        GROUP BY clause
+  @param  possible_dup  the expression that might be a duplicate of
+                        another expression preceding it the ORDER BY
+                        or GROUP BY clause
+
+  @returns true if possible_dup is a duplicate, false otherwise
+*/
+static bool duplicate_order(const ORDER *first_order, 
+                            const ORDER *possible_dup)
+{
+  const ORDER *order;
+  for (order=first_order; order ; order=order->next)
+  {
+    if (order == possible_dup)
+    {
+      // all expressions preceding possible_dup have been checked.
+      return false;
+    }
+    else 
+    {
+      const Item *it1= order->item[0]->real_item();
+      const Item *it2= possible_dup->item[0]->real_item();
+
+      if (it1->type() == Item::FIELD_ITEM &&
+          it2->type() == Item::FIELD_ITEM &&
+          (static_cast<const Item_field*>(it1)->field ==
+           static_cast<const Item_field*>(it2)->field))
+      {
+        return true;
+      }
+    }
+  }
+  return false;
+}
 
 /** Update the dependency map for the tables. */
 
@@ -13376,6 +13417,16 @@ remove_const(JOIN *join,ORDER *first_ord
       trace_one_item.add("uses_only_constant_tables", true);
       continue;					// skip const item
     }
+    else if (duplicate_order(first_order, order))
+    {
+      /* 
+        If 'order' is a duplicate of an expression earlier in the
+        ORDER/GROUP BY sequence, it can be removed from the ORDER BY
+        or GROUP BY clause.
+      */
+      trace_one_item.add("duplicate_item", true);
+      continue;
+    }
     else
     {
       if (order_tables & (RAND_TABLE_BIT | OUTER_REF_TABLE_BIT))

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3617 to 3618) Bug#11761078Jorgen Loland17 Nov