[IGNITE-19931] Sql. Varbinary. Index is not selected for key = '1'::VARBINARY

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.0.0-beta1
• Component/s: sql
• Labels: ignite-3
• Assignee: Konstantin Orlov
• Reporter: Maksim Zhuravkov
• Created: 07/Jul/23 08:00
• Updated: 18/Jul/23 11:52

Description

Index is not selected for the following query:

@Test
public void test1() {
  sql("CREATE TABLE t_test (key VARBINARY PRIMARY KEY, val INTEGER)");

  assertQuery("SELECT * FROM t_test WHERE key = '1'::VARBINARY")
    .matches(containsIndexScan("PUBLIC", "T_TEST", "T_TEST_PK"))
    .check();
}

Actual result:

java.lang.AssertionError: Invalid plan:
IgniteExchange(distribution=[single]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=11500.0, cpu=41500.0, memory=0.0, io=0.0, network=12000.0], id = 28
  IgniteTableScan(table=[[PUBLIC, T_TEST]], tableId=[3], filters=[=(CAST($t0):VARBINARY NOT NULL, X'31')], requiredColumns=[{0, 1}]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=10000.0, cpu=40000.0, memory=0.0, io=0.0, network=0.0], id = 25

Expected: a string contains once ".*IgniteIndexScan\\(table=\\[\\[PUBLIC, T_TEST\\]\\], tableId=\\[.*\\], index=\\[T_TEST_PK\\].*\\)"
     but: was "IgniteExchange(distribution=[single]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=11500.0, cpu=41500.0, memory=0.0, io=0.0, network=12000.0], id = 28
  IgniteTableScan(table=[[PUBLIC, T_TEST]], tableId=[3], filters=[=(CAST($t0):VARBINARY NOT NULL, X'31')], requiredColumns=[{0, 1}]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=10000.0, cpu=40000.0, memory=0.0, io=0.0, network=0.0], id = 25
"

Query with a HEX literal uses index.

@Test
public void test2() {
  sql("CREATE TABLE t_test (key VARBINARY PRIMARY KEY, val INTEGER)");

  assertQuery("SELECT * FROM t_test WHERE key = X'31'")
    .matches(containsIndexScan("PUBLIC", "T_TEST", "T_TEST_PK"))
     .check();
}

Query with redundant cast from a HEX literal to VARBINARY does not use index:

@Test
public void test3() {
  sql("CREATE TABLE t_test (key VARBINARY PRIMARY KEY, val INTEGER)");

  assertQuery("SELECT * FROM t_test WHERE key = X'31'::VARBINARY")
    .matches(containsIndexScan("PUBLIC", "T_TEST", "T_TEST_PK"))
    .check();
}

Issue Links

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