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