[jira] [Assigned] (TRAFODION-3257) SPLIT_PART behavior for null values is inconsistent

["zhang.lei (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-3257?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

zhang.lei reassigned TRAFODION-3257:
------------------------------------

    Assignee: zhang.lei

> SPLIT_PART behavior for null values is inconsistent
> ---------------------------------------------------
>
>                 Key: TRAFODION-3257
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-3257
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.4
>            Reporter: David Wayne Birdsall
>            Assignee: zhang.lei
>            Priority: Major
>
> The following script demonstrates the inconsistencies:
> >>drop table if exists temp1;
> --- SQL operation complete.
> >>create table temp1(a int not null, b char(40), c char(1), d int);
> --- SQL operation complete.
> >>insert into temp1 values (1,'hi there!','i',1),
> +> (2,null,'i',1),
> +> (3,'hi there!',null,1),
> +> (4,'hi there!','i',null);
> --- 4 row(s) inserted.
> >>
> >>select split_part('hi there!','i',1) from dual;
> (EXPR) 
> ---------
> h
> --- 1 row(s) selected.
> >>
> >>select split_part(null,'i',1) from dual;
> *** ERROR[4097] A NULL operand is not allowed in function (SPLIT_PART(NULL, 'i', 1)).
> *** ERROR[8822] The statement was not prepared.
> >>
> >>select split_part('hi there!',null,1) from dual;
> *** ERROR[4097] A NULL operand is not allowed in function (SPLIT_PART('hi there!', NULL, 1)).
> *** ERROR[8822] The statement was not prepared.
> >>
> >>select split_part('hi there!','i',null) from dual;
> *** ERROR[4097] A NULL operand is not allowed in function (SPLIT_PART('hi there!', 'i', NULL)).
> *** ERROR[8822] The statement was not prepared.
> >>
> >>select a,split_part(b,c,d) from temp1;
> A (EXPR) 
> ----------- ----------------------------------------
> 1 h 
>  2 ? 
>  3 ? 
>  4 ?
> --- 4 row(s) selected.
> >>
> So, if NULL is explicitly specified, we get error 4097. But if a null value is passed via a column, split_part returns null. 
> Almost all functions in Trafodion SQL return null in both cases. So I think the error 4097 behavior is incorrect; instead the statement should compile and split_part should return null.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)