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?
