[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17156752#comment-17156752
]
Christian Ehrlicher commented on IGNITE-11563:
--
Any progress on this? I think I also hit this problem with 2.8.1 so I assume
the fix is not yet in :(
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16816194#comment-16816194
]
Pavel Kuznetsov commented on IGNITE-11563:
--
One more point to think:
guess we decided to store internally decimal values with any scale. We have
column with type {{decimal(19,5)}} , but in cache we store decimal with scale =
7. We perform select returning this value - what should we do in this case:
return value with greater scale (7) that specified in the type (5)? Or should
we round it ( to scale = 5)? Jdbc spec says we should return full *precision*
value.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16815220#comment-16815220
]
Andrew Mashenkov commented on IGNITE-11563:
---
[~pkouznet],
# Rounding\cast value to column scale-precision by default with an option to
disable this behavior looks acceptable. Let's make this like MsSql goes.
# It seems we can do nothing for cache API for now, but just point to decimal
limitations in Ignite documentation .
We could compare 2 decimals with rounding up to some common scale, but it is
impossible due to key are compares as byte[]. Even if we found a solution to
compare decimal fields without deserialization, comparing key binary objects
field-by-field will hit performance too much.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16814819#comment-16814819
]
Pavel Kuznetsov commented on IGNITE-11563:
--
So we cannot find the value in the cache except full scan which is slow for the
PK lookup.
What I think we can do
Let's treat scale of the decimal part of the column type: If user defined
column type as {{DECIMAL(19, 5)}} we should store internally decimal number
with 5 digits after point.
as [1]:
{quote}For decimal and numeric data types, SQL Server considers each
combination of precision and scale as a different data type. For example,
decimal(5,5) and decimal(5,0) are considered different data types.{quote}
If user specified value with more scale digits, let's raise error, as H2 does.
MsSQL uses rounding by default, though. But it rises error if special system
property is set (also see [1])
What to do with the decimal keys, that have been inserted using cache api -
open question. In this case full scans would include some "weird" records.
Could we forbid such puts (with different scale)? Another option - is to add
filter condition in the indexes.
[1]
https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16814801#comment-16814801
]
Pavel Kuznetsov commented on IGNITE-11563:
--
bug is that we use {{BPlusTree#findOne}} that has the same issue with
BigDecimal 1 and 1.0
https://github.com/apache/ignite/blob/af041491423cc2c91668de3790a81e3631bcfa5c/modules/indexing/src/main/java/org/apache/ignite/internal/processors/query/h2/database/H2TreeIndex.java#L353
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16813563#comment-16813563
]
Pavel Kuznetsov commented on IGNITE-11563:
--
Fast update conversion is fixed. Got another delete issue: if double (or
string) argument is passed, only in partitioned mode, delete operation could do
nothing.
{code:java}
execute("CREATE TABLE TEST_TABLE (" +
"ID DECIMAL(19,1)," +
"VALUE VARCHAR2(255 CHAR)," +
"PRIMARY KEY (ID))with \"template=partitioned\"");
execute("INSERT INTO TEST_TABLE (id, value) VALUES (1, 'this row should
be deleted'), (2, 'value')");
Object one = 1.0d;
execute("DELETE FROM TEST_TABLE WHERE ID = ? AND VALUE = 'this row
should be deleted'", one);
List> expRows = Collections.singletonList(asList(new
BigDecimal(2), "value"));
assertEqualsCollections("Argument of class " +
one.getClass().getSimpleName() + " is converted incorrectly",
expRows, execute("SELECT * FROM TEST_TABLE ORDER BY ID"));
{code}
research shown that select generated for this delete also doesn't work.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16811995#comment-16811995
]
Pavel Kuznetsov commented on IGNITE-11563:
--
nb: for the query
{code}
DELETE FROM TEST_TABLE WHERE ID = ? AND VALUE = 'this row should be deleted'
{code}
Fast delete is not used, but it should.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16809418#comment-16809418
]
Ignite TC Bot commented on IGNITE-11563:
{panel:title=--> Run :: All: No blockers
found!|borderStyle=dashed|borderColor=#ccc|titleBGColor=#D6F7C1}{panel}
[TeamCity *--> Run :: All*
Results|https://ci.ignite.apache.org/viewLog.html?buildId=3501046&buildTypeId=IgniteTests24Java8_RunAll]
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
> Time Spent: 10m
> Remaining Estimate: 0h
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16806952#comment-16806952
]
Pavel Kuznetsov commented on IGNITE-11563:
--
This is not client-specific bug, reproducible using native sql. Key point is to
use sql positional parameters inside DELETE query.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
[
https://issues.apache.org/jira/browse/IGNITE-11563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16806941#comment-16806941
]
Pavel Kuznetsov commented on IGNITE-11563:
--
Reproduced.
> DELETE WHERE does not work in prepared statements
> -
>
> Key: IGNITE-11563
> URL: https://issues.apache.org/jira/browse/IGNITE-11563
> Project: Ignite
> Issue Type: Bug
> Components: sql
>Affects Versions: 2.7
>Reporter: Stefan
>Assignee: Pavel Kuznetsov
>Priority: Minor
>
> With SQL I cannot delete a row using a prepared statement. The following
> statement is simply ignored:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = ?}}
> This happens with JDBC-Thin and with ODBC so I suspect that the cluster gets
> the correct data but handles it wrong. By adding an always-true-condition it
> works as expected:
> {{DELETE}} {{FROM}} {{AnyTable WHERE}} {{id = id AND}} {{id = ?}}
> I tested with a very simple table that was created with:
> {{CREATE TABLE testtable (}}
> {{ "ID" NUMBER(19,0),}}
> {{ "VALUE" VARCHAR2(255 CHAR),}}
> {{ PRIMARY KEY (ID)}}
> {{) WITH "template=replicated,cache_name=testtable"}}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
