[Commits] Rev 3575: Fixed bug mdev-622 (LP bug #1002508). in file:///home/igor/maria/maria-5.5/

Igor Babaev igor at askmonty.org
Tue Nov 20 05:29:28 EET 2012


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

------------------------------------------------------------
revno: 3575
revision-id: igor at askmonty.org-20121120032927-ywvi9sj18yqpgx62
parent: igor at askmonty.org-20121111194744-5pspezgkyp7u4k2k
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.5
timestamp: Mon 2012-11-19 19:29:27 -0800
message:
  Fixed bug mdev-622 (LP bug #1002508).
  Back-ported the fix and the test case for bug 13528826
  from mysql-5.6.
-------------- next part --------------
=== modified file 'mysql-test/r/order_by.result'
--- a/mysql-test/r/order_by.result	2012-11-07 07:18:07 +0000
+++ b/mysql-test/r/order_by.result	2012-11-20 03:29:27 +0000
@@ -1993,4 +1993,31 @@
 Handler_read_rnd_deleted	0
 Handler_read_rnd_next	250
 drop table t0, t1;
+#
+# LP bug #1002508 : the number  of expected rows to be examined is off
+# (bug #13528826)
+#             
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+(5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+(103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+(107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index
+1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using index
+DROP TABLE t1,t2;
 End of 5.5 tests

=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect.result	2012-11-20 03:29:27 +0000
@@ -6860,7 +6860,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6894,6 +6894,6 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2012-11-20 03:29:27 +0000
@@ -6858,7 +6858,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6891,7 +6891,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2012-11-20 03:29:27 +0000
@@ -6855,7 +6855,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6889,7 +6889,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/r/subselect_no_scache.result'
--- a/mysql-test/r/subselect_no_scache.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect_no_scache.result	2012-11-20 03:29:27 +0000
@@ -6866,7 +6866,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6900,7 +6900,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2012-10-18 21:33:06 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2012-11-20 03:29:27 +0000
@@ -6855,7 +6855,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 #
@@ -6889,7 +6889,7 @@
 EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 1 ) ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
-2	SUBQUERY	t1	index	a	a	5	NULL	1	Using where; Using index
+2	SUBQUERY	t1	index	a	a	5	NULL	2	Using where; Using index
 2	SUBQUERY	t2	ref	b	b	5	test.t1.a	2	Using index
 DROP TABLE t1,t2;
 set @optimizer_switch_for_subselect_test=null;

=== modified file 'mysql-test/t/order_by.test'
--- a/mysql-test/t/order_by.test	2012-11-07 07:18:07 +0000
+++ b/mysql-test/t/order_by.test	2012-11-20 03:29:27 +0000
@@ -1690,6 +1690,30 @@
 
 drop table t0, t1;
 
+--echo #
+--echo # LP bug #1002508 : the number  of expected rows to be examined is off
+--echo # (bug #13528826)
+--echo #             
+
+CREATE TABLE t1(a int PRIMARY KEY, b int) ENGINE=myisam;
+INSERT INTO t1 VALUES
+  (5, 10), (2, 70), (7, 80), (6, 20), (1, 50), (9, 40), (8, 30), (3, 60);
+CREATE TABLE t2 (p int, a int, INDEX i_a(a)) ENGINE=myisam;
+INSERT INTO t2 VALUES
+  (103, 7), (109, 3), (102, 3), (108, 1), (106, 3),
+  (107, 7), (105, 1), (101, 3), (100, 7), (110, 1);
+
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a;
+
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 8;
+
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.a=t2.a ORDER BY t1.a LIMIT 100;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.5 tests
 
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-11-11 19:47:44 +0000
+++ b/sql/sql_select.cc	2012-11-20 03:29:27 +0000
@@ -22831,6 +22831,7 @@
   ha_rows table_records= table->file->stats.records;
   bool group= join && join->group && order == join->group_list;
   ha_rows ref_key_quick_rows= HA_POS_ERROR;
+  const bool has_limit= (select_limit_arg != HA_POS_ERROR);
 
   /*
     If not used with LIMIT, only use keys if the whole query can be
@@ -22962,7 +22963,7 @@
             be included into the result set.
           */  
           if (select_limit > table_records/rec_per_key)
-              select_limit= table_records;
+            select_limit= table_records;
           else
             select_limit= (ha_rows) (select_limit*rec_per_key);
         } /* group */
@@ -23044,7 +23045,7 @@
   
   *new_key= best_key;
   *new_key_direction= best_key_direction;
-  *new_select_limit= best_select_limit;
+  *new_select_limit= has_limit ? best_select_limit : table_records;
   if (new_used_key_parts != NULL)
     *new_used_key_parts= best_key_parts;
 



More information about the commits mailing list