Recursive CTE Postgres vs MySQL(MariaDB)

Простая задача построение дерева с динамическим количеством уровней вложенности и возможностью настраиваемой сортировки ветвей и листьев 

**Schema (PostgreSQL v12)**

create table catalog
    (id int, parent int, name text, ordered int);
    
insert into catalog (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 3),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (7, 2, 'child3-root2', 30),
    (8, 2, 'child1-root2', 10),
    (9, 2, 'child2-root2', 20)
    ;
---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, (row_number() over (partition by parent order by ordered))::text path from catalog c1 where c1.parent is null
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, row_number() over (partition by c2.parent order by c2.ordered)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 1       | 1   | 1    |
| 4   | 3      | child1-root1  | 1       | 2   | 11   |
| 6   | 3      | child2-root1  | 2       | 2   | 12   |
| 5   | 3      | child3-root1  | 3       | 2   | 13   |
| 2   |        | root2         | 8       | 1   | 2    |
| 8   | 2      | child1-root2  | 10      | 2   | 21   |
| 9   | 2      | child2-root2  | 20      | 2   | 22   |
| 7   | 2      | child3-root2  | 30      | 2   | 23   |
| 1   |        | root10        | 10      | 1   | 3    |
| 9   | 1      | child1-root10 | 10      | 2   | 31   |
| 8   | 1      | child2-root10 | 20      | 2   | 32   |
| 7   | 1      | child3-root10 | 30      | 2   | 33   |

С Postgres все ОК, в Oracle и MS SQL Server тоже все работает.

MySQL и MariaDB заявляют о поддержке рекурсивных обобщенных табличных выражений, но похоже не полностью.


with recursive
r as (
select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
  union all
select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path  from catalog c2, r where c2.parent = r.id
)
select * from r order by path

MySQL 8.0.12
Query Error: Error: UNKNOWN_CODE_PLEASE_REPORT: Recursive Common Table Expression 'r' can contain neither aggregation nor window functions in recursive query block
---
MariaDB 10.4.12-MariaDB
ER_NOT_STANDARD_COMPLIANT_RECURSIVE (conn=2466, no: 4008, SQLState: HY000) Restrictions imposed on recursive definitions are violated for table 'r' sql: with recursive r as ( select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null union all select c2.*, r.lvl + 1 lvl, concat(r.path, convert(row_number() over (partition by c2.parent order by c2.ordered), char)) path from catalog c2, r where c2.parent = r.id ) select * from r order by path - parameters:[]

А вот если из второго запроса убрать оконную функцию row_number, и немного изменив логику формирования пути сортировки, то получим нужный результат.


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered int);
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 3),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (10, 2, 'child3-root2', 30),
    (11, 2, 'child1-root2', 10),
    (12, 2, 'child2-root2', 20),
    (13, 8, 'child1-child2-root10', 2),
    (14, 8, 'child2-child2-root10', 3)
    ;    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name                 | ordered | lvl | path |
| --- | ------ | -------------------- | ------- | --- | ---- |
| 3   |        | root1                | 1       | 1   | 1    |
| 4   | 3      | child1-root1         | 1       | 2   | 11   |
| 6   | 3      | child2-root1         | 2       | 2   | 12   |
| 5   | 3      | child3-root1         | 3       | 2   | 13   |
| 2   |        | root2                | 8       | 1   | 2    |
| 11  | 2      | child1-root2         | 10      | 2   | 210  |
| 12  | 2      | child2-root2         | 20      | 2   | 220  |
| 10  | 2      | child3-root2         | 30      | 2   | 230  |
| 1   |        | root10               | 10      | 1   | 3    |
| 9   | 1      | child1-root10        | 10      | 2   | 310  |
| 8   | 1      | child2-root10        | 20      | 2   | 320  |
| 13  | 8      | child1-child2-root10 | 2       | 3   | 3202 |
| 14  | 8      | child2-child2-root10 | 3       | 3   | 3203 |
| 7   | 1      | child3-root10        | 30      | 2   | 330  |

UPDATE

