QFQ

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

Then
10.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}}


prints
1 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 is
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}}