BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Дата
Msg-id 17061-dd7f4825b7da3a9d@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      17061
Logged by:          Benoit Lobréau
Email address:      blo.talkto@gmail.com
PostgreSQL version: 14beta1
Operating system:   Fedora 33
Description:

Hi,

I try to access the field inside the tuple generated by SEARCH BREADTH
FIRST. 
A use case would be to get all the records with depth 3.

I fail to access it and I don't understand the error. I wonder if it works
as intended ?

Here is a test case :

DROP TABLE tree;
CREATE TABLE tree(id int, parent_id int, name text);
ALTER TABLE tree ADD PRIMARY KEY (id);
INSERT INTO tree(id, parent_id, name) 
VALUES (1, NULL, 'Albert'),
       (2, 1, 'Bob'),
       (3, 1, 'Barbara'),
       (4, 1, 'Britney'),
       (5, 3, 'Clara'),
       (6, 3, 'Clement'),
       (7, 2, 'Craig'),
       (8, 5, 'Debby'),
       (9, 5, 'Dave'),
       (10, 9, 'Edwin');

-- The following query shows that the fields of the tuple are named:
--
--      row_to_json
-- -----------------------
--  {"*DEPTH*":0,"id":1}
--  {"*DEPTH*":1,"id":2}
--  {"*DEPTH*":1,"id":3}
--  {"*DEPTH*":1,"id":4}
--  {"*DEPTH*":2,"id":5}
--  {"*DEPTH*":2,"id":6}
--  {"*DEPTH*":2,"id":7}
--  {"*DEPTH*":3,"id":8}
--  {"*DEPTH*":3,"id":9}
--  {"*DEPTH*":4,"id":10}
-- (10 rows)

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY id SET breadth
SELECT row_to_json(breadth)
FROM mtree m;

-- So I try to get the "*DEPTH*" column but it fails with (I get the same
error with (breadth).id) :
--
-- ERROR:  CTE m does not have attribute 3

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY id SET breadth
SELECT (breadth)."*DEPTH*"
FROM mtree m;

-- The following works but feels a little hacky

WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY id SET breadth
SELECT row_to_json(breadth) -> '*DEPTH*'
FROM mtree m;


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Pawel Kudzia
Дата:
Сообщение: Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #16792: silent corruption of GIN index resulting in SELECTs returning non-matching rows