2010
2010
SET @@SESSION.optimizer_switch= @save_optimizer_switch;
2011
2011
DROP TABLE t2, t1;
2012
2012
# End of test for Bug #17814492
2014
# Bug #18607971 : 5.5 TO 5.6 REGRESSION WITH A SUBQUERY IN THE FROM
2017
CREATE TABLE t(id INT PRIMARY KEY,
2018
c1 INT, c2 INT, key(c2)) engine=InnoDB;
2019
INSERT INTO t(id, c1, c2) VALUES(1, 2, 3), (2, 3, 4), (3, 3, 4), (4, 3, 4);
2021
Table Op Msg_type Msg_text
2022
test.t analyze status OK
2023
EXPLAIN SELECT * FROM
2025
FROM t t1 INNER JOIN t t2 ON t1.c1= 3
2026
GROUP BY t1.c1) a, t b
2027
WHERE b.id BETWEEN 1 AND 10;
2028
id select_type table type possible_keys key key_len ref rows Extra
2029
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2030
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2031
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where
2032
2 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop)
2033
EXPLAIN SELECT * FROM
2035
FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
2036
GROUP BY t1.id, t2.c2) a, t b
2037
WHERE b.id BETWEEN 1 AND 10;
2038
id select_type table type possible_keys key key_len ref rows Extra
2039
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2040
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2041
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2042
2 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL
2043
EXPLAIN SELECT * FROM
2045
FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
2046
GROUP BY t1.c1) a, t b
2047
WHERE b.id BETWEEN 1 AND 10;
2048
id select_type table type possible_keys key key_len ref rows Extra
2049
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2050
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2051
2 DERIVED t2 ref c2 c2 5 const 1 Using index
2052
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
2053
EXPLAIN SELECT * FROM
2055
FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
2056
GROUP BY t1.c1, t2.c2) a, t b
2057
WHERE b.id BETWEEN 1 AND 10;
2058
id select_type table type possible_keys key key_len ref rows Extra
2059
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2060
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2061
2 DERIVED t2 ref c2 c2 5 const 1 Using index
2062
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
2063
EXPLAIN SELECT * FROM
2065
FROM t t1 INNER JOIN t t2 ON t1.c1= 3 AND t2.c2= 3
2066
GROUP BY t1.c1, t1.id) a, t b
2067
WHERE b.id BETWEEN 1 AND 10;
2068
id select_type table type possible_keys key key_len ref rows Extra
2069
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2070
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop)
2071
2 DERIVED t2 ref c2 c2 5 const 1 Using index; Using temporary; Using filesort
2072
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (Block Nested Loop)
2073
EXPLAIN SELECT * FROM
2075
FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.c1
2076
GROUP BY t2.c1, t1.id) a, t b
2077
WHERE b.id BETWEEN 1 AND 10;
2078
id select_type table type possible_keys key key_len ref rows Extra
2079
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2080
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2081
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2082
2 DERIVED t2 ALL NULL NULL NULL NULL 4 Using where
2083
EXPLAIN SELECT * FROM
2085
FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
2086
GROUP BY t2.c1, t1.id) a, t b
2087
WHERE b.id BETWEEN 1 AND 10;
2088
id select_type table type possible_keys key key_len ref rows Extra
2089
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2090
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2091
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2092
2 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL
2093
EXPLAIN SELECT * FROM
2095
FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
2096
GROUP BY t2.c2, t1.id) a, t b
2097
WHERE b.id BETWEEN 1 AND 10;
2098
id select_type table type possible_keys key key_len ref rows Extra
2099
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2100
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2101
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2102
2 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL
2103
EXPLAIN SELECT * FROM
2105
FROM t t1 INNER JOIN t t2 ON t1.id=1 AND t1.c1=t2.id
2106
GROUP BY t1.id, t2.c2) a, t b
2107
WHERE b.id BETWEEN 1 AND 10;
2108
id select_type table type possible_keys key key_len ref rows Extra
2109
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2110
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2111
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2112
2 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL
2113
EXPLAIN SELECT * FROM
2115
FROM t t1 INNER JOIN t t2 INNER JOIN t t3 ON t1.id=1 AND t1.c1=t2.id AND t2.c1=t3.id
2116
GROUP BY t1.id, t2.c2, t3.c2) a, t b
2117
WHERE b.id BETWEEN 1 AND 10;
2118
id select_type table type possible_keys key key_len ref rows Extra
2119
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2120
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2121
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 NULL
2122
2 DERIVED t2 const PRIMARY PRIMARY 4 const 1 NULL
2123
2 DERIVED t3 const PRIMARY PRIMARY 4 const 1 NULL
2124
EXPLAIN SELECT * FROM
2125
(SELECT DISTINCT t1.id
2127
WHERE t1.id= 1) a, t b
2128
WHERE b.id BETWEEN 1 AND 10;
2129
id select_type table type possible_keys key key_len ref rows Extra
2130
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2131
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2132
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 Using index
2133
EXPLAIN SELECT * FROM
2135
FROM t t1 INNER JOIN t t2 ON t1.id= 1
2136
GROUP BY t1.id + 1) a, t b
2137
WHERE b.id BETWEEN 1 AND 10;
2138
id select_type table type possible_keys key key_len ref rows Extra
2139
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2140
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2141
2 DERIVED t1 const PRIMARY PRIMARY 4 const 1 Using index
2142
2 DERIVED t2 index NULL c2 5 NULL 4 Using index
2143
EXPLAIN SELECT * FROM
2145
FROM t t1 INNER JOIN t t2 ON t1.c1= 3
2146
GROUP BY 1.5) a, t b
2147
WHERE b.id BETWEEN 1 AND 10;
2148
id select_type table type possible_keys key key_len ref rows Extra
2149
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2150
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2151
2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using where
2152
2 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop)
2153
EXPLAIN SELECT * FROM
2155
FROM t t1 INNER JOIN t t2 ON mod(t1.id,1000)= 1
2156
GROUP BY t1.id) a, t b
2157
WHERE b.id BETWEEN 1 AND 10;
2158
id select_type table type possible_keys key key_len ref rows Extra
2159
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2160
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 Using join buffer (Block Nested Loop)
2161
2 DERIVED t1 index PRIMARY,c2 c2 5 NULL 4 Using where; Using index; Using temporary; Using filesort
2162
2 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop)
2163
EXPLAIN SELECT * FROM
2165
FROM t t1 INNER JOIN t t2 ON t1.id + 1= 2
2166
GROUP BY t1.id + 1) a, t b
2167
WHERE b.id BETWEEN 1 AND 10;
2168
id select_type table type possible_keys key key_len ref rows Extra
2169
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2170
1 PRIMARY b range PRIMARY PRIMARY 4 NULL 4 Using where
2171
2 DERIVED t1 index PRIMARY,c2 c2 5 NULL 4 Using where; Using index
2172
2 DERIVED t2 index NULL c2 5 NULL 4 Using index; Using join buffer (Block Nested Loop)
2173
CREATE VIEW v1 AS SELECT c1 a FROM t WHERE c1 = 3;
2174
CREATE VIEW v2 AS SELECT c2 b FROM t WHERE c2 > 3;
2175
EXPLAIN SELECT * FROM (SELECT v1.a
2176
FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b
2177
GROUP BY v1.a) p, t q
2178
WHERE q.id BETWEEN 1 AND 10;
2179
id select_type table type possible_keys key key_len ref rows Extra
2180
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2181
1 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where
2182
2 DERIVED t ALL NULL NULL NULL NULL 4 Using where
2183
2 DERIVED t ref c2 c2 5 const 1 Using where; Using index
2185
CREATE VIEW v1 AS SELECT c1 a FROM t;
2186
EXPLAIN SELECT * FROM (SELECT v1.a
2187
FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b AND v1.a = 10
2188
GROUP BY v1.a) p, t q
2189
WHERE q.id BETWEEN 1 AND 10;
2190
id select_type table type possible_keys key key_len ref rows Extra
2191
1 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where
2192
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using join buffer (Block Nested Loop)
2193
2 DERIVED t ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
2194
2 DERIVED t ref c2 c2 5 func 1 Using where; Using index
2195
EXPLAIN SELECT * FROM (SELECT v1.a
2196
FROM v1 LEFT OUTER JOIN v2 ON v1.a = v2.b
2198
GROUP BY v1.a) p, t q
2199
WHERE q.id BETWEEN 1 AND 10;
2200
id select_type table type possible_keys key key_len ref rows Extra
2201
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 NULL
2202
1 PRIMARY q range PRIMARY PRIMARY 4 NULL 4 Using where
2203
2 DERIVED t ALL NULL NULL NULL NULL 4 Using where
2204
2 DERIVED t ref c2 c2 5 const 1 Using where; Using index