--! qt:dataset:srcpart
--! qt:dataset:src
set hive.mapred.mode=nonstrict;
set hive.optimize.ppd=true;
set hive.ppd.remove.duplicatefilters=true;

-- SORT_QUERY_RESULTS

explain
select b.key,b.cc
from (
  select a.*
  from (
    select key, count(value) as cc
    from srcpart a
    where a.ds = '2008-04-08' and a.hr = '11'
    group by key
  )a
  distribute by a.key
  sort by a.key,a.cc desc) b
where b.cc>1;

select b.key,b.cc
from (
  select a.*
  from (
    select key, count(value) as cc
    from srcpart a
    where a.ds = '2008-04-08' and a.hr = '11'
    group by key
  )a
  distribute by a.key
  sort by a.key,a.cc desc) b
where b.cc>1;

EXPLAIN
SELECT user_id 
FROM (
  SELECT 
  CAST(key AS INT) AS user_id
  ,CASE WHEN (value LIKE 'aaa%' OR value LIKE 'vvv%')
  THEN 1
  ELSE 0 END AS tag_student
  FROM srcpart
) sub
WHERE sub.tag_student > 0;

EXPLAIN 
SELECT x.key, x.value as v1, y.key  FROM SRC x JOIN SRC y ON (x.key = y.key)  where x.key = 20 CLUSTER BY v1;

set hive.optimize.ppd=true;
set hive.ppd.remove.duplicatefilters=false;

explain
select b.key,b.cc
from (
  select a.*
  from (
    select key, count(value) as cc
    from srcpart a
    where a.ds = '2008-04-08' and a.hr = '11'
    group by key
  )a
  distribute by a.key
  sort by a.key,a.cc desc) b
where b.cc>1;

select b.key,b.cc
from (
  select a.*
  from (
    select key, count(value) as cc
    from srcpart a
    where a.ds = '2008-04-08' and a.hr = '11'
    group by key
  )a
  distribute by a.key
  sort by a.key,a.cc desc) b
where b.cc>1;


set hive.explain.user=false;
set hive.cbo.enable=false;
set hive.optimize.ppd=true;
set hive.ppd.remove.duplicatefilters=true;

DROP TABLE arc;
CREATE table arc(`dt_from` string, `dt_to` string);

DROP TABLE loc1;
CREATE table loc1(`dt_from` string, `dt_to` string);

-- INSERT INTO arc VALUES('2020', '2020');
-- INSERT INTO loc1 VALUES('2020', '2020');

DROP VIEW view;
CREATE
 VIEW view AS
     SELECT
        '9999' as DT_FROM,
        uuid() as DT_TO
     FROM
       loc1
 UNION ALL
     SELECT
        dt_from as DT_FROM,
        uuid() as DT_TO
     FROM
       arc;

EXPLAIN
    SELECT
      dt_from, dt_to
    FROM
      view
    WHERE
      '2020'  between dt_from and dt_to;