Bug #8316
Documentation/Behaviour for Nested Queries and Record-Store confusing
0%
Description
We got hit by a bug in our setup today, caused by the fact that an Store-Variable will remember the previous subquery.
e.g.
Given
Tabel: persons pid | name ----+---------- 1 | Carsten 2 | Nicola Table: addresses pid | address ----+--------- 1 | foo
Then10.sql = SELECT pid AS _pid FROM persons
10.2.sql = SELECT address AS _adr FROM addresses WHERE pid={{pid:R}}
10.3.sql = SELECT {{pid:R}}, {{adr:RE}}
prints1 foo
2 foo
even though there is no address defined for 2, as {{adr}} still contains the result from the last iteration (pid = 1). This behavior is mentioned with The STORE_RECORD will always be merged with previous content. But it is nevertheless unexpected for a loop - and the example in the documentation is not very helpful either (I still don't understand what the explanation wants to tell me)
So the correct setup is10.sql = SELECT pid AS _pid FROM persons
10.1.sql = SELECT '' AS adr
10.2.sql = SELECT address AS _adr FROM addresses WHERE pid={{pid:R}}
10.3.sql = SELECT {{pid:R}}, {{adr:RE}}
History
#1
Updated by Carsten Rose about 1 month ago
Updated by - Status changed from New to Feedback
- Assignee set to Carsten Rose
Hallo Nicola
Das Verhalten ist in der Tat gewoehnungsbeduerftig. Ich werde die Doku anpassen und darauf hinweisen.
Eine etwas kuerze Variante fuer
10.sql = SELECT pid AS _pid FROM persons
10.1.sql = SELECT '' AS adr
10.2.sql = SELECT address AS _adr FROM addresses WHERE pid={{pid:R}}
10.3.sql = SELECT {{pid:R}}, {{adr:RE}}
waere
10.sql = SELECT pid AS _pid, '' AS adr FROM persons
10.2.sql = SELECT address AS _adr FROM addresses WHERE pid={{pid:R}}
10.3.sql = SELECT {{pid:R}}, {{adr:RE}}
Das entspricht dann dem 'Best Practice' Variablen immer erst zu initialisieren.
CU
Carsten