[Commits] Rev 3604: Merge in file:///home/igor/maria/maria-5.3/

Igor Babaev igor at askmonty.org
Thu Nov 22 20:30:40 EET 2012


At file:///home/igor/maria/maria-5.3/

------------------------------------------------------------
revno: 3604 [merge]
revision-id: igor at askmonty.org-20121122183039-dm575rqkw40558qp
parent: sergii at pisem.net-20121117155015-4ab41ncach4iavao
parent: igor at askmonty.org-20121122055504-vdyz74a9d7n48x0o
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3
timestamp: Thu 2012-11-22 10:30:39 -0800
message:
  Merge
modified:
  mysql-test/r/group_by.result   sp1f-group_by.result-20001228015633-bgjibbiwynctdjq73ms5muj5g6hfpv4d
  mysql-test/r/subselect_sj_jcl6.result subselect_sj_jcl6.re-20100117143928-7vzk51yaf29cdavp-1
  mysql-test/t/group_by.test     sp1f-group_by.test-20001228015636-5zwfyi7n5e5jsbyby7a7fvitybcefqwu
  sql/sql_select.cc              sp1f-sql_select.cc-19700101030959-egb7whpkh76zzvikycs5nsnuviu4fdlb
-------------- next part --------------
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2012-11-17 15:50:15 +0000
+++ b/mysql-test/r/group_by.result	2012-11-22 18:30:39 +0000
@@ -2122,4 +2122,45 @@
 show status like 'Created_tmp_disk_tables';
 Variable_name	Value
 Created_tmp_disk_tables	1
+#
+#  Bug #1002146: Unneeded filesort if usage of join buffer is not allowed
+#  (bug mdev-645)
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, a int, INDEX idx(a));
+INSERT INTO t1 VALUES (3,2), (2,3), (5,3), (6,4);
+CREATE TABLE t2 (pk int PRIMARY KEY, a int, INDEX idx(a));
+INSERT INTO t2 VALUES (9,0), (10,3), (6,4), (1,6), (3,100), (5,200);
+set join_cache_level=0;
+EXPLAIN
+SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	5	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+a
+3
+4
+100
+200
+set join_cache_level=default;
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='outer_join_with_cache=off';
+EXPLAIN
+SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	idx	idx	5	NULL	5	Using where; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
+SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+GROUP BY t2.a;
+a
+0
+3
+4
+100
+200
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
 # End of 5.3 tests

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-05-24 21:20:40 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-11-22 05:55:04 +0000
@@ -2978,7 +2978,7 @@
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	1	Using index
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index
@@ -2992,7 +2992,7 @@
 SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a)
 GROUP BY a HAVING a != 'z';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t	index	idx_a	idx_a	4	NULL	3	Using index; Using temporary; Using filesort
+1	PRIMARY	t	index	idx_a	idx_a	4	NULL	1	Using index
 1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 2	MATERIALIZED	t1	ref	idx_a	idx_a	4	test.t2.b	2	Using index

=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test	2012-11-17 15:50:15 +0000
+++ b/mysql-test/t/group_by.test	2012-11-22 18:30:39 +0000
@@ -1486,4 +1486,39 @@
 --echo the value below *must* be 1
 show status like 'Created_tmp_disk_tables';
 
+--echo #
+--echo #  Bug #1002146: Unneeded filesort if usage of join buffer is not allowed
+--echo #  (bug mdev-645)
+--echo #
+
+CREATE TABLE t1 (pk int PRIMARY KEY, a int, INDEX idx(a));
+INSERT INTO t1 VALUES (3,2), (2,3), (5,3), (6,4);
+
+CREATE TABLE t2 (pk int PRIMARY KEY, a int, INDEX idx(a));
+INSERT INTO t2 VALUES (9,0), (10,3), (6,4), (1,6), (3,100), (5,200);
+
+set join_cache_level=0;
+
+EXPLAIN
+SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+  GROUP BY t2.a;
+SELECT t2.a FROM t2 STRAIGHT_JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+  GROUP BY t2.a;
+
+set join_cache_level=default;
+
+set @save_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='outer_join_with_cache=off';
+
+EXPLAIN
+SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+  GROUP BY t2.a;
+SELECT t2.a FROM t2 LEFT JOIN t1 ON t2.a <> 0 WHERE t2.a <> 6
+  GROUP BY t2.a;
+
+set optimizer_switch=@save_optimizer_switch;
+
+
+DROP TABLE t1,t2;
+
 --echo # End of 5.3 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-11-19 13:38:27 +0000
+++ b/sql/sql_select.cc	2012-11-22 05:55:04 +0000
@@ -7422,8 +7422,9 @@
     if ( !(keyuse= join->best_positions[tablenr].key))
     {
       j->type=JT_ALL;
-      if (tablenr != join->const_tables)
-	join->full_join=1;
+      if (join->best_positions[tablenr].use_join_buffer &&
+          tablenr != join->const_tables)
+	join->full_join= 1;
     }
 
     /*if (join->best_positions[tablenr].sj_strategy == SJ_OPT_LOOSE_SCAN)
@@ -8436,7 +8437,9 @@
           We will use join cache here : prevent sorting of the first
           table only and sort at the end.
         */
-        if (i != join->const_tables && join->table_count > join->const_tables + 1)
+        if (i != join->const_tables &&
+            join->table_count > join->const_tables + 1 &&
+            join->best_positions[i].use_join_buffer)
           join->full_join= 1;
       }
 



More information about the commits mailing list