[IGNITE-16085] Calcite engine. Index can't be used with IS NULL condition

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.15
• Labels: calcite
• Release Note: SQL Calcite: Added support for index scans by IS NULL/IS NOT NULL conditions
• Assignee: Aleksey Plekhanov
• Reporter: Aleksey Plekhanov
• Created: 08/Dec/21 12:44
• Updated: 26/Sep/22 10:45
• Resolved: 26/Sep/22 10:39

Description

Currently, we support only EQUALS, LESS_THAN, GREATER_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN_OR_EQUAL conditions for index scans. Our indexes also can be scanned for null values by design, but full table scan now performed for IS NULL condition on indexed fields.
Perhaps IS NOT NULL, IS TRUE, IS FALSE, IS NOT TRUE, IS NOT FALSE also can be used in index scan.
Also, SortedSegmentedIndex#find method have lowerIncl and upperIncl parameters now, perhaps we can set they to false for LESS_THAN and GREATER_THAN operators to more effective index scans.

Issue Links

• is fixed by: IGNITE-15425 Calcite engine. Add native support for SEARCH/SARG operator
• is part of: IGNITE-12248 Apache Calcite based query execution engine