Timeline for Why PostgreSQL RECURSIVE query iteration does not return the same result as manually created one?
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 9, 2023 at 16:50 | comment | added | Andrea B. | @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. | |
| Mar 9, 2023 at 5:51 | comment | added | user1602 | 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) ) | |
| Mar 8, 2023 at 16:31 | vote | accept | user1602 | ||
| Mar 8, 2023 at 16:30 | comment | added | user1602 | I finally got it. Thank you for your help. Everything makes sense now :-) | |
| Mar 8, 2023 at 16:25 | comment | added | Andrea B. | @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. | |
| Mar 8, 2023 at 14:46 | comment | added | Andrea B. | @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. | |
| Mar 8, 2023 at 12:53 | history | answered | Andrea B. | CC BY-SA 4.0 |