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

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Evgeny Stanilovsky
• Reporter: Maksim Zhuravkov
• Created: 29/Jun/23 12:24
• Updated: 03/Sep/24 08:00

Description

When decimal column is compared with literal/dynamic parameter of integral numeric type, index is not picked:

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();

Expected behaviour: Queries 3, 4, 5 should use an index.

Issue Links

• is related to: IGNITE-19997 Sql. Enhancing test coverage of type coercion