[
https://issues.apache.org/jira/browse/TRAFODION-1027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14714178#comment-14714178
]
Suresh Subbiah commented on TRAFODION-1027:
-------------------------------------------
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;
> LP Bug: 1426605 - UDF: query compilation with scalar UDFs returns internal
> assertion in ../common/BaseTypes.cpp
> ---------------------------------------------------------------------------------------------------------------
>
> Key: TRAFODION-1027
> URL: https://issues.apache.org/jira/browse/TRAFODION-1027
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Weishiun Tsai
> Assignee: Suresh Subbiah
> Priority: Blocker
> Fix For: 1.1 (pre-incubation)
>
>
> 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
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
