Description:
following queries results do not much to results of queries in which HAVING clause replaced with WHERE one:
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 HAVING b = 2 );
SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a > ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a < ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a = ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a >= ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <= ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
SELECT a FROM t1 WHERE a <> ALL ( SELECT a FROM t1 HAVING b = 2 UNION SELECT a FROM t1 HAVING b = 2);
How to repeat:
above subqueries are rewritten subqueries with WHERE clause in subselect.test
just add them after
#
# ALL/ANY test
#
CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
and compare results with analog with WHERE clause