bk commit into 5.0 tree (igor:1.2254) BUG#5500

[igor]

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@stripped, 2006-09-06 08:21:43-07:00, igor@stripped +8 -0
  Fixed bug #5500: EXPLAIN returned a wrong select_type for queries using views.
  Select_type in the EXPLAIN output for the query SELECT * FROM t1 was
  'SIMPLE', while for the query SELECT * FROM v1, where the view v1
  was defined as SELECT * FROM t1, the EXPLAIN output contained 'PRIMARY'
  for the select_type column.
   

  mysql-test/r/group_by.result@stripped, 2006-09-06 08:21:35-07:00, igor@stripped +2 -2
    Adjusted results after the fix for bug #5500.

  mysql-test/r/information_schema.result@stripped, 2006-09-06 08:21:36-07:00,
igor@stripped +1 -1
    Adjusted results after the fix for bug #5500.

  mysql-test/r/olap.result@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +1 -1
    Adjusted results after the fix for bug #5500.

  mysql-test/r/range.result@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +4 -4
    Adjusted results after the fix for bug #5500.

  mysql-test/r/view.result@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +35 -16
    Added a test case for bug #5500.
    Adjusted other results.

  mysql-test/r/view_grant.result@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +3
-3
    Adjusted results after the fix for bug #5500.

  mysql-test/t/view.test@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +18 -0
    Added a test case for bug #5500.

  sql/sql_select.cc@stripped, 2006-09-06 08:21:36-07:00, igor@stripped +7 -4
    Fixed bug #5500: EXPLAIN returned a wrong select_type for queries using views.
    Select_type in the EXPLAIN output for the query SELECT * FROM t1 was
    'SIMPLE', while for the query SELECT * FROM v1, where the view v1
    was defined as SELECT * FROM t1, the EXPLAIN output contained 'PRIMARY'
    for the select_type column.
     

# 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-opt/mysql-5.0-opt-bug5500

--- 1.447/sql/sql_select.cc	2006-09-06 08:21:52 -07:00
+++ 1.448/sql/sql_select.cc	2006-09-06 08:21:52 -07:00
@@ -14221,9 +14221,12 @@
 	item_list.push_back(new Item_string(table_name_buffer, len, cs));
       }
       else
