Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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
1.2010 06/01/27 21:20:28 igor@stripped +3 -0
Fixed bug #16260.
The problem has manifested itself in the cases when we have a nested outer join
for which it can be inferred that one of the inner tables is a single row table.
sql/sql_select.cc
1.389 06/01/27 21:20:09 igor@stripped +2 -1
Fixed bug #16260.
The problem has manifested itself in the cases when we have a nested outer join
for which it can be inferred that one of the inner tables is a single row table.
A table is never considered as a const table if it is used in a nested join
that serves as an inner operand of an outer join.
mysql-test/t/join_nested.test
1.18 06/01/27 21:20:09 igor@stripped +28 -0
Added a test case for bug #16260.
mysql-test/r/join_nested.result
1.22 06/01/27 21:20:09 igor@stripped +23 -0
Added a test case for bug #16260.
# 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: igor
# Host: rurik.mysql.com
# Root: /home/igor/dev/mysql-5.0-0
--- 1.388/sql/sql_select.cc 2006-01-24 04:48:13 -08:00
+++ 1.389/sql/sql_select.cc 2006-01-27 21:20:09 -08:00
@@ -2172,7 +2172,8 @@
if (eq_part.is_prefix(table->key_info[key].key_parts) &&
((table->key_info[key].flags & (HA_NOSAME | HA_END_SPACE_KEY)) ==
HA_NOSAME) &&
- !table->fulltext_searched)
+ !table->fulltext_searched &&
+ !table->pos_in_table_list->embedding)
{
if (const_ref == eq_part)
{ // Found everything for ref.
--- 1.21/mysql-test/r/join_nested.result 2006-01-13 12:55:22 -08:00
+++ 1.22/mysql-test/r/join_nested.result 2006-01-27 21:20:09 -08:00
@@ -1481,3 +1481,26 @@
1 SIMPLE t2 ref a a 5 test.t1.a 1
1 SIMPLE t3 ref a a 5 test.t2.a 1
drop table t1, t2, t3;
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
+id int NOT NULL,
+pid int NOT NULL);
+INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
+SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
+ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
+LEFT JOIN t2 ON (t3.pid=t2.pid)
+WHERE p.id=1;
+id type cid id pid id type pid type
+1 A NULL NULL NULL NULL NULL NULL NULL
+CREATE VIEW v1 AS
+SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
+SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
+LEFT JOIN t2 ON v1.pid=t2.pid
+WHERE p.id=1;
+id type cid id pid pid type
+1 A NULL NULL NULL NULL NULL
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
--- 1.17/mysql-test/t/join_nested.test 2006-01-13 12:55:22 -08:00
+++ 1.18/mysql-test/t/join_nested.test 2006-01-27 21:20:09 -08:00
@@ -914,3 +914,31 @@
on (t1.a = t2.a);
drop table t1, t2, t3;
+#
+# Bug #16260: single row table in the inner nest of an outer join
+#
+
+CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t2 (pid int NOT NULL PRIMARY KEY, type varchar(10));
+CREATE TABLE t3 (cid int NOT NULL PRIMARY KEY,
+ id int NOT NULL,
+ pid int NOT NULL);
+
+INSERT INTO t1 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t2 VALUES (1, 'A'), (3, 'C');
+INSERT INTO t3 VALUES (1, 1, 1), (3, 3, 3);
+
+SELECT * FROM t1 p LEFT JOIN (t3 JOIN t1)
+ ON (t1.id=t3.id AND t1.type='B' AND p.id=t3.id)
+ LEFT JOIN t2 ON (t3.pid=t2.pid)
+ WHERE p.id=1;
+
+CREATE VIEW v1 AS
+ SELECT t3.* FROM t3 JOIN t1 ON t1.id=t3.id AND t1.type='B';
+
+SELECT * FROM t1 p LEFT JOIN v1 ON p.id=v1.id
+ LEFT JOIN t2 ON v1.pid=t2.pid
+ WHERE p.id=1;
+
+DROP VIEW v1;
+DROP TABLE t1,t2,t3;
Thread |
---|
• bk commit into 5.0 tree (igor:1.2010) BUG#16260 | igor | 28 Jan |