[
https://issues.apache.org/jira/browse/TRAFODION-1027?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Subbiah closed TRAFODION-1027.
-------------------------------------
> 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)
