From: Jorgen Loland Date: November 17 2011 10:09am Subject: bzr push into mysql-trunk branch (jorgen.loland:3617 to 3618) Bug#11761078 List-Archive: http://lists.mysql.com/commits/142009 X-Bug: 11761078 Message-Id: <20111117100926.5324228A@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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(it1)->field == + static_cast(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).