inputs/ansi/decimalArithmeticOperations.sql

0001 -- SPARK-23179: SQL ANSI 2011 states that in case of overflow during arithmetic operations,
0002 -- an exception should be thrown instead of returning NULL.
0003 -- This is what most of the SQL DBs do (eg. SQLServer, DB2).
0004
0005 -- tests for decimals handling in operations
0006 create table decimals_test(id int, a decimal(38,18), b decimal(38,18)) using parquet;
0007
0008 insert into decimals_test values(1, 100.0, 999.0), (2, 12345.123, 12345.123),
0009   (3, 0.1234567891011, 1234.1), (4, 123456789123456789.0, 1.123456789123456789);
0010
0011 -- test operations between decimals and constants
0012 select id, a*10, b/10 from decimals_test order by id;
0013
0014 -- test operations on constants
0015 select 10.3 * 3.0;
0016 select 10.3000 * 3.0;
0017 select 10.30000 * 30.0;
0018 select 10.300000000000000000 * 3.000000000000000000;
0019 select 10.300000000000000000 * 3.0000000000000000000;
0020
0021 -- arithmetic operations causing an overflow throw exception
0022 select (5e36BD + 0.1) + 5e36BD;
0023 select (-4e36BD - 0.1) - 7e36BD;
0024 select 12345678901234567890.0 * 12345678901234567890.0;
0025 select 1e35BD / 0.1;
0026
0027 -- arithmetic operations causing a precision loss throw exception
0028 select 123456789123456789.1234567890 * 1.123456789123456789;
0029 select 123456789123456789.1234567890 * 1.123456789123456789;
0030 select 12345678912345.123456789123 / 0.000000012345678;
0031
0032 drop table decimals_test;