CTE执行结果与MySQL不一致

[Learner113]

SQL文本

WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
  SELECT 1, 0, 1 
  UNION ALL
  SELECT n + 1, next_fib_n, fib_n + next_fib_n
    FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci
union all
SELECT n + 1, next_fib_n, fib_n + next_fib_n
	FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;

测试环境

MySQL

MySQL 版本

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

结果

mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    -> (
    -> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    -> (
    ->   SELECT 1, 0, 1
    ->   UNION ALL
    ->   SELECT n + 1, next_fib_n, fib_n + next_fib_n
    ->     FROM fibonacci WHERE n < 10
    -> )
    -> SELECT * FROM fibonacci
    -> union all
    -> SELECT n + 1, next_fib_n, fib_n + next_fib_n
    -> FROM fibonacci WHERE n < 10
    -> )
    -> SELECT * FROM fibonacci;
+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+
19 rows in set (0.00 sec)

OceanBase

OB版本(MySQL模式)

执行结果

问题

请问这个是OB暂时不支持这种写法，还是一个BUG？

[王利博]

语法不同 参考obCTE语法 OceanBase分布式数据库-海量数据 笔笔算数

[昱旻]

可能是同名 RCTE 解析的 bug，可以换名字区分下试试

[Learner113]

换了一下第二个递归CTE的表名，结果与MySQL一致。

MySQL结果

mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    -> (
    -> WITH RECURSIVE cte (n, fib_n, next_fib_n) AS
    -> (
    ->   SELECT 1, 0, 1
    ->   UNION ALL
    ->   SELECT n + 1, next_fib_n, fib_n + next_fib_n
    ->     FROM cte WHERE n < 10
    -> )
    -> SELECT * FROM cte
    -> union all
    -> SELECT n + 1, next_fib_n, fib_n + next_fib_n
    -> FROM fibonacci WHERE n < 10
    -> )
    -> SELECT * FROM fibonacci;
+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
|   10 |    34 |         55 |
+------+-------+------------+
55 rows in set (0.00 sec)

OB结果

…

[Learner113]

好的，谢谢