-	item_list.push_back(new Item_string(table->alias,
-					    strlen(table->alias),
+      {
+        TABLE_LIST *tab=table->pos_in_table_list; 
+	item_list.push_back(new Item_string(tab->alias,
+					    strlen(tab->alias),
 					    cs));
+      }
       /* type */
       item_list.push_back(new Item_string(join_type_str[tab->type],
 					  strlen(join_type_str[tab->type]),
@@ -14410,8 +14413,8 @@
     // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
     uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
     sl->type= (((&thd->lex->select_lex)==sl)?
-	       ((thd->lex->all_selects_list != sl) ? 
-		primary_key_name : "SIMPLE"):
+	       (sl->first_inner_unit() || sl->next_select() ? 
+		"PRIMARY" : "SIMPLE"):
 	       ((sl == first)?
 		((sl->linkage == DERIVED_TABLE_TYPE) ?
 		 "DERIVED":

--- 1.33/mysql-test/r/olap.result	2006-09-06 08:21:52 -07:00
+++ 1.34/mysql-test/r/olap.result	2006-09-06 08:21:52 -07:00
@@ -611,7 +611,7 @@
 NULL
 EXPLAIN SELECT type FROM v1 GROUP BY type WITH ROLLUP;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	Using filesort
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE t1 (a int(11) NOT NULL);

--- 1.171/mysql-test/r/view.result	2006-09-06 08:21:52 -07:00
+++ 1.172/mysql-test/r/view.result	2006-09-06 08:21:52 -07:00
@@ -49,7 +49,7 @@
 ERROR 42S22: Unknown column 'v1.b' in 'field list'
 explain extended select c from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
 Warnings:
 Note	1003	select (`test`.`t1`.`b` + 1) AS `c` from `test`.`t1`
 create algorithm=temptable view v2 (c) as select b+1 from t1;
@@ -83,7 +83,7 @@
 12
 explain extended select c from v3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
 Warnings:
 Note	1003	select ((`test`.`t1`.`b` + 1) + 1) AS `c` from `test`.`t1`
 create algorithm=temptable view v4 (c) as select c+1 from v2;
@@ -376,7 +376,7 @@
 30
 explain extended select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	Using where
 Warnings:
 Note	1003	select `test`.`t1`.`b` AS `c` from `test`.`t1` where (`test`.`t1`.`a` < 3)
 update v1 set c=c+1;
@@ -1391,9 +1391,9 @@
 4	NULL	NULL
 explain extended select * from t3 left join v3 on (t3.a = v3.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 Warnings:
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b`
from `test`.`t3` left join (`test`.`t1` left join `test`.`t2` on((`test`.`t1`.`a` =
`test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
 create view v1 (a) as select a from t1;
@@ -1406,9 +1406,9 @@
 4	NULL	NULL
 explain extended select * from t3 left join v4 on (t3.a = v4.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	
 Warnings:
 Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `b`
from `test`.`t3` left join (`test`.`t1` left join (`test`.`t2`) on((`test`.`t1`.`a` =
`test`.`t2`.`a`))) on((`test`.`t3`.`a` = `test`.`t1`.`a`)) where 1
 prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
@@ -2321,12 +2321,12 @@
 1	SIMPLE	t2	ref	a	a	10	const,test.t1.b	2	Using where; Using index
 EXPLAIN SELECT * FROM v1 WHERE a=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-1	PRIMARY	t2	ref	a	a	10	const,test.t1.b	2	Using where; Using index
+1	SIMPLE	t1	ref	a	a	5	const	1	Using where; Using index
+1	SIMPLE	t2	ref	a	a	10	const,test.t1.b	2	Using where; Using index
 EXPLAIN SELECT * FROM v2 WHERE a=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ref	a	a	5	const	1	Using where; Using index
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	SIMPLE	t1	ref	a	a	5	const	1	Using where; Using index
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	Using where
 DROP VIEW v1,v2;
 DROP TABLE t1,t2,t3;
 create table t1 (f1 int);
@@ -2409,7 +2409,7 @@
 create view v1 as select * from t1;
 explain select id from v1 order by id;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	PRIMARY	4	NULL	2	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	2	Using index
 drop view v1;
 drop table t1;
 create table t1(f1 int, f2 int);
@@ -2480,7 +2480,7 @@
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 EXPLAIN SELECT MAX(a) FROM v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 SELECT MIN(a) FROM t1;
 MIN(a)
 0
@@ -2492,7 +2492,7 @@
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 EXPLAIN SELECT MIN(a) FROM v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
 DROP VIEW v1;
 DROP TABLE t1;
 CREATE TABLE t1 (x varchar(10));
@@ -2879,3 +2879,22 @@
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1`
AS select `t1`.`f1` AS `f1` from `t1` where (`t1`.`f1` between now() and (now() +
interval 1 minute))
 drop view v1;
 drop table t1;
+CREATE TABLE t1 (s1 int);
+CREATE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT * FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+EXPLAIN SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+INSERT INTO t1 VALUES (1), (3), (2);
+EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
+EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	
+DROP VIEW v1;
+DROP TABLE t1;

--- 1.158/mysql-test/t/view.test	2006-09-06 08:21:52 -07:00
+++ 1.159/mysql-test/t/view.test	2006-09-06 08:21:52 -07:00
@@ -2760,3 +2760,21 @@
 show create view v1;
 drop view v1;
 drop table t1;
+
+#
+# Bug #5500: wrong select_type in EXPLAIN output for queries over views
+#
+
+CREATE TABLE t1 (s1 int); 
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN SELECT * FROM t1;
+EXPLAIN SELECT * FROM v1;
+
+INSERT INTO t1 VALUES (1), (3), (2);
+
+EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 
+EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); 
+
+DROP VIEW v1;
+DROP TABLE t1;

--- 1.113/mysql-test/r/information_schema.result	2006-09-06 08:21:52 -07:00
+++ 1.114/mysql-test/r/information_schema.result	2006-09-06 08:21:52 -07:00
@@ -337,7 +337,7 @@
 test
 explain select * from v0;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	#	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	#	ALL	NULL	NULL	NULL	NULL	2	
 create view v1 (c) as select table_name from information_schema.tables
 where table_name="v1";
 select * from v1;

--- 1.18/mysql-test/r/view_grant.result	2006-09-06 08:21:52 -07:00
+++ 1.19/mysql-test/r/view_grant.result	2006-09-06 08:21:52 -07:00
@@ -103,7 +103,7 @@
 grant select on mysqltest.t1 to mysqltest_1@localhost;
 explain select c from mysqltest.v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
 show create view mysqltest.v1;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v1'
 explain select c from mysqltest.v2;
@@ -123,7 +123,7 @@
 grant show view on mysqltest.* to mysqltest_1@localhost;
 explain select c from mysqltest.v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
 show create view mysqltest.v1;
 View	Create View
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`mysqltest`.`v1` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1)
AS `d` from `mysqltest`.`t1`
@@ -136,7 +136,7 @@
 v2	CREATE ALGORITHM=TEMPTABLE DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`mysqltest`.`v2` AS select (`mysqltest`.`t1`.`a` + 1) AS `c`,(`mysqltest`.`t1`.`b` + 1)
AS `d` from `mysqltest`.`t1`
 explain select c from mysqltest.v3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
 show create view mysqltest.v3;
 View	Create View
 v3	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`mysqltest`.`v3` AS select (`mysqltest`.`t2`.`a` + 1) AS `c`,(`mysqltest`.`t2`.`b` + 1)
AS `d` from `mysqltest`.`t2`

--- 1.70/mysql-test/r/group_by.result	2006-09-06 08:21:52 -07:00
+++ 1.71/mysql-test/r/group_by.result	2006-09-06 08:21:52 -07:00
@@ -807,8 +807,8 @@
 SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
 where t2.b=v1.a GROUP BY t2.b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	b	b	2	NULL	10	Using index
-1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	1	test.t2.b	1	
+1	SIMPLE	t2	index	b	b	2	NULL	10	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	1	test.t2.b	1	
 SELECT straight_join sql_no_cache v1.a, v1.b, v1.real_b from t2, v1
 where t2.b=v1.a GROUP BY t2.b;
 a	b	real_b

--- 1.49/mysql-test/r/range.result	2006-09-06 08:21:52 -07:00
+++ 1.50/mysql-test/r/range.result	2006-09-06 08:21:52 -07:00
@@ -750,13 +750,13 @@
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
 EXPLAIN SELECT a,b FROM v1 WHERE a < 2 and b=3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
 EXPLAIN SELECT a,b FROM t1 WHERE a < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
 EXPLAIN SELECT a,b FROM v1 WHERE a < 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using index
 SELECT a,b FROM t1 WHERE a < 2 and b=3;
 a	b
 1	3
@@ -799,13 +799,13 @@
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
 explain select * from v1 where a in (3,4)  and b in (1,2,3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
 explain select * from t1 where a between 3 and 4 and b between 1 and 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
 explain select * from v1 where a between 3 and 4 and b between 1 and 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	8	NULL	#	Using where; Using index
 drop view v1;
 drop table t1;
 create table t3 (a int);