set hive.mapred.mode=nonstrict;
DROP TABLE IF EXISTS DECIMAL_PRECISION_n0;

CREATE TABLE DECIMAL_PRECISION_n0(`dec` decimal(20,10))
ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ' '
STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '../../data/files/kv8.txt' INTO TABLE DECIMAL_PRECISION_n0;

SELECT * FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;

SELECT `dec`, `dec` + 1, `dec` - 1 FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;
SELECT `dec`, `dec` * 2, `dec` / 3  FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;
SELECT `dec`, `dec` / 9 FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;
SELECT `dec`, `dec` / 27 FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;
SELECT `dec`, `dec` * `dec` FROM DECIMAL_PRECISION_n0 ORDER BY `dec`;

EXPLAIN SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_n0;
SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_n0;

SELECT `dec` * cast('12345678901234567890.12345678' as decimal(38,18)) FROM DECIMAL_PRECISION_n0 LIMIT 1;
SELECT * from DECIMAL_PRECISION_n0 WHERE `dec` > cast('1234567890123456789012345678.12345678' as decimal(38,18)) LIMIT 1;
SELECT `dec` * 12345678901234567890.12345678 FROM DECIMAL_PRECISION_n0 LIMIT 1;

SELECT MIN(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION_n0;
SELECT COUNT(cast('12345678901234567890.12345678' as decimal(38,18))) FROM DECIMAL_PRECISION_n0;

DROP TABLE DECIMAL_PRECISION_n0;

-- Expect overflow and return null as the value
CREATE TABLE DECIMAL_PRECISION_n0(`dec` decimal(38,18));
INSERT INTO DECIMAL_PRECISION_n0 VALUES(98765432109876543210.12345), (98765432109876543210.12345);
SELECT SUM(`dec`) FROM DECIMAL_PRECISION_n0;

DROP TABLE DECIMAL_PRECISION_n0;