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#11761078 | Jorgen Loland | 17 Nov |
