set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
SET hive.vectorized.execution.enabled=true;
set hive.fetch.task.conversion=none;

DROP TABLE IF EXISTS DECIMAL_PRECISION_txt;
DROP TABLE IF EXISTS DECIMAL_PRECISION;
DROP TABLE IF EXISTS DECIMAL_PRECISION_txt_small;

CREATE TABLE DECIMAL_PRECISION_txt(`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_txt;

CREATE TABLE DECIMAL_PRECISION(`dec` decimal(20,10))
STORED AS ORC;

INSERT OVERWRITE TABLE DECIMAL_PRECISION SELECT * FROM DECIMAL_PRECISION_txt;

SELECT * FROM DECIMAL_PRECISION ORDER BY `dec`;

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

EXPLAIN VECTORIZATION DETAIL SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION;
SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION;

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

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

-- DECIMAL_64

CREATE TABLE DECIMAL_PRECISION_txt_small(`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_txt_small;

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

EXPLAIN VECTORIZATION DETAIL SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_txt_small;
SELECT avg(`dec`), sum(`dec`) FROM DECIMAL_PRECISION_txt_small;

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

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

DROP TABLE DECIMAL_PRECISION_txt;
DROP TABLE DECIMAL_PRECISION;
DROP TABLE DECIMAL_PRECISION_txt_small;