set hive.stats.kll.enable=true;
set metastore.stats.fetch.bitvector=true;
set metastore.stats.fetch.kll=true;
set hive.stats.autogather=true;
set hive.stats.column.autogather=true;

CREATE TABLE test_stats (a string, b int, c double, d float, e decimal(5,2), f timestamp, g date)
STORED AS ORC;

INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("a", 2, 1.1, 12.2, 1.3, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("b", 2, 2.1, NULL, 6.3, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("c", 2, 2.1, NULL, -8.3, "2020-11-2 00:00:00", "2020-11-02");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("d", 2, 3.1, 13.2, 10.2, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("e", 2, 3.1, 14.2, 10.2, "2020-11-02 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("f", 2, 4.1, NULL, 12.2, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("g", 2, 5.1, 15.2, -10.2, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("h", 2, 6.1, 16.2, 12.2, "2020-11-2 00:00:00", "2020-11-2");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("i", 3, 6.1, 17.2, 7.2, "2020-11-03 00:00:00", "2020-11-3");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("j", 4, NULL, 20.2, 1.2, "2020-11-4 00:00:00", "2020-11-4");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("k", 5, NULL, 50.2, -123.2, "2020-11-5 00:00:00", "2020-11-05");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("l", 6, NULL, 55.2, 1.2, "2020-11-6 00:00:00", "2020-11-6");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("m", 7, 9.1, 57.2, 1001.2, "2020-11-7 00:00:00", "2020-11-7");
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("n", NULL, 100.1, 1000.2, 0.2, NULL, NULL);
INSERT INTO test_stats (a, b, c, d, e, f, g) VALUES ("o", NULL, 101.1, 2000.2, -1.2, NULL, NULL);

DESCRIBE FORMATTED test_stats;
DESCRIBE FORMATTED test_stats a;
DESCRIBE FORMATTED test_stats b;
DESCRIBE FORMATTED test_stats c;
DESCRIBE FORMATTED test_stats d;
DESCRIBE FORMATTED test_stats e;
DESCRIBE FORMATTED test_stats f;
DESCRIBE FORMATTED test_stats g;

EXPLAIN SELECT COUNT(*)
FROM test_stats t1 JOIN test_stats t2 ON (t1.a = t2.a)
WHERE t1.b BETWEEN 3 AND 5 AND t2.c > 6.0;
SELECT COUNT(*)
FROM test_stats t1 JOIN test_stats t2 ON (t1.a = t2.a)
WHERE t1.b BETWEEN 3 AND 5 AND t2.c > 6.0;

EXPLAIN SELECT COUNT(*) FROM test_stats WHERE b < 3;
SELECT COUNT(*) FROM test_stats WHERE b < 3;

EXPLAIN SELECT COUNT(*) FROM test_stats WHERE b >= 7;
SELECT COUNT(*) FROM test_stats WHERE b >= 7;

EXPLAIN SELECT COUNT(*) FROM test_stats WHERE d NOT BETWEEN 3 AND 7 AND e > 0;
SELECT COUNT(*) FROM test_stats WHERE d NOT BETWEEN 3 AND 7 AND e > 0;

EXPLAIN SELECT COUNT(*) FROM test_stats WHERE f >= "2020-11-7" AND g >= "2020-11-7";
SELECT COUNT(*) FROM test_stats WHERE f >= "2020-11-7" AND g >= "2020-11-7";

EXPLAIN SELECT COUNT(*) FROM test_stats WHERE f BETWEEN "2020-11-01" AND "2020-11-06" AND g >= "2020-11-01";
SELECT COUNT(*) FROM test_stats WHERE f BETWEEN "2020-11-01" AND "2020-11-06" AND g >= "2020-11-01";

DROP TABLE test_stats;