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
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.
    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

    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
which returns a row and shows a better execution plan :-)


JerryLusa 23 posts Joined 08/13
21 Oct 2015

Thanks.  Ticket opened.

Jerry L.

You must sign in to leave a comment.