Hans Zeller created TRAFODION-2912:
--------------------------------------
Summary: Non-deterministic scalar UDFs not executed once per row
Key: TRAFODION-2912
URL: https://issues.apache.org/jira/browse/TRAFODION-2912
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Affects Versions: 2.0-incubating
Reporter: Hans Zeller
Assignee: Hans Zeller
Fix For: 2.3
This problem was found by Andy Yang.
Andy created a random generator scalar UDF and found that it did not return a
different random value for each row:
{noformat}
>>select scalar_rand_udf(), scalar_rand_udf()
+>from (values (1), (2), (3)) T(s);
RND RND
----------- -----------
846930886 1804289383
846930886 1804289383
846930886 1804289383
--- 3 row(s) selected.
>>
{noformat}
Here is the explain, it shows that we are using hash joins, not nested joins,
to evaluate the UDFs:
{noformat}
>>explain options 'f' s;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
5 . 6 root 3.00E+000
4 1 5 hybrid_hash_join 3.00E+000
3 2 4 hybrid_hash_join 1.00E+000
. . 3 isolated_scalar_udf SCALAR_RAND_UDF 1.00E+000
. . 2 isolated_scalar_udf SCALAR_RAND_UDF 1.00E+000
. . 1 tuplelist 3.00E+000
--- SQL operation complete.
>>
{noformat}
The problem is that we don't check for non-deterministic UDFs when we transform
a TSJ to a regular join in the transformer or normalizer. We don't even set the
non-deterministic flag in the group attributes of the IsolatedScalarUDF node.
The fix is to set this flag correctly and to add a check and not transform
routine joins for non-deterministic isolated scalar UDFs into a regular join.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
