pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: blo.talkto@gmail.com
Subject: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Date: Thu, 17 Jun 2021 11:44:28 +0000
Message-ID: <17061-dd7f4825b7da3a9d@postgresql.org> (raw)

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;



view thread (8+ messages)  latest in thread

Message-ID: <17061-dd7f4825b7da3a9d@postgresql.org>
Permalink:  ../17061-dd7f4825b7da3a9d@postgresql.org/
Also on:    postgresql.org/message-id/17061-dd7f4825b7da3a9d@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, blo.talkto@gmail.com
  Subject: Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
  In-Reply-To: <17061-dd7f4825b7da3a9d@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox