Fixed LP bug #917990.

If the expression for a derived table of a query contained a LIMIT
clause the estimate of the number of rows in this derived table
returned by the EXPLAIN command could be badly off since the
optimizer ignored the limit number from the LIMIT clause when
getting the estimate. 
The call of the method SELECT_LEX_UNIT->set_limit added in the code
of mysql_derived_optimize() will be needed also in maria-5.5 where
parameters in the LIMIT clause are supported. 

5 files changed, 36 insertions, 5 deletions

diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index cee19237230..416c68ffb46 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1879,5 +1879,22 @@ ORDER BY CONCAT(alias2.col_varchar_nokey);
 col_varchar_key	pk	col_varchar_key	col_varchar_nokey
 set max_heap_table_size= @tmp_882994;
 drop table t1,t2,t3;
+#
+# LP bug #917990: Bad estimate of #rows for derived table with LIMIT
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES
+(8), (3), (4), (7), (9), (5), (1), (2);
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+a
+8
+3
+4
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	8	
+DROP TABLE t1;
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 644f7f7289e..8a71d00291c 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -304,7 +304,7 @@ a+1
 4
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 drop view v1;
 drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_view_innodb.result b/mysql-test/suite/vcol/r/vcol_view_innodb.result
index 94c311fb8b9..d117b030f94 100644
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result
@@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
 MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 drop view v1;
 create view v1 as select c+1 from t1 order by 1 desc limit 2;
@@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
 MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 drop view v1;
 drop table t1;
diff --git a/mysql-test/suite/vcol/r/vcol_view_myisam.result b/mysql-test/suite/vcol/r/vcol_view_myisam.result
index b96e003e1cc..fe48be4c794 100644
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result
@@ -107,7 +107,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
 MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 drop view v1;
 create view v1 as select c+1 from t1 order by 1 desc limit 2;
@@ -119,7 +119,7 @@ MariaDB-5.3: the following EXPLAIN produces incorrect #rows for table t1.
 MariaDB-5.3: this is expected to go away when FROM subquery optimizations are pushed
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
 drop view v1;
 drop table t1;
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index b674350af0a..f8ba1f347f3 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -1277,6 +1277,20 @@ ORDER BY CONCAT(alias2.col_varchar_nokey);
 set max_heap_table_size= @tmp_882994;
 drop table t1,t2,t3;
 
+--echo #
+--echo # LP bug #917990: Bad estimate of #rows for derived table with LIMIT
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES
+  (8), (3), (4), (7), (9), (5), (1), (2);
+
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+EXPLAIN
+SELECT * FROM (SELECT * FROM t1 LIMIT 3) t;
+
+DROP TABLE t1;
+
 # The following command must be the last one the file 
 set optimizer_switch=@exit_optimizer_switch;
 set join_cache_level=@exit_join_cache_level;