[
https://issues.apache.org/jira/browse/IGNITE-10601?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Abhishek Kumar updated IGNITE-10601:
------------------------------------
Description:
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 and 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
was:
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
> 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
> Priority: Major
>
> 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 and 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)
