--! qt:disabled:HIVE-24816
--! qt:dataset:druid_table_alltypesorc
SET hive.vectorized.execution.enabled=true ;
SET hive.ctas.external.tables=true;
SET hive.external.table.purge.default = true;

-- GROUP BY TIME EXTRACT
--SECONDS
SELECT floor(`__time` to SECOND) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to SECOND);

EXPLAIN SELECT floor(`__time` to SECOND) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to SECOND);


-- MINUTES
SELECT floor(`__time` to MINUTE) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to MINUTE);

EXPLAIN SELECT floor(`__time` to MINUTE) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to MINUTE);

-- HOUR
SELECT floor(`__time` to HOUR) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to HOUR);

EXPLAIN SELECT floor(`__time` to HOUR) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY floor(`__time` to HOUR);

-- DAY
SELECT EXTRACT(DAY from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(DAY from `__time`);

EXPLAIN SELECT EXTRACT(DAY from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(DAY from `__time`);

--WEEK
SELECT EXTRACT(WEEK from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(WEEK from `__time`);


EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(WEEK from `__time`);

--MONTH
SELECT EXTRACT(MONTH from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(MONTH from `__time`);

EXPLAIN SELECT EXTRACT(MONTH from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(MONTH from `__time`);

--QUARTER

SELECT EXTRACT(QUARTER from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(QUARTER from `__time`);

EXPLAIN SELECT EXTRACT(QUARTER from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(QUARTER from `__time`);

-- YEAR
SELECT EXTRACT(YEAR from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(YEAR from `__time`);


EXPLAIN SELECT EXTRACT(YEAR from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 GROUP BY EXTRACT(YEAR from `__time`);

-- SELECT WITHOUT GROUP BY

-- SECOND

EXPLAIN SELECT EXTRACT(SECOND from `__time`) FROM druid_table_alltypesorc WHERE EXTRACT(SECOND from `__time`) = 0  LIMIT 1;

SELECT EXTRACT(SECOND from `__time`) FROM druid_table_alltypesorc WHERE EXTRACT(SECOND from `__time`) = 0  LIMIT 1;

-- MINUTE

EXPLAIN SELECT EXTRACT(MINUTE from `__time`) FROM druid_table_alltypesorc
WHERE  EXTRACT(MINUTE from `__time`) >= 0 LIMIT 2;

SELECT EXTRACT(MINUTE from `__time`) as minute FROM druid_table_alltypesorc
       WHERE  EXTRACT(MINUTE from `__time`) >= 0 order by minute LIMIT 2;
-- HOUR

EXPLAIN SELECT EXTRACT(HOUR from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 LIMIT 1;

SELECT EXTRACT(HOUR from `__time`) FROM druid_table_alltypesorc
WHERE character_length(CAST(ctinyint AS STRING)) > 1 AND char_length(CAST(ctinyint AS STRING)) < 10
AND power(cfloat, 2) * pow(csmallint, 3) > 1 AND SQRT(ABS(ctinyint)) > 3 LIMIT 1;

--DAY

EXPLAIN SELECT EXTRACT(DAY from `__time`), EXTRACT(DAY from `__time`) DIV 7 AS WEEK, SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) AS day_str
FROM druid_table_alltypesorc WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2)  = 31 LIMIT 1;

SELECT EXTRACT(DAY from `__time`) , EXTRACT(DAY from `__time`) DIV 7 AS WEEK, SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2) AS dar_str
FROM druid_table_alltypesorc WHERE SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 9, 2)  = 31 LIMIT 1 ;

-- WEEK

EXPLAIN SELECT EXTRACT(WEEK from `__time`) FROM druid_table_alltypesorc WHERE EXTRACT(WEEK from `__time`) >= 1
AND  EXTRACT(WEEK from `__time`) DIV 4 + 1 = 1 LIMIT 1;

SELECT EXTRACT(WEEK from `__time`) FROM druid_table_alltypesorc WHERE EXTRACT(WEEK from `__time`) >= 1
AND  EXTRACT(WEEK from `__time`) DIV 4 + 1 = 1 LIMIT 1 ;

--MONTH

EXPLAIN SELECT EXTRACT(MONTH FROM  `__time`) / 4 + 1, EXTRACT(MONTH FROM  `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table_alltypesorc
WHERE EXTRACT(MONTH FROM  `__time`) / 4 + 1 = 4 AND EXTRACT(MONTH FROM  `__time`) BETWEEN 11 AND 12 LIMIT 1;

SELECT EXTRACT(MONTH FROM  `__time`) / 4 + 1, EXTRACT(MONTH FROM  `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 6, 2) as month_str FROM druid_table_alltypesorc
       WHERE EXTRACT(MONTH FROM  `__time`) / 4 + 1 = 4 AND EXTRACT(MONTH FROM  `__time`) BETWEEN 11 AND 12 LIMIT 1;


--QUARTER

EXPLAIN SELECT EXTRACT(QUARTER from `__time`),  EXTRACT(MONTH FROM  `__time`) / 4 + 1 as q_number FROM druid_table_alltypesorc WHERE EXTRACT(QUARTER from `__time`) >= 4
          AND EXTRACT(MONTH FROM  `__time`) / 4 + 1 = 4 LIMIT 1;

SELECT EXTRACT(QUARTER from `__time`), EXTRACT(MONTH FROM  `__time`) / 4 + 1  as q_number FROM druid_table_alltypesorc WHERE EXTRACT(QUARTER from `__time`) >= 4
  AND EXTRACT(MONTH FROM  `__time`) / 4 + 1 = 4 LIMIT 1;

--YEAR

EXPLAIN SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) AS year_str FROM druid_table_alltypesorc WHERE EXTRACT(YEAR from `__time`) >= 1969
AND CAST(EXTRACT(YEAR from `__time`) as STRING) = '1969' LIMIT 1;

SELECT EXTRACT(YEAR from `__time`), SUBSTRING(CAST(CAST(`__time` AS DATE) AS STRING), 1, 4) as year_str FROM druid_table_alltypesorc WHERE EXTRACT(YEAR from `__time`) >= 1969
AND CAST(EXTRACT(YEAR from `__time`) as STRING) = '1969' LIMIT 1;