-- SORT_QUERY_RESULTS
--! qt:dataset:lineitem
set hive.cli.print.header=true;
set hive.mapred.mode=nonstrict;
set hive.explain.user=false;
set hive.fetch.task.conversion=none;
set hive.vectorized.execution.enabled=true;

CREATE TABLE lineitem_test_txt (L_ORDERKEY      INT,
                                L_PARTKEY       INT,
                                L_SUPPKEY       INT,
                                L_LINENUMBER    INT,
                                L_QUANTITY      INT,
                                L_EXTENDEDPRICE DOUBLE,
                                L_DISCOUNT      DOUBLE,
                                L_TAX           DECIMAL(10,2),
                                L_RETURNFLAG    CHAR(1),
                                L_LINESTATUS    CHAR(1),
                                l_shipdate      DATE,
                                L_COMMITDATE    DATE,
                                L_RECEIPTDATE   DATE,
                                L_SHIPINSTRUCT  VARCHAR(20),
                                L_SHIPMODE      CHAR(10),
                                L_COMMENT       STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|';

LOAD DATA LOCAL INPATH '../../data/files/tpch/tiny/lineitem.tbl.bz2' OVERWRITE INTO TABLE lineitem_test_txt;
CREATE TABLE lineitem_test STORED AS ORC AS SELECT * FROM lineitem_test_txt;
INSERT INTO TABLE lineitem_test VALUES (NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

SET hive.vectorized.if.expr.mode=adaptor;

EXPLAIN VECTORIZATION DETAIL
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;

SET hive.vectorized.if.expr.mode=good;

EXPLAIN VECTORIZATION DETAIL
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;

SET hive.vectorized.if.expr.mode=better;

EXPLAIN VECTORIZATION DETAIL
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;
SELECT
   L_QUANTITY as Quantity,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE "Huge number" END AS Quantity_Description,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN "Many"
     ELSE NULL END AS Quantity_Description_2,
   CASE
     WHEN L_QUANTITY = 1 THEN "Single"
     WHEN L_QUANTITY = 2 THEN "Two"
     WHEN L_QUANTITY < 10 THEN "Some"
     WHEN L_QUANTITY < 100 THEN NULL
     ELSE NULL END AS Quantity_Description_3,
   IF(L_SHIPMODE = "SHIP", DATE_ADD(l_shipdate, 10), DATE_ADD(l_shipdate, 5)) AS Expected_Date,
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE 0 END AS Field_1,  -- The 0 will be an integer and requires implicit casting.
   CASE WHEN L_RETURNFLAG = "N"
            THEN l_extendedprice * (1 - l_discount)
        ELSE CAST(0 AS DOUBLE) END AS Field_2,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", NULL, L_TAX) AS Field_3,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, NULL) AS Field_4,
   -- For the next 2 IF stmts, the 0s are integer and require implicit casting to decimal.
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0, L_TAX) AS Field_5,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0) AS Field_6,
   IF(L_SHIPINSTRUCT = "DELIVER IN PERSON", 0BD, L_TAX) AS Field_7,
   IF(L_SHIPINSTRUCT = "TAKE BACK RETURN", L_TAX, 0BD) AS Field_8,
   IF(L_PARTKEY > 30, CAST(L_RECEIPTDATE AS TIMESTAMP), CAST(L_COMMITDATE AS TIMESTAMP)) AS Field_9,
   IF(L_SUPPKEY > 10000, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE), NULL) AS Field_10,
   IF(L_SUPPKEY > 10000, NULL, DATEDIFF(L_RECEIPTDATE, L_COMMITDATE)) AS Field_11,
   IF(L_SUPPKEY % 500 > 100, DATE_ADD('2008-12-31', 1), DATE_ADD('2008-12-31', 365)) AS Field_12
FROM lineitem_test;