All Forums Database
JerryLusa 23 posts Joined 08/13
17 Sep 2015
Row not returned when percentile_cont() only sees null values, even when other columns would have data

Any ideas why percentile_cont() will prevent a row from returning when the source rows (the 'group') all contain null values?  I expect to see the row with perhaps NULL in that column.  Teradata Release 14.10.02 Version 14.10.02.08f
Here's some SQL that demonstrates the problem.  It uses a table with one row (zz_OneRow in this case) as a skeleton to create artificial values that are unioned.  Any one-row table will work here.
note:  change either of the ColWithAllNulls values to non-null value and you get a row. When both are null, no row.
select
    C.NameCol,
    count(C.ColWithNonNulls) as ColWithNonNulls_n,
    average(C.ColWithNonNulls) as ColWithNonNulls_Avg,
    percentile_cont(.50) within group (order by C.ColWithNonNulls) as ColWithNonNulls_50pct,
   
    count(C.ColWithAllNulls) as ColWithAllNulls_n,
    average(C.ColWithAllNulls) as ColWithAllNulls_Avg,
    percentile_cont(.50) within group (order by C.ColWithAllNulls) as ColWithAllNulls_50pct,
    1 as TheEnd

from
(
    select '1' as NameCol, null as ColWithAllNulls, 1 as ColWithNonNulls from zz_OneRow
    union all
    select '1' as NameCol, null as ColWithAllNulls, 1 as ColWithNonNulls from zz_OneRow

) as C

group by NameCol

Jerry L.
dnoeth 4628 posts Joined 11/04
24 Sep 2015

Hi Jerry,
Explain reveals that NULLs are filtered before calculation.
This seems to be a bug, you might open an incdent with Teradata's Customer Service.
 
For workaround you might the query from
Missing Functions: PERCENTILE_DISC, PERCENTILE_CONT & MEDIAN
which returns a row and shows a better execution plan :-)

Dieter

JerryLusa 23 posts Joined 08/13
21 Oct 2015

Thanks.  Ticket opened.

Jerry L.

You must sign in to leave a comment.