Hi Martin,
When calling each function individually the result is correct, the problem occurs only when 2 calls are made within the same query.
Please find below the sample code (simplified to scalar version not Bat version) to reproduce what I am facing concerning variable name scope in functions:
The version is Jan2022-SP1 (V11.43.9.).
MAL code
module udf;
inline function halfofyearbracket(pdate:date):str;
y:= mtime.date_to_str(pdate, "%YH");
hybm:= mtime.month(pdate);
hyb:= calc.-(hybm, 1);
hyb:= calc./(hyb, 6);
hyb:= calc.+(hyb, 1);
hybs:= calc.str(hyb);
return halfofyearbracket:= calc.+(y, hybs);
end halfofyearbracket;
inline function halfofyear(pdate:date):int;
hym:= mtime.month(pdate);
hy:= calc.-(hym, 1);
hy:= calc./(hy, 6);
hy:= calc.+(hy, 1);
return halfofyear:= hy;
end halfofyear;
SQL code
CREATE OR REPLACE FUNCTION halfofyearbracket(pdate date)
returns string external name udf.halfofyearbracket;
CREATE OR REPLACE FUNCTION halfofyear(pdate date)
returns integer external name udf.halfofyear;
Test Query
trace
select halfofyearbracket('2015-11-23') as halfofyearbracket, halfofyear('2015-11-23') as halfofyear
+-------------------+------------+
| halfofyearbracket | halfofyear |
+===================+============+
| 2015H2 | 1 |
+-------------------+------------+
statement
X_1=0@0:void := querylog.define("trace\nselect \n halfofyearbracket(\\'2015-11-23\\') as halfofyearbracket,\n halfofyear(\\'2015-11-23\\') as halfofyear\n;":str, "default_pipe":str, 14:int);
X_64=10:int := calc.-(11:int, 1:int);
X_64=1:int := calc./(X_64=1:int, 6:int);
X_64=2:int := calc.+(X_64=2:int, 1:int);
X_67="2":str := calc.str(X_64=2:int);
X_9="2015H2":str := calc.+("2015H":str, X_67="2":str);
X_64=0:int := calc./(X_64=0:int, 6:int);
X_64=1:int := calc.+(X_64=1:int, 1:int);
X_13=[2]:bat[:str] := bat.pack(".":str, ".":str);
X_15=[2]:bat[:str] := bat.pack("clob":str, "int":str);
X_17=[2]:bat[:int] := bat.pack(0:int, 0:int);
X_14=[2]:bat[:str] := bat.pack("halfofyearbracket":str, "halfofyear":str);
X_16=[2]:bat[:int] := bat.pack(0:int, 32:int);
barrier X_85=false:bit := language.dataflow();
X_12=11:int := sql.resultSet(X_13=[2]:bat[:str], X_14=[2]:bat[:str], X_15=[2]:bat[:str], X_16=[2]:bat[:int], X_17=[2]:bat[:int], X_9="2015H2":str, X_64=1:int);
As you notice, variable X_64 is shared between the 2 function calls and the calculation for the second function resumes calculation on the modified variables from the previous call instead of resetting it to the month value.
And this is happening although the variable names are different in the function definition.
Please note that changing the date in the second function return a correct result:
trace
select halfofyearbracket('2015-11-23') as halfofyearbracket, halfofyear('2015-12-24') as halfofyear
statement
X_1=0@0:void := querylog.define("trace\nselect \n halfofyearbracket(\\'2015-11-23\\') as halfofyearbracket,\n halfofyear(\\'2015-12-24\\') as halfofyear\n;":str, "default_pipe":str, 14:int);
X_65=10:int := calc.-(11:int, 1:int);
X_65=1:int := calc./(X_65=1:int, 6:int);
X_65=2:int := calc.+(X_65=2:int, 1:int);
X_68="2":str := calc.str(X_65=2:int);
X_9="2015H2":str := calc.+("2015H":str, X_68="2":str);
X_72=11:int := calc.-(12:int, 1:int);
X_72=1:int := calc./(X_72=1:int, 6:int);
X_72=2:int := calc.+(X_72=2:int, 1:int);
X_17=[2]:bat[:int] := bat.pack(0:int, 32:int);
X_18=[2]:bat[:int] := bat.pack(0:int, 0:int);
X_15=[2]:bat[:str] := bat.pack("halfofyearbracket":str, "halfofyear":str);
X_16=[2]:bat[:str] := bat.pack("clob":str, "int":str);
X_14=[2]:bat[:str] := bat.pack(".":str, ".":str);
barrier X_84=false:bit := language.dataflow();
X_13=19:int := sql.resultSet(X_14=[2]:bat[:str], X_15=[2]:bat[:str], X_16=[2]:bat[:str], X_17=[2]:bat[:int], X_18=[2]:bat[:int], X_9="2015H2":str, X_72=2:int);
As you notice here we have 2 separate variables X_65 and X_72 for the calculation.
Thank you.