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

[Maksim Zhuravkov (Jira)]

Maksim Zhuravkov created IGNITE-19882:
-----------------------------------------

             Summary: 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


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)