MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mhansson Date:June 27 2007 12:11pm
Subject:bk commit into 5.0 tree (mhansson:1.2498) BUG#28700
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-06-27 15:11:37+03:00, mhansson@stripped +4 -0
  Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN
  
  When an ALGORITHM = MERGE VIEW is defined using a STRAIGHT_JOIN, the optimizer 
  ignores that hint, since it would affect any query using the view.
  Fixed by rewriting all queries such as SELECT STRAIGHT_JOIN ... t1, ..., tn to 
  SELECT STRAIGHT_JOIN ... t1 STRAIGHT JOIN ... STRAIGHT_JOIN tn. The
  initial STRAIGHT_JOIN hint is now redundant but is left as is, since this lets the 
  optimizer work as previously in all other cases.

  BitKeeper/etc/ignore@stripped, 2007-06-27 15:11:35+03:00, mhansson@stripped +6 -0
    Added cscope.in.out cscope.out cscope.po.out debian/control debian/defs.mk include/abi_check to the ignore list

  mysql-test/r/view.result@stripped, 2007-06-27 15:11:34+03:00, mhansson@stripped +106 -0
    Bug #28700: Test cases

  mysql-test/t/view.test@stripped, 2007-06-27 15:11:35+03:00, mhansson@stripped +95 -0
    Bug #28700: Correct results

  sql/sql_select.cc@stripped, 2007-06-27 15:11:35+03:00, mhansson@stripped +4 -0
    Bug #28700: The fix

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/bug28700/my50-bug28700

--- 1.521/sql/sql_select.cc	2007-05-15 23:16:08 +03:00
+++ 1.522/sql/sql_select.cc	2007-06-27 15:11:35 +03:00
@@ -431,6 +431,10 @@ JOIN::prepare(Item ***rref_pointer_array
   if (optimized)
     DBUG_RETURN(0);
 
+  if (select_lex_arg->options & SELECT_STRAIGHT_JOIN)    
+    for (TABLE_LIST *t= tables_init; t; t= t->next_local)
+      t->straight= true;
+
   conds= conds_init;
   order= order_init;
   group_list= group_init;

--- 1.200/mysql-test/r/view.result	2007-05-09 22:17:20 +03:00
+++ 1.201/mysql-test/r/view.result	2007-06-27 15:11:34 +03:00
@@ -3367,4 +3367,110 @@ SHOW CREATE VIEW v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(1.23456789 as decimal(8,0)) AS `col`
 DROP VIEW v1;
+CREATE TABLE t1 (
+a INT AUTO_INCREMENT, 
+b INT, 
+PRIMARY KEY(a),
+KEY (a, b)
+);
+CREATE TABLE t2 (
+a INT  AUTO_INCREMENT, 
+PRIMARY KEY(a)
+);
+CREATE TABLE t3 (
+a INT AUTO_INCREMENT, 
+b INT, 
+PRIMARY KEY(a),
+KEY (a, b)
+);
+CREATE TABLE t4 (
+a INT  AUTO_INCREMENT, 
+PRIMARY KEY(a)
+);
+INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t2 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t3 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
+(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t4 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	15	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY,a	PRIMARY	4	test.t2.a	1	
+EXPLAIN SELECT * FROM t3 JOIN t4 USING( a );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t4	index	PRIMARY	PRIMARY	4	NULL	15	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY,a	PRIMARY	4	test.t4.a	1	
+CREATE ALGORITHM = MERGE VIEW v1_using
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a);
+CREATE ALGORITHM = MERGE VIEW v1_where
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+WHERE t1.a = t2.a;
+CREATE ALGORITHM = MERGE VIEW v1_on
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2 
+ON (t1.a = t2.a);
+CREATE ALGORITHM = MERGE VIEW v1_subquery
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a)
+WHERE
+t1.a IN (
+SELECT a
+FROM t3 JOIN t4
+USING (a)
+);
+CREATE ALGORITHM = MERGE VIEW v2_subquery
+AS
+SELECT t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a)
+WHERE
+t1.a IN (
+SELECT STRAIGHT_JOIN a
+FROM t3 JOIN t4
+USING (a)
+);
+EXPLAIN SELECT * FROM v1_using;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,a	a	9	NULL	15	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+EXPLAIN SELECT * FROM t3 JOIN v1_using USING(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,a	a	9	NULL	15	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY,a	PRIMARY	4	test.t1.a	1	
+EXPLAIN SELECT * FROM v1_using JOIN t3 USING(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,a	a	9	NULL	15	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+1	SIMPLE	t3	eq_ref	PRIMARY,a	PRIMARY	4	test.t2.a	1	Using where
+EXPLAIN SELECT * FROM v1_on;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,a	a	9	NULL	15	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+EXPLAIN SELECT * FROM v1_where;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY,a	a	9	NULL	15	Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+EXPLAIN SELECT * FROM v1_subquery;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	PRIMARY,a	a	9	NULL	15	Using where; Using index
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
+3	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Using index
+3	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,a	PRIMARY	4	func	1	Using where; Using index
+EXPLAIN SELECT * FROM v2_subquery;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	PRIMARY	PRIMARY	4	NULL	15	Using where; Using index
+1	PRIMARY	t1	eq_ref	PRIMARY,a	PRIMARY	4	test.t2.a	1	
+3	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY,a	PRIMARY	4	func	1	Using where; Using index
+3	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Using index
+DROP TABLE t1, t2, t3, t4;
+DROP VIEW v1_using, v1_on, v1_where, v1_subquery, v2_subquery;
 End of 5.0 tests.

--- 1.183/mysql-test/t/view.test	2007-05-09 22:17:20 +03:00
+++ 1.184/mysql-test/t/view.test	2007-06-27 15:11:35 +03:00
@@ -3233,4 +3233,99 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789
 SHOW CREATE VIEW v1;
 DROP VIEW v1;
 
+#
+# Bug #28700: VIEWs using the MERGE algorithm ignore STRAIGHT_JOIN
+#
+CREATE TABLE t1 (
+  a INT AUTO_INCREMENT, 
+  b INT, 
+  PRIMARY KEY(a),
+  KEY (a, b)
+);
+
+CREATE TABLE t2 (
+  a INT  AUTO_INCREMENT, 
+  PRIMARY KEY(a)
+);
+
+CREATE TABLE t3 (
+  a INT AUTO_INCREMENT, 
+  b INT, 
+  PRIMARY KEY(a),
+  KEY (a, b)
+);
+
+CREATE TABLE t4 (
+  a INT  AUTO_INCREMENT, 
+  PRIMARY KEY(a)
+);
+
+INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t2 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t3 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
+                      (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+INSERT INTO t4 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
+
+
+-- Ensure that these orders are not preferred by optimizer
+EXPLAIN SELECT * FROM t1 JOIN t2 USING( a );
+EXPLAIN SELECT * FROM t3 JOIN t4 USING( a );
+
+CREATE ALGORITHM = MERGE VIEW v1_using
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a);
+
+CREATE ALGORITHM = MERGE VIEW v1_where
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+WHERE t1.a = t2.a;
+
+CREATE ALGORITHM = MERGE VIEW v1_on
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2 
+ON (t1.a = t2.a);
+
+-- Ensure that the subselect doesn't get STRAIGHT_JOIN'ed
+CREATE ALGORITHM = MERGE VIEW v1_subquery
+AS
+SELECT STRAIGHT_JOIN t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a)
+WHERE
+t1.a IN (
+  SELECT a
+  FROM t3 JOIN t4
+  USING (a)
+);
+
+-- Ensure that the main subselect doesn't STRAIGHT_JOIN'ed
+CREATE ALGORITHM = MERGE VIEW v2_subquery
+AS
+SELECT t1.a, t1.b 
+FROM t1 JOIN t2
+USING (a)
+WHERE
+t1.a IN (
+  SELECT STRAIGHT_JOIN a
+  FROM t3 JOIN t4
+  USING (a)
+);
+
+
+EXPLAIN SELECT * FROM v1_using;
+-- The following two queries should have the same plan
+EXPLAIN SELECT * FROM t3 JOIN v1_using USING(a);
+EXPLAIN SELECT * FROM v1_using JOIN t3 USING(a);
+EXPLAIN SELECT * FROM v1_on;
+EXPLAIN SELECT * FROM v1_where;
+EXPLAIN SELECT * FROM v1_subquery;
+EXPLAIN SELECT * FROM v2_subquery;
+
+DROP TABLE t1, t2, t3, t4;
+DROP VIEW v1_using, v1_on, v1_where, v1_subquery, v2_subquery;
+
 --echo End of 5.0 tests.

--- 1.247/BitKeeper/etc/ignore	2007-04-23 22:41:22 +03:00
+++ 1.248/BitKeeper/etc/ignore	2007-06-27 15:11:35 +03:00
@@ -1342,3 +1342,9 @@ win/vs71cache.txt
 win/vs8cache.txt
 zlib/*.ds?
 zlib/*.vcproj
+cscope.in.out
+cscope.out
+cscope.po.out
+debian/control
+debian/defs.mk
+include/abi_check
Thread
bk commit into 5.0 tree (mhansson:1.2498) BUG#28700mhansson27 Jun