Multiplying two positive numbers resulting in a negative number

confused Hive ??
Hive multiplication error

Weird multiplication Error

What would you do when multiplying two positive numbers resulting in a negative result ?

If its a person, we can teach them how to multiply but what if its done by popular big data library like Hive ?
Doesn’t sound right ?

Try this on Hive..

select 59 * 38810000;
= -2005177296

If you try the same with Microsoft SQL Server, you will get error message. Arithmetic overflow error converting expression to data type int.

Luckily MySQL 5.7 handles this right way. select 59 * 38810000;
= 2289790000

Reason: Result exceeds range of int allowed for Hive and SQL Server so it results in bad result.

How to fix the problem in Hive & SQL Server ?

Simple fix to cast both numbers to bigint.

SELECT cast(59 as bigint) * cast (38810000 as bigint);

If casting both numbers may look weird.. then try casting at least one of them for multiplication work.

SELECT 59 * cast (38810000 as bigint)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: