[jira] [Commented] (IGNITE-19882) Sql. Queries that access columns of type DECIMAL do not pick index, when compared with INT/BIGINT.

["Pavel Pereslegin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-19882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17744998#comment-17744998 ] 

Pavel Pereslegin commented on IGNITE-19882:
-------------------------------------------

Since this is another problem with removing unnecessary implicit casts, it is recommended to solve it according to the plan proposed in IGNITE-19997.

> Sql. Queries that access columns of type DECIMAL do not pick index, when compared with INT/BIGINT.
> --------------------------------------------------------------------------------------------------
>
>                 Key: IGNITE-19882
>                 URL: https://issues.apache.org/jira/browse/IGNITE-19882
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 3.0.0-beta1
>            Reporter: Maksim Zhuravkov
>            Priority: Minor
>              Labels: ignite-3
>
> When decimal column is compared with literal/dynamic parameter of integral numeric type, index is not picked:
> {code:java}
> sql("CREATE TABLE t_dec (ID INTEGER PRIMARY KEY, VAL DECIMAL(5,3))");
> sql("CREATE INDEX t_dec_idx ON t_dec(VAL)");
> sql("INSERT INTO t_dec VALUES (1, 42)");
> // 1 passes
> assertQuery("SELECT id FROM t_dec WHERE val = 42.000")
>                 .matches(containsIndexScan("PUBLIC", "T_DEC", "T_DEC_IDX"))
>                 .returns(1)
>                 .check();
> // 2 passes
> assertQuery("SELECT id FROM t_dec WHERE val = 42::DECIMAL(5,3)")
>    .matches(containsIndexScan("PUBLIC", "T_DEC", "T_DEC_IDX"))
>    .returns(1)
>    .check();
> // 3 fails
> assertQuery("SELECT id FROM t_dec WHERE val = 42")
>   .matches(containsIndexScan("PUBLIC", "T_DEC", "T_DEC_IDX"))
>   .returns(1)
>   .check();
> // 4 fails
> assertQuery("SELECT id FROM t_dec WHERE val = ?")
>   .withParams(42)
>   .matches(containsIndexScan("PUBLIC", "T_DEC", "T_DEC_IDX"))
>   .returns(1)
>   .check();
> // 5 fails
> assertQuery("SELECT id FROM t_dec WHERE val = ?")
>   .withParams(42L)
>   .matches(containsIndexScan("PUBLIC", "T_DEC", "T_DEC_IDX"))
>   .returns(1)
>   .check();
> {code}
> Expected behaviour: Queries 3, 4, 5 should use an index.



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