ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abhishek Kumar (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IGNITE-10601) USE INDEX ("index_name") does not work with DELETE query
Date Fri, 07 Dec 2018 15:33:00 GMT
Abhishek Kumar created IGNITE-10601:

             Summary: USE INDEX ("index_name") does not work with DELETE query
                 Key: IGNITE-10601
                 URL: https://issues.apache.org/jira/browse/IGNITE-10601
             Project: Ignite
          Issue Type: Bug
          Components: jdbc
    Affects Versions: 2.6
            Reporter: Abhishek Kumar

At my work we're using ignite for our data caching and we mostly request data from ignite
with range queries. I recently found out that H2 database does not support range queries very
well. Range query works if it is the only condition in the "where" clause but i want to have
range plus bounded query condition together which does not work vary well. In this case, Ignite
is not able to resolve the best suited index. Although i found a nice solution that i can
use "USE INDEX" in my query and specify which index to use. Unfortunately this does not work
with "Delete" query.


Query :- DELETE FROM Person USE INDEX(index_age) WHERE name = 'Perter' AND age < 35;


Exception :- 

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "DELETE FROM Person USE
INDEX[*] (index_age) WHERE (name= CAST(? AS VARCHAR) AND age < CAST(? AS BIGINT)) "; SQL
delete from Person use index (index_age) where (name= cast(? as varchar) and age < cast(?
as bigint)) [42000-195]
 at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
 at org.h2.message.DbException.get(DbException.java:179)
 at org.h2.message.DbException.get(DbException.java:155)
 at org.h2.message.DbException.getSyntaxError(DbException.java:191)
 at org.h2.command.Parser.getSyntaxError(Parser.java:533)
 at org.h2.command.Parser.prepareCommand(Parser.java:260)
 at org.h2.engine.Session.prepareLocal(Session.java:573)
 at org.h2.engine.Session.prepareCommand(Session.java:514)
 at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
 at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
 at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepare0(IgniteH2Indexing.java:484)
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:452)
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:419)
 at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatementAndCaches(IgniteH2Indexing.java:2008)
 ... 54 more

This message was sent by Atlassian JIRA

View raw message