ignite-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Pavel Kuznetsov (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (IGNITE-11563) DELETE WHERE does not work in prepared statements
Date Wed, 10 Apr 2019 19:30:00 GMT

    [ 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)

Mime
View raw message