[jira] [Created] (IGNITE-10601) USE INDEX ("index_name") does not work with DELETE query

["Abhishek Kumar (JIRA)" <ji...@apache.org>]

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 statement:
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
(v7.6.3#76005)