Project

General

Profile

Bug #8316

Documentation/Behaviour for Nested Queries and Record-Store confusing

Added by Nicola Chiapolini about 1 month ago. Updated about 1 month ago.

Status:
Feedback
Priority:
Normal
Assignee:
Target version:
-
Start date:
12.05.2019
Due date:
% Done:

0%

Estimated time:
Discuss:

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

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}}

History

#1 Updated by Carsten Rose about 1 month ago

  • 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

Also available in: Atom PDF