[jira] [Commented] (IGNITE-4362) DML: the multiplication for SET uses the data type for the result from the right side

["Alexander Paschenko (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-4362?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15716737#comment-15716737 ] 

Alexander Paschenko commented on IGNITE-4362:
---------------------------------------------

[~skozlov], you are misinterpreting the exception. It's not about type of {{shortCol}}, it's about how H2 handles numeric types. And it turns out to be that it handles them just like Java does - for example, when multiplying {{short}} by {{int}}, it promotes {{short}} to {{int}}, but it *does not* promote neither to {{long}} - whatever it is we're going to do with result. So exception is not because {{shortCol * 100000}} does not fit into {{short}}, but because {{(int) shortCol * 100000}} does not fit into {{int}}.

Say, if we have following expression in Java, {{long val = 2300000 * 100000}}, then we *will not* get correct results because of int overflow (because we have {{int * int -> int}} on the right, and *not* {{int * int -> long}} just because we have long on the left.

In order for the right part of expression to be {{long}}, *at least one of the int operands has to be long*. So just cast either {{shortCol}} or {{100000}} in your example to {{BIGINT}},  and you'll be good.

Example:

{code:java}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestSimple {
    public static void main(String... args) throws Exception {
        org.h2.Driver.load();
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:test", "sa", "");
        Statement stat = conn.createStatement();
        stat.execute("CREATE MEMORY TABLE IF NOT EXISTS TBL(longCol BIGINT, shortCol SMALLINT) NOT PERSISTENT");
        stat.execute("INSERT INTO TBL (shortCol) VALUES (23000)");
        stat.execute("UPDATE TBL SET longCol = shortCol*100000");
        ResultSet rs = stat.executeQuery("SELECT longCol from TBL");
        rs.next();
        Object o = rs.getObject(1);
        assert o instanceof Long;
        System.out.println((Long) o);
        conn.close();
    }
}
{code}

This fails on {{UPDATE}} with the exception you created this issue about. Just change {{UPDATE}} query to

{code:sql}
UPDATE TBL SET longCol = CAST(shortCol as BIGINT) *100000
{code}

and it will work. So this issue has nothing to do neither with Ignite, nor with its DML, nor with implementation of {{UPDATE}} in particular, it's about H2's internal behavior. Won't fix.

Proof: {{org.h2.expression.Operation#optimize}}, at the very end of the method there's following code computing resulting type of expression, and it reads
{{dataType = Value.getHigherOrder(l, r);}}
So result type cares only about operands, nothing else.

> DML: the multiplication for SET uses the data type for the result from the right side
> -------------------------------------------------------------------------------------
>
>                 Key: IGNITE-4362
>                 URL: https://issues.apache.org/jira/browse/IGNITE-4362
>             Project: Ignite
>          Issue Type: Bug
>            Reporter: Sergey Kozlov
>            Assignee: Alexander Paschenko
>
> Lets run following statement:
> {{update AllTypes set longCol = shortCol*100000 where _key = ?}}
> The exception below has been thrown if {{shortCol*100000}} violates the short integer borders:
> {noformat}
> Caused by: org.h2.jdbc.JdbcSQLException: ... "-46000"
> Numeric value out of range: "-46000"; SQL statement:
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)