[Commits] Rev 3089: Back-ported the fix and the test case for bug #50257 from mariadb-5.3 code line. in file:///home/igor/maria/maria-5.2/

Igor Babaev igor at askmonty.org
Sat Jan 14 05:00:51 EET 2012


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

------------------------------------------------------------
revno: 3089
revision-id: igor at askmonty.org-20120114030050-jabx25n2fopqjnen
parent: igor at askmonty.org-20120113202319-8u2exriofkfz6dz4
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.2
timestamp: Fri 2012-01-13 19:00:50 -0800
message:
  Back-ported the fix and the test case for bug #50257 from mariadb-5.3 code line.
  Adjusted results for a few test cases.
-------------- next part --------------
=== modified file 'mysql-test/r/index_merge_myisam.result'
--- a/mysql-test/r/index_merge_myisam.result	2010-11-24 22:57:34 +0000
+++ b/mysql-test/r/index_merge_myisam.result	2012-01-14 03:00:50 +0000
@@ -1486,7 +1486,7 @@
 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	system	PRIMARY	NULL	NULL	NULL	1	
-2	DEPENDENT SUBQUERY	t2	ref	f2,f3	f2	5		1	Using where
+2	DEPENDENT SUBQUERY	t2	ref	f2,f3	f2	5	const	1	Using where
 DROP TABLE t1,t2;
 #
 # Generic @@optimizer_switch tests (move those into a separate file if

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-01-13 20:23:19 +0000
+++ b/mysql-test/r/subselect.result	2012-01-14 03:00:50 +0000
@@ -363,9 +363,9 @@
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
-4	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
+4	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
 2	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	
-3	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
+3	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
 Warnings:
 Note	1003	select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM
@@ -4780,7 +4780,6 @@
 WHERE t1.a = d1.a;
 ERROR 42S22: Unknown column 'd1.a' in 'where clause'
 DROP TABLE t1;
-End of 5.1 tests.
 Set up test tables.
 CREATE TABLE t1 (
 t1_id INT UNSIGNED,
@@ -5141,4 +5140,19 @@
 SET SESSION sql_mode=@old_sql_mode;
 drop table t2, t1;
 drop view v1;
+#
+# BUG#50257: Missing info in REF column of the EXPLAIN 
+#            lines for subselects
+#
+CREATE TABLE t1 (a INT, b INT, INDEX (a));
+INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t1	ref	a	a	5	const	1	Using where
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	
+2	SUBQUERY	t1	ref	a	a	5	const	1	Using where; Using index
+DROP TABLE t1;
 End of 5.2 tests

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2011-12-19 22:55:30 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2012-01-14 03:00:50 +0000
@@ -1739,7 +1739,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	5		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	5	const	5	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
 ENGINE=InnoDB;
@@ -1753,7 +1753,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	9		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	9	const	5	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 
 KEY (c3), KEY (c2, c3))
@@ -1768,7 +1768,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	7		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	7	const	5	Using where; Using filesort
 DROP TABLE t1;
 End of 5.1 tests
 drop table if exists t1, t2, t3;

=== modified file 'mysql-test/suite/innodb_plugin/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result	2011-12-19 22:55:30 +0000
+++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result	2012-01-14 03:00:50 +0000
@@ -1739,7 +1739,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	5		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	5	const	5	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
 ENGINE=InnoDB;
@@ -1753,7 +1753,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	9		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	9	const	5	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 
 KEY (c3), KEY (c2, c3))
@@ -1768,7 +1768,7 @@
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	7		5	Using where; Using filesort
+2	DERIVED	t1	ALL	c3,c2	c3	7	const	5	Using where; Using filesort
 DROP TABLE t1;
 End of 5.1 tests
 drop table if exists t1, t2, t3;

=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result	2011-12-27 21:19:13 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result	2012-01-14 03:00:50 +0000
@@ -363,9 +363,9 @@
 EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
-4	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
+4	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
 2	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	
-3	SUBQUERY	t8	const	PRIMARY	PRIMARY	37		1	100.00	Using index
+3	SUBQUERY	t8	const	PRIMARY	PRIMARY	37	const	1	100.00	Using index
 Warnings:
 Note	1003	select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1
 SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2012-01-13 20:23:19 +0000
+++ b/mysql-test/t/subselect.test	2012-01-14 03:00:50 +0000
@@ -3710,8 +3710,6 @@
 DROP TABLE t1;
 
 
---echo End of 5.1 tests.
-
 #
 # Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index
 #
@@ -4011,4 +4009,17 @@
 drop table t2, t1;
 drop view v1;
 
+--echo #
+--echo # BUG#50257: Missing info in REF column of the EXPLAIN 
+--echo #            lines for subselects
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, INDEX (a));
+INSERT INTO t1 VALUES (3, 10), (2, 20), (7, 10), (5, 20);
+
+EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE a=7) t;
+EXPLAIN SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE a=7);
+
+DROP TABLE t1;
+
 --echo End of 5.2 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-05 09:06:52 +0000
+++ b/sql/sql_select.cc	2012-01-14 03:00:50 +0000
@@ -5913,8 +5913,7 @@
       if (keyuse->null_rejecting) 
         j->ref.null_rejecting |= 1 << i;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
-      if (!keyuse->used_tables &&
-	  !(join->select_options & SELECT_DESCRIBE))
+      if (!keyuse->used_tables &&!thd->lex->describe)
       {					// Compare against constant
 	store_key_item tmp(thd, keyinfo->key_part[i].field,
                            key_buff + maybe_null,



More information about the commits mailing list