Verified on the v0330 build installed on a workstation. This problem is now fixed: >>create schema mytest1; --- SQL operation complete. >>set schema mytest1; --- SQL operation complete. >> >>create library qa_udf_lib file '/designs/seaquest/wtsai/udf_bugs/udf_bug_LP142 6605/myudf.so'; --- SQL operation complete. >> >>create function MYUDF +>(INVAL int) +>returns (OUTVAL int) +>language c +>parameter style sql +>external name 'myudf' +>library qa_udf_lib +>deterministic +>state area size 1024 +>allow any parallelism +>no sql; --- SQL operation complete. >> >>Create table region ( +>r_regionkey int not null not droppable, +>r_name char(25) not null not droppable, +>r_comment varchar(152) not null not droppable, +>primary key (r_regionkey) not droppable) +>store by primary key salt using 8 partitions; --- SQL operation complete. >> >>Create table nation ( +>n_nationkey int not null not droppable, +>n_name char(25) not null not droppable, +>n_regionkey int not null not droppable, +>n_comment varchar(152) not null not droppable, +>primary key (n_nationkey) not droppable) +>store by primary key salt using 8 partitions; --- SQL operation complete. >> >>Create table supplier ( +>s_suppkey int not null not droppable, +>s_name char(25) not null not droppable, +>s_address varchar(40) not null not droppable, +>s_nationkey int not null not droppable, +>s_phone char(15) not null not droppable, +>s_acctbal numeric(12,2) not null not droppable, +>s_comment varchar(101) not null not droppable, +>primary key (s_suppkey) not droppable) +>store by primary key salt using 8 partitions; --- SQL operation complete. >> >>Create table part ( +>p_partkey int not null not droppable, +>p_name varchar(55) not null not droppable, +>p_mfgr char(25) not null not droppable, +>p_brand char(10) not null not droppable, +>p_type varchar(25) not null not droppable, +>p_size int not null not droppable, +>p_container char(10) not null not droppable, +>p_retailprice numeric(12,2) not null not droppable, +>p_comment varchar(23) not null not droppable, +>primary key (p_partkey) not droppable) +>store by primary key salt using 8 partitions; --- SQL operation complete. >> >>Create table partsupp ( +>ps_partkey int not null not droppable, +>ps_suppkey int not null not droppable, +>ps_availqty int not null not droppable, +>ps_supplycost numeric(12,2) not null not droppable, +>ps_comment varchar(199) not null not droppable, +>primary key (ps_partkey,ps_suppkey) not droppable) +>store by primary key salt using 8 partitions; --- SQL operation complete. >> >>prepare x1 from select +>count(*) +>from +>part, +>supplier, +>partsupp, +>nation, +>region +>where +>p_partkey = ps_partkey +>and s_suppkey = ps_suppkey +>and p_size = 5 +>and p_type like '%NICKEL' +>and s_nationkey = n_nationkey +>and n_regionkey = r_regionkey +>and r_name = 'AMERICA' +>and ps_supplycost = ( +>select +>min(ps_supplycost) +>from +>partsupp, +>supplier, +>nation, +>region +>where +>p_partkey = ps_partkey +>and s_suppkey = ps_suppkey +>and s_nationkey = n_nationkey +>and n_regionkey = r_regionkey +>and r_name = 'AMERICA' +>); --- SQL command prepared. >> >>explain options 'f' x1; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 26 . 27 root 1.00E+000 25 . 26 sort_scalar_aggr 1.00E+000 24 1 25 hybrid_hash_join 3.20E+001 23 2 24 hybrid_hash_join 8.00E+000 22 3 23 hybrid_hash_join 4.00E+000 21 4 22 hybrid_hash_join 2.00E+000 20 . 21 hash_partial_groupby 1.00E+000 19 . 20 esp_exchange 1:5(hash2) 1.00E+000 18 . 19 hash_partial_groupby 1.00E+000 17 6 18 hybrid_hash_join 1.25E+006 16 8 17 hybrid_hash_join 2.50E+005 15 10 16 hybrid_hash_join 5.00E+003 14 12 15 hybrid_hash_join 1.00E+002 13 . 14 esp_exchange 5(hash2):4(hash2) 1.00E+002 . . 13 trafodion_scan PARTSUPP 1.00E+002 11 . 12 esp_exchange 5(rep-b):4(hash2) 1.00E+000 . . 11 trafodion_scan PART 1.00E+000 9 . 10 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 9 trafodion_scan SUPPLIER 1.00E+002 7 . 8 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 7 trafodion_scan NATION 1.00E+002 5 . 6 esp_exchange 5(rep-b):4(hash2) 1.00E+001 . . 5 trafodion_scan REGION 1.00E+001 . . 4 trafodion_scan PARTSUPP 1.00E+002 . . 3 trafodion_scan SUPPLIER 1.00E+002 . . 2 trafodion_scan NATION 1.00E+002 . . 1 trafodion_scan REGION 1.00E+001 --- SQL operation complete. >>execute x1; (EXPR) -------------------- 0 --- 1 row(s) selected. >> >>prepare x2 from select +>count(*) +>from +>part, +>supplier, +>partsupp, +>nation, +>region +>where +>p_partkey = ps_partkey +>and s_suppkey = ps_suppkey +>and myudf(p_size) = myudf(5) +>and p_type like '%NICKEL' +>and s_nationkey = n_nationkey +>and n_regionkey = r_regionkey +>and r_name = 'AMERICA' +>and ps_supplycost = ( +>select +>min(ps_supplycost) +>from +>partsupp, +>supplier, +>nation, +>region +>where +>p_partkey = ps_partkey +>and myudf(s_suppkey) = myudf(ps_suppkey) +>and s_nationkey = n_nationkey +>and n_regionkey = r_regionkey +>and r_name = 'AMERICA' +>); --- SQL command prepared. >> >>explain options 'f' x2; LC RC OP OPERATOR OPT DESCRIPTION CARD ---- ---- ---- -------------------- -------- -------------------- --------- 43 . 44 root 1.00E+000 42 . 43 sort_partial_aggr_ro 1.00E+000 41 . 42 esp_exchange 1:5(hash2) 1.00E+000 40 . 41 sort_partial_aggr_le 1.00E+000 39 2 40 hybrid_hash_join 4.68E+012 38 4 39 hybrid_hash_join 9.37E+011 37 6 38 hybrid_hash_join 1.87E+010 36 8 37 hybrid_hash_join 3.75E+008 35 . 36 hash_groupby 1.50E+007 34 10 35 hybrid_hash_join 1.50E+007 33 12 34 hybrid_hash_join 3.00E+006 32 18 33 hybrid_hash_join 6.00E+004 31 20 32 hybrid_hash_join u 6.00E+002 28 30 31 nested_join 3.00E+002 29 . 30 probe_cache 1.00E+000 . . 29 isolated_scalar_udf MYUDF 1.00E+000 27 22 28 hybrid_hash_join 3.00E+002 24 26 27 nested_join 6.00E+000 25 . 26 probe_cache 1.00E+000 . . 25 isolated_scalar_udf MYUDF 1.00E+000 23 . 24 esp_exchange 5(hash2):4(hash2) 6.00E+000 . . 23 trafodion_scan PART 6.00E+000 21 . 22 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 21 trafodion_scan PARTSUPP 1.00E+002 19 . 20 esp_exchange 5(rep-b):1 1.00E+000 . . 19 isolated_scalar_udf MYUDF 1.00E+000 17 . 18 esp_exchange 5(rep-b):5(hash2) 1.00E+002 14 16 17 nested_join 1.00E+002 15 . 16 probe_cache 1.00E+000 . . 15 isolated_scalar_udf MYUDF 1.00E+000 13 . 14 esp_exchange 5(hash2):4(hash2) 1.00E+002 . . 13 trafodion_scan SUPPLIER 1.00E+002 11 . 12 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 11 trafodion_scan NATION 1.00E+002 9 . 10 esp_exchange 5(rep-b):4(hash2) 1.00E+001 . . 9 trafodion_scan REGION 1.00E+001 7 . 8 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 7 trafodion_scan PARTSUPP 1.00E+002 5 . 6 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 5 trafodion_scan SUPPLIER 1.00E+002 3 . 4 esp_exchange 5(rep-b):4(hash2) 1.00E+002 . . 3 trafodion_scan NATION 1.00E+002 1 . 2 esp_exchange 5(rep-b):4(hash2) 1.00E+001 . . 1 trafodion_scan REGION 1.00E+001 --- SQL operation complete. >>execute x2; (EXPR) -------------------- 0 >>execute x2; (EXPR) -------------------- 0 --- 1 row(s) selected. >> >>drop function myudf cascade; --- SQL operation complete. >>drop library qa_udf_lib cascade; --- SQL operation complete. >>drop schema mytest1 cascade;