sql-tests/inputs/decimalArithmeticOperations.sql

0001 --
0002 --   Licensed to the Apache Software Foundation (ASF) under one or more
0003 --   contributor license agreements.  See the NOTICE file distributed with
0004 --   this work for additional information regarding copyright ownership.
0005 --   The ASF licenses this file to You under the Apache License, Version 2.0
0006 --   (the "License"); you may not use this file except in compliance with
0007 --   the License.  You may obtain a copy of the License at
0008 --
0009 --      http://www.apache.org/licenses/LICENSE-2.0
0010 --
0011 --   Unless required by applicable law or agreed to in writing, software
0012 --   distributed under the License is distributed on an "AS IS" BASIS,
0013 --   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014 --   See the License for the specific language governing permissions and
0015 --   limitations under the License.
0016 --
0017
0018 CREATE TEMPORARY VIEW t AS SELECT 1.0 as a, 0.0 as b;
0019
0020 -- division, remainder and pmod by 0 return NULL
0021 select a / b from t;
0022 select a % b from t;
0023 select pmod(a, b) from t;
0024
0025 -- tests for decimals handling in operations
0026 create table decimals_test(id int, a decimal(38,18), b decimal(38,18)) using parquet;
0027
0028 insert into decimals_test values(1, 100.0, 999.0), (2, 12345.123, 12345.123),
0029   (3, 0.1234567891011, 1234.1), (4, 123456789123456789.0, 1.123456789123456789);
0030
0031 -- test decimal operations
0032 select id, a+b, a-b, a*b, a/b from decimals_test order by id;
0033
0034 -- test operations between decimals and constants
0035 select id, a*10, b/10 from decimals_test order by id;
0036
0037 -- test operations on constants
0038 select 10.3 * 3.0;
0039 select 10.3000 * 3.0;
0040 select 10.30000 * 30.0;
0041 select 10.300000000000000000 * 3.000000000000000000;
0042 select 10.300000000000000000 * 3.0000000000000000000;
0043 select 2.35E10 * 1.0;
0044
0045 -- arithmetic operations causing an overflow return NULL
0046 select (5e36BD + 0.1) + 5e36BD;
0047 select (-4e36BD - 0.1) - 7e36BD;
0048 select 12345678901234567890.0 * 12345678901234567890.0;
0049 select 1e35BD / 0.1;
0050 select 1.2345678901234567890E30BD * 1.2345678901234567890E25BD;
0051
0052 -- arithmetic operations causing a precision loss are truncated
0053 select 12345678912345678912345678912.1234567 + 9999999999999999999999999999999.12345;
0054 select 123456789123456789.1234567890 * 1.123456789123456789;
0055 select 12345678912345.123456789123 / 0.000000012345678;
0056
0057 -- return NULL instead of rounding, according to old Spark versions' behavior
0058 set spark.sql.decimalOperations.allowPrecisionLoss=false;
0059
0060 -- test decimal operations
0061 select id, a+b, a-b, a*b, a/b from decimals_test order by id;
0062
0063 -- test operations between decimals and constants
0064 select id, a*10, b/10 from decimals_test order by id;
0065
0066 -- test operations on constants
0067 select 10.3 * 3.0;
0068 select 10.3000 * 3.0;
0069 select 10.30000 * 30.0;
0070 select 10.300000000000000000 * 3.000000000000000000;
0071 select 10.300000000000000000 * 3.0000000000000000000;
0072 select 2.35E10 * 1.0;
0073
0074 -- arithmetic operations causing an overflow return NULL
0075 select (5e36BD + 0.1) + 5e36BD;
0076 select (-4e36BD - 0.1) - 7e36BD;
0077 select 12345678901234567890.0 * 12345678901234567890.0;
0078 select 1e35BD / 0.1;
0079 select 1.2345678901234567890E30BD * 1.2345678901234567890E25BD;
0080
0081 -- arithmetic operations causing a precision loss return NULL
0082 select 12345678912345678912345678912.1234567 + 9999999999999999999999999999999.12345;
0083 select 123456789123456789.1234567890 * 1.123456789123456789;
0084 select 12345678912345.123456789123 / 0.000000012345678;
0085
0086 drop table decimals_test;