PREHOOK: query: -- SORT_QUERY_RESULTS
-- This test is used for testing the TableAccessAnalyzer

CREATE TABLE T1(key STRING, val STRING) STORED AS TEXTFILE
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@T1
PREHOOK: query: LOAD DATA LOCAL INPATH '../../data/files/T1.txt' INTO TABLE T1
PREHOOK: type: LOAD
#### A masked pattern was here ####
PREHOOK: Output: default@t1
PREHOOK: query: CREATE TABLE T2(key STRING, val STRING) STORED AS TEXTFILE
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@T2
PREHOOK: query: CREATE TABLE T3(key STRING, val STRING) STORED AS TEXTFILE
PREHOOK: type: CREATETABLE
PREHOOK: Output: database:default
PREHOOK: Output: default@T3
PREHOOK: query: -- Simple group-by queries
SELECT key, count(1) FROM T1 GROUP BY key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

1	1
2	1
3	1
7	1
8	2
PREHOOK: query: SELECT key, val, count(1) FROM T1 GROUP BY key, val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key,val

1	11	1
2	12	1
3	13	1
7	17	1
8	18	1
8	28	1
PREHOOK: query: -- With subqueries and column aliases
SELECT key, count(1) FROM (SELECT key, val FROM T1) subq1 GROUP BY key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

1	1
2	1
3	1
7	1
8	2
PREHOOK: query: SELECT k, count(1) FROM (SELECT key as k, val as v FROM T1) subq1 GROUP BY k
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

1	1
2	1
3	1
7	1
8	2
PREHOOK: query: -- With constants
SELECT 1, key, count(1) FROM T1 GROUP BY 1, key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

1	1	1
1	2	1
1	3	1
1	7	1
1	8	2
PREHOOK: query: SELECT key, 1, val, count(1) FROM T1 GROUP BY key, 1, val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key,val

1	1	11	1
2	1	12	1
3	1	13	1
7	1	17	1
8	1	18	1
8	1	28	1
PREHOOK: query: SELECT key, 1, val, 2, count(1) FROM T1 GROUP BY key, 1, val, 2
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key,val

1	1	11	2	1
2	1	12	2	1
3	1	13	2	1
7	1	17	2	1
8	1	18	2	1
8	1	28	2	1
PREHOOK: query: -- no mapping with functions
SELECT key, key + 1, count(1) FROM T1 GROUP BY key, key + 1
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
1	2.0	1
2	3.0	1
3	4.0	1
7	8.0	1
8	9.0	2
PREHOOK: query: SELECT key + key, sum(cnt) from
(SELECT key, count(1) as cnt FROM T1 GROUP BY key) subq1
group by key + key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

14.0	1
16.0	2
2.0	1
4.0	1
6.0	1
PREHOOK: query: -- group by followed by union
SELECT * FROM (
SELECT key, count(1) as c FROM T1 GROUP BY key
 UNION ALL
SELECT key, count(1) as c FROM T1 GROUP BY key
) subq1
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key

Operator:GBY_10
Table:default@t1
Keys:key

1	1
1	1
2	1
2	1
3	1
3	1
7	1
7	1
8	2
8	2
PREHOOK: query: -- group by followed by a join
SELECT * FROM
(SELECT key, count(1) as c FROM T1 GROUP BY key) subq1
JOIN
(SELECT key, count(1) as c FROM T1 GROUP BY key) subq2
ON subq1.key = subq2.key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_2
Table:default@t1
Keys:key

Operator:GBY_8
Table:default@t1
Keys:key

1	1	1	1
2	1	2	1
3	1	3	1
7	1	7	1
8	2	8	2
PREHOOK: query: SELECT * FROM
(SELECT key, count(1) as c FROM T1 GROUP BY key) subq1
JOIN
(SELECT key, val, count(1) as c FROM T1 GROUP BY key, val) subq2
ON subq1.key = subq2.key
ORDER BY subq1.key ASC, subq1.c ASC, subq2.key ASC, subq2.val ASC, subq2.c ASC
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_2
Table:default@t1
Keys:key

Operator:GBY_8
Table:default@t1
Keys:key,val

1	1	1	11	1
2	1	2	12	1
3	1	3	13	1
7	1	7	17	1
8	2	8	18	1
8	2	8	28	1
PREHOOK: query: -- constants from sub-queries should work fine
SELECT key, constant, val, count(1) from
(SELECT key, 1 as constant, val from T1) subq1
group by key, constant, val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key,val

1	1	11	1
2	1	12	1
3	1	13	1
7	1	17	1
8	1	18	1
8	1	28	1
PREHOOK: query: -- multiple levels of constants from sub-queries should work fine
SELECT key, constant3, val, count(1) FROM
(
  SELECT key, constant AS constant2, val, 2 AS constant3
  FROM
  (
    SELECT key, 1 AS constant, val
    FROM T1
  ) subq
) subq2
GROUP BY key, constant3, val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
#### A masked pattern was here ####
Operator:GBY_3
Table:default@t1
Keys:key,val

1	2	11	1
2	2	12	1
3	2	13	1
7	2	17	1
8	2	18	1
8	2	28	1
PREHOOK: query: -- work with insert overwrite
FROM T1
INSERT OVERWRITE TABLE T2 SELECT key, count(1) GROUP BY key, 1
INSERT OVERWRITE TABLE T3 SELECT key, sum(val) GROUP BY key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Output: default@t2
PREHOOK: Output: default@t3
Operator:GBY_2
Table:default@t1
Keys:key