Ан, нет, проглядел я, без использования оконной функции во втором подзапросе правильный «путь» не получить. В примере все хорошо, так как данные удачно легли.

А если, так, то беда


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered int);
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', 10),
    (2, null, 'root2', 8),
    (3, null, 'root1', 1),
    (4, 3, 'child1-root1', 1),
    (5, 3, 'child3-root1', 10),
    (6, 3, 'child2-root1', 2),
    (7, 1, 'child3-root10', 30),
    (8, 1, 'child2-root10', 20),
    (9, 1, 'child1-root10', 10),
    (7, 2, 'child3-root2', 30),
    (8, 2, 'child1-root2', 10),
    (9, 2, 'child2-root2', 20)
    ;
    
    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, convert(row_number() over (partition by parent order by ordered), char) path from catalog c1 where c1.parent is null  
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, convert(c2.ordered, char)) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 1       | 1   | 1    |
| 4   | 3      | child1-root1  | 1       | 2   | 11   |
| 5   | 3      | child3-root1  | 10      | 2   | 110  |
| 6   | 3      | child2-root1  | 2       | 2   | 12   |
| 2   |        | root2         | 8       | 1   | 2    |
| 8   | 2      | child1-root2  | 10      | 2   | 210  |
| 9   | 2      | child2-root2  | 20      | 2   | 220  |
| 7   | 2      | child3-root2  | 30      | 2   | 230  |
| 1   |        | root10        | 10      | 1   | 3    |
| 9   | 1      | child1-root10 | 10      | 2   | 310  |
| 8   | 1      | child2-root10 | 20      | 2   | 320  |
| 7   | 1      | child3-root10 | 30      | 2   | 330  |

---

UPDATE 2

Есть выход, но нужно будет немного усложнить механизм сохранения значений сортировки в разрезе ветви одного уровня. Например, добиваем «0»-ми до максимального кол-ва знаков в числе по ветви одного уровня


**Schema (MySQL v8.0)**

    create table catalog
    (id int, parent int, name text, ordered varchar(10));
    
    insert into catalog
    (id, parent, name, ordered)
    VALUES
    (1, null, 'root10', '10'),
    (2, null, 'root2', '08'),
    (3, null, 'root1', '01'),
    (4, 3, 'child1-root1', '01'),
    (5, 3, 'child3-root1', '10'),
    (6, 3, 'child2-root1', '02'),
    (7, 1, 'child3-root10', '30'),
    (8, 1, 'child2-root10', '20'),
    (9, 1, 'child1-root10', '10'),
    (7, 2, 'child3-root2', '30'),
    (8, 2, 'child1-root2', '10'),
    (9, 2, 'child2-root2', '20')
    ;
    
    

---

**Query #1**

    with recursive
    r as (
    select c1.*, 1 lvl, c1.ordered path from catalog c1 where c1.parent is null  
      union all
    select c2.*, r.lvl + 1 lvl, concat(r.path, c2.ordered) path  from catalog c2, r where c2.parent = r.id
    )
    select * from r
     order by path;

| id  | parent | name          | ordered | lvl | path |
| --- | ------ | ------------- | ------- | --- | ---- |
| 3   |        | root1         | 01      | 1   | 01   |
| 4   | 3      | child1-root1  | 01      | 2   | 0101 |
| 6   | 3      | child2-root1  | 02      | 2   | 0102 |
| 5   | 3      | child3-root1  | 10      | 2   | 0110 |
| 2   |        | root2         | 08      | 1   | 08   |
| 8   | 2      | child1-root2  | 10      | 2   | 0810 |
| 9   | 2      | child2-root2  | 20      | 2   | 0820 |
| 7   | 2      | child3-root2  | 30      | 2   | 0830 |
| 1   |        | root10        | 10      | 1   | 10   |
| 9   | 1      | child1-root10 | 10      | 2   | 1010 |
| 8   | 1      | child2-root10 | 20      | 2   | 1020 |
| 7   | 1      | child3-root10 | 30      | 2   | 1030 |

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *