Row not returned when percentile_cont() only sees null values, even when other columns would have data

Database

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

Tags (1)
2 REPLIES
N/A

Re: Row not returned when percentile_cont() only sees null values, even when other columns would have data

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 :-)

Re: Row not returned when percentile_cont() only sees null values, even when other columns would have data

Thanks.  Ticket opened.