Comment on page
Null value support
By default, null handling is disabled (
nullHandlingEnabled=false) in the Table index configuration (tableIndexConfig). When null support is disabled,
IS NOT NULLevaluates to
IS NULLevaluates to
false. For example, the predicate in the query below matches all records.
select count(*) from my_table where column IS NOT NULL
When null support is enabled,
IS NOT NULLand
IS NULLevaluate to
falseaccording to whether a null is detected.
If you're not able to generate the null index for your use case, you may filter for null values using a default value specified in your schema or a specific value included in your query.
The following example queries work when the null value is not used in a dataset. Errors may occur if the specified null value is a valid value in the dataset.
- 2.To filter out the specified default null value, for example, you could write a query like the following:
select count(*) from my_table where column <> 'default_null_value'
Filter for a specific value in your query that will not be included in the dataset. For example, to calculate the average age, use
-1to indicate the value of
- Rewrite the following query:
select avg(Age) from my_table
- To cover null values as follows:
select avg(Age) from my_table WHERE Age <> -1
Under development to improve performance for advanced null handling.
Pinot provides advanced null handling support similar to standard SQL null handling. Because this feature carries a notable performance impact (even queries without null values), this feature is not enabled by default. For optimal query latency, we recommend enabling basic null support.
NULLhandling, do the following:
During real-time or offline ingestion, Pinot checks to see if null handling is enabled, and stores null values in the segment itself. Data ingested when null handling is disabled does not store null values, and should be ingested again.
nullHandlingEnabledconfiguration affects all columns in a Pinot table.
Column-level null support is under development.
By default, null usage in the predicate is disabled.
For handling nulls in aggregation functions, explicitly enable the null support by setting the query option
true. Configure this option in one of the following ways:
Set enableNullHandling=trueat the beginning of the query.
- 2.If using JDBC, set the connection option
enableNullHandling=true(either in the URL or as a property).
When this option is enabled, the Pinot query engine uses a different execution path that checks null predicates. Therefore, some indexes may not be usable, and the query is significantly more expensive. This is the main reason why null handling is not enabled by default.
If the query includes a
IS NOT NULLpredicate, Pinot fetches the
NULLvalue vector for the corresponding column within
FilterPlanNodeand retrieves the corresponding bitmap that represents all document IDs containing
NULLvalues for that column. This bitmap is then used to create a
BitmapBasedFilterOperatorto do the filtering operation.