Fixed LP bug #793436.

When looking for the execution plan of a derived table to be materialized
JOIN::optimize finds  out that all joined tables of the derived table
contain not more than one row then the derived table should be maretialized
at the optimization stage.
Added a test case for the bug.
Adjusted results in other test cases.

8 files changed, 47 insertions, 13 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 7cc2af3616f..fe803ed37a5 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -407,3 +407,21 @@ MIN(i)
 1
 DROP TABLE t1;
 # End of 5.0 tests
+#
+#  LP bug #793436: query with a derived table for which optimizer proves
+#                  that it contains not more than 1 row
+#
+CREATE TABLE t1 (a int, KEY (a)) ;
+INSERT INTO t1 VALUES (3), (1);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (3);
+EXPLAIN
+SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t1	ref	a	a	5	const	1	Using index
+2	DERIVED	t2	system	NULL	NULL	NULL	NULL	1	
+SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;
+a	a
+3	3
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index d8cface69a6..c84d4538a88 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -47,13 +47,13 @@ SELECT (SELECT a) as a;
 ERROR 42S22: Reference 'a' not supported (forward reference in item list)
 EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1)
+Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><1>((select 1)) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index dccf4e1900c..f9a4a4373c2 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -51,13 +51,13 @@ SELECT (SELECT a) as a;
 ERROR 42S22: Reference 'a' not supported (forward reference in item list)
 EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1)
+Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><1>((select 1)) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 5b7e76561a2..493ab9dba59 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -48,13 +48,13 @@ SELECT (SELECT a) as a;
 ERROR 42S22: Reference 'a' not supported (forward reference in item list)
 EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1)
+Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><1>((select 1)) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 54213a20279..434bcd33a21 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -48,13 +48,13 @@ SELECT (SELECT a) as a;
 ERROR 42S22: Reference 'a' not supported (forward reference in item list)
 EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1)
+Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><1>((select 1)) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result
index 261743e9d3c..0348a8428a5 100644
--- a/mysql-test/r/view_grant.result
+++ b/mysql-test/r/view_grant.result
@@ -110,7 +110,7 @@ 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;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 show create view mysqltest.v2;
 ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v2'
@@ -131,7 +131,7 @@ View	Create View	character_set_client	collation_connection
 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`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 show create view mysqltest.v2;
 View	Create View	character_set_client	collation_connection
@@ -144,7 +144,7 @@ View	Create View	character_set_client	collation_connection
 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`	latin1	latin1_swedish_ci
 explain select c from mysqltest.v4;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
 show create view mysqltest.v4;
 View	Create View	character_set_client	collation_connection
diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result
index e91e720c6dd..e183cf389c0 100644
--- a/mysql-test/suite/pbxt/r/subselect.result
+++ b/mysql-test/suite/pbxt/r/subselect.result
@@ -44,13 +44,13 @@ SELECT (SELECT a) as a;
 ERROR 42S22: Reference 'a' not supported (forward reference in item list)
 EXPLAIN EXTENDED SELECT 1 FROM (SELECT 1 as a) as b  HAVING (SELECT a)=1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	100.00	
 3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
 Note	1276	Field or reference 'b.a' of SELECT #3 was resolved in SELECT #1
-Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><`b`.`a`>((select `b`.`a`)) = 1)
+Note	1003	select 1 AS `1` from (select 1 AS `a`) `b` having (<expr_cache><1>((select 1)) = 1)
 SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 962cec95add..75368925499 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -315,3 +315,19 @@ WHERE j = SUBSTRING('12', (SELECT * FROM (SELECT MIN(j) FROM t1) t2))) t3;
 DROP TABLE t1;
 
 --echo # End of 5.0 tests
+
+--echo #
+--echo #  LP bug #793436: query with a derived table for which optimizer proves
+--echo #                  that it contains not more than 1 row
+--echo #
+
+CREATE TABLE t1 (a int, KEY (a)) ;
+INSERT INTO t1 VALUES (3), (1);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (3);
+
+EXPLAIN
+SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;
+SELECT * FROM (SELECT DISTINCT * FROM t2) t, t1  WHERE t1.a = t.a;
+
+DROP TABLE t1,t2;