[
https://issues.apache.org/jira/browse/TRAFODION-3257?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16743741#comment-16743741
]
zhang.lei commented on TRAFODION-3257:
--------------------------------------
Thanks for your reminding，after comparing the behavior of other databases, I
think you are right. So i will create new PR again after fixed it.
> 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)
