[
https://issues.apache.org/jira/browse/IGNITE-19931?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-19931:
--------------------------------------
Description:
Index is not selected for the following query:
{code:java}
@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();
}
{code}
*Actual result*:
{code:java}
Expected: a string contains once ".*IgniteIndexScan\\(table=\\[\\[PUBLIC,
T_TEST\\]\\], tableId=\\[.*\\], index=\\[T_TEST_KEY_IDX\\].*\\)"
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
"
{code}
Query with HEX literals uses index.
{code:java}
@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();
}
{code}
Query with a redundant cast from HEX literal to VARBINARY does not use index:
{code:java}
@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();
}
{code}
was:
Index is not selected for the following query:
{code:java}
@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_KEY_IDX"))
.check();
}
{code}
*Actual result*:
{code:java}
Expected: a string contains once ".*IgniteIndexScan\\(table=\\[\\[PUBLIC,
T_TEST\\]\\], tableId=\\[.*\\], index=\\[T_TEST_KEY_IDX\\].*\\)"
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
"
{code}
Query with HEX literals uses index.
{code:java}
@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();
}
{code}
Query with a redundant cast from HEX literal to VARBINARY does not use index:
{code:java}
@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();
}
{code}
> Sql. Varbinary. Index is not selected for key = '1'::VARBINARY
> --------------------------------------------------------------
>
> Key: IGNITE-19931
> URL: https://issues.apache.org/jira/browse/IGNITE-19931
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0.0-beta1
> Reporter: Maksim Zhuravkov
> Priority: Minor
>
> Index is not selected for the following query:
> {code:java}
> @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();
> }
> {code}
> *Actual result*:
> {code:java}
> Expected: a string contains once ".*IgniteIndexScan\\(table=\\[\\[PUBLIC,
> T_TEST\\]\\], tableId=\\[.*\\], index=\\[T_TEST_KEY_IDX\\].*\\)"
> 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
> "
> {code}
> Query with HEX literals uses index.
> {code:java}
> @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();
> }
> {code}
> Query with a redundant cast from HEX literal to VARBINARY does not use index:
> {code:java}
> @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();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
