List:Commits« Previous MessageNext Message »
From:Igor Babaev Date:March 10 2009 1:34am
Subject:bzr commit into mysql-6.0-opt branch (igor:2727) Bug#42955
View as plain text  
#At file:///home/igor/dev-bzr/mysql-6.0-opt-bug42955/

 2727 Igor Babaev	2009-03-09
      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-10 01:34:11 +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-10 01:34:11 +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-10 01:34:11 +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-10 01:34:11 +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-10 01:34:11 +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-10 01:34:11 +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-10 01:34:11 +0000
@@ -1909,7 +1909,19 @@ JOIN::optimize()
 	      (group_list && order) ||
 	      test(select_options & OPTION_BUFFER_RESULT)));
 
-  uint no_jbuf_after= make_join_orderinfo(this);
+  /*
+    If 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, then
+    the proper value for no_jbuf_after should be yielded by a call to
+    the make_join_orderinfo function. 
+    Yet the current implementation of FORCE INDEX hints does not
+    allow us to do it in a clean manner.
+  */   
+  uint no_jbuf_after= tables;
+#if 0
+  no_jbuf_after= make_join_orderinfo(this);
+#endif
+
   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 +9334,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 +10246,31 @@ make_join_readinfo(JOIN *join, ulonglong
     }
   }
   join->join_tab[join->tables-1].next_select=0; /* Set by do_select */
+
+  /* 
+    If a join buffer is used to join a table the ordering by an index
+    for the first non-constant table cannot be employed anymore.
+  */
+  for (i=join->const_tables ; i < join->tables ; i++)
+  {
+    JOIN_TAB *tab=join->join_tab+i;
+    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-10 01:34:11 +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) ?
+              NULL : join_tab+const_tables;
+  }
 private:
   bool make_simple_join(JOIN *join, TABLE *tmp_table);
 };

Thread
bzr commit into mysql-6.0-opt branch (igor:2727) Bug#42955Igor Babaev10 Mar