summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/order_by.result27
-rw-r--r--mysql-test/r/subselect.result4
-rw-r--r--mysql-test/r/subselect_no_mat.result4
-rw-r--r--mysql-test/r/subselect_no_opts.result4
-rw-r--r--mysql-test/r/subselect_no_scache.result4
-rw-r--r--mysql-test/r/subselect_no_semijoin.result4
-rw-r--r--mysql-test/t/order_by.test24
7 files changed, 61 insertions, 10 deletions
diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
index 7aa286962c9..94e7d5e757a 100644
--- a/mysql-test/r/order_by.result
+++ b/mysql-test/r/order_by.result
@@ -1993,4 +1993,31 @@ Handler_read_rnd 1
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
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index a4bad836d1f..3134096b8c3 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6860,7 +6860,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
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 @@ INSERT INTO t2 VALUES (45),(17),(20);
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;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index cb9847a0d99..43d71a08ec4 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6858,7 +6858,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
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 @@ INSERT INTO t2 VALUES (45),(17),(20);
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;
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 63eeb816b38..879e429d674 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6855,7 +6855,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
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 @@ INSERT INTO t2 VALUES (45),(17),(20);
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;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 6fd21f8d0b0..bf141948765 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6866,7 +6866,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
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 @@ INSERT INTO t2 VALUES (45),(17),(20);
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;
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index b924a18ca8f..7c692e0898d 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6855,7 +6855,7 @@ INSERT INTO t2 VALUES (45),(17),(20);
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 @@ INSERT INTO t2 VALUES (45),(17),(20);
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;
diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test
index 52c801e99f7..3e20d22d726 100644
--- a/mysql-test/t/order_by.test
+++ b/mysql-test/t/order_by.test
@@ -1690,6 +1690,30 @@ show status like '%Handler_read%';
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
View Lorry Controller Status