diff options
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/subselect4.result | 376 |
1 files changed, 376 insertions, 0 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index c8d786e2532..97a9e0b7f16 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -757,3 +757,379 @@ WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); f1 f3 f10 SET SESSION optimizer_switch = @old_optimizer_switch; drop table t1,t2; +# +# LP BUG#715738: Wrong result with implicit grouping and empty result set +# +CREATE TABLE t1 (f1 int, f2 int); +CREATE TABLE t2 (f3 int, f4 int not null, PRIMARY KEY (f3)); +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'materialization=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +f1 f2 +SET @@optimizer_switch = 'materialization=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2); +f1 f2 +INSERT INTO t1 VALUES (1, 2); +INSERT INTO t1 VALUES (3, 4); +INSERT INTO t2 VALUES (5, 6); +INSERT INTO t2 VALUES (7, 8); +SET @@optimizer_switch = 'materialization=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +SET @@optimizer_switch = 'materialization=off,semijoin=off'; +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3+f4, min(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, min(f4)+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, min(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT f3, f3 + count(f4) FROM t2 WHERE f3 > 10); +f1 f2 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) > 7) as not_in; +not_in +1 +EXPLAIN +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT f3, count(f4) FROM t2 WHERE f3 > 10 HAVING max(f4) is null) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4) FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using MRR +SELECT (2, 0) NOT IN (SELECT max(f3+f3), count(f4)+f3 FROM t2 WHERE f3 > 10) as not_in; +not_in +NULL +EXPLAIN +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No matching min/max row +SELECT * FROM t1 WHERE (2, 0) NOT IN (SELECT min(f3)+f3, min(f4)+f3+max(f4) FROM t2 WHERE f3 > 10); +f1 f2 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1,t2; |