COALESCE giving NULL on derived columns

Database
Teradata Employee

COALESCE giving NULL on derived columns

I am trying to execute a query in which derived columns are created and coalesce is applied to one of those derived columns.

But, its giving me null value, while we know coalesce is used to replace null values.

 

select 'active' as status,

 

case
when status = 'expired' then  else cast(fromDT as timestamp(0)) - interval '365' day
else cast(frmDTas timestamp(0))
end as frmTime,

case
when status = 'expired' then frmTime
else cast(toDT as timestamp(0)) + interval '1' day
end as toTime,

coalesce(
first_value(frmTime) over (partition by pNo , Obj_Id order by toTime asc, frmTime asc rows between 1 following and 1 following),
timestamp '9999-12-31 23:59:59'
) as nxtTime

from CCD.clsTblDetail
where pNo = 1122;

 

-- Obj_Id = 1, pNo = 1122

 

***** Coalesce part gives me nulls. *****

Dataset that i get is like this.

status,  fromTime,               toTime,                          nxtTime

active, 4/1/2018 00:00:00, 4/1/2019 00:00:00    12/31/9999 23:59:59
active, 4/1/2018 00:00:00, 4/1/2019 00:00:00     ?

 

Teradata Version is 15.10.07.04.

18 REPLIES
Junior Supporter

Re: COALESCE giving NULL on derived columns

Mobeen,

 

As is described in the manual, using a non-deterministic function (such as ordered analytical functions) may have unexpected results.

 

Change your SQL as below, should give the result you need;

first_value(coalesce(frmTime,timestamp '9999-12-31 23:59:59'))
over (partition by pNo , Obj_Id order by toTime asc, coalesce(frmTime,timestamp '9999-12-31 23:59:59') asc
rows between 1 following and 1 following) as nxtTime

 

Teradata Frank, Certified Master
Junior Contributor

Re: COALESCE giving NULL on derived columns

It doesn't matter if it deterministic or not, the outer COALESCE cannot return a NULL, otherwise it's a bug.

 

And your workaround will still return a NULL for the last row in a partition, because there's no next frmTime :-)

Teradata Employee

Re: COALESCE giving NULL on derived columns

Hi Frank,

 

Thanks for ther reply,  I just tried your workaround, but unfortunately its still giving me NULL values.

Dataset that i got now is like this. 

 

status, frmTime,                   toTime,                    nxtTime

active, 4/1/2018 00:00:00, 4/1/2019 00:00:00,   4/1/2018 00:00:00
active, 4/1/2018 00:00:00, 4/1/2019 00:00:00,   ?

 

One thing i have noticed is that at least it has given me one correct nxtTime value, because in my last dataset, it was giving me that default null replaced value "12/31/9999 23:59:59". 

Highlighted
Teradata Employee

Re: COALESCE giving NULL on derived columns

Hi dnoeth,

 

Thanks for the reply. I agree with you that it will give NULL for last row in the partition, that is why i have applied COALESCE on it.

Issue i am facing is that its showing me NULL value and even after applying COALESCE on it, i still get NULL value. 

 

Uptill now, i too myself feel that it seems to be a bug, but still i want to try around different things untill i am completely sure.

Junior Contributor

Re: COALESCE giving NULL on derived columns

I run a similar query on my old 15.10.02.06 VM and it returns the expected result.

Teradata Employee

Re: COALESCE giving NULL on derived columns

Well, our Teradata Version is 15.10.07.04, which is newer than yours. By expected do you mean that u got the dataset without NULLS ? If yes, then it appears to be a bug in the newer release and it should be raised with Teradata.

 

Junior Supporter

Re: COALESCE giving NULL on derived columns

Quoting from the TD15.10 SQL Functions, Operators, Expressions, and Predicates, page 494
Using a nondeterministic function, such as RANDOM, in a scalar_expression_n may have unexpected results, because if the rst calculation of scalar_expression_n is not NULL, the second calculation of that scalar_expression_n, which is returned as the value of the COALESCE expression, might be NULL.

 

Mobeen, your nxtTime must be NULL, as you specify "1following and 1 following". I think you want to use ;

first_value(coalesce(frmTime,timestamp '9999-12-31 23:59:59'))
over (partition by pNo , Obj_Id order by toTime asc, coalesce(frmTime,timestamp '9999-12-31 23:59:59') asc
rows between CURRENT ROW and 1 following) as nxtTime

Teradata Frank, Certified Master
Teradata Employee

Re: COALESCE giving NULL on derived columns

Hi Frank,

 

I just had a look on the same reference (TD15.10 SQL Functions, Operators, Expressions, and Predicates, page 494) as u mentioned. But, i still did not get this point, "first calculation of scalar_expression_n is not NULL, the second calculation of that scalar_expression_n, which is returned as the value of the COALESCE expression, might be NULL." I mean why it might be NULL and should we not use COALESCE in such type cases because of it's unexpected results.

 

Secondly, I put another outer COALESCE on the result value, but it is still giving me NULL.

Junior Supporter

Re: COALESCE giving NULL on derived columns

Mobeen,

 

Did you change the "between 1 following and 1 following" to "between current row and 1 following" ?

If not, on the last row the function has no rows and must return NULL.

 

Is your desired result, to only look at the "1 following" row ?

If so, you may have to try doing this with sub-select.

 

The 1st part of the CASE (generated in lower code for the COALESCE), executed before the FIRST_VALUE is resolved.

The 2nd part is evaluated after the FIRST_VALUE is resolved, and as such can return NULL.

Teradata Frank, Certified Master