#At file:///home/igor/dev-bzr/mysql-6.0-opt-bug42955/
2727 Igor Babaev 2009-03-04
Fixed bug #42955.
If a join buffer is employed to join a table through BNL or
BKA algorithm for a query with a ORDER BY / GROUP BY clause
then the result set has always to be sorted unless the clause
can be optimized away.
modified:
mysql-test/r/join_cache.result
mysql-test/r/join_outer.result
mysql-test/r/join_outer_jcl6.result
mysql-test/r/select_jcl6.result
mysql-test/t/join_cache.test
mysql-test/t/join_outer.test
sql/sql_select.cc
sql/sql_select.h
per-file messages:
mysql-test/r/join_cache.result
Added a test case for bug #42955.
Adjusted results for a test case.
mysql-test/r/join_outer.result
Changed a test case to make it predictable after the fix
for bug #42955.
mysql-test/r/join_outer_jcl6.result
Changed a test case to make it predictable after the fix
for bug #42955.
mysql-test/r/select_jcl6.result
Adjusted results after the fix for bug #42955.
mysql-test/t/join_cache.test
Added a test case for bug #42955.
mysql-test/t/join_outer.test
Changed a test case to make it predictable after the fix
for bug #42955.
sql/sql_select.cc
If a join buffer is is used to join a table the ordering by an index
for the first non-constant table cannot be employed anymore.
sql/sql_select.h
Added the method get_sort_by_join_tab to the the JOIN class.
It returns the table for which an index scan can be used to
satisfy the sort order needed by the ORDER BY/GROUP BY clause.
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result 2009-02-20 11:53:55 +0000
+++ b/mysql-test/r/join_cache.result 2009-03-05 05:38:19 +0000
@@ -3449,8 +3449,8 @@ explain
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 4 Using index
-1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where
+1 SIMPLE t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort
+1 SIMPLE t2 ref i_a i_a 5 test.t1.a 2 Using where; Using join buffer
select t1.a, count(t2.p) as count
from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
a count
@@ -3787,3 +3787,45 @@ id select_type table type possible_keys
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
drop table t1,t2;
set join_cache_level=default;
+#
+# Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled
+#
+create table t1 (d int, id1 int, index idx1 (d, id1));
+insert into t1 values
+(3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30);
+create table t2 (id1 int, id2 int, index idx2 (id1));
+insert into t2 values
+(20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
+(40, 200), (30, 300), (10, 400), (20, 200), (20, 300);
+set join_cache_level=6;
+explain
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
+where t1.d=3 group by t1.id1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using index; Using temporary; Using filesort
+1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using join buffer
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
+where t1.d=3 group by t1.id1;
+id1 sum(t2.id2)
+10 900
+20 2000
+30 900
+explain
+select t1.id1 from t1 join t2 on t1.id1=t2.id1
+where t1.d=3 and t2.id2 > 200 order by t1.id1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref idx1 idx1 5 const 4 Using index; Using temporary; Using filesort
+1 SIMPLE t2 ref idx2 idx2 5 test.t1.id1 2 Using where; Using join buffer
+select t1.id1 from t1 join t2 on t1.id1=t2.id1
+where t1.d=3 and t2.id2 > 200 order by t1.id1;
+id1
+10
+10
+20
+20
+20
+20
+30
+30
+set join_cache_level=default;
+drop table t1,t2;
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result 2008-10-25 00:35:49 +0000
+++ b/mysql-test/r/join_outer.result 2009-03-05 05:38:19 +0000
@@ -414,7 +414,7 @@ n m o n m o
1 2 9 1 2 3
1 3 9 NULL NULL NULL
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
-t1.m = t2.m where t1.n = 1 order by t1.o;
+t1.m = t2.m where t1.n = 1 order by t1.o,t1.m;
n m o n m o
1 2 7 1 2 3
1 2 9 1 2 3
=== modified file 'mysql-test/r/join_outer_jcl6.result'
--- a/mysql-test/r/join_outer_jcl6.result 2008-11-05 00:53:38 +0000
+++ b/mysql-test/r/join_outer_jcl6.result 2009-03-05 05:38:19 +0000
@@ -418,12 +418,12 @@ n m o n m o
1 2 9 1 2 3
1 3 9 NULL NULL NULL
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
-t1.m = t2.m where t1.n = 1 order by t1.o;
+t1.m = t2.m where t1.n = 1 order by t1.o,t1.m;
n m o n m o
1 2 7 1 2 3
1 2 9 1 2 3
-1 2 11 1 2 3
1 3 9 NULL NULL NULL
+1 2 11 1 2 3
drop table t1,t2;
CREATE TABLE t1 (id1 INT NOT NULL PRIMARY KEY, dat1 CHAR(1), id2 INT);
INSERT INTO t1 VALUES (1,'a',1);
=== modified file 'mysql-test/r/select_jcl6.result'
--- a/mysql-test/r/select_jcl6.result 2009-01-06 14:19:37 +0000
+++ b/mysql-test/r/select_jcl6.result 2009-03-05 05:38:19 +0000
@@ -611,11 +611,11 @@ id select_type table type possible_keys
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer
explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t3 index period period 4 NULL 1
+1 SIMPLE t3 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
1 SIMPLE t1 ref period period 4 test.t3.period 4181 Using join buffer
explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index period period 4 NULL 1
+1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort
1 SIMPLE t3 ref period period 4 test.t1.period 4181 Using join buffer
select period from t1;
period
=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test 2009-02-19 10:50:48 +0000
+++ b/mysql-test/t/join_cache.test 2009-03-05 05:38:19 +0000
@@ -1142,3 +1142,37 @@ where t1.f1=t2.f1 and t2.f2 between t1.f
drop table t1,t2;
set join_cache_level=default;
+
+--echo #
+--echo # Bug #42955: join with GROUP BY/ORDER BY and when BKA is enabled
+--echo #
+
+create table t1 (d int, id1 int, index idx1 (d, id1));
+insert into t1 values
+ (3, 20), (2, 40), (3, 10), (1, 10), (3, 20), (1, 40), (2, 30), (3, 30);
+
+create table t2 (id1 int, id2 int, index idx2 (id1));
+insert into t2 values
+ (20, 100), (30, 400), (20, 400), (30, 200), (10, 300), (10, 200), (40, 100),
+ (40, 200), (30, 300), (10, 400), (20, 200), (20, 300);
+
+set join_cache_level=6;
+
+explain
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
+ where t1.d=3 group by t1.id1;
+
+select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1
+ where t1.d=3 group by t1.id1;
+
+explain
+select t1.id1 from t1 join t2 on t1.id1=t2.id1
+ where t1.d=3 and t2.id2 > 200 order by t1.id1;
+
+select t1.id1 from t1 join t2 on t1.id1=t2.id1
+ where t1.d=3 and t2.id2 > 200 order by t1.id1;
+
+set join_cache_level=default;
+
+drop table t1,t2;
+
=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test 2007-06-06 17:57:07 +0000
+++ b/mysql-test/t/join_outer.test 2009-03-05 05:38:19 +0000
@@ -309,7 +309,7 @@ insert into t2 values (1, 2, 3),(2, 2, 8
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
t1.m = t2.m where t1.n = 1;
select t1.*, t2.* from t1 left join t2 on t1.n = t2.n and
-t1.m = t2.m where t1.n = 1 order by t1.o;
+t1.m = t2.m where t1.n = 1 order by t1.o,t1.m;
drop table t1,t2;
# Test bug with NATURAL join:
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-02-16 21:18:45 +0000
+++ b/sql/sql_select.cc 2009-03-05 05:38:19 +0000
@@ -1909,7 +1909,16 @@ JOIN::optimize()
(group_list && order) ||
test(select_options & OPTION_BUFFER_RESULT)));
- uint no_jbuf_after= make_join_orderinfo(this);
+ /*
+ Here we'd rather make a call of the make_join_orderinfo function
+ to return the proper value for no_jbuf_after in the case when
+ the hint FORCE INDEX FOR ORDER BY/GROUP BY is used for the table
+ whose columns are required to be returned in a sorted order.
+ Yet the current implementation of FORCE INDEX hints does not
+ allow us to do it in a clean manner.
+ */
+ uint no_jbuf_after= 1 ? tables : make_join_orderinfo(this);
+
ulonglong select_opts_for_readinfo=
(select_options & (SELECT_DESCRIBE | SELECT_NO_JOIN_CACHE)) |
(select_lex->ftfunc_list->elements ? SELECT_NO_JOIN_CACHE : 0);
@@ -9322,22 +9331,11 @@ static void push_index_cond(JOIN_TAB *ta
static uint make_join_orderinfo(JOIN *join)
{
- uint i;
+ JOIN_TAB *tab;
if (join->need_tmp)
return join->tables;
-
- for (i=join->const_tables ; i < join->tables ; i++)
- {
- JOIN_TAB *tab=join->join_tab+i;
- TABLE *table=tab->table;
- if ((table == join->sort_by_table &&
- (!join->order || join->skip_sort_order)) ||
- (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
- {
- break;
- }
- }
- return i-1;
+ tab= join->get_sort_by_join_tab();
+ return tab ? tab-join->join_tab : join->tables;
}
/*
@@ -10245,6 +10243,29 @@ make_join_readinfo(JOIN *join, ulonglong
}
}
join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
+ for (i=join->const_tables ; i < join->tables ; i++)
+ {
+ JOIN_TAB *tab=join->join_tab+i;
+ /*
+ If a join buffer is is used to join a table the ordering by an index
+ for the first non-constant table cannot be employed anymore.
+ */
+ if (tab->use_join_cache)
+ {
+ JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab();
+ if (sort_by_tab && !join->need_tmp)
+ {
+ join->need_tmp= 1;
+ join->simple_order= join->simple_group= 0;
+ if (sort_by_tab->type == JT_NEXT)
+ {
+ sort_by_tab->type= JT_ALL;
+ sort_by_tab->read_first_record= join_init_read_record;
+ }
+ }
+ break;
+ }
+ }
DBUG_RETURN(FALSE);
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-02-16 21:18:45 +0000
+++ b/sql/sql_select.h 2009-03-05 05:38:19 +0000
@@ -1726,6 +1726,15 @@ public:
return (unit == &thd->lex->unit && (unit->fake_select_lex == 0 ||
select_lex == unit->fake_select_lex));
}
+ /*
+ Return the table for which an index scan can be used to satisfy
+ the sort order needed by the ORDER BY/GROUP BY clause
+ */
+ JOIN_TAB *get_sort_by_join_tab()
+ {
+ return (need_tmp || !sort_by_table || skip_sort_order) ?
+ 0 : join_tab+const_tables;
+ }
private:
bool make_simple_join(JOIN *join, TABLE *tmp_table);
};
