David Wayne Birdsall created TRAFODION-3257:
-----------------------------------------------
Summary: 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
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)
