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)
