Wrong result from a query with ALL subquery predicate in WHERE

[Igor Babaev]

The following sequence of commands returns an incorrect result set in maridb-5.2:

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0);

MariaDB [test]> SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0);
Empty set (0.00 sec)

(see also bug #12888306 for mysql code line)

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

1) The problem is not repeatable on mysql-test run.
2) it is repeatable om 5.1, 5.2, 5.3, 5.5

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

In mysql-test and mysql execution looks same and return_zero_rows called in both cases, so we probably have some other bug in mysql-test so it returns 1

[Elena Stepanova (elenst)]

If that's what you see in MTR output

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
SELECT 1 FROM t1 WHERE 1 > ALL(SELECT 1 FROM t1 WHERE a!=0);
1

it does not mean that mysqltest returns 1; '1' is the automatic column name here, while the result is empty. If it was returning a result, it would look like this:

SELECT 1 FROM t1;
1
1

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that constant WHERE condition (the subquery) evaluates incorrectly to 0 in optimize_cond (in the SELECT list the subquery returns correct result - TRUE).

[Oleksandr "Sanja" Byelkin (sanja-byelkin)]

The problem is that in the subquery set do_send_rows so it does not send empty set in case of FALSE WHERE clause...