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

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;

[ screwed up the cc somehow the first time, sorry for the duplicate ]

PG Bug reporting form <noreply@postgresql.org> writes:
> WITH RECURSIVE mtree(id, name) AS ( ...
> ) SEARCH BREADTH FIRST BY id SET breadth
> SELECT (breadth)."*DEPTH*"
> FROM mtree m;
> ERROR:  CTE m does not have attribute 3

Yeah, I get that with "SELECT (breadth).*" as well.  I'm not entirely sure
what this silly-looking syntax is supposed to mean, but it seems to be
adding an output column named "breadth" to the CTE.  The error is
occurring because said column has not been added to the relevant
CommonTableExpr struct.  Peter?

            regards, tom lane

On 17.06.21 13:44, PG Bug reporting form wrote:
> -- 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;

We could put a workaround for this into expandRecordVariable() to handle 
the extra columns similar to what we have in markTargetListOrigin(), but 
it's still not going to work, because then you'll get

ERROR:  record type has not been registered

This is similar to what you get now if you write something like

...
) SEARCH DEPTH FIRST BY id SET seq
SELECT (seq[1]).*
FROM mtree m;

It's not really meant to be used that way.  I'm not sure whether it's 
worth spending extra effort on.

> -- 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;

This works because it does not require type information for the fields 
in the row you're digging into (which is what the "registering" of the 
record type would accomplish).

On Tue, Jul 06, 2021 at 07:56:10PM +0200, Peter Eisentraut wrote:
> It's not really meant to be used that way.  I'm not sure whether it's worth
> spending extra effort on.

Peter has contacted the RTM about this issue, and we are classifying
this item as a "won't fix" per this argument.
--
Michael

On Thu, Jun 17, 2021 at 10:50:14AM -0400, Tom Lane wrote:
> [ screwed up the cc somehow the first time, sorry for the duplicate ]
> 
> PG Bug reporting form <noreply@postgresql.org> writes:
> > WITH RECURSIVE mtree(id, name) AS ( ...
> > ) SEARCH BREADTH FIRST BY id SET breadth
> > SELECT (breadth)."*DEPTH*"
> > FROM mtree m;
> > ERROR:  CTE m does not have attribute 3
> 
> Yeah, I get that with "SELECT (breadth).*" as well.  I'm not entirely sure
> what this silly-looking syntax is supposed to mean, but it seems to be
> adding an output column named "breadth" to the CTE.  The error is
> occurring because said column has not been added to the relevant
> CommonTableExpr struct.  Peter?
> 

Just to add fuel to the fire, I just noted that you cannot create a view
based on a recursive CTE using this syntax.

"""
create view v1 as
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 *
FROM mtree m;

ERROR:  column "breadth" has pseudo-type record
"""

-- 
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL

Jaime Casanova <jcasanov@systemguards.com.ec> writes:
> Just to add fuel to the fire, I just noted that you cannot create a view
> based on a recursive CTE using this syntax.
> ...
> ERROR:  column "breadth" has pseudo-type record

Yeah, I've run into that too.  There's no time to reconsider the
implementation for v14, but I'd sure like to see this revisited
later.  I'm kind of wondering if, instead of a single RECORD
column, we could add each of the SEARCH columns and the depth
column as a separate resjunk column.  (However, I'm not sure
how to extend that approach to the DEPTH FIRST case, which
wants an array.)

            regards, tom lane