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#28700 | mhansson | 27 Jun |
