0

I have the following test table:

SELECT * FROM attempts order by id;

 id | user_id | name
----+---------+------
  1 |       1 | a
  2 |       2 | b
  3 |       1 | b
  4 |       1 | c
  5 |       2 | a
  6 |       1 | a
  7 |       1 | b
  8 |       1 | a
  9 |       3 | c
 10 |       4 | d
 11 |       3 | d
(11 rows)

I am trying to debug the following recursive query, so I print out each iteration id in the result set.

WITH RECURSIVE t(iter_id, id, user_id, name, aname, iname) AS (
  select 1, min(id) - 1, user_id, null, null, null as name from attempts group by user_id
UNION ALL
  select * from (
    with iter as (select * from t),
    t_min as (select min(id) as id from iter),
    next_id as (
      select min(attempts.id) as id
      from attempts
      left join iter using(name)
      where attempts.id > (select id from t_min)
      and iter.name is null
    )
    select iter.iter_id + 1, next_id.id, iter.user_id, coalesce(attempts.name, iter.name) as name, attempts.name aname, iter.name as iname
    from iter cross join next_id left join attempts
    on (attempts.id = next_id.id)
    and (attempts.user_id = iter.user_id)
    where next_id.id is not null
  ) as t2
)
select *
from t
order by iter_id, user_id, id
;

 iter_id | id | user_id | name | aname | iname
---------+----+---------+------+-------+-------
       1 |  0 |       1 | NULL | NULL  | NULL
       1 |  1 |       2 | NULL | NULL  | NULL
       1 |  8 |       3 | NULL | NULL  | NULL
       1 |  9 |       4 | NULL | NULL  | NULL
       2 |  1 |       1 | a    | a     | NULL
       2 |  1 |       2 | NULL | NULL  | NULL
       2 |  1 |       3 | NULL | NULL  | NULL
       2 |  1 |       4 | NULL | NULL  | NULL
       3 |  2 |       1 | a    | NULL  | a
       3 |  2 |       2 | b    | b     | NULL
       3 |  2 |       3 | NULL | NULL  | NULL
       3 |  2 |       4 | NULL | NULL  | NULL
       4 |  4 |       1 | c    | c     | a
       4 |  4 |       2 | b    | NULL  | b
       4 |  4 |       3 | NULL | NULL  | NULL
       4 |  4 |       4 | NULL | NULL  | NULL
       5 |  5 |       1 | c    | NULL  | c
       5 |  5 |       2 | a    | a     | b
       5 |  5 |       3 | NULL | NULL  | NULL
       5 |  5 |       4 | NULL | NULL  | NULL
       6 |  7 |       1 | b    | b     | c
       6 |  7 |       2 | a    | NULL  | a
       6 |  7 |       3 | NULL | NULL  | NULL
       6 |  7 |       4 | NULL | NULL  | NULL
       7 |  9 |       1 | b    | NULL  | b
       7 |  9 |       2 | a    | NULL  | a
       7 |  9 |       3 | c    | c     | NULL
       7 |  9 |       4 | NULL | NULL  | NULL
       8 | 10 |       1 | b    | NULL  | b
       8 | 10 |       2 | a    | NULL  | a
       8 | 10 |       3 | c    | NULL  | c
       8 | 10 |       4 | d    | d     | NULL
(32 rows)

I do not understand why on iteration number 6, the next attempt id becomes 7 instead of 6. To debug this step I created a temporary table with the values representing the recursive step number 5:

select * from tmp;

 id | user_id | name
----+---------+------
  5 |       1 | c
  5 |       2 | b
  5 |       3 | NULL
  5 |       4 | NULL
(4 rows)

When I run the same query against tmp table, I get the result that I expect.

with iter as (select * from tmp),
t_min as (select min(id) as id from iter),
next_id as (
  select min(attempts.id) as id
  from attempts
  left join iter using(name)
  where attempts.id > (select id from t_min)
  and iter.name is null
)
select next_id.id, iter.user_id, coalesce(attempts.name, iter.name) as name, attempts.name aname, iter.name as iname
from iter cross join next_id left join attempts
on (attempts.id = next_id.id)
and (attempts.user_id = iter.user_id)
where next_id.id is not null
;

 id | user_id | name | aname | iname
----+---------+------+-------+-------
  6 |       1 | a    | a     | c
  6 |       2 | b    | NULL  | b
  6 |       3 | NULL | NULL  | NULL
  6 |       4 | NULL | NULL  | NULL
(4 rows)

Why there is such discrepancy? Is it a possible bug in PostgreSQL?

1 Answer 1

2

You are LEFT JOINing attempts and iter using(name). Name is the first of your 3 text columns and in iteration 5 it contains c and a, not c and b, which are instead on the last column you named iname.

5
  • @user1602 the using part IS relevant. You LEFT join attempts to the previous iteration using attempt.name = iter.name (and NOT iter.iname). You then keep only the rows where iter.name is null, which means you discard the matches and keep the ones which didn't match). So you discard attempts 6-1-a, 8-1-a and 9-3-c and keep 7-1-b, 10-4-d, 11-3-d, so minimum is 7, not 6.
    – Andrea B.
    Mar 8, 2023 at 14:46
  • @user1602 The temporary table is NOT a correct reproduction of what happens in the recursive query in iteration 6. Because in the temporary table you have c and b, while in the recursive query, in iteration 6, you reference the 'name' column of iteration 5, which has letters c and a.
    – Andrea B.
    Mar 8, 2023 at 16:25
  • 1
    I finally got it. Thank you for your help. Everything makes sense now :-)
    – user1602
    Mar 8, 2023 at 16:30
  • Do you think the next_id left join is hard to understand? Should it be re-written with: with next_id as ( select min(a.id) as id from attempts a where a.id > (select min(id) as id from iter) and not exists (select * from iter where iter.name = a.name) )
    – user1602
    Mar 9, 2023 at 5:51
  • @user1602 Hard to say. Your question was about the discrepancy in your two queries. If you want help on rewriting the whole thing you should provide a lot more information on what the table contains and what you want to achieve with the final query you were trying to debug.
    – Andrea B.
    Mar 9, 2023 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.