References → Null Handling
Introduction
Before the 2022.12.0 Cloud release and 6.0 On-Premises release, Incorta handled null values differently within the system. Null values were not considered true nulls, but instead real values. This means that, null values in numeric columns were considered zero values, in string columns empty strings, and in date columns the maximum Java date. This approach caused discrepancies in various aspects of Incorta, particularly between Spark-based objects (such as Materialized Views or MVs) and Incorta-native objects (physical schema tables, Incorta SQL tables, and Incorta Analyzer tables). The discrepancies also extended to other functionalities like filters, grouping, functions, and subtotals.
Null handling initiative
Incorta has initiated a project to ensure ANSI SQL compliance in handling null values, enhancing data accuracy and consistency across the platform. Incorta Analytics Service started to account for null values when sorting and filtering data in dashboards, insights, Analyzer tables, and Analyzer views based on physical columns. Incorta has also extended its support of handling null values to more functions and operations where the Analytics Service is handling data: in dashboards, insights, Analyzer tables, and business schema objects.
- The Null Handling feature is a preview feature for now. However, enabling this feature is strongly advised, with plans to make it default in future releases.
- As of the 2024.1.x releases, only Incorta Analytics Service accounts for null values in the supported contexts. Other components, such as Incorta Loader Service, session and global variables, and joins, do not account for null values.
- Starting 2024.7.x, the following accounts for null values:
- Supported functions and formulas in physical schemas, including formula columns and load filters (when the feature is enabled)
- Join and PK-index calculations (regardless of enabling or disabling the feature)
By distinguishing between null values and real values in the supported contexts, Incorta provides more accurate and meaningful results in your data analysis and reporting, ensuring that null values are handled consistently and appropriately in various operations.
Null value representation
Starting 2024.7.x, Incorta has improved the handling and representation of null values across various visualization components, ensuring clearer and more consistent data presentation.
- Tabular insights: Null values are consistently displayed as
null. - All Chart visualizations: Null values within string data types are visible within charts' dimensions as
null. - Scatter and Bubble insights: An option to plot null values as zero is available.
- The Analyzer: When creating or modifying Chart visualizations, the Analyzer shows an indicator of the presence of null values. It also displays the null values as
nullwhen creating or modifying Analyzer tables or views. - When drilling down using a NULL value, the system will use the
IS_NULLfilter instead ofIN.
- Incorta will represent null values whether or not the Null Handling option is enabled in the CMC.
- Due to a limitation in the highchart component, it's advisable to avoid using 'NULL' as a literal string. This ensures compatibility and prevents potential issues with data visualization.
Controlling null handling
As this project is ongoing and not all system components fully handle null values as true nulls from the beginning, Incorta has provided administrators with the ability to control how the system handles null values in the supported contexts. Cluster Management Console (CMC) administrators can manage how Incorta handles null values by enabling or disabling the Null Handling option.
- Location:
- Before 2024.1.x: CMC > Server Configurations > Customizations > Null Handling.
- Starting 2024.1.x: CMC > Server Configurations > Incorta Labs > Null Handling.
- Status: By default, the Null Handling option is disabled.
Enabling the Advanced SQL Interface or Notebook for Business Users automatically enforces accounting for null values in the supported contexts regardless of enabling or disabling the Null Handling option.
Enabling or disabling this feature requires loading related physical schemas to have nulls updated accordingly.
- Before 2024.7.x, you must load schemas from staging.
- Starting 2024.7.x, an incremental load is sufficient to have all data updated.
Contexts accounting for Null values
When you enable Null Handling in Incorta, the system distinguishes between null values and real values in the following contexts:
| Context | Behavior when enabling null handling |
|---|---|
| Aggregation functions (Including formulas involving aggregation functions) | Null values are ignored. For details and examples, see Null handling in detail > Aggregation functions. |
| Insight Grouping Dimensions based on physical columns | Null values in a grouping dimension column are considered a distinct group or category. Note: There is no change in behavior when the feature is disabled. |
| Sorting based on physical columns | ● In ascending order, null values come first. ● In descending order, null values come last. |
| Insight and dashboard filters based on physical columns | Null values are not considered zeros or empty strings. For details, see Null handling in detail > Filters. |
| Sorting based on formulas | ● In ascending order, null values come first. ● In descending order, null values come last. |
| Insight and dashboard filters based on formulas | Null values are not considered zeros or empty strings. For details, see Null handling in detail > Filters. |
| Filters in other contexts, such as conditional formatting and formula runtime security filters | Null values are not considered zeros or empty strings. For details, see Null handling in detail > Filters. |
| More functions and operations in dashboards, insights, Analyzer tables, and business schema objects | The behavior varies according to the function or operation. For details and examples, see the different sections under Null handling in detail. |
Starting 2024.7.x, functions (except for the lookup function and functions with an unlimited number of parameters, including and, in, case, caseContains, bin, decode, and or) in physical schemas, business schemas, dashboards, and visualizations | The behavior varies according to the function or operation. For details and examples, see the different sections under Null handling in detail. |
| Starting 2024.7.x, join and PK index calculations | The Loader Service accounts for null values when calculating joins and the PK index regardless of the state of the Null Handling feature. |
Null handling in detail
Enabling the Null Handling option may result in a different output for existing calculations. The following sections describe in detail how Incorta handles null values when enabling or disabling this option.
- Null handling by the Loader Service
- Filters
- Aggregation functions
- Analytics functions
- Arithmetic functions
- Boolean functions
- Conversion functions
- Date functions
- String functions
- Arithmetic operations
- Logical and Comparison operators
- Filter functions
- Conditional functions
- Miscellaneous functions
In releases before 2024.7.x, the following contexts do not differentiate between null values and real values.
- Physical schema table formula columns
- Materialized view formula columns
- Load filters
- Runtime security filters based on session variables
- Joins and join filters
As of 2024.7.x, session variables do not support null handling. This limitation affects all items that reference session variables. For example, runtime security filters based on session variables do not account for null values.
Null handling by the Loader Service
Before 2024.7.x, the Loader Service did not account for null values, resulting in inconsistent behavior whether during the deduplication, PK-index, or join calculations. Null values were considered real values, such as zeros and empty strings.
Starting 2024.7.x, Incorta Loader accounts for null values in the following contexts:
- Deduplication and PK-index calculations
- Join calculations and join filters
- Materializing formula columns
- Partial loading
Deduplication and PK-index calculations
Before 2024.7.x, two records with a null and zero or empty value in a key column were considered the same, consequently, these records were updated inconsistently during incremental loads.
The following table shows the updates after an incremental load job before 2024.7.x. Data after the load job might inconsistently vary.
| Data in Incorta | Updated Source Data | Data in Incorta After Incremental Load |
|---|---|---|
| ID,Name,Salary 0,John,2500 1,Matt,3000 NULL,Ella,2000 3,Dan,4000 | ID,Name,Salary,LastUpdated 0,John,4000,23/09/2024 NULL,Sara,5000,22/07/2024 NULL,Ella,2000,23/05/2020 | ID,Name,Salary 0,John,4000 1,Matt,3000 3,Dan,4000 |
Starting 2024.7.x, the Loader Service considers null values as undefined values. Thus, null values are no longer considered zeros or empty values. When loading data in full or incremental load jobs, the Loader Service retrieves or updates records with null key values apart from records with zero or empty key values. However, to preserve backward compatibility, Incorta considers a null value equal to another null value when retrieving or updating data. Accordingly, only one record with a null value will be loaded or updated if the Enforce Primary Key Constraint option is enabled.
The following table shows the updates after an incremental load job starting 2024.7.x.
| Data in Incorta | Updated Source Data | Data in Incorta After Incremental Load |
|---|---|---|
| ID,Name,Salary 0,John,2500 1,Matt,3000 NULL,Ella,2000 3,Dan,4000 | ID,Name,Salary,LastUpdated 0,John,4000,23/09/2024 NULL,Sara,5000,22/07/2024 NULL,Ella,2000,23/05/2020 | ID,Name,Salary 0,John,4000 1,Matt,3000 NULL,Sara,5000 3,Dan,4000 |
Join calculations and join filters
During join calculations before 2024.7.x, the Loader Service would inconsistently match rows with null values in the child’s join column to rows with a null, zero, or empty value in the parent’s join column.
Starting 2024.7.x, the Loader Service will account for records with null values individually. During join calculations, a null value does not equal another null value, zero, or empty value regardless of enabling or disabling the null handling feature. Additionally, the Loader Service will account for null values when evaluating the join filter.
Materializing formula columns
When the null handling feature is enabled, the Loader Service will account for null values during formula materialization. A full calculation of formula columns is performed during the first load job after enabling or disabling the feature.
Partial loading
When the feature is enabled, the Loader Service will account for null values while partially loading data based on a custom condition. The Loader Service will not load records that return Null for the loading condition. When the feature is disabled, the Loader Service returns records per the result of evaluating the custom condition, true or false. Data loading is required whenever you turn the Null Handling option on or off.
| Data in Incorta | Custom condition | Data loaded when disabling the feature | Data loaded when enabling the feature |
|---|---|---|---|
| ID,Name,Salary 0,John,2500 1,Matt,3000 NULL,Ella,2000 3,Dan,4000 | ID>=0 | ID,Name,Salary 0,John,2500 1,Matt,3000 NULL,Ella,2000 3,Dan,4000 | ID,Name,Salary 0,John,2500 1,Matt,3000 3,Dan,4000 |
Filters
| Filter type | Behavior when enabling null handling |
|---|---|
| Insight and dashboard filters based on physical columns: ● Individual filters ● Prompts ● Runtime filters ● Drilldown ● Aggregate filters ● Distinct filters Notes: The following filters are not supported: ● Dynamic filters inside dashboards ● Formula filters based on session or global variables Filters using the following operators are not supported: ● In Query ● Is Descendant ● First Version (Before 2024.7.x) ● Last Version (Before 2024.7.x) | Null values are not considered zeros or empty strings. Consequently, ● If the filter column contains only nulls, no rows are returned, except for the Null operator, all rows are returned. ● If the filter column contains real values and nulls, using the Null operator returns only rows with null values while in the case of all other supported operators, null values are ignored. ● For a distinct filter, if the column contains null values, the result list will contain only one row to represent these values. |
| Insight and dashboard filters based on formulas: ● Individual filters ● Prompts ● Runtime filters ● Drilldown ● Aggregate filters ● Is Descendant (if the self-join columns do not contain null values; otherwise, the filtering results are not consistent.) Notes: ● Only formulas with the supported functions account for null values. ● The following filters are not supported: formula filters based on session or global variables and dynamic filters. ● Filters using the In Query operator are not supported. | The same as filtering based on physical columns |
| Filters in other contexts: ● Both regular and formula runtime security filters (However, runtime security filters based on session variables are not supported.) ● Conditional formatting | Null values are not considered zeros or empty strings. |
Aggregation functions
| Function | Behavior when enabling null handling | Example | Result when disabling the option | Result when enabling the option |
|---|---|---|---|---|
● sum() ● median() ● average() ● min() ● max() | ● If all column values are null, the function returns NULL. ● Otherwise, the function ignores all null values and considers real values only. | Col1 NULL NULL NULL -1000.23 0 1000 2000.25 5000 8000.37 10000.45 | median(Col1) 500 | median(Col1) 2000.25 |
● count() ● distinct() | ● If all column values are null, the function returns 0. ● Otherwise, the function ignores null values and returns the count of all real values or distinct real values. | ID,Col1 1,1000 1,2000.25 1,5000 1,-1000.23 1,-1000.23 1,0 1,0 1,NULL 1,NULL 1,NULL 1,10000.45 1,8000.37 1,8000.37 | distinct(Col1) 8 | distinct(Col1) 7 |
Analytics functions
| Function | Behavior when enabling null handling | Example | Result when disabling the option | Result when enabling the option |
|---|---|---|---|---|
● ago() ● toDate() ● toDate() with ago() | ● If the date column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,Col2 10,23/1/2000 20,12/8/2000 25,NULL 40,15/4/2002 | Quarter(Col2),sum(Col1, toDate(Col2, "Quarter")) 1,10 2,40 3,45 (The column with a value of NULL for Col2 returns quarter 3.) | Quarter(Col2),sum(Col1, toDate(Col2, "Quarter")) NULL,25 1,10 2,40 3,20 |
| Starting 2024.7.x ● rank() ● denseRank() ● index() | ● If the groupBy field has NULL values, they will be in a separate group. ● If the orderBy field has NULL values, they are sorted first in ascending order and last in descending order. | Col1,Col2,Col3 Fruit,Lemon,NULL Fruit,Banana,NULL Fruit,NULL,-1 Fruit,Apple,0.25 Fruit,Orange,0.35 | Col1,Col2,denseRank(groupBy(Col1), orderBy(min(Col3),true)) Fruit,NULL,0 Fruit,Apple,2 Fruit,Banana,1 Fruit,Lemon,1 Fruit,Orange,3 | Col1,Col2,denseRank(groupBy(Col1), orderBy(min(Col3),true)) Fruit,NULL,2 Fruit,Apple,3 Fruit,Banana,1 Fruit,Lemon,1 Fruit,Orange,4 |
Arithmetic functions
| Function | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
● abs() ● ceil() ● floor() ● mod() ● round() ● exp() ● sqrt() ● trunc() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,abs(Col1) NULL,0 -1,1 | Col1,abs(Col1) NULL,NULL -1,1 |
| Starting 2024.7.x ● min() ● max() | ● If both double expressions are NULL, the function returns NULL. ● If one expression is NULL, the function ignores the NULL expression and returns the other expression. ● Otherwise, no change in behavior. The function returns the minimum or maximum value of the two expressions. | Col1,Col2,min(Col1,Col2) NULL,NULL,0 NULL,10,0 20,30,20 | Col1,Col2,min(Col1,Col2) NULL,NULL,NULL NULL,10,10 20,30,20 |
Boolean functions
| Function | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
● contains() ● startsWith() ● endsWith() ● like() | ● If the column value in a row is NULL, the function returns NULL (instead of true or false). ● Otherwise, there is no change in behavior. | Col1,contains(Col1, 'M') NULL,false Manager,true | Col1,contains(Col1, 'M') NULL,NULL Manager,true |
| not | ● If the column value in a row is NULL, the function returns NULL (instead of true or false). ● Otherwise, there is no change in behavior. | Col1,not(Col1>100) NULL,true 90,true 200,false | Col1,not(Col1>100) NULL,NULL 90,true 200,false |
| Starting 2024.7.x ● inList() | ● If the first parameter or expression is NULL, the function returns NULL. ● If the first parameter or expression is not NULL: If a match is found, the function returns true. If no match is found, the function checks: ● If any value in the list is NULL, the function returns NULL. ● Otherwise, the function returns false. | Col1,Col2,Col3,inList(Col1,Col2,Col3) NULL,NULL,HR,true Adam,NULL,SW,false NULL,John,CFO,false Liza,Ella,SW,false Matt,Matt,NULL,true | Col1,Col2,Col3,inList(Col1,Col2,Col3) NULL,NULL,HR,NULL Adam,NULL,SW,NULL NULL,John,CFO,NULL Liza,Ella,SW,false Matt,Matt,NULL,true |
| Starting 2024.7.x ● between() | It returns true only when no parameter is NULL, and the exp value is greater than or equal to the exp min and less than or equal to the exp max. It returns false in the following cases: ● No parameter is NULL, and the exp value is less than the exp min or greater than the exp max. ● The exp min is NULL, and the exp value is greater than the exp max. ● The exp max is NULL, and the exp value is less than exp min. It returns NULL in the following cases: ● The exp value is NULL. ● The exp min is NULL, and the exp value is less than or equal to the exp max. ● The exp max is NULL, and the exp value is greater than or equal to the exp min. | Col1,Col2,Col3,between(Col1,Col2,Col3) 2,0,3,true 0,2,3,false 2,NULL,0,false 2,3,NULL,false NULL,0,3,true 2,NULL,3,true 2,0,NULL,false | Col1,Col2,Col3,between(Col1,Col2,Col3) 2,0,3,true 0,2,3,false 2,NULL,0,false 2,3,NULL,false NULL,0,3,NULL 2,NULL,3,NULL 2,0,NULL,NULL |
| Starting 2024.7.x ● isNull() | There is no change in behavior. ● It returns true if the expression is NULL. ● Otherwise, it returns false. | Col1,isNull(Col1) 0,false Adam,false NULL,true | Col1,isNull(Col1) 0,false Adam,false NULL,true |
| Starting 2024.7.x ● isNan() | If the expression is NULL, it returns false. Otherwise, no change in behavior: ● It returns false if the expression is a number. ● It returns true if the expression is not a number. | Col1,Col2,isNan(Col1/Col2) 5,0,true 10,NULL,true 15,5,false NULL,5,false | Col1,Col2,isNan(Col1/Col2) 5,0,true 10,NULL,false 15,5,false NULL,5,false |
| Starting 2024.7.x ● isAlpha() ● isNumeric() | ● If the expression is NULL, it returns false. ● Otherwise, no change in behavior | Col1,isNumeric(Col1) 10,true NULL,true a+b,false | Col1,isNumeric(Col1) 10,true NULL,false a+b,false |
Conversion functions
Some conversion functions have two versions, one whose parameter is a column and the other whose parameter is the current date, such as day() and month(). There is no change in behavior for functions whose parameter is the current date, and the functions mentioned in the following table are the ones that take columns as a parameter.
| Function | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
● epoch() ● int() ● long() ● parseDate() ● parseDouble() ● parseTimestamp() ● string() ● timestamp() ● double() ● decimal() ● toChar() ● quarter() ● quarterDay() ● quarterMonth() ● quarterWeek() ● month() ● monthName() ● monthsBetween() ● monthWeek() ● weeknum() ● quarterStartDate() ● quarterEndDate() ● monthStartDate() ● monthEndDate() ● weekStartDate() ● weekEndDate() ● day() ● dayOfYear() ● hour() ● minute() ● second() ● year() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,long(Col1) NULL,0 20.33,20 50.67,51 | Col1,long(Col1) NULL,NULL 20.33,20 50.67,51 |
● fiscalPeriod() ● fiscalPeriodEndDate() ● fiscalPeriodStartDate() ● fiscalQuarter() ● fiscalQuarterEndDate() ● fiscalQuarterStartDate() ● fiscalYear() ● fiscalYearEndDate() ● fiscalYearStartDate() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | (If the fiscal year start is July) Col1,fiscalQuarter(Col1) 20/10/2023,2 19/7/2020,1 NULL,1 (inconsistent value) 15/1/2020,3 | (If the fiscal year start is July) Col1,fiscalQuarter(Col1) 20/10/2023,2 19/7/2020,1 NULL,NULL 15/1/2020,3 |
| weekDay() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,weekDay(Col1) NULL,1 | Col1,weekDay(Col1) NULL,NULL |
Date functions
| Function | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
● addMilliseconds() ● addSeconds() ● addMinutes() ● addHours() ● addDays() ● addMonths() ● addQuarters() ● addYears() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,addYears(Col1,3) 20/10/2023,20/10/2026 19/7/2020,19/7/2023 NULL,2/12/29226905 15/1/2020,15/1/2023 | Col1,addYears(Col1,3) 20/10/2023,20/10/2026 19/7/2020,19/7/2023 NULL,NULL 15/1/2020,15/1/2023 |
● date() ● formatDate() ● daysBetween ● timeBetween() ● yearsBetween() ● removeTime() ● dateTrunc() ● formatDuration() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,yearsBetween(Col1,$currentDate) 20/10/2023,-0.07 19/7/2020,3.19 NULL,-292276970.89 15/1/2020,3.69 | Col1,yearsBetween(Col1,$currentDate) 20/10/2023,-0.07 19/7/2020,3.19 NULL,NULL 15/1/2020,3.69 |
● yearMonth(date_timestamp exp) ● yearQuarter(date_timestamp) | ● If the column value in a row is NULL or the expression is evaluated to NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,yearMonth(Col1) 20/10/2023,202310 19/7/2020,202007 NULL,-836871669 (inconsistent value) 15/1/2020,202001 | Col1,yearMonth(Col1) 20/10/2023,202310 19/7/2020,202007 NULL,NULL 15/1/2020,202001 |
● yearMonth ● yearQuarter | No change in behavior as these functions depend on the current date | Today,yearMonth() 20/1/2023,202301 | Today,yearMonth() 20/1/2023,202301 |
String functions
| Function | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
| subString() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1(),subString(Col1,2) Lisa,sa NULL,"" Mark,rk | Col1,subString(Col1,2) Lisa,sa NULL,NULL Mark,rk |
| replace() | ● If any of the function parameters (string field, old string, or new string) is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,Col2,Col3,replace(Col1, Col2, Col3) Lisa,L,l,lisa NULL,Z,z,"" Mark,NULL,m,mMmamrmkm Rose,R,NULL,ose | Col1,Col2,Col3,replace(Col1, Col2, Col3) Lisa,L,l,lisa NULL, Z,z,NULL Mark,NULL,m,NULL Rose,R,NULL,NULL |
● find() ● findLast() | ● If any of the function parameters is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,Col2,find(Col2, Col1) Lisa,L,0 NULL,Z,-1 Mark,NULL,0 Rose,S,2 | Col1,Col2,find(Col2, Col1) Lisa,L,0 NULL,Z,NULL Mark,NULL,NULL Rose,S,2 |
| splitPart() | ● If any of the function parameters is NULL, the function returns NULL. ● If the int findNum parameter is greater than the string length, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,splitPart(Col1, "-",3) A-B-C,C NULL,"" D-E,"" | Col1,splitPart(Col1, "-", 3) A-B-C,C NULL,NULL D-E, NULL |
● lower() ● upper() ● lTrim() ● rTrim() ● trim() ● length() ● repeat() ● reverse() | ● If the column value in a row is NULL, the function returns NULL. ● Otherwise, there is no change in behavior. | Col1,lower(Col1) ABC,abc NULL,"" | Col1,lower(Col1) ABC,abc NULL,NULL |
| Starting 2024.7.x ● concat() | ● If all string expressions are NULL, the formula returns NULL. ● Otherwise, it ignores the null values. | Col1,Col2,concat(Col1,Col2) NULL,NULL,"" NULL,Adam,Adam | Col1,Col2,concat(Col1,Col2) NULL,NULL,NULL NULL,Adam,Adam |
| Starting 2024.7.x ● ifNull() | There is no change in behavior. ● If exp1 is NULL, the function returns exp2. ● If exp2 is NULL, it returns the NULL value. | Col1,Col2,ifNULL(Col1,Col2) 1,Number,1 NULL,NULL,"" | Col1,Col2,ifNULL(Col1,Col2) 1,Number,1 NULL,NULL,NULL |
Arithmetic operations
| Operation | Behavior when enabling null handling | Result when disabling the option | Result when enabling the option |
|---|---|---|---|
| Arithmetic operations: ● Addition (+) ● Subtraction (-) ● Multiplication (*) ● Division (/) ● Remainder (%) | If any of the operands is NULL, the operation result is NULL. ● If the operand data type is double, long, or integer, the result is NULL with a data type of double. ● If the operand data type is decimal, the result is NULL with a data type of decimal. | Col1,Col1+100 NULL,100 0,100 1000,1100 | Col1,Col1+100 NULL,NULL 0,100 1000,1100 |
Logical and Comparison operators
| Operator | Behavior when enabling null handling | Result when option disabled | Result when option enabled |
|---|---|---|---|
● Equal (=) ● Less than (<) ● Less than or equal to (<=) ● Greater than (>) ● Greater than or equal to (>=) ● Not equal (<> or !=) | ● If any or both operation operands are NULL, the operation result is NULL. ● Otherwise, the operation result is true or false. | Col1,Col2,Col1>Col2 1000,5000,false 1000,0,true NULL,1000,false NULL,NULL,false 5000,NULL,true | Col1,Col2,Col1>Col2 1000,5000,false 1000,0,true NULL,1000,NULL NULL,NULL,NULL 5000,NULL,NULL |
Filter functions
The following Filter functions support null handling starting 2024.7.x.
| Function | Behavior when enabling null handling | Behavior when option disabled |
|---|---|---|
● firstVersion() ● lasttVersion() | ● If the order by expression returns NULL values, they are ordered first. ● If the group by expression returns NULL values, they have a separate group. | ● Null values in the order by expression are considered zeros or empty strings and the sorting is inconsistent. ● Null values in the group by expression are considered zeros or empty strings depending on the data type, and are accordingly added to the same group. |
Conditional functions
The following Conditional functions support null handling starting 2024.7.x.
| Function | Behavior when enabling null handling | Result when option disabled | Result when option enabled |
|---|---|---|---|
| if() | ● If the bool expr is true, the function returns exp1. ● If the bool expr is false or NULL, it returns exp2. | Col1,if(Col1=0,"Pass", "Failed") 0,Pass 45,Failed NULL,Pass | Col1,if(Col1=0,"Pass", "Failed") 0,Pass 45,Failed NULL,Failed |
Miscellaneous functions
The following Miscellaneous functions support null handling starting 2024.7.x.
| Function | Behavior when enabling null handling | Result when option disabled | Result when option enabled |
|---|---|---|---|
| schemaRefreshTime() | ● It returns NULL if the schema has not been loaded before. ● Otherwise, it returns the last load time. | schemaRefreshTime("demo") 1/1/70 12:00:00 AM | schemaRefreshTime("demo") NULL |
● descendantOf(field, value, depth) ● descendantOf(field, value) | ● If depth is NULL, then both functions are the same. ● If the field is NULL, the function returns NULL. ● If the value is NULL, the function returns false. ● Otherwise, there is no change in behavior. | Col1,Col2,descendant Of(Col2,145) Sam,145,true Terry,110,false Liza,NULL,false | Col1,Col2,descendant Of(Col2,145) Sam,145,true Terry,110,false Liza,NULL,NULL |
Limitations and known issues
The following table shows the feature limitations and known issues as of the 2024.x releases.
| Issue | Affected Versions | Fix Version |
|---|---|---|
| A formula column that only references a physical column with null values shows wrong results as the Loader Service does not respect the null values when materializing the formula column. | 2022.12.0, 6.0, and later | 2024.7.x |
The count and distinct functions return null values instead of zeros when using them in a flat table or a session variable. | 2022.12.0, 6.0, and later | |
| Geo data types do not support null handling. | 2024.1.x and later | |
| The Public API does not support null handling. | 2022.12.0, 6.0, and later |