UDF: query compilation with scalar UDFs returns internal assertion in ../common/BaseTypes.cpp

[Weishiun Tsai]

In the following example, x1 does not contain any UDF. x2 contains UDFs in its predicates. x1 and x2 are almost identical except for the 2 predicates in x1 that are replaced with myudf():

x1:
and p_size = 5
…
and s_suppkey = ps_suppkey

x2:
and myudf(p_size) = myudf(5)
….
and myudf(s_suppkey) = myudf(ps_suppkey)

myudf() is a simple scalar UDF that takes an INT value and returns the same INT value back again. Therefore, these 2 queries should return the same results as well. But as shown in the execution output, x1 compiles fine, x2 returns an internal assertion at the compile time:

*** ERROR[2006] Internal error: assertion failure () in file ../common/BaseTypes.cpp at line 118.

Tables used in this example all have 0 rows.

This is seen on the v0210 build installed on a workstation. To reproduce this problem:

(1) Download the attached tar file and untar it to get the 4 files in there. Put the 4 files in any directory <mydir>
(2) Run build.sh from <mydir> to build the UDF so file.
(3) Change the line ‘create library qa_udf_lib file '<mydir>/myudf.so'; in mytest.sql and fill in <mydir>
(4) From sqlci, obey mytest.sql

Here is the execution output:

>>create schema mytest1;

--- SQL operation complete.
>>set schema mytest1;

--- SQL operation complete.
>>
>> create library qa_udf_lib file '<mydir>/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'
+>);

*** ERROR[2006] Internal error: assertion failure () in file ../common/BaseTypes.cpp at line 118.

*** ERROR[8822] The statement was not prepared.

>>
>>explain options 'f' x2;

*** ERROR[8804] The provided input statement does not exist in the current context.

--- SQL operation failed with errors.
>>execute x2;

*** ERROR[15017] Statement X2 was not found.

>>
>>drop function myudf cascade;

--- SQL operation complete.
>>drop library qa_udf_lib cascade;

--- SQL operation complete.
>>drop schema mytest1 cascade;

--- SQL operation complete.
>>
>>exit;

End of MXCI Session

[Suresh Subbiah (suresh-subbiah)]

As a wokaround CQD SUBQUERY_UNNESTING 'OFF' can be used. A fix will be committed soon.

[Weishiun Tsai (wei-shiun-tsai)]

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,
+>suppli...