[
https://issues.apache.org/jira/browse/IGNITE-21736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-21736:
--------------------------------------
Description:
IGNITE-19615 introduced saturated values to overcome a problem in index
lookups, caused by the fact that SQL allows implict conversions between numeric
types.
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 (See example).
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 IndexScanNode to always produce no rows in cause when impossible
bounds are being used.
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}
was:
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 (See example).
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}
> 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.
> 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 (See example).
> 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 IndexScanNode to always produce no rows in cause when impossible
> bounds are being used.
> 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)
