2817
2817
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
2818
2818
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
2819
2819
id select_type table type possible_keys key key_len ref rows filtered Extra
2820
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
2821
1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
2820
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where
2821
2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where
2823
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))
2823
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))
2824
2824
explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;
2825
2825
id select_type table type possible_keys key key_len ref rows filtered Extra
2826
2826
1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
4117
4117
INSERT INTO t1 VALUES ('a', 'aaa');
4118
4118
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4120
4122
CREATE INDEX I1 ON t1 (a);
4121
4123
CREATE INDEX I2 ON t1 (b);
4122
4124
EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4123
4125
id select_type table type possible_keys key key_len ref rows Extra
4124
1 PRIMARY t1 index I1 I1 7 NULL # Using index; LooseScan
4125
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
4126
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4127
2 SUBQUERY t1 index NULL I1 7 NULL # Using index
4126
4128
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);
4128
4132
CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));
4129
4133
INSERT INTO t2 SELECT * FROM t1;
4130
4134
CREATE INDEX I1 ON t2 (a);
4131
4135
CREATE INDEX I2 ON t2 (b);
4132
4136
EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4133
4137
id select_type table type possible_keys key key_len ref rows Extra
4134
1 PRIMARY t2 index I1 I1 7 NULL # Using index; LooseScan
4135
1 PRIMARY t2 ref I2 I2 43 test.t2.a # Using where
4138
1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where
4139
2 SUBQUERY t2 index NULL I1 7 NULL # Using index
4136
4140
SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);
4139
4145
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4140
4146
id select_type table type possible_keys key key_len ref rows Extra
4141
1 PRIMARY t1 index I1 I1 7 NULL # Using where; Using index; LooseScan
4142
1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where
4147
1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where
4148
2 SUBQUERY t1 index NULL I1 7 NULL # Using where; Using index
4143
4149
SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);
4145
4153
DROP TABLE t1,t2;
4147
4155
# Bug #32400: Complex SELECT query returns correct result only on some
added
removed
tests/r/subselect_no_mat_and_semi_join.result
