Bug #86535 WL#9603: RANK and uncorrelated subquery: wrong result
Submitted: 1 Jun 2017 2:39 Modified: 12 Jun 2017 18:42
Reporter: Dag Wanvik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2017 2:39] Dag Wanvik 
Description:
All rows from twf should satisfy IN, and none NOT IN.

How to repeat:
CREATE TABLE t(a int);
INSERT INTO t VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(-1802764288),(-1438121984),(-1237843968),(6),(9),(10),(11),(12),(13),(15),(476839936),(780206080),(1887961088);

CREATE TABLE twf AS SELECT RANK() OVER ( ORDER BY a ) AS rnk FROM t  GROUP BY a;
SELECT RANK() OVER ( ORDER BY a ) AS rnk FROM t GROUP BY a;
SELECT * FROM twf WHERE rnk IN (SELECT RANK() OVER ( ORDER BY a ) AS rnk FROM t  GROUP BY a);
SELECT * FROM twf WHERE rnk NOT IN (SELECT RANK() OVER ( ORDER BY a ) AS rnk FROM t  GROUP BY a);
[24 Oct 2017 14:27] Paul DuBois 
Fixed in 8.0.2.