diff options
| author | unknown <timour@askmonty.org> | 2013-02-07 15:33:24 +0200 |
|---|---|---|
| committer | unknown <timour@askmonty.org> | 2013-02-07 15:33:24 +0200 |
| commit | e648ff111a1d3fe79b6bd4ff0a25e85d1b949fb2 (patch) | |
| tree | b6bbc11cf7e2408fe0f18a33540bc35050ffb1e6 /mysql-test/r/subselect4.result | |
| parent | c7fa9fa72b2df857dac33a76ea79d66e37918acb (diff) | |
| download | mariadb-git-e648ff111a1d3fe79b6bd4ff0a25e85d1b949fb2.tar.gz | |
MDEV-537 Make multi-column non-top level subqueries to be executed via index (index/unique subquery)
instead of single_select_engine
This task changes the IN-EXISTS rewrite for multi-column subqueries
"(a, b) IN (select b, c ...)" to work in the same way as for
single-column subqueries "a IN (select b ...) with respect to the
injection of NULL-rejecting predicates.
More specifically, the method
Item_in_subselect::create_row_in_to_exists_cond()
adds Item_is_not_null_test and Item_func_trig_cond only if the left
IN operand can be NULL. Not having these predicates when not necessary,
makes it possible to rewrite the subquery into a "unique_subquery" or
"index_subquery" when there is a suitable index on the only
subquery table.
Diffstat (limited to 'mysql-test/r/subselect4.result')
| -rw-r--r-- | mysql-test/r/subselect4.result | 4 |
1 files changed, 2 insertions, 2 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 2814e5a6dcd..0d5209d0ec2 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -303,7 +303,7 @@ EXPLAIN SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2a eq_ref PRIMARY PRIMARY 8 const,test.t1.pk 1 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t2a unique_subquery PRIMARY PRIMARY 8 const,test.t1.pk 1 Using index; Using where; Full scan on NULL key SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); pk i 0 10 @@ -335,7 +335,7 @@ EXPLAIN SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t2c ref it2c it2c 8 const,test.t1.pk 2 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t2c index_subquery it2c it2c 8 const,test.t1.pk 2 Using index; Using where; Full scan on NULL key SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2c.i, t2c.pk FROM t2c WHERE t2c.pk = t1.pk); pk i 0 10 |