Recursive CTE with UNION ALL and DISTINCT in the recursive part does not terminate on acyclic data and returns duplicates

If the recursive part of a WITH RECURSIVE ... UNION ALL query contains SELECT DISTINCT, MariaDB evaluates it not over the rows produced by the previous iteration but over the whole result accumulated so far. Every iteration therefore produces the same rows again, the working table never becomes empty, and the query does not terminate even when the data graph is acyclic. Only max_recursive_iterations stops the evaluation: the query returns an incomplete result with many duplicate rows and raises only warning 1931.

The same query without DISTINCT, or with UNION instead of UNION ALL, returns the correct result.

How to repeat

CREATE DATABASE IF NOT EXISTS cte_bug;
USE cte_bug;

DROP TABLE IF EXISTS edges;
CREATE TABLE edges (
parent CHAR(1) NOT NULL,
child CHAR(1) NOT NULL,
PRIMARY KEY (parent, child)
);
INSERT INTO edges VALUES ('a','b'), ('b','c'); – a -> b -> c, no cycles

SET SESSION max_recursive_iterations = 5;

WITH RECURSIVE h(id) AS (
SELECT CAST('a' AS CHAR(1))
UNION ALL
SELECT DISTINCT e.child FROM edges e JOIN h ON e.parent = h.id
)
SELECT id, COUNT AS cnt FROM h GROUP BY id ORDER BY id;

SHOW WARNINGS;

Expected result

On each iteration the recursive part is applied to the rows produced by the previous iteration:

->

->

-> {}. The query terminates after three iterations:
---------+

---------+

---------+

MariaDB returns exactly this result for the same query without DISTINCT and for the query with UNION. PostgreSQL 17.6 and SQLite 3.46.1 return it with DISTINCT as well.

Actual result

---------+

---------+

---------+
Warning 1931: Query execution was interrupted. The query exceeded
max_recursive_iterations = 5. The query result may be incomplete

With the default max_recursive_iterations = 1000 the query returns a - 1, b - 1000, c - 999, i.e. 2000 rows instead of 3, with the same warning.

Analysis
The result matches exactly the following scheme: on each iteration the recursive part receives the whole accumulated result, and DISTINCT removes duplicates only from its own output.

The output of the recursive part never becomes empty, so the recursion never stops.

The same happens on a branching acyclic graph a -> b, a -> c, b -> d, c -> d, d -> e:

DELETE FROM edges;
INSERT INTO edges VALUES ('a','b'), ('a','c'), ('b','d'), ('c','d'), ('d','e');

SET SESSION max_recursive_iterations = 4;
WITH RECURSIVE h(id) AS (
SELECT CAST('a' AS CHAR(1))
UNION ALL
SELECT DISTINCT e.child FROM edges e JOIN h ON e.parent = h.id
)
SELECT id, COUNT AS cnt FROM h GROUP BY id ORDER BY id;

Actual: a - 1, b - 4, c - 4, d - 3, e - 2 (with a limit of 3 iterations: 1, 3, 3, 2, 1). Expected, if DISTINCT removes duplicates within a single iteration: a - 1, b - 1, c - 1, d - 1, e - 1, and the query terminates by itself. PostgreSQL 17.6 returns exactly this result. Without DISTINCT MariaDB correctly returns a - 1, b - 1, c - 1, d - 2, e - 2.

Control queries (correct results in MariaDB)

– without DISTINCT: a, b, c once each
WITH RECURSIVE h(id) AS (
SELECT CAST('a' AS CHAR(1))
UNION ALL
SELECT e.child FROM edges e JOIN h ON e.parent = h.id
)
SELECT id, COUNT AS cnt FROM h GROUP BY id ORDER BY id;

– UNION + DISTINCT: a, b, c once each
WITH RECURSIVE h(id) AS (
SELECT CAST('a' AS CHAR(1))
UNION
SELECT DISTINCT e.child FROM edges e JOIN h ON e.parent = h.id
)
SELECT id, COUNT AS cnt FROM h GROUP BY id ORDER BY id;

Impact

• A query that is correct for acyclic data does not terminate.
• The user gets a wrong result with duplicates. Applications and drivers often do not handle warning 1931, so the error is easy to miss.
• MySQL 8.4 rejects DISTINCT in the recursive part with an error. Since MariaDB accepts this construct, its semantics should match the query without DISTINCT, with duplicate elimination only within a single iteration, as in PostgreSQL.