[
https://issues.apache.org/jira/browse/IGNITE-19917?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-19917:
--------------------------------------
Description:
The following query issues redundant requests to perform index lookups:
{code:java}
sql("SELECT * FROM smallint_val = 1000000000");
{code}
This query returns no result because `1000000000` lies out of valid value range
for `SMALLINT` type.
*Explanation*:
1. Type coercion rules correctly applies a cast to the left side operand,
translating the query into:
{code:java}
sql("SELECT * FROM smallint_val::INTEGER = 1000000000");
{code}
2. Since `smallint_val` column has type SMALLINT, we replace `1000000000` with
SMALLINT.MAX_VALUE.
3. Index scan/lookups are issued for ExactBounds
smallint_val=`SMALLINT.MAX_VALUE`, are then filtered by `predicate` specified
by `IgniteIndexScan`, thus producing no results.
It would be better if such queries would not issue redundant physical
scans/lookups at all. Such approach was implemented for IS NULL predicate (see
RangeConditionImpl defined in ExpressionFactoryImpl).
was:
The following query issues redundant requests to perform index lookups:
{code:java}
sql("SELECT * FROM smallint_val = 1000000000");
{code}
This query returns no result because `1000000000` lies out of valid value range
for `SMALLINT` type.
*Explanation*:
1. Type coercion rules correctly applies a cast to the left side operand,
translating the query into:
{code:java}
sql("SELECT * FROM smallint_val::INTEGER = 1000000000");
{code}
2. Since `smallint_val` column has type SMALLINT, we replace `1000000000` with
SMALLINT.MAX_VALUE.
3. Index scan/lookups are issued for ExactBounds
smallint_val=`SMALLINT.MAX_VALUE`, are then filtered by `predicate` specified
by `IgniteIndexScan`, thus producing no results.
It would be better if such queries would not issue redundant requests at all.
Such approach was implemented for IS NULL predicate (see RangeConditionImpl
defined in ExpressionFactoryImpl).
> Sql. Index scans/lookups should not result in physical scans/looks when
> search conditions can not be satisfied.
> ---------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-19917
> URL: https://issues.apache.org/jira/browse/IGNITE-19917
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Maksim Zhuravkov
> Priority: Minor
> Labels: ignite-3
> Fix For: 3.0.0-beta2
>
>
> The following query issues redundant requests to perform index lookups:
> {code:java}
> sql("SELECT * FROM smallint_val = 1000000000");
> {code}
> This query returns no result because `1000000000` lies out of valid value
> range for `SMALLINT` type.
> *Explanation*:
> 1. Type coercion rules correctly applies a cast to the left side operand,
> translating the query into:
> {code:java}
> sql("SELECT * FROM smallint_val::INTEGER = 1000000000");
> {code}
> 2. Since `smallint_val` column has type SMALLINT, we replace `1000000000`
> with SMALLINT.MAX_VALUE.
> 3. Index scan/lookups are issued for ExactBounds
> smallint_val=`SMALLINT.MAX_VALUE`, are then filtered by `predicate` specified
> by `IgniteIndexScan`, thus producing no results.
> It would be better if such queries would not issue redundant physical
> scans/lookups at all. Such approach was implemented for IS NULL predicate
> (see RangeConditionImpl defined in ExpressionFactoryImpl).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
