[MDEV-3667] LP:993726 - Wrong result from a query with ALL subquery predicate in WHERE Created: 2012-05-03  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Critical
Reporter: Igor Babaev Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug993726.xml    

 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)

 Comments   
Comment by Oleksandr Byelkin [ 2012-05-03 ]

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

Comment by Oleksandr Byelkin [ 2012-05-04 ]

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

Comment by Elena Stepanova [ 2012-05-04 ]

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

Comment by Oleksandr Byelkin [ 2012-05-04 ]

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).

Comment by Oleksandr Byelkin [ 2012-05-04 ]

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...

Comment by Rasmus Johansson (Inactive) [ 2012-05-07 ]

Launchpad bug id: 993726

Generated at Thu Feb 08 06:50:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.