MySQL Bugs: #99025: Incorrect result from CTE subquery with outer reference

Bug #99025	Incorrect result from CTE subquery with outer reference
Submitted:	22 Mar 2020 20:00	Modified:	29 May 2020 18:36
Reporter:	Jonas Staudenmeir	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86
Tags:	regression

Description:
I ran into an issue with a CTE query where MySQL apparently returns an incorrect result.

I have a table that stores a nested page hierarchy and I'm using a CTE query to find all active pages that also only have active ancestors (recursive parents). The query looks for active pages without any inactive ancestors but returns more rows than it should.

The query works on PostgreSQL, so I think it is correct: http://sqlfiddle.com/#!17/3d5e21/7

How to repeat:
CREATE TABLE `pages` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int unsigned DEFAULT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `pages` (`id`, `parent_id`, `active`) VALUES
(1,	NULL,	1),
(2,	1,	0),
(3,	2,	1),
(4,	3,	1);

SELECT *
FROM `pages`
WHERE NOT EXISTS (
  WITH RECURSIVE `cte` AS (
    (
      SELECT *
      FROM `pages` AS `alias`
      WHERE `alias`.`id` = `pages`.`parent_id`
    ) UNION ALL (
      SELECT `alias`.*
      FROM `pages` AS `alias`
      INNER JOIN `cte` ON `cte`.`parent_id` = `alias`.`id`
    )
  )
  SELECT *
  FROM `cte`
  WHERE `active` = 0
)
AND `active` = 1;

# Expected result: 1
# Actual result: 1, 4

Thank you for the bug report. Looks like a regression:

d:\tmp\mysql-8.0.16-winx64>bin\mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE q;
Query OK, 1 row affected (0.01 sec)

mysql> USE q
Database changed
mysql> CREATE TABLE `pages` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `parent_id` int unsigned DEFAULT NULL,
    ->   `active` tinyint(1) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO `pages` (`id`, `parent_id`, `active`) VALUES
    -> (1,NULL,1),
    -> (2,1,0),
    -> (3,2,1),
    -> (4,3,1);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM `pages`
    -> WHERE NOT EXISTS (
    ->   WITH RECURSIVE `cte` AS (
    ->     (
    ->       SELECT *
    ->       FROM `pages` AS `alias`
    ->       WHERE `alias`.`id` = `pages`.`parent_id`
    ->     ) UNION ALL (
    ->       SELECT `alias`.*
    ->       FROM `pages` AS `alias`
    ->       INNER JOIN `cte` ON `cte`.`parent_id` = `alias`.`id`
    ->     )
    ->   )
    ->   SELECT *
    ->   FROM `cte`
    ->   WHERE `active` = 0
    -> )
    -> AND `active` = 1;
+----+-----------+--------+
| id | parent_id | active |
+----+-----------+--------+
|  1 |      NULL |      1 |
+----+-----------+--------+
1 row in set (0.01 sec)

mysql>
------------------------------------------------------------------------
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --local-infile=1 --prompt="mysql 8.0 > " --default-character-set=latin1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 Source distribution BUILT: 2020-FEB-16

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > USE q
Database changed
mysql 8.0 > SELECT *
    -> FROM `pages`
    -> WHERE NOT EXISTS (
    ->   WITH RECURSIVE `cte` AS (
    ->     (
    ->       SELECT *
    ->       FROM `pages` AS `alias`
    ->       WHERE `alias`.`id` = `pages`.`parent_id`
    ->     ) UNION ALL (
    ->       SELECT `alias`.*
    ->       FROM `pages` AS `alias`
    ->       INNER JOIN `cte` ON `cte`.`parent_id` = `alias`.`id`
    ->     )
    ->   )
    ->   SELECT *
    ->   FROM `cte`
    ->   WHERE `active` = 0
    -> )
    -> AND `active` = 1;
+----+-----------+--------+
| id | parent_id | active |
+----+-----------+--------+
|  1 |      NULL |      1 |
|  4 |         3 |      1 |
+----+-----------+--------+
2 rows in set (0.05 sec)

mysql 8.0 >

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE q;
Query OK, 1 row affected (0.01 sec)

mysql> USE q
Database changed
mysql> CREATE TABLE `pages` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `parent_id` int unsigned DEFAULT NULL,
    ->   `active` tinyint(1) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql>
mysql> INSERT INTO `pages` (`id`, `parent_id`, `active`) VALUES
    -> (1,NULL,1),
    -> (2,1,0),
    -> (3,2,1),
    -> (4,3,1);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT *
    -> FROM `pages`
    -> WHERE NOT EXISTS (
    ->   WITH RECURSIVE `cte` AS (
    ->     (
    ->       SELECT *
    ->       FROM `pages` AS `alias`
    ->       WHERE `alias`.`id` = `pages`.`parent_id`
    ->     ) UNION ALL (
    ->       SELECT `alias`.*
    ->       FROM `pages` AS `alias`
    ->       INNER JOIN `cte` ON `cte`.`parent_id` = `alias`.`id`
    ->     )
    ->   )
    ->   SELECT *
    ->   FROM `cte`
    ->   WHERE `active` = 0
    -> )
    -> AND `active` = 1;
+----+-----------+--------+
| id | parent_id | active |
+----+-----------+--------+
|  1 |      NULL |      1 |
|  4 |         3 |      1 |
+----+-----------+--------+
2 rows in set (0.02 sec)

mysql>

Posted by developer:
 
Fixed in 8.0.21.

Queries that used a recursive common table expression with an outer
reference could return incorrect results.