MariaDB

  1. Bug #833702
  2. Comment #3

Comment 3 for bug 833702

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

The following query has a TRUE WHERE clause for the IN subquery,
thus it's IN predicate must be TRUE in the same way as the previous query.

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

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

SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);
--> empty result, should be same as above => BUG!