Operator:GBY_8
Table:default@t1
Keys:key

PREHOOK: query: -- simple joins
SELECT *
FROM T1 JOIN T2
ON T1.key = t2.key
ORDER BY T1.key ASC, T1.val ASC
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_6
Table:default@t1
Keys:key
Table:default@t2
Keys:key

1	11	1	1
2	12	2	1
3	13	3	1
7	17	7	1
8	18	8	2
8	28	8	2
PREHOOK: query: SELECT *
FROM T1 JOIN T2
ON T1.key = T2.key AND T1.val = T2.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_6
Table:default@t1
Keys:key,val
Table:default@t2
Keys:key,val

PREHOOK: query: -- map join
SELECT /*+ MAPJOIN(a) */ * 
FROM T1 a JOIN T2 b 
ON a.key = b.key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_6
Table:default@t1
Keys:key
Table:default@t2
Keys:key

1	11	1	1
2	12	2	1
3	13	3	1
7	17	7	1
8	18	8	2
8	28	8	2
PREHOOK: query: -- with constant in join condition
SELECT *
FROM T1 JOIN T2
ON T1.key = T2.key AND T1.val = 3 and T2.val = 3
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_6
Table:default@t1
Keys:key
Table:default@t2
Keys:key

PREHOOK: query: -- subqueries
SELECT *
FROM 
(
  SELECT val FROM T1 WHERE key = 5  
) subq1
JOIN 
(
  SELECT val FROM T2 WHERE key = 6
) subq2 
ON subq1.val = subq2.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_10
Table:default@t1
Keys:val
Table:default@t2
Keys:val

PREHOOK: query: SELECT *
FROM
(
  SELECT val FROM T1 WHERE key = 5
) subq1
JOIN
T2
ON subq1.val = T2.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_8
Table:default@t1
Keys:val
Table:default@t2
Keys:val

PREHOOK: query: -- with column aliases in subqueries
SELECT *
FROM
(
  SELECT val as v FROM T1 WHERE key = 5
) subq1
JOIN
(
  SELECT val FROM T2 WHERE key = 6
) subq2
ON subq1.v = subq2.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_10
Table:default@t1
Keys:val
Table:default@t2
Keys:val

PREHOOK: query: -- with constants in subqueries
SELECT *
FROM
(
  SELECT key, val FROM T1
) subq1
JOIN
(
  SELECT key, 'teststring' as val FROM T2
) subq2
ON subq1.val = subq2.val AND subq1.key = subq2.key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_8
Table:default@t1
Keys:val,key
Table:default@t2
Keys:key

PREHOOK: query: -- multiple levels of constants in subqueries
SELECT *
FROM
(
  SELECT key, val from 
  (
    SELECT key, 'teststring' as val from T1
  ) subq1
) subq2
JOIN
(
  SELECT key, val FROM T2
) subq3
ON subq3.val = subq2.val AND subq3.key = subq2.key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_9
Table:default@t1
Keys:key
Table:default@t2
Keys:val,key

PREHOOK: query: -- no mapping on functions
SELECT *
FROM
(
  SELECT key, val from T1
) subq1
JOIN
(
  SELECT key, val FROM T2
) subq2
ON subq1.val = subq2.val AND subq1.key + 1 = subq2.key
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
PREHOOK: query: -- join followed by group by
SELECT subq1.val, COUNT(*)
FROM
(
  SELECT key, val FROM T1
) subq1
JOIN
(
  SELECT key, 'teststring' as val FROM T2
) subq2
ON subq1.val = subq2.val AND subq1.key = subq2.key
GROUP BY subq1.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
#### A masked pattern was here ####
Operator:JOIN_8
Table:default@t1
Keys:val,key
Table:default@t2
Keys:key

PREHOOK: query: -- join followed by union
SELECT * 
FROM
(
  SELECT subq1.val, COUNT(*)
  FROM
  (
    SELECT key, val FROM T1
  ) subq1
  JOIN
  (
    SELECT key, 'teststring' as val FROM T2 
  ) subq2
  ON subq1.val = subq2.val AND subq1.key = subq2.key
  GROUP BY subq1.val
 UNION ALL
  SELECT val, COUNT(*)
  FROM T3
  GROUP BY val
) subq4
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
PREHOOK: Input: default@t3
#### A masked pattern was here ####
Operator:JOIN_8
Table:default@t1
Keys:val,key
Table:default@t2
Keys:key

Operator:GBY_16
Table:default@t3
Keys:val

11.0	1
12.0	1
13.0	1
17.0	1
46.0	1
PREHOOK: query: -- join followed by join
SELECT *
FROM
(
  SELECT subq1.val as val, COUNT(*)
  FROM
  (
    SELECT key, val FROM T1
  ) subq1
  JOIN
  (
    SELECT key, 'teststring' as val FROM T2
  ) subq2
  ON subq1.val = subq2.val AND subq1.key = subq2.key
  GROUP by subq1.val
) T4
JOIN T3
ON T3.val = T4.val
PREHOOK: type: QUERY
PREHOOK: Input: default@t1
PREHOOK: Input: default@t2
PREHOOK: Input: default@t3
#### A masked pattern was here ####
Operator:JOIN_8
Table:default@t1
Keys:val,key
Table:default@t2
Keys:key