summaryrefslogtreecommitdiff
path: root/mysql-test/r
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r')
-rw-r--r--mysql-test/r/analyze_format_json.result2
-rw-r--r--mysql-test/r/distinct.result18
-rw-r--r--mysql-test/r/func_group.result2
-rw-r--r--mysql-test/r/group_by.result35
-rw-r--r--mysql-test/r/having.result10
-rw-r--r--mysql-test/r/limit.result4
-rw-r--r--mysql-test/r/limit_rows_examined.result29
-rw-r--r--mysql-test/r/myisam.result4
-rw-r--r--mysql-test/r/select_found.result20
-rw-r--r--mysql-test/r/show_explain.result2
-rw-r--r--mysql-test/r/subselect4.result2
11 files changed, 82 insertions, 46 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 69930bc0215..2e106371c63 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -488,7 +488,7 @@ ANALYZE
"select_id": 1,
"r_loops": 1,
"volatile parameter": "REPLACED",
- "having_condition": "(TOP > a)",
+ "having_condition": "(TOP > t2.a)",
"filesort": {
"r_loops": 1,
"volatile parameter": "REPLACED",
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index d6e5a69e217..e2a7c462efd 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -175,7 +175,7 @@ explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 4 Using where; Using temporary
1 SIMPLE t3 ref a a 5 test.t1.b 2 Using index
-1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Distinct; Using join buffer (flat, BNL join)
+1 SIMPLE t2 index a a 4 NULL 5 Using where; Using index; Using join buffer (flat, BNL join)
SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
a
1
@@ -302,11 +302,11 @@ WHERE
AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index id id 4 NULL 2 Using index; Using temporary
-1 SIMPLE t2 index id id 8 NULL 1 Using index; Distinct; Using join buffer (flat, BNL join)
-1 SIMPLE t3 index id id 8 NULL 1 Using index; Distinct; Using join buffer (flat, BNL join)
-1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer (flat, BNL join)
-1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index; Distinct
-1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Distinct; Using join buffer (flat, BNL join)
+1 SIMPLE t2 index id id 8 NULL 1 Using index; Using join buffer (flat, BNL join)
+1 SIMPLE t3 index id id 8 NULL 1 Using index; Using join buffer (flat, BNL join)
+1 SIMPLE j_lj_t2 index id id 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+1 SIMPLE t2_lj ref id id 4 test.j_lj_t2.id 1 Using where; Using index
+1 SIMPLE j_lj_t3 index id id 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
1 SIMPLE t3_lj ref id id 4 test.j_lj_t3.id 1 Using where; Using index; Distinct
SELECT DISTINCT
t1.id
@@ -518,7 +518,7 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1_1 ALL NULL NULL NULL NULL 3 Using temporary
-1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Distinct; Using join buffer (flat, BNL join)
+1 SIMPLE t1_2 index NULL PRIMARY 4 NULL 3 Using index; Using join buffer (flat, BNL join)
EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
WHERE t1_1.a = t1_2.a;
id select_type table type possible_keys key key_len ref rows Extra
@@ -916,8 +916,8 @@ SELECT STRAIGHT_JOIN DISTINCT t1.id FROM
t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 96 100.00 Using where; Using temporary
-1 PRIMARY <derived2> ref key0 key0 5 test.t1.i1 9 100.00 Using where; Distinct
-1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Distinct; Using join buffer (flat, BNL join)
+1 PRIMARY <derived2> ref key0 key0 5 test.t1.i1 9 100.00 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
2 DERIVED t1 ALL NULL NULL NULL NULL 96 100.00
Warnings:
Note 1003 select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3))
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 659580059b5..aa12100e0dc 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1519,7 +1519,7 @@ SELECT MAX(pk) as max, i
FROM t1
ORDER BY max;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
# Only 11 is correct for collumn i in this result
SELECT MAX(pk) as max, i
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7f32643b727..f55489b7d93 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1346,12 +1346,43 @@ id select_type table type possible_keys key key_len ref rows Extra
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 144
+#
+# For this explain, the query plan is weird: if we are using
+# the primary key for reasons other than doing grouping, can't
+# GROUP BY code take advantage of this? Well, currently it doesnt:
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort
+# Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+show status like 'Sort_%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_priority_queue_sorts 0
+Sort_range 0
+Sort_rows 144
+Sort_scan 1
+# Proof ends.
+#
+# For this explain, the query plan is weird: if we are using
+# the primary key for reasons other than doing sorting, can't
+# ORDER BY code take advantage of this? Well, currently it doesnt:
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 144 Using index; Using filesort
+# Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+show status like 'Sort_%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_priority_queue_sorts 0
+Sort_range 0
+Sort_rows 144
+Sort_scan 1
+# Proof ends.
+#
SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
a
1
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index eda67460205..627edd60141 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -470,10 +470,9 @@ WHERE table2.f1 = 2
GROUP BY table1.f1, table2.f2
HAVING (table2.f2 = 8 AND table1.f1 >= 6);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE table2 const PRIMARY PRIMARY 4 const 1 100.00 Using filesort
-1 SIMPLE table1 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables
Warnings:
-Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having ((7 = 8) and (`test`.`table1`.`f1` >= 6))
+Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having 0
EXPLAIN EXTENDED
SELECT table1.f1, table2.f2
FROM t1 AS table1
@@ -482,10 +481,9 @@ WHERE table2.f1 = 2
GROUP BY table1.f1, table2.f2
HAVING (table2.f2 = 8);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE table2 const PRIMARY PRIMARY 4 const 1 100.00 Using filesort
-1 SIMPLE table1 ALL NULL NULL NULL NULL 4 100.00 Using where
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible HAVING noticed after reading const tables
Warnings:
-Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having (7 = 8)
+Note 1003 select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having 0
DROP TABLE t1;
#
# Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index 176a93c7a46..064fa5a18a7 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -80,13 +80,13 @@ create table t1 (a int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
c
7
explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where; Using temporary
+1 SIMPLE t1 ALL NULL NULL NULL NULL 7 Using where
select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
c
28
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 130d17ae270..318039db068 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -471,9 +471,11 @@ id select_type table type possible_keys key key_len ref rows Extra
select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0;
c1 sum(c2)
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 1;
-ERROR HY000: Sort aborted:
+c1 sum(c2)
+Warnings:
+Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete.
select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 20;
c1 sum(c2)
aa 3
@@ -496,9 +498,11 @@ id select_type table type possible_keys key key_len ref rows Extra
select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0;
c1 sum(c2)
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 1;
-ERROR HY000: Sort aborted:
+c1 sum(c2)
+Warnings:
+Warning 1931 Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete.
select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 20;
c1 sum(c2)
aa 3
@@ -627,7 +631,7 @@ CREATE TABLE t4 (a int);
INSERT INTO t4 values (1), (2);
INSERT INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 0;
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
select * from t4;
a
1
@@ -666,7 +670,7 @@ MDEV-115
SET @@optimizer_switch='in_to_exists=on,outer_join_with_cache=on';
CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM;
-INSERT INTO t1 VALUES ('USA');
+INSERT INTO t1 VALUES ('USA'),('CAN');
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (3899),(3914),(3888);
CREATE TABLE t3 ( c VARCHAR(33), d INT );
@@ -676,8 +680,8 @@ SELECT DISTINCT a AS field1 FROM t1, t2
WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 system NULL NULL NULL NULL 1 Using temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Distinct
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
2 SUBQUERY t3 ALL NULL NULL NULL NULL 3
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
SELECT DISTINCT a AS field1 FROM t1, t2
@@ -685,24 +689,27 @@ WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
field1
Warnings:
-Warning 1931 Query execution was interrupted. The query examined at least 23 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete.
+Warning 1931 Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete.
EXPLAIN
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary
-1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Distinct
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary
+1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using join buffer (flat, BNL join)
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 14;
a
+USA
Warnings:
Warning 1931 Query execution was interrupted. The query examined at least 15 rows, which exceeds LIMIT ROWS EXAMINED (14). The query result may be incomplete.
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 15;
a
USA
+CAN
Warnings:
Warning 1931 Query execution was interrupted. The query examined at least 16 rows, which exceeds LIMIT ROWS EXAMINED (15). The query result may be incomplete.
SELECT DISTINCT a FROM t1, t2 HAVING a > ' ' LIMIT ROWS EXAMINED 16;
a
USA
+CAN
Warnings:
Warning 1931 Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
drop table t1,t2,t3;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 093de1339f3..59f3a6fc5cc 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -523,11 +523,11 @@ a
explain select sql_big_result distinct t1.a from t1,t2 order by t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary
-1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index
explain select distinct t1.a from t1,t2 order by t2.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary
-1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index; Distinct
+1 SIMPLE t2 index NULL PRIMARY 4 NULL 2 Using index
drop table t1,t2;
create table t1 (
c1 varchar(32),
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 92758fa134b..238a9414ead 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -83,20 +83,20 @@ UNIQUE KEY e_n (email,name)
);
EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found
-1 SIMPLE t2 index NULL e_n 104 NULL 10
+1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found; Using temporary
+1 SIMPLE t2 ALL NULL NULL NULL NULL 200
SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
email
email1
+email2
+email3
+email4
+email5
+email6
+email7
+email8
+email9
email10
-email100
-email101
-email102
-email103
-email104
-email105
-email106
-email107
SELECT FOUND_ROWS();
FOUND_ROWS()
200
diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 0dc6f2b28ac..8a9cb4c9997 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1044,7 +1044,7 @@ set debug_dbug='+d,show_explain_probe_do_select';
SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort
+1 SIMPLE t2 system NULL NULL NULL NULL 1
1 SIMPLE t1 range b b 6 NULL 107 Using where; Using index
1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index
Warnings:
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 87645d187f2..4cd814468a4 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -19,7 +19,7 @@ SELECT 1 FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
ORDER BY count(*);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index; Using temporary
+1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
3 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found
# should not crash the next statement
View Lorry Controller Status