-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 8


-- !query 0
CREATE TEMPORARY VIEW t1 AS SELECT * FROM VALUES
  (1, 2, 3)
AS t1(t1a, t1b, t1c)
-- !query 0 schema
struct<>
-- !query 0 output



-- !query 1
CREATE TEMPORARY VIEW t2 AS SELECT * FROM VALUES
  (1, 0, 1)
AS t2(t2a, t2b, t2c)
-- !query 1 schema
struct<>
-- !query 1 output



-- !query 2
CREATE TEMPORARY VIEW t3 AS SELECT * FROM VALUES
  (3, 1, 2)
AS t3(t3a, t3b, t3c)
-- !query 2 schema
struct<>
-- !query 2 output



-- !query 3
SELECT t1a, t2b
FROM   t1, t2
WHERE  t1b = t2c
AND    t2b = (SELECT max(avg)
              FROM   (SELECT   t2b, avg(t2b) avg
                      FROM     t2
                      WHERE    t2a = t1.t1b
                     )
             )
-- !query 3 schema
struct<>
-- !query 3 output
org.apache.spark.sql.AnalysisException
grouping expressions sequence is empty, and 't2.`t2b`' is not an aggregate function. Wrap '(avg(CAST(t2.`t2b` AS BIGINT)) AS `avg`)' in windowing function(s) or wrap 't2.`t2b`' in first() (or first_value) if you don't care which value you get.;


-- !query 4
SELECT *
FROM   t1
WHERE  t1a IN (SELECT   min(t2a)
               FROM     t2
               GROUP BY t2c
               HAVING   t2c IN (SELECT   max(t3c)
                                FROM     t3
                                GROUP BY t3b
                                HAVING   t3b > t2b ))
-- !query 4 schema
struct<>
-- !query 4 output
org.apache.spark.sql.AnalysisException
resolved attribute(s) t2b#x missing from min(t2a)#x,t2c#x in operator !Filter t2c#x IN (list#x [t2b#x]);


-- !query 5
SELECT t1a 
FROM   t1
GROUP  BY 1
HAVING EXISTS (SELECT 1 
               FROM  t2
               WHERE t2a < min(t1a + t2a))
-- !query 5 schema
struct<>
-- !query 5 output
org.apache.spark.sql.AnalysisException
Found an aggregate expression in a correlated predicate that has both outer and local references, which is not supported yet. Aggregate expression: min((t1.`t1a` + t2.`t2a`)), Outer references: t1.`t1a`, Local references: t2.`t2a`.;


-- !query 6
SELECT t1a 
FROM   t1
WHERE  t1a IN (SELECT t2a 
               FROM   t2
               WHERE  EXISTS (SELECT 1 
                              FROM   t3
                              GROUP BY 1
                              HAVING min(t2a + t3a) > 1))
-- !query 6 schema
struct<>
-- !query 6 output
org.apache.spark.sql.AnalysisException
Found an aggregate expression in a correlated predicate that has both outer and local references, which is not supported yet. Aggregate expression: min((t2.`t2a` + t3.`t3a`)), Outer references: t2.`t2a`, Local references: t3.`t3a`.;


-- !query 7
SELECT t1a 
FROM   t1
WHERE  t1a IN (SELECT t2a 
               FROM   t2
               WHERE  EXISTS (SELECT min(t2a) 
                              FROM   t3))
-- !query 7 schema
struct<>
-- !query 7 output
org.apache.spark.sql.AnalysisException
Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Aggregate [min(outer(t2a#x)) AS min(outer())#x]
+- SubqueryAlias t3
   +- Project [t3a#x, t3b#x, t3c#x]
      +- SubqueryAlias t3
         +- LocalRelation [t3a#x, t3b#x, t3c#x]
;