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;
