MariaDB

  1. Bug #833702
  2. Comment #2

Comment 2 for bug 833702

Revision history for this message
Timour Katchaounov (timour) wrote : Re: Wrong result with nested correlated subqueries, OR condition

The problem is related to IN-TO_EXISTS, and not to materialization.
The initial example cannot show this because the subquery is correlated,
and materialization is not applicable.

Also the OR in the outer query is not relevant.

The following simpler example demonstrates this:

set @@optimizer_switch='in_to_exists=off,materialization=on';
SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);
+------+------+------+
| c | a | b |
 +------+------+------+
| 10 | 7 | 0 |
 +------+------+------+

set @@optimizer_switch='in_to_exists=on,materialization=off';
SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);

=> empty result