--! qt:dataset:src

set hive.support.concurrency=true;
set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

CREATE TABLE src_txn_2 stored as orc TBLPROPERTIES ('transactional' = 'true')
AS SELECT * FROM src where key > 200 and key < 300;

CREATE MATERIALIZED VIEW partition_mv_1 PARTITIONED ON (key) AS
SELECT value, key FROM src_txn_2;

CREATE MATERIALIZED VIEW partition_mv_2 PARTITIONED ON (value) AS
SELECT key, value FROM src_txn_2;

CREATE MATERIALIZED VIEW partition_mv_3 PARTITIONED ON (key) AS
SELECT value, key FROM src_txn_2 where key > 220 and key < 230;

CREATE MATERIALIZED VIEW partition_mv_4 PARTITIONED ON (key) AS
SELECT value, key FROM src_txn_2 where key > 222 and key < 228;

-- SHOULD CHOOSE partition_mv_1 SINCE ONLY partition_mv_1 AND
-- partition_mv_2 ARE VALID BUT PRUNING KICKS IN FOR THE FORMER
EXPLAIN
SELECT * FROM src_txn_2 where key > 210 and key < 230;

SELECT * FROM src_txn_2 where key > 210 and key < 230;

-- SHOULD CHOOSE partition_mv_2 SINCE ONLY partition_mv_2 AND
-- partition_mv_1 ARE VALID BUT PRUNING KICKS IN FOR THE FORMER
EXPLAIN
SELECT * FROM src_txn_2 where value > 'val_220' and value < 'val_230';

SELECT * FROM src_txn_2 where value > 'val_220' and value < 'val_230';

-- SHOULD CHOOSE partition_mv_1, partition_mv_3 OR partition_mv_4
-- SINCE IT IS THE MOST EFFICIENT READING ONLY ONE PARTITION
EXPLAIN
SELECT * FROM src_txn_2 where key > 224 and key < 226;

SELECT * FROM src_txn_2 where key > 223 and key < 225;

DROP MATERIALIZED VIEW partition_mv_1;
DROP MATERIALIZED VIEW partition_mv_2;
DROP MATERIALIZED VIEW partition_mv_3;
DROP MATERIALIZED VIEW partition_mv_4;
DROP TABLE src_txn_2;