ql/src/test/queries/clientpositive/show_partitions2.q

CREATE TABLE mpart1 (key1 INT, value1 STRING) PARTITIONED BY (ds DATE, hr INT, rs STRING);
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-09', hr=17, rs='EU');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-09', hr=19, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-09', hr=22, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=12, rs='EU');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=10, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=15, rs='EU');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=16, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=20, rs='AF');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-10', hr=21, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-11', hr=16, rs='AS');
ALTER TABLE mpart1 ADD PARTITION (ds='1980-11-11', hr=22, rs='AS');

CREATE TABLE srcpart1 (key1 INT, value1 STRING, ds DATE, hr INT, rs STRING);
INSERT INTO TABLE srcpart1 VALUES (1, 'val1', 'null', 'null', 'AS'), (2, 'val2', '1980-11-11', '12', 'AS'),
    (3, 'val3', '1980-11-10', '21', 'NA'), (4, 'val4', '1980-11-11', 'null', 'NA'), (5, 'val5', '1980-11-10', 'null', 'NA');

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE mpart1 PARTITION(ds, hr, rs) SELECT * FROM srcpart1;

SHOW PARTITIONS mpart1 WHERE ds = '1980-11-10' ORDER BY rs DESC, hr LIMIT 4;
SHOW PARTITIONS mpart1 PARTITION (rs = 'AS') WHERE ds = '1980-11-10' AND hr >= 20;
SHOW PARTITIONS mpart1 WHERE hr > 9 AND hr < 19 ORDER BY hr DESC, ds;

SHOW PARTITIONS mpart1 ORDER BY hr ASC,  ds DESC;
SHOW PARTITIONS mpart1 PARTITION (rs='AS') ORDER BY ds DESC;

SHOW PARTITIONS mpart1 LIMIT 3;
SHOW PARTITIONS mpart1 PARTITION(ds = '1980-11-10') LIMIT 3;

SHOW PARTITIONS mpart1 where ds = '__HIVE_DEFAULT_PARTITION__' AND hr = '__HIVE_DEFAULT_PARTITION__';
SHOW PARTITIONS mpart1 where (ds = '__HIVE_DEFAULT_PARTITION__' OR hr = '__HIVE_DEFAULT_PARTITION__') AND rs = 'NA';
SHOW PARTITIONS mpart1 where ds >= '1980-11-10' AND hr = '__HIVE_DEFAULT_PARTITION__' ORDER BY ds DESC;
SHOW PARTITIONS mpart1 where ds = '1980-11-10' AND hr != '__HIVE_DEFAULT_PARTITION__' ORDER BY hr DESC;

EXPLAIN SHOW PARTITIONS mpart1 WHERE ds = '1980-11-10' AND hr >= 20;
EXPLAIN SHOW PARTITIONS mpart1 WHERE ds = '1980-11-10' ORDER BY rs DESC, hr LIMIT 4;
EXPLAIN SHOW PARTITIONS mpart1 PARTITION (rs = 'AS') WHERE ds = '1980-11-10' AND hr >= 20;
EXPLAIN SHOW PARTITIONS mpart1 ORDER BY hr DESC, ds DESC;
EXPLAIN SHOW PARTITIONS mpart1 PARTITION(ds = '1980-11-10') LIMIT 3;
EXPLAIN SHOW PARTITIONS mpart1 where (ds = '__HIVE_DEFAULT_PARTITION__' OR hr = '__HIVE_DEFAULT_PARTITION__') AND rs = 'NA';
EXPLAIN SHOW PARTITIONS mpart1 where ds >= '1980-11-10' AND hr = '__HIVE_DEFAULT_PARTITION__' ORDER BY ds DESC;