| Bug #28526 | Correlated subquery with GROUP BY on outer query returns no results in 5.0.40+ | ||
|---|---|---|---|
| Submitted: | 18 May 2007 17:15 | Modified: | 31 May 2007 5:52 |
| Reporter: | Zak Kipling | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.40, 5.0.41, 5.0.42-20070517 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_31, regression | ||
[20 May 2007 16:45]
MySQL Verification Team
verified exactly as described in initial report. >=5.0.40 returned 0 rows, < 5.0.40 returned 1 row.
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.42-debug |
+--------------+
<cut>
mysql> SELECT location FROM subquery_bug_test AS a
-> WHERE location=2
-> AND location=( SELECT location FROM subquery_bug_test AS b
-> WHERE a.ref=b.ref
-> AND b.location=2
-> LIMIT 1 )
-> GROUP BY location;
Empty set (0.03 sec)
------------------
mysql> select version();
+--------------------------+
| version() |
+--------------------------+
| 5.0.36-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)
<cut>
mysql> SELECT location FROM subquery_bug_test AS a
-> WHERE location=2
-> AND location=( SELECT location FROM subquery_bug_test AS b
-> WHERE a.ref=b.ref
-> AND b.location=2
-> LIMIT 1 )
-> GROUP BY location;
+----------+
| location |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
[31 May 2007 5:52]
Igor Babaev
This bug is a duplicate of bug #28337 which is already fixed: mysql> SELECT VERSION(); +--------------+ | VERSION() | +--------------+ | 5.0.44-debug | +--------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE subquery_bug_test (ref BIGINT not null, location BIGINT not null); Query OK, 0 rows affected (0.49 sec) mysql> INSERT INTO subquery_bug_test (ref, location) VALUES (1,2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO subquery_bug_test (ref, location) VALUES (3,2); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT location FROM subquery_bug_test AS a -> WHERE location=2 -> AND location=( SELECT location FROM subquery_bug_test AS b -> WHERE a.ref=b.ref -> AND b.location=2 -> LIMIT 1 ) -> GROUP BY location; +----------+ | location | +----------+ | 2 | +----------+ 1 row in set (0.01 sec)
Description: This appears to be a regression in 5.0.40 relative to 5.0.38. On MySQL 5.0.38, the following returns one row as I would expect, while on 5.0.40, 5.0.41 and the 5.0.42-20070517 snapshot it returns none. CREATE TABLE subquery_bug_test (ref BIGINT not null, location BIGINT not null); -- Note that having two rows with the same "location" field -- is necessary for the bug to manifest. INSERT INTO subquery_bug_test (ref, location) VALUES (1,2); INSERT INTO subquery_bug_test (ref, location) VALUES (3,2); -- Returns one row on 5.0.38 but none on 5.0.40+ -- (yes, the two "location=" clauses are superfluous, but -- the "location=2" one was originally an IN clause with several values -- this -- is heavily stripped down) SELECT location FROM subquery_bug_test AS a WHERE location=2 AND location=( SELECT location FROM subquery_bug_test AS b WHERE a.ref=b.ref AND b.location=2 LIMIT 1 ) GROUP BY location; Initially I thought this might be the same issue as bug #27333, but applying the proposed patch for that bug doesn't appear to fix the problem. How to repeat: Run the above SQL fragment on one of the affected versions of the MySQL server.