Hi Team, This is regarding null handling in Pinot. I have read about table based, column based and advanced query time null handling. My requirement is simple : Good old way of having nulls when there is no value for a column. Right now, I have enabled column based null handling, but it gives default values for non existent values for eg. -infinity for decimal type columns I tried advanced query based null handling along with column based null handling, but now it gives 0.0 for decimal types. How can I achieve simple nulls when there is no value for a column ie. null for all data types if there is no value for that column in the row?

Are you filtering out using IS Null?

Yes, and also other things like select avg(x), avg(y) from table considering x and/or y have -infinity as value if I query now, I get -infinity as avg. I would have liked if nulls would be ignored like sql. How can we do that?

There are a few things 1. Use metrics for x and y since metrics default value is 0. 2. In the where clause filter where x is not null and y is not null

1. Use metrics for x and y since metrics default value is 0. Won't it skew the average as we are counting zeroes now. 1. In the where clause filter where x is not null and y is not null What if only one of them is null

1. That’s why you need 2 2. You can use aggregation filter to filter out individually during aggregation Also tagging @Yash Mayya if there’s a better way for 2

If you're using null handling correctly, you don't need to worry about metric vs dimension behavior and nor do you need explicit filters. With null handling enabled at ingestion time (using column based null handling), and advanced query time null handling using

SET enableNullHandling = true;

, the behavior adheres to the SQL standard. If you're seeing values like

0.0

,

-infinity

etc. that indicates that either your null values are not being ingested correctly (as actual null values), or you're not actually enabling query time null handling using the query option.