diff options
Diffstat (limited to 'mysql-test/r/join.result')
| -rw-r--r-- | mysql-test/r/join.result | 208 |
1 files changed, 206 insertions, 2 deletions
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result index 679b8a14385..eef7e89c61e 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 @@ -753,7 +753,7 @@ columns.TABLE_CATALOG, columns.TABLE_SCHEMA, columns.COLUMN_DEFAULT, columns.IS_ from information_schema.statistics join information_schema.columns using(table_name,column_name) where table_name='user'; TABLE_NAME COLUMN_NAME TABLE_CATALOG TABLE_SCHEMA NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT TABLE_CATALOG TABLE_SCHEMA COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA COLUMN_COMMENT user Host NULL mysql 0 mysql PRIMARY 1 A NULL NULL NULL BTREE NULL mysql NO char 60 180 NULL NULL utf8 utf8_bin char(60) PRI -user User NULL mysql 0 mysql PRIMARY 2 A 2 NULL NULL BTREE NULL mysql NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI +user User NULL mysql 0 mysql PRIMARY 2 A 3 NULL NULL BTREE NULL mysql NO char 16 48 NULL NULL utf8 utf8_bin char(16) PRI drop table t1; drop table t2; drop table t3; @@ -859,4 +859,208 @@ 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; End of 5.0 tests. |