summaryrefslogtreecommitdiff
path: root/mysql-test/r/join.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/join.result')
-rw-r--r--mysql-test/r/join.result246
1 files changed, 239 insertions, 7 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index beba47cd39b..c60c6bfb3c8 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -404,7 +404,7 @@ SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
ORDER BY t1.b, t1.c;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 32 Using temporary; Using filesort
-1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 16 Using where; Using join buffer
SELECT STRAIGHT_JOIN t2.e FROM t1,t2 WHERE t2.d=1 AND t1.b=t2.e
ORDER BY t1.b, t1.c;
e
@@ -859,6 +859,210 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 5
drop table t1, t2, t3;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, filler char(100), key(a), key(b));
+create table t3 (a int, b int, filler char(100), key(a), key(b));
+insert into t2
+select @a:= A.a + 10*(B.a + 10*C.a), @a, 'filler' from t1 A, t1 B, t1 C;
+insert into t3 select * from t2 where a < 800;
+explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL a,b NULL NULL NULL 1000 Using where
+1 SIMPLE t3 ref b b 5 test.t2.b 1 Using where
+drop table t1, t2, t3;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, primary key(a));
+insert into t2 select @v:=A.a+10*B.a, @v from t1 A, t1 B;
+explain select * from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+show status like '%cost%';
+Variable_name Value
+Last_query_cost 4.016090
+select 'The cost of accessing t1 (dont care if it changes' '^';
+The cost of accessing t1 (dont care if it changes
+The cost of accessing t1 (dont care if it changes^
+select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
+Z
+vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv
+explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 10
+1 SIMPLE A eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.b 1
+show status like '%cost%';
+Variable_name Value
+Last_query_cost 24.016090
+select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
+Z
+^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
+drop table t1, t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
+CREATE TABLE t2 (c INT PRIMARY KEY, d INT);
+INSERT INTO t1 VALUES(1,NULL),(2,NULL),(3,NULL),(4,NULL);
+INSERT INTO t1 SELECT a + 4, b FROM t1;
+INSERT INTO t1 SELECT a + 8, b FROM t1;
+INSERT INTO t1 SELECT a + 16, b FROM t1;
+INSERT INTO t1 SELECT a + 32, b FROM t1;
+INSERT INTO t1 SELECT a + 64, b FROM t1;
+INSERT INTO t2 SELECT a, b FROM t1;
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a LIMIT 2;
+a b c d
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a LIMIT 2;
+a b c d
+1 NULL 1 NULL
+2 NULL 2 NULL
+EXPLAIN SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1
+EXPLAIN SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 128 Using filesort
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
+SELECT * FROM t1 JOIN t2 ON b=c ORDER BY a;
+a b c d
+SELECT * FROM t1 JOIN t2 ON a=c ORDER BY a;
+a b c d
+1 NULL 1 NULL
+2 NULL 2 NULL
+3 NULL 3 NULL
+4 NULL 4 NULL
+5 NULL 5 NULL
+6 NULL 6 NULL
+7 NULL 7 NULL
+8 NULL 8 NULL
+9 NULL 9 NULL
+10 NULL 10 NULL
+11 NULL 11 NULL
+12 NULL 12 NULL
+13 NULL 13 NULL
+14 NULL 14 NULL
+15 NULL 15 NULL
+16 NULL 16 NULL
+17 NULL 17 NULL
+18 NULL 18 NULL
+19 NULL 19 NULL
+20 NULL 20 NULL
+21 NULL 21 NULL
+22 NULL 22 NULL
+23 NULL 23 NULL
+24 NULL 24 NULL
+25 NULL 25 NULL
+26 NULL 26 NULL
+27 NULL 27 NULL
+28 NULL 28 NULL
+29 NULL 29 NULL
+30 NULL 30 NULL
+31 NULL 31 NULL
+32 NULL 32 NULL
+33 NULL 33 NULL
+34 NULL 34 NULL
+35 NULL 35 NULL
+36 NULL 36 NULL
+37 NULL 37 NULL
+38 NULL 38 NULL
+39 NULL 39 NULL
+40 NULL 40 NULL
+41 NULL 41 NULL
+42 NULL 42 NULL
+43 NULL 43 NULL
+44 NULL 44 NULL
+45 NULL 45 NULL
+46 NULL 46 NULL
+47 NULL 47 NULL
+48 NULL 48 NULL
+49 NULL 49 NULL
+50 NULL 50 NULL
+51 NULL 51 NULL
+52 NULL 52 NULL
+53 NULL 53 NULL
+54 NULL 54 NULL
+55 NULL 55 NULL
+56 NULL 56 NULL
+57 NULL 57 NULL
+58 NULL 58 NULL
+59 NULL 59 NULL
+60 NULL 60 NULL
+61 NULL 61 NULL
+62 NULL 62 NULL
+63 NULL 63 NULL
+64 NULL 64 NULL
+65 NULL 65 NULL
+66 NULL 66 NULL
+67 NULL 67 NULL
+68 NULL 68 NULL
+69 NULL 69 NULL
+70 NULL 70 NULL
+71 NULL 71 NULL
+72 NULL 72 NULL
+73 NULL 73 NULL
+74 NULL 74 NULL
+75 NULL 75 NULL
+76 NULL 76 NULL
+77 NULL 77 NULL
+78 NULL 78 NULL
+79 NULL 79 NULL
+80 NULL 80 NULL
+81 NULL 81 NULL
+82 NULL 82 NULL
+83 NULL 83 NULL
+84 NULL 84 NULL
+85 NULL 85 NULL
+86 NULL 86 NULL
+87 NULL 87 NULL
+88 NULL 88 NULL
+89 NULL 89 NULL
+90 NULL 90 NULL
+91 NULL 91 NULL
+92 NULL 92 NULL
+93 NULL 93 NULL
+94 NULL 94 NULL
+95 NULL 95 NULL
+96 NULL 96 NULL
+97 NULL 97 NULL
+98 NULL 98 NULL
+99 NULL 99 NULL
+100 NULL 100 NULL
+101 NULL 101 NULL
+102 NULL 102 NULL
+103 NULL 103 NULL
+104 NULL 104 NULL
+105 NULL 105 NULL
+106 NULL 106 NULL
+107 NULL 107 NULL
+108 NULL 108 NULL
+109 NULL 109 NULL
+110 NULL 110 NULL
+111 NULL 111 NULL
+112 NULL 112 NULL
+113 NULL 113 NULL
+114 NULL 114 NULL
+115 NULL 115 NULL
+116 NULL 116 NULL
+117 NULL 117 NULL
+118 NULL 118 NULL
+119 NULL 119 NULL
+120 NULL 120 NULL
+121 NULL 121 NULL
+122 NULL 122 NULL
+123 NULL 123 NULL
+124 NULL 124 NULL
+125 NULL 125 NULL
+126 NULL 126 NULL
+127 NULL 127 NULL
+128 NULL 128 NULL
+DROP TABLE IF EXISTS t1,t2;
#
# Bug #42116: Mysql crash on specific query
#
@@ -895,7 +1099,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t4 ALL NULL NULL NULL NULL 0
1 SIMPLE t5 ALL NULL NULL NULL NULL 0
1 SIMPLE t6 ALL NULL NULL NULL NULL 0
-1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where
+1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where; Using join buffer
SELECT *
FROM
t1 JOIN t2 ON t1.a = t2.a
@@ -921,11 +1125,11 @@ INSERT INTO t1 VALUES (1),(2);
CREATE VIEW v1 AS SELECT 1 FROM t1 LEFT JOIN t1 AS t2 on 1=1;
EXPLAIN EXTENDED
SELECT 1 FROM v1 right join v1 AS v2 ON RAND();
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
-1 SIMPLE t1 ALL NULL NULL NULL NULL 2
-1 SIMPLE t2 ALL NULL NULL NULL NULL 2
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00
Warnings:
Note 1003 select 1 AS `1` from `test`.`t1` left join `test`.`t1` `t2` on((1 = 1)) left join (`test`.`t1` left join `test`.`t1` `t2` on((1 = 1))) on(rand()) where 1
DROP VIEW v1;
@@ -953,3 +1157,31 @@ a b a b
1 1 1 1
DROP TABLE t1;
End of 5.0 tests.
+CREATE TABLE t1 (f1 int);
+CREATE TABLE t2 (f1 int);
+INSERT INTO t2 VALUES (1);
+CREATE VIEW v1 AS SELECT * FROM t2;
+PREPARE stmt FROM 'UPDATE t2 AS A NATURAL JOIN v1 B SET B.f1 = 1';
+EXECUTE stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+DROP VIEW v1;
+DROP TABLE t1, t2;
+CREATE TABLE t1(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM;
+CREATE TABLE t2(a CHAR(9),b INT,KEY(b),KEY(a)) ENGINE=MYISAM;
+INSERT INTO t1 VALUES ('1',null),(null,null);
+INSERT INTO t2 VALUES ('1',null),(null,null);
+CREATE TABLE mm1(a CHAR(9),b INT,KEY(b),KEY(a))
+ENGINE=MERGE UNION=(t1,t2);
+SELECT t1.a FROM mm1,t1;
+a
+NULL
+1
+NULL
+1
+NULL
+1
+NULL
+1
+DROP TABLE t1, t2, mm1;
+End of 5.1 tests
View Lorry Controller Status