[Commits] Rev 3382: Fixed LP bug #917990. in file:///home/igor/maria/maria-5.3-bug917990/
Igor Babaev
igor at askmonty.org
Wed Jan 18 09:04:10 EET 2012
At file:///home/igor/maria/maria-5.3-bug917990/
------------------------------------------------------------
revno: 3382
revision-id: igor at askmonty.org-20120118070405-kkhod3rktio2ge0c
parent: sanja at montyprogram.com-20120111083516-fei2c3fzara3n152
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.3-bug917990
timestamp: Tue 2012-01-17 23:04:05 -0800
message:
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.
-------------- next part --------------
=== modified file 'mysql-test/r/derived_view.result'
--- a/mysql-test/r/derived_view.result 2011-12-24 16:55:10 +0000
+++ b/mysql-test/r/derived_view.result 2012-01-18 07:04:05 +0000
@@ -1879,5 +1879,22 @@
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;
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2011-12-15 08:21:15 +0000
+++ b/mysql-test/r/view.result 2012-01-18 07:04:05 +0000
@@ -304,7 +304,7 @@
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;
=== modified file 'mysql-test/suite/vcol/r/vcol_view_innodb.result'
--- a/mysql-test/suite/vcol/r/vcol_view_innodb.result 2011-12-15 08:21:15 +0000
+++ b/mysql-test/suite/vcol/r/vcol_view_innodb.result 2012-01-18 07:04:05 +0000
@@ -107,7 +107,7 @@
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: 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;
=== modified file 'mysql-test/suite/vcol/r/vcol_view_myisam.result'
--- a/mysql-test/suite/vcol/r/vcol_view_myisam.result 2011-12-15 08:21:15 +0000
+++ b/mysql-test/suite/vcol/r/vcol_view_myisam.result 2012-01-18 07:04:05 +0000
@@ -107,7 +107,7 @@
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: 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;
=== modified file 'mysql-test/t/derived_view.test'
--- a/mysql-test/t/derived_view.test 2011-12-24 16:55:10 +0000
+++ b/mysql-test/t/derived_view.test 2012-01-18 07:04:05 +0000
@@ -1277,6 +1277,20 @@
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;
=== modified file 'sql/sql_derived.cc'
--- a/sql/sql_derived.cc 2011-12-14 18:36:51 +0000
+++ b/sql/sql_derived.cc 2012-01-18 07:04:05 +0000
@@ -753,6 +753,7 @@
if (!derived->is_merged_derived())
{
JOIN *join= first_select->join;
+ unit->set_limit(first_select);
unit->optimized= TRUE;
if ((res= join->optimize()))
goto err;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2012-01-11 08:35:16 +0000
+++ b/sql/sql_select.cc 2012-01-18 07:04:05 +0000
@@ -3581,6 +3581,7 @@
for (i= 0; i < join->table_count ; i++)
records*= join->best_positions[i].records_read ?
(ha_rows)join->best_positions[i].records_read : 1;
+ set_if_smaller(records, unit->select_limit_cnt);
join->select_lex->increase_derived_records(records);
}
}
More information about the commits
mailing list