[jira] [Updated] (IGNITE-21736) Sql. Do not perform index scan operations for impossible search conditions for numeric literals.

["Maksim Zhuravkov (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/IGNITE-21736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Maksim Zhuravkov updated IGNITE-21736:
--------------------------------------
    Labels: ignite-3  (was: )

> Sql. Do not perform index scan operations for impossible search conditions for numeric literals.
> ------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-21736
>                 URL: https://issues.apache.org/jira/browse/IGNITE-21736
>             Project: Ignite
>          Issue Type: Improvement
>          Components: sql
>            Reporter: Maksim Zhuravkov
>            Priority: Minor
>              Labels: ignite-3
>
> IGNITE-19615 introduced saturated values to overcome a problem in index lookups, caused by the fact that SQL allows implict conversions between numeric types (see examples).
> This approach is not effiecent because instead of immediately returning an empty collection of rows, IndexScanNode performs a look up for TYPE::MAX_VALUE and then applying post condition to remove rows.
> 1. Let's fix remove this redudant lookup by introducing/updating search bounds that represent unsatisfiable/impossible conditions (conditions that never hold true). 
> 2. Let's fix code related to introduction of saturated values to use these bounds.
> 3. Update Index*ScanNode, to always produce no row, in cause when they use such bounds.     
> Example:
> {code:java}
>  SELECT * FROM t WHERE t.tinyint_col = 1111111111     
> {code}
> With type annotations:
> {code:java}
> SELECT * FROM t WHERE t.t.tinyint_col (TINYINT) = 1111111111 (INT)
> {code}
> After applying type coercions rules:
> {code:java}
> SELECT * FROM t WHERE CAST(t.tiny_int AS INT) = 1111111111
> // This operation perform as an index look up to returns all rows that have tinyint_col = SHORT::MAX_VALUE
> // and then applies a predicate CAST(tinyint_col) = 1111111111 (to eliminate all results)
> {code}



--
This message was sent by Atlassian Jira
(v8.20.10#820010)