Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result from a query with ALL subquery predicate in WHERE
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
Re: Wrong result from a query with ALL subquery predicate in WHERE
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
Re: Wrong result from a query with ALL subquery predicate in WHERE
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).
Re: Wrong result from a query with ALL subquery predicate in WHERE
The problem is that in the subquery set do_send_rows so it does not send empty set in case of FALSE WHERE clause...
Launchpad bug id: 993726
Re: Wrong result from a query with ALL subquery predicate in WHERE
1) The problem is not repeatable on mysql-test run.
2) it is repeatable om 5.1, 5.2, 5.3, 5.5