-- Automatically generated by SQLQueryTestSuite
-- !query
CREATE OR REPLACE TEMPORARY VIEW INT2_TBL(f1) AS VALUES
  (smallint(trim('0   '))),
  (smallint(trim('  1234 '))),
  (smallint(trim('    -1234'))),
  (smallint('32767')),
  (smallint('-32767'))
-- !query analysis
CreateViewCommand `INT2_TBL`, [(f1,None)], VALUES
  (smallint(trim('0   '))),
  (smallint(trim('  1234 '))),
  (smallint(trim('    -1234'))),
  (smallint('32767')),
  (smallint('-32767')), false, true, LocalTempView, true
   +- LocalRelation [col1#x]


-- !query
CREATE OR REPLACE TEMPORARY VIEW INT4_TBL AS SELECT * FROM
  (VALUES (0), (123456), (-123456), (2147483647), (-2147483647))
  AS v(f1)
-- !query analysis
CreateViewCommand `INT4_TBL`, SELECT * FROM
  (VALUES (0), (123456), (-123456), (2147483647), (-2147483647))
  AS v(f1), false, true, LocalTempView, true
   +- Project [f1#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x]
            +- LocalRelation [col1#x]


-- !query
CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM
  (VALUES
    (123, 456),
    (123, 4567890123456789),
    (4567890123456789, 123),
    (4567890123456789, 4567890123456789),
    (4567890123456789, -4567890123456789))
  AS v(q1, q2)
-- !query analysis
CreateViewCommand `INT8_TBL`, SELECT * FROM
  (VALUES
    (123, 456),
    (123, 4567890123456789),
    (4567890123456789, 123),
    (4567890123456789, 4567890123456789),
    (4567890123456789, -4567890123456789))
  AS v(q1, q2), false, true, LocalTempView, true
   +- Project [q1#xL, q2#xL]
      +- SubqueryAlias v
         +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
            +- LocalRelation [col1#xL, col2#xL]


-- !query
CREATE OR REPLACE TEMPORARY VIEW FLOAT8_TBL AS SELECT * FROM
  (VALUES (0.0), (1004.30), (-34.84),
    (cast('1.2345678901234e+200' as double)), (cast('1.2345678901234e-200' as double)))
  AS v(f1)
-- !query analysis
CreateViewCommand `FLOAT8_TBL`, SELECT * FROM
  (VALUES (0.0), (1004.30), (-34.84),
    (cast('1.2345678901234e+200' as double)), (cast('1.2345678901234e-200' as double)))
  AS v(f1), false, true, LocalTempView, true
   +- Project [f1#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x]
            +- LocalRelation [col1#x]


-- !query
CREATE OR REPLACE TEMPORARY VIEW TEXT_TBL AS SELECT * FROM
  (VALUES ('doh!'), ('hi de ho neighbor'))
  AS v(f1)
-- !query analysis
CreateViewCommand `TEXT_TBL`, SELECT * FROM
  (VALUES ('doh!'), ('hi de ho neighbor'))
  AS v(f1), false, true, LocalTempView, true
   +- Project [f1#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x]
            +- LocalRelation [col1#x]


-- !query
CREATE OR REPLACE TEMPORARY VIEW tenk2 AS SELECT * FROM tenk1
-- !query analysis
CreateViewCommand `tenk2`, SELECT * FROM tenk1, false, true, LocalTempView, true
   +- Project [unique1#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, fivethous#x, tenthous#x, odd#x, even#x, stringu1#x, stringu2#x, string4#x]
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
CREATE TABLE J1_TBL (
  i integer,
  j integer,
  t string
) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`J1_TBL`, false


-- !query
CREATE TABLE J2_TBL (
  i integer,
  k integer
) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`J2_TBL`, false


-- !query
INSERT INTO J1_TBL VALUES (1, 4, 'one')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (2, 3, 'two')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (3, 2, 'three')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (4, 1, 'four')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (5, 0, 'five')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (6, 6, 'six')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (7, 7, 'seven')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (8, 8, 'eight')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (0, NULL, 'zero')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (NULL, NULL, 'null')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J1_TBL VALUES (NULL, 0, 'zero')
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1_tbl], Append, `spark_catalog`.`default`.`j1_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1_tbl), [i, j, t]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS j#x, cast(col3#x as string) AS t#x]
   +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
INSERT INTO J2_TBL VALUES (1, -1)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (2, 2)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (3, -3)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (2, 4)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (5, -5)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (5, -5)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (0, NULL)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (NULL, NULL)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO J2_TBL VALUES (NULL, 0)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2_tbl, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2_tbl], Append, `spark_catalog`.`default`.`j2_tbl`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2_tbl), [i, k]
+- Project [cast(col1#x as int) AS i#x, cast(col2#x as int) AS k#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t)
  FROM J1_TBL AS tx
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x]
+- SubqueryAlias tx
   +- SubqueryAlias spark_catalog.default.j1_tbl
      +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet


-- !query
SELECT udf(udf('')) AS `xxx`, udf(udf(i)), udf(j), udf(t)
  FROM J1_TBL tx
-- !query analysis
Project [cast(udf(cast(cast(udf(cast( as string)) as string) as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) AS udf(udf(i))#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x]
+- SubqueryAlias tx
   +- SubqueryAlias spark_catalog.default.j1_tbl
      +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet


-- !query
SELECT udf('') AS `xxx`, a, udf(udf(b)), c
  FROM J1_TBL AS t1 (a, b, c)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, a#x, cast(udf(cast(cast(udf(cast(b#x as string)) as int) as string)) as int) AS udf(udf(b))#x, c#x]
+- SubqueryAlias t1
   +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
      +- SubqueryAlias spark_catalog.default.j1_tbl
         +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(a), udf(b), udf(udf(c))
  FROM J1_TBL t1 (a, b, c)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(a#x as string)) as int) AS udf(a)#x, cast(udf(cast(b#x as string)) as int) AS udf(b)#x, cast(udf(cast(cast(udf(cast(c#x as string)) as string) as string)) as string) AS udf(udf(c))#x]
+- SubqueryAlias t1
   +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
      +- SubqueryAlias spark_catalog.default.j1_tbl
         +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(a), b, udf(c), udf(d), e
  FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(a#x as string)) as int) AS udf(a)#x, b#x, cast(udf(cast(c#x as string)) as string) AS udf(c)#x, cast(udf(cast(d#x as string)) as int) AS udf(d)#x, e#x]
+- Join Inner
   :- SubqueryAlias t1
   :  +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
   :     +- SubqueryAlias spark_catalog.default.j1_tbl
   :        +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias t2
      +- Project [i#x AS d#x, k#x AS e#x]
         +- SubqueryAlias spark_catalog.default.j2_tbl
            +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, *
  FROM J1_TBL CROSS JOIN J2_TBL
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, i#x, j#x, t#x, i#x, k#x]
+- Join Cross
   :- SubqueryAlias spark_catalog.default.j1_tbl
   :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias spark_catalog.default.j2_tbl
      +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i) AS i, udf(k), udf(t) AS t
  FROM J1_TBL CROSS JOIN J2_TBL
-- !query analysis
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "AMBIGUOUS_REFERENCE",
  "sqlState" : "42704",
  "messageParameters" : {
    "name" : "`i`",
    "referenceNames" : "[`spark_catalog`.`default`.`j1_tbl`.`i`, `spark_catalog`.`default`.`j2_tbl`.`i`]"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 30,
    "stopIndex" : 30,
    "fragment" : "i"
  } ]
}


-- !query
SELECT udf('') AS `xxx`, udf(t1.i) AS i, udf(k), udf(t)
  FROM J1_TBL t1 CROSS JOIN J2_TBL t2
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS i#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x]
+- Join Cross
   :- SubqueryAlias t1
   :  +- SubqueryAlias spark_catalog.default.j1_tbl
   :     +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias t2
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf(udf('')) AS `xxx`, udf(udf(ii)) AS ii, udf(udf(tt)) AS tt, udf(udf(kk))
  FROM (J1_TBL CROSS JOIN J2_TBL)
    AS tx (ii, jj, tt, ii2, kk)
-- !query analysis
Project [cast(udf(cast(cast(udf(cast( as string)) as string) as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(ii#x as string)) as int) as string)) as int) AS ii#x, cast(udf(cast(cast(udf(cast(tt#x as string)) as string) as string)) as string) AS tt#x, cast(udf(cast(cast(udf(cast(kk#x as string)) as int) as string)) as int) AS udf(udf(kk))#x]
+- SubqueryAlias tx
   +- Project [i#x AS ii#x, j#x AS jj#x, t#x AS tt#x, i#x AS ii2#x, k#x AS kk#x]
      +- Join Cross
         :- SubqueryAlias spark_catalog.default.j1_tbl
         :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
         +- SubqueryAlias spark_catalog.default.j2_tbl
            +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(udf(j1_tbl.i)), udf(j), udf(t), udf(a.i), udf(a.k), udf(b.i),  udf(b.k)
  FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) AS udf(udf(i))#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Join Cross
   :- Join Cross
   :  :- SubqueryAlias spark_catalog.default.j1_tbl
   :  :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   :  +- SubqueryAlias a
   :     +- SubqueryAlias spark_catalog.default.j2_tbl
   :        +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet
   +- SubqueryAlias b
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i) AS i, udf(j), udf(t) AS t, udf(k)
  FROM J1_TBL INNER JOIN J2_TBL USING (i)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS i#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS t#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Project [i#x, j#x, t#x, k#x]
   +- Join Inner, (i#x = i#x)
      :- SubqueryAlias spark_catalog.default.j1_tbl
      :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf(udf('')) AS `xxx`, udf(i), udf(j) AS j, udf(t), udf(k) AS k
  FROM J1_TBL JOIN J2_TBL USING (i)
-- !query analysis
Project [cast(udf(cast(cast(udf(cast( as string)) as string) as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS j#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS k#x]
+- Project [i#x, j#x, t#x, k#x]
   +- Join Inner, (i#x = i#x)
      :- SubqueryAlias spark_catalog.default.j1_tbl
      :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, *
  FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a)
  ORDER BY udf(udf(a)), udf(d)
-- !query analysis
Sort [cast(udf(cast(cast(udf(cast(a#x as string)) as int) as string)) as int) ASC NULLS FIRST, cast(udf(cast(d#x as string)) as int) ASC NULLS FIRST], true
+- Project [cast(udf(cast( as string)) as string) AS xxx#x, a#x, b#x, c#x, d#x]
   +- Project [a#x, b#x, c#x, d#x]
      +- Join Inner, (a#x = a#x)
         :- SubqueryAlias t1
         :  +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
         :     +- SubqueryAlias spark_catalog.default.j1_tbl
         :        +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
         +- SubqueryAlias t2
            +- Project [i#x AS a#x, k#x AS d#x]
               +- SubqueryAlias spark_catalog.default.j2_tbl
                  +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf(udf('')) AS `xxx`, udf(i), udf(j), udf(t), udf(k)
  FROM J1_TBL NATURAL JOIN J2_TBL
-- !query analysis
Project [cast(udf(cast(cast(udf(cast( as string)) as string) as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Project [i#x, j#x, t#x, k#x]
   +- Join Inner, (i#x = i#x)
      :- SubqueryAlias spark_catalog.default.j1_tbl
      :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(udf(udf(a))) AS a, udf(b), udf(c), udf(d)
  FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(cast(udf(cast(a#x as string)) as int) as string)) as int) as string)) as int) AS a#x, cast(udf(cast(b#x as string)) as int) AS udf(b)#x, cast(udf(cast(c#x as string)) as string) AS udf(c)#x, cast(udf(cast(d#x as string)) as int) AS udf(d)#x]
+- Project [a#x, b#x, c#x, d#x]
   +- Join Inner, (a#x = a#x)
      :- SubqueryAlias t1
      :  +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
      :     +- SubqueryAlias spark_catalog.default.j1_tbl
      :        +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias t2
         +- Project [i#x AS a#x, k#x AS d#x]
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(udf(a)), udf(udf(b)), udf(udf(c)) AS c, udf(udf(udf(d))) AS d
  FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(a#x as string)) as int) as string)) as int) AS udf(udf(a))#x, cast(udf(cast(cast(udf(cast(b#x as string)) as int) as string)) as int) AS udf(udf(b))#x, cast(udf(cast(cast(udf(cast(c#x as string)) as string) as string)) as string) AS c#x, cast(udf(cast(cast(udf(cast(cast(udf(cast(d#x as string)) as int) as string)) as int) as string)) as int) AS d#x]
+- Project [a#x, b#x, c#x, d#x]
   +- Join Inner, (a#x = a#x)
      :- SubqueryAlias t1
      :  +- Project [i#x AS a#x, j#x AS b#x, t#x AS c#x]
      :     +- SubqueryAlias spark_catalog.default.j1_tbl
      :        +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias t2
         +- Project [i#x AS d#x, k#x AS a#x]
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(J1_TBL.i), udf(udf(J1_TBL.j)), udf(J1_TBL.t), udf(J2_TBL.i), udf(J2_TBL.k)
  FROM J1_TBL JOIN J2_TBL ON (udf(J1_TBL.i) = J2_TBL.i)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(cast(udf(cast(j#x as string)) as int) as string)) as int) AS udf(udf(j))#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Join Inner, (cast(udf(cast(i#x as string)) as int) = i#x)
   :- SubqueryAlias spark_catalog.default.j1_tbl
   :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias spark_catalog.default.j2_tbl
      +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(udf(J1_TBL.i)), udf(udf(J1_TBL.j)), udf(udf(J1_TBL.t)), J2_TBL.i, J2_TBL.k
  FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = udf(J2_TBL.k))
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) AS udf(udf(i))#x, cast(udf(cast(cast(udf(cast(j#x as string)) as int) as string)) as int) AS udf(udf(j))#x, cast(udf(cast(cast(udf(cast(t#x as string)) as string) as string)) as string) AS udf(udf(t))#x, i#x, k#x]
+- Join Inner, (i#x = cast(udf(cast(k#x as string)) as int))
   :- SubqueryAlias spark_catalog.default.j1_tbl
   :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias spark_catalog.default.j2_tbl
      +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(J1_TBL.i), udf(J1_TBL.j), udf(J1_TBL.t), udf(J2_TBL.i), udf(J2_TBL.k)
  FROM J1_TBL JOIN J2_TBL ON (udf(J1_TBL.i) <= udf(udf(J2_TBL.k)))
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Join Inner, (cast(udf(cast(i#x as string)) as int) <= cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int))
   :- SubqueryAlias spark_catalog.default.j1_tbl
   :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias spark_catalog.default.j2_tbl
      +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf(udf('')) AS `xxx`, udf(i), udf(j), udf(t), udf(k)
  FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i)
  ORDER BY udf(udf(i)), udf(k), udf(t)
-- !query analysis
Project [xxx#x, udf(i)#x, udf(j)#x, udf(t)#x, udf(k)#x]
+- Sort [cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) ASC NULLS FIRST, cast(udf(cast(k#x as string)) as int) ASC NULLS FIRST, cast(udf(cast(t#x as string)) as string) ASC NULLS FIRST], true
   +- Project [cast(udf(cast(cast(udf(cast( as string)) as string) as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x, i#x, k#x, t#x]
      +- Project [i#x, j#x, t#x, k#x]
         +- Join LeftOuter, (i#x = i#x)
            :- SubqueryAlias spark_catalog.default.j1_tbl
            :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(k)
  FROM J1_TBL LEFT JOIN J2_TBL USING (i)
  ORDER BY udf(i), udf(udf(k)), udf(t)
-- !query analysis
Project [xxx#x, udf(i)#x, udf(j)#x, udf(t)#x, udf(k)#x]
+- Sort [cast(udf(cast(i#x as string)) as int) ASC NULLS FIRST, cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int) ASC NULLS FIRST, cast(udf(cast(t#x as string)) as string) ASC NULLS FIRST], true
   +- Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x, i#x, k#x, t#x]
      +- Project [i#x, j#x, t#x, k#x]
         +- Join LeftOuter, (i#x = i#x)
            :- SubqueryAlias spark_catalog.default.j1_tbl
            :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(udf(i)), udf(j), udf(t), udf(k)
  FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) AS udf(udf(i))#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Project [i#x, j#x, t#x, k#x]
   +- Join RightOuter, (i#x = i#x)
      :- SubqueryAlias spark_catalog.default.j1_tbl
      :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(udf(j)), udf(t), udf(k)
  FROM J1_TBL RIGHT JOIN J2_TBL USING (i)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(cast(udf(cast(j#x as string)) as int) as string)) as int) AS udf(udf(j))#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Project [i#x, j#x, t#x, k#x]
   +- Join RightOuter, (i#x = i#x)
      :- SubqueryAlias spark_catalog.default.j1_tbl
      :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
      +- SubqueryAlias spark_catalog.default.j2_tbl
         +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), udf(udf(t)), udf(k)
  FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i)
  ORDER BY udf(udf(i)), udf(k), udf(t)
-- !query analysis
Project [xxx#x, udf(i)#x, udf(j)#x, udf(udf(t))#x, udf(k)#x]
+- Sort [cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) ASC NULLS FIRST, cast(udf(cast(k#x as string)) as int) ASC NULLS FIRST, cast(udf(cast(t#x as string)) as string) ASC NULLS FIRST], true
   +- Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(cast(udf(cast(t#x as string)) as string) as string)) as string) AS udf(udf(t))#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x, i#x, k#x, t#x]
      +- Project [coalesce(i#x, i#x) AS i#x, j#x, t#x, k#x]
         +- Join FullOuter, (i#x = i#x)
            :- SubqueryAlias spark_catalog.default.j1_tbl
            :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), t, udf(udf(k))
  FROM J1_TBL FULL JOIN J2_TBL USING (i)
  ORDER BY udf(udf(i)), udf(k), udf(udf(t))
-- !query analysis
Project [xxx#x, udf(i)#x, udf(j)#x, t#x, udf(udf(k))#x]
+- Sort [cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) ASC NULLS FIRST, cast(udf(cast(k#x as string)) as int) ASC NULLS FIRST, cast(udf(cast(cast(udf(cast(t#x as string)) as string) as string)) as string) ASC NULLS FIRST], true
   +- Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, t#x, cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int) AS udf(udf(k))#x, i#x, k#x]
      +- Project [coalesce(i#x, i#x) AS i#x, j#x, t#x, k#x]
         +- Join FullOuter, (i#x = i#x)
            :- SubqueryAlias spark_catalog.default.j1_tbl
            :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(udf(k))
  FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (udf(k) = 1)
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int) AS udf(udf(k))#x]
+- Filter (cast(udf(cast(k#x as string)) as int) = 1)
   +- Project [i#x, j#x, t#x, k#x]
      +- Join LeftOuter, (i#x = i#x)
         :- SubqueryAlias spark_catalog.default.j1_tbl
         :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
         +- SubqueryAlias spark_catalog.default.j2_tbl
            +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(k)
  FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (udf(udf(i)) = udf(1))
-- !query analysis
Project [cast(udf(cast( as string)) as string) AS xxx#x, cast(udf(cast(i#x as string)) as int) AS udf(i)#x, cast(udf(cast(j#x as string)) as int) AS udf(j)#x, cast(udf(cast(t#x as string)) as string) AS udf(t)#x, cast(udf(cast(k#x as string)) as int) AS udf(k)#x]
+- Filter (cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) = cast(udf(cast(1 as string)) as int))
   +- Project [i#x, j#x, t#x, k#x]
      +- Join LeftOuter, (i#x = i#x)
         :- SubqueryAlias spark_catalog.default.j1_tbl
         :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
         +- SubqueryAlias spark_catalog.default.j2_tbl
            +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
CREATE TABLE t1 (name STRING, n INTEGER) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`t1`, false


-- !query
CREATE TABLE t2 (name STRING, n INTEGER) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`t2`, false


-- !query
CREATE TABLE t3 (name STRING, n INTEGER) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`t3`, false


-- !query
INSERT INTO t1 VALUES ( 'bb', 11 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t1, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t1], Append, `spark_catalog`.`default`.`t1`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t1), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t2 VALUES ( 'bb', 12 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t2, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t2], Append, `spark_catalog`.`default`.`t2`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t2), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t2 VALUES ( 'cc', 22 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t2, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t2], Append, `spark_catalog`.`default`.`t2`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t2), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t2 VALUES ( 'ee', 42 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t2, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t2], Append, `spark_catalog`.`default`.`t2`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t2), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t3 VALUES ( 'bb', 13 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t3, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t3], Append, `spark_catalog`.`default`.`t3`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t3), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t3 VALUES ( 'cc', 23 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t3, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t3], Append, `spark_catalog`.`default`.`t3`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t3), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO t3 VALUES ( 'dd', 33 )
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/t3, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/t3], Append, `spark_catalog`.`default`.`t3`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/t3), [name, n]
+- Project [cast(col1#x as string) AS name#x, cast(col2#x as int) AS n#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name)
-- !query analysis
Project [name#x, n#x, n#x, n#x]
+- Project [coalesce(name#x, name#x) AS name#x, n#x, n#x, n#x]
   +- Join FullOuter, (name#x = name#x)
      :- Project [coalesce(name#x, name#x) AS name#x, n#x, n#x]
      :  +- Join FullOuter, (name#x = name#x)
      :     :- SubqueryAlias spark_catalog.default.t1
      :     :  +- Relation spark_catalog.default.t1[name#x,n#x] parquet
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias spark_catalog.default.t3
         +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(name) as name, t2.n FROM t2) as s2
INNER JOIN
(SELECT udf(udf(name)) as name, t3.n FROM t3) s3
USING (name)
-- !query analysis
Project [name#x, n#x, n#x]
+- Project [name#x, n#x, n#x]
   +- Join Inner, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, n#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, n#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(udf(name)) as name, t2.n FROM t2) as s2
LEFT JOIN
(SELECT udf(name) as name, t3.n FROM t3) s3
USING (name)
-- !query analysis
Project [name#x, n#x, n#x]
+- Project [name#x, n#x, n#x]
   +- Join LeftOuter, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, n#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, n#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT udf(name), udf(udf(s2.n)), udf(s3.n) FROM
(SELECT * FROM t2) as s2
FULL JOIN
(SELECT * FROM t3) s3
USING (name)
-- !query analysis
Project [cast(udf(cast(name#x as string)) as string) AS udf(name)#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS udf(udf(n))#x, cast(udf(cast(n#x as string)) as int) AS udf(n)#x]
+- Project [coalesce(name#x, name#x) AS name#x, n#x, n#x]
   +- Join FullOuter, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [name#x, n#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [name#x, n#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(udf(name)) as name, udf(n) as s2_n, udf(2) as s2_2 FROM t2) as s2
NATURAL INNER JOIN
(SELECT udf(name) as name, udf(udf(n)) as s3_n, udf(3) as s3_2 FROM t3) s3
-- !query analysis
Project [name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
+- Project [name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
   +- Join Inner, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s2_n#x, cast(udf(cast(2 as string)) as int) AS s2_2#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s3_n#x, cast(udf(cast(3 as string)) as int) AS s3_2#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(name) as name, udf(udf(n)) as s2_n, 2 as s2_2 FROM t2) as s2
NATURAL LEFT JOIN
(SELECT udf(udf(name)) as name, udf(n) as s3_n, 3 as s3_2 FROM t3) s3
-- !query analysis
Project [name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
+- Project [name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
   +- Join LeftOuter, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s2_n#x, 2 AS s2_2#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s3_n#x, 3 AS s3_2#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(name) as name, udf(n) as s2_n, 2 as s2_2 FROM t2) as s2
NATURAL FULL JOIN
(SELECT udf(udf(name)) as name, udf(udf(n)) as s3_n, 3 as s3_2 FROM t3) s3
-- !query analysis
Project [name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
+- Project [coalesce(name#x, name#x) AS name#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
   +- Join FullOuter, (name#x = name#x)
      :- SubqueryAlias s2
      :  +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s2_n#x, 2 AS s2_2#x]
      :     +- SubqueryAlias spark_catalog.default.t2
      :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s3_n#x, 3 AS s3_2#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(udf(name)) as name, udf(n) as s1_n, 1 as s1_1 FROM t1) as s1
NATURAL INNER JOIN
(SELECT udf(name) as name, udf(n) as s2_n, 2 as s2_2 FROM t2) as s2
NATURAL INNER JOIN
(SELECT udf(udf(udf(name))) as name, udf(n) as s3_n, 3 as s3_2 FROM t3) s3
-- !query analysis
Project [name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
+- Project [name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
   +- Join Inner, (name#x = name#x)
      :- Project [name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x]
      :  +- Join Inner, (name#x = name#x)
      :     :- SubqueryAlias s1
      :     :  +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s1_n#x, 1 AS s1_1#x]
      :     :     +- SubqueryAlias spark_catalog.default.t1
      :     :        +- Relation spark_catalog.default.t1[name#x,n#x] parquet
      :     +- SubqueryAlias s2
      :        +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s2_n#x, 2 AS s2_2#x]
      :           +- SubqueryAlias spark_catalog.default.t2
      :              +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s3_n#x, 3 AS s3_2#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT udf(name) as name, udf(n) as s1_n, udf(udf(1)) as s1_1 FROM t1) as s1
NATURAL FULL JOIN
(SELECT udf(name) as name, udf(udf(n)) as s2_n, udf(2) as s2_2 FROM t2) as s2
NATURAL FULL JOIN
(SELECT udf(udf(name)) as name, udf(n) as s3_n, udf(3) as s3_2 FROM t3) s3
-- !query analysis
Project [name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
+- Project [coalesce(name#x, name#x) AS name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x, s3_n#x, s3_2#x]
   +- Join FullOuter, (name#x = name#x)
      :- Project [coalesce(name#x, name#x) AS name#x, s1_n#x, s1_1#x, s2_n#x, s2_2#x]
      :  +- Join FullOuter, (name#x = name#x)
      :     :- SubqueryAlias s1
      :     :  +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s1_n#x, cast(udf(cast(cast(udf(cast(1 as string)) as int) as string)) as int) AS s1_1#x]
      :     :     +- SubqueryAlias spark_catalog.default.t1
      :     :        +- Relation spark_catalog.default.t1[name#x,n#x] parquet
      :     +- SubqueryAlias s2
      :        +- Project [cast(udf(cast(name#x as string)) as string) AS name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s2_n#x, cast(udf(cast(2 as string)) as int) AS s2_2#x]
      :           +- SubqueryAlias spark_catalog.default.t2
      :              +- Relation spark_catalog.default.t2[name#x,n#x] parquet
      +- SubqueryAlias s3
         +- Project [cast(udf(cast(cast(udf(cast(name#x as string)) as string) as string)) as string) AS name#x, cast(udf(cast(n#x as string)) as int) AS s3_n#x, cast(udf(cast(3 as string)) as int) AS s3_2#x]
            +- SubqueryAlias spark_catalog.default.t3
               +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT name, udf(udf(s1_n)), udf(s2_n), udf(s3_n) FROM
(SELECT name, udf(udf(n)) as s1_n FROM t1) as s1
NATURAL FULL JOIN
  (SELECT * FROM
    (SELECT name, udf(n) as s2_n FROM t2) as s2
    NATURAL FULL JOIN
    (SELECT name, udf(udf(n)) as s3_n FROM t3) as s3
  ) ss2
-- !query analysis
Project [name#x, cast(udf(cast(cast(udf(cast(s1_n#x as string)) as int) as string)) as int) AS udf(udf(s1_n))#x, cast(udf(cast(s2_n#x as string)) as int) AS udf(s2_n)#x, cast(udf(cast(s3_n#x as string)) as int) AS udf(s3_n)#x]
+- Project [coalesce(name#x, name#x) AS name#x, s1_n#x, s2_n#x, s3_n#x]
   +- Join FullOuter, (name#x = name#x)
      :- SubqueryAlias s1
      :  +- Project [name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s1_n#x]
      :     +- SubqueryAlias spark_catalog.default.t1
      :        +- Relation spark_catalog.default.t1[name#x,n#x] parquet
      +- SubqueryAlias ss2
         +- Project [name#x, s2_n#x, s3_n#x]
            +- Project [coalesce(name#x, name#x) AS name#x, s2_n#x, s3_n#x]
               +- Join FullOuter, (name#x = name#x)
                  :- SubqueryAlias s2
                  :  +- Project [name#x, cast(udf(cast(n#x as string)) as int) AS s2_n#x]
                  :     +- SubqueryAlias spark_catalog.default.t2
                  :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
                  +- SubqueryAlias s3
                     +- Project [name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s3_n#x]
                        +- SubqueryAlias spark_catalog.default.t3
                           +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT * FROM
(SELECT name, n as s1_n FROM t1) as s1
NATURAL FULL JOIN
  (SELECT * FROM
    (SELECT name, udf(udf(n)) as s2_n, 2 as s2_2 FROM t2) as s2
    NATURAL FULL JOIN
    (SELECT name, udf(n) as s3_n FROM t3) as s3
  ) ss2
-- !query analysis
Project [name#x, s1_n#x, s2_n#x, s2_2#x, s3_n#x]
+- Project [coalesce(name#x, name#x) AS name#x, s1_n#x, s2_n#x, s2_2#x, s3_n#x]
   +- Join FullOuter, (name#x = name#x)
      :- SubqueryAlias s1
      :  +- Project [name#x, n#x AS s1_n#x]
      :     +- SubqueryAlias spark_catalog.default.t1
      :        +- Relation spark_catalog.default.t1[name#x,n#x] parquet
      +- SubqueryAlias ss2
         +- Project [name#x, s2_n#x, s2_2#x, s3_n#x]
            +- Project [coalesce(name#x, name#x) AS name#x, s2_n#x, s2_2#x, s3_n#x]
               +- Join FullOuter, (name#x = name#x)
                  :- SubqueryAlias s2
                  :  +- Project [name#x, cast(udf(cast(cast(udf(cast(n#x as string)) as int) as string)) as int) AS s2_n#x, 2 AS s2_2#x]
                  :     +- SubqueryAlias spark_catalog.default.t2
                  :        +- Relation spark_catalog.default.t2[name#x,n#x] parquet
                  +- SubqueryAlias s3
                     +- Project [name#x, cast(udf(cast(n#x as string)) as int) AS s3_n#x]
                        +- SubqueryAlias spark_catalog.default.t3
                           +- Relation spark_catalog.default.t3[name#x,n#x] parquet


-- !query
SELECT s1.name, udf(s1_n), s2.name, udf(udf(s2_n)) FROM
  (SELECT name, udf(n) as s1_n FROM t1) as s1
FULL JOIN
  (SELECT name, 2 as s2_n FROM t2) as s2
ON (udf(udf(s1_n)) = udf(s2_n))
-- !query analysis
Project [name#x, cast(udf(cast(s1_n#x as string)) as int) AS udf(s1_n)#x, name#x, cast(udf(cast(cast(udf(cast(s2_n#x as string)) as int) as string)) as int) AS udf(udf(s2_n))#x]
+- Join FullOuter, (cast(udf(cast(cast(udf(cast(s1_n#x as string)) as int) as string)) as int) = cast(udf(cast(s2_n#x as string)) as int))
   :- SubqueryAlias s1
   :  +- Project [name#x, cast(udf(cast(n#x as string)) as int) AS s1_n#x]
   :     +- SubqueryAlias spark_catalog.default.t1
   :        +- Relation spark_catalog.default.t1[name#x,n#x] parquet
   +- SubqueryAlias s2
      +- Project [name#x, 2 AS s2_n#x]
         +- SubqueryAlias spark_catalog.default.t2
            +- Relation spark_catalog.default.t2[name#x,n#x] parquet


-- !query
create or replace temporary view x as select * from
  (values (1,11), (2,22), (3,null), (4,44), (5,null))
  as v(x1, x2)
-- !query analysis
CreateViewCommand `x`, select * from
  (values (1,11), (2,22), (3,null), (4,44), (5,null))
  as v(x1, x2), false, true, LocalTempView, true
   +- Project [x1#x, x2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS x1#x, col2#x AS x2#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view y as select * from
  (values (1,111), (2,222), (3,333), (4,null))
  as v(y1, y2)
-- !query analysis
CreateViewCommand `y`, select * from
  (values (1,111), (2,222), (3,333), (4,null))
  as v(y1, y2), false, true, LocalTempView, true
   +- Project [y1#x, y2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS y1#x, col2#x AS y2#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select udf(udf(x1)), udf(x2) from x
-- !query analysis
Project [cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) AS udf(udf(x1))#x, cast(udf(cast(x2#x as string)) as int) AS udf(x2)#x]
+- SubqueryAlias x
   +- View (`x`, [x1#x, x2#x])
      +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
         +- Project [x1#x, x2#x]
            +- SubqueryAlias v
               +- Project [col1#x AS x1#x, col2#x AS x2#x]
                  +- LocalRelation [col1#x, col2#x]


-- !query
select udf(y1), udf(udf(y2)) from y
-- !query analysis
Project [cast(udf(cast(y1#x as string)) as int) AS udf(y1)#x, cast(udf(cast(cast(udf(cast(y2#x as string)) as int) as string)) as int) AS udf(udf(y2))#x]
+- SubqueryAlias y
   +- View (`y`, [y1#x, y2#x])
      +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
         +- Project [y1#x, y2#x]
            +- SubqueryAlias v
               +- Project [col1#x AS y1#x, col2#x AS y2#x]
                  +- LocalRelation [col1#x, col2#x]


-- !query
select * from x left join y on (udf(x1) = udf(udf(y1)) and udf(x2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x]
+- Join LeftOuter, ((cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(cast(udf(cast(y1#x as string)) as int) as string)) as int)) AND isnotnull(cast(udf(cast(x2#x as string)) as int)))
   :- SubqueryAlias x
   :  +- View (`x`, [x1#x, x2#x])
   :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :        +- Project [x1#x, x2#x]
   :           +- SubqueryAlias v
   :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :                 +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias y
      +- View (`y`, [y1#x, y2#x])
         +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
            +- Project [y1#x, y2#x]
               +- SubqueryAlias v
                  +- Project [col1#x AS y1#x, col2#x AS y2#x]
                     +- LocalRelation [col1#x, col2#x]


-- !query
select * from x left join y on (udf(udf(x1)) = udf(y1) and udf(y2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x]
+- Join LeftOuter, ((cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) = cast(udf(cast(y1#x as string)) as int)) AND isnotnull(cast(udf(cast(y2#x as string)) as int)))
   :- SubqueryAlias x
   :  +- View (`x`, [x1#x, x2#x])
   :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :        +- Project [x1#x, x2#x]
   :           +- SubqueryAlias v
   :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :                 +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias y
      +- View (`y`, [y1#x, y2#x])
         +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
            +- Project [y1#x, y2#x]
               +- SubqueryAlias v
                  +- Project [col1#x AS y1#x, col2#x AS y2#x]
                     +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(x1) = udf(udf(y1)))) left join x xx(xx1,xx2)
on (udf(udf(x1)) = udf(xx1))
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Join LeftOuter, (cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) = cast(udf(cast(xx1#x as string)) as int))
   :- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(cast(udf(cast(y1#x as string)) as int) as string)) as int))
   :  :- SubqueryAlias x
   :  :  +- View (`x`, [x1#x, x2#x])
   :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :  :        +- Project [x1#x, x2#x]
   :  :           +- SubqueryAlias v
   :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :  :                 +- LocalRelation [col1#x, col2#x]
   :  +- SubqueryAlias y
   :     +- View (`y`, [y1#x, y2#x])
   :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
   :           +- Project [y1#x, y2#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias xx
      +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
         +- SubqueryAlias x
            +- View (`x`, [x1#x, x2#x])
               +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                  +- Project [x1#x, x2#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS x1#x, col2#x AS x2#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2)
on (udf(x1) = xx1 and udf(x2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Join LeftOuter, ((cast(udf(cast(x1#x as string)) as int) = xx1#x) AND isnotnull(cast(udf(cast(x2#x as string)) as int)))
   :- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(y1#x as string)) as int))
   :  :- SubqueryAlias x
   :  :  +- View (`x`, [x1#x, x2#x])
   :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :  :        +- Project [x1#x, x2#x]
   :  :           +- SubqueryAlias v
   :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :  :                 +- LocalRelation [col1#x, col2#x]
   :  +- SubqueryAlias y
   :     +- View (`y`, [y1#x, y2#x])
   :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
   :           +- Project [y1#x, y2#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias xx
      +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
         +- SubqueryAlias x
            +- View (`x`, [x1#x, x2#x])
               +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                  +- Project [x1#x, x2#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS x1#x, col2#x AS x2#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (x1 = udf(y1))) left join x xx(xx1,xx2)
on (udf(x1) = udf(udf(xx1)) and udf(y2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Join LeftOuter, ((cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(cast(udf(cast(xx1#x as string)) as int) as string)) as int)) AND isnotnull(cast(udf(cast(y2#x as string)) as int)))
   :- Join LeftOuter, (x1#x = cast(udf(cast(y1#x as string)) as int))
   :  :- SubqueryAlias x
   :  :  +- View (`x`, [x1#x, x2#x])
   :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :  :        +- Project [x1#x, x2#x]
   :  :           +- SubqueryAlias v
   :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :  :                 +- LocalRelation [col1#x, col2#x]
   :  +- SubqueryAlias y
   :     +- View (`y`, [y1#x, y2#x])
   :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
   :           +- Project [y1#x, y2#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias xx
      +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
         +- SubqueryAlias x
            +- View (`x`, [x1#x, x2#x])
               +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                  +- Project [x1#x, x2#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS x1#x, col2#x AS x2#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(x1) = y1)) left join x xx(xx1,xx2)
on (udf(udf(x1)) = udf(xx1) and udf(udf(xx2)) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Join LeftOuter, ((cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) = cast(udf(cast(xx1#x as string)) as int)) AND isnotnull(cast(udf(cast(cast(udf(cast(xx2#x as string)) as int) as string)) as int)))
   :- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = y1#x)
   :  :- SubqueryAlias x
   :  :  +- View (`x`, [x1#x, x2#x])
   :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
   :  :        +- Project [x1#x, x2#x]
   :  :           +- SubqueryAlias v
   :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
   :  :                 +- LocalRelation [col1#x, col2#x]
   :  +- SubqueryAlias y
   :     +- View (`y`, [y1#x, y2#x])
   :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
   :           +- Project [y1#x, y2#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias xx
      +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
         +- SubqueryAlias x
            +- View (`x`, [x1#x, x2#x])
               +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                  +- Project [x1#x, x2#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS x1#x, col2#x AS x2#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(udf(x1)) = udf(udf(y1)))) left join x xx(xx1,xx2)
on (udf(x1) = udf(xx1)) where (udf(x2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Filter isnotnull(cast(udf(cast(x2#x as string)) as int))
   +- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(xx1#x as string)) as int))
      :- Join LeftOuter, (cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(y1#x as string)) as int) as string)) as int))
      :  :- SubqueryAlias x
      :  :  +- View (`x`, [x1#x, x2#x])
      :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
      :  :        +- Project [x1#x, x2#x]
      :  :           +- SubqueryAlias v
      :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
      :  :                 +- LocalRelation [col1#x, col2#x]
      :  +- SubqueryAlias y
      :     +- View (`y`, [y1#x, y2#x])
      :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
      :           +- Project [y1#x, y2#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
      :                    +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias xx
         +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
            +- SubqueryAlias x
               +- View (`x`, [x1#x, x2#x])
                  +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                     +- Project [x1#x, x2#x]
                        +- SubqueryAlias v
                           +- Project [col1#x AS x1#x, col2#x AS x2#x]
                              +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2)
on (udf(x1) = xx1) where (udf(y2) is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Filter isnotnull(cast(udf(cast(y2#x as string)) as int))
   +- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = xx1#x)
      :- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(y1#x as string)) as int))
      :  :- SubqueryAlias x
      :  :  +- View (`x`, [x1#x, x2#x])
      :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
      :  :        +- Project [x1#x, x2#x]
      :  :           +- SubqueryAlias v
      :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
      :  :                 +- LocalRelation [col1#x, col2#x]
      :  +- SubqueryAlias y
      :     +- View (`y`, [y1#x, y2#x])
      :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
      :           +- Project [y1#x, y2#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
      :                    +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias xx
         +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
            +- SubqueryAlias x
               +- View (`x`, [x1#x, x2#x])
                  +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                     +- Project [x1#x, x2#x]
                        +- SubqueryAlias v
                           +- Project [col1#x AS x1#x, col2#x AS x2#x]
                              +- LocalRelation [col1#x, col2#x]


-- !query
select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2)
on (x1 = udf(xx1)) where (xx2 is not null)
-- !query analysis
Project [x1#x, x2#x, y1#x, y2#x, xx1#x, xx2#x]
+- Filter isnotnull(xx2#x)
   +- Join LeftOuter, (x1#x = cast(udf(cast(xx1#x as string)) as int))
      :- Join LeftOuter, (cast(udf(cast(x1#x as string)) as int) = cast(udf(cast(y1#x as string)) as int))
      :  :- SubqueryAlias x
      :  :  +- View (`x`, [x1#x, x2#x])
      :  :     +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
      :  :        +- Project [x1#x, x2#x]
      :  :           +- SubqueryAlias v
      :  :              +- Project [col1#x AS x1#x, col2#x AS x2#x]
      :  :                 +- LocalRelation [col1#x, col2#x]
      :  +- SubqueryAlias y
      :     +- View (`y`, [y1#x, y2#x])
      :        +- Project [cast(y1#x as int) AS y1#x, cast(y2#x as int) AS y2#x]
      :           +- Project [y1#x, y2#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS y1#x, col2#x AS y2#x]
      :                    +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias xx
         +- Project [x1#x AS xx1#x, x2#x AS xx2#x]
            +- SubqueryAlias x
               +- View (`x`, [x1#x, x2#x])
                  +- Project [cast(x1#x as int) AS x1#x, cast(x2#x as int) AS x2#x]
                     +- Project [x1#x, x2#x]
                        +- SubqueryAlias v
                           +- Project [col1#x AS x1#x, col2#x AS x2#x]
                              +- LocalRelation [col1#x, col2#x]


-- !query
select udf(udf(count(*))) from tenk1 a where udf(udf(unique1)) in
  (select udf(unique1) from tenk1 b join tenk1 c using (unique1)
   where udf(udf(b.unique2)) = udf(42))
-- !query analysis
Aggregate [cast(udf(cast(cast(udf(cast(count(1) as string)) as bigint) as string)) as bigint) AS udf(udf(count(1)))#xL]
+- Filter cast(udf(cast(cast(udf(cast(unique1#x as string)) as int) as string)) as int) IN (list#x [])
   :  +- Project [cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x]
   :     +- Filter (cast(udf(cast(cast(udf(cast(unique2#x as string)) as int) as string)) as int) = cast(udf(cast(42 as string)) as int))
   :        +- Project [unique1#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, fivethous#x, tenthous#x, odd#x, even#x, stringu1#x, stringu2#x, string4#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, ... 7 more fields]
   :           +- Join Inner, (unique1#x = unique1#x)
   :              :- SubqueryAlias b
   :              :  +- SubqueryAlias spark_catalog.default.tenk1
   :              :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   :              +- SubqueryAlias c
   :                 +- SubqueryAlias spark_catalog.default.tenk1
   :                    +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   +- SubqueryAlias a
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(count(*)) from tenk1 x where
  udf(x.unique1) in (select udf(a.f1) from int4_tbl a,float8_tbl b where udf(udf(a.f1))=b.f1) and
  udf(x.unique1) = 0 and
  udf(x.unique1) in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=udf(udf(bb.f1)))
-- !query analysis
Aggregate [cast(udf(cast(count(1) as string)) as bigint) AS udf(count(1))#xL]
+- Filter ((cast(udf(cast(unique1#x as string)) as int) IN (list#x []) AND (cast(udf(cast(unique1#x as string)) as int) = 0)) AND cast(udf(cast(unique1#x as string)) as int) IN (list#x []))
   :  :- Project [cast(udf(cast(f1#x as string)) as int) AS udf(f1)#x]
   :  :  +- Filter (cast(cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int) as double) = f1#x)
   :  :     +- Join Inner
   :  :        :- SubqueryAlias a
   :  :        :  +- SubqueryAlias int4_tbl
   :  :        :     +- View (`INT4_TBL`, [f1#x])
   :  :        :        +- Project [cast(f1#x as int) AS f1#x]
   :  :        :           +- Project [f1#x]
   :  :        :              +- SubqueryAlias v
   :  :        :                 +- Project [col1#x AS f1#x]
   :  :        :                    +- LocalRelation [col1#x]
   :  :        +- SubqueryAlias b
   :  :           +- SubqueryAlias float8_tbl
   :  :              +- View (`FLOAT8_TBL`, [f1#x])
   :  :                 +- Project [cast(f1#x as double) AS f1#x]
   :  :                    +- Project [f1#x]
   :  :                       +- SubqueryAlias v
   :  :                          +- Project [col1#x AS f1#x]
   :  :                             +- LocalRelation [col1#x]
   :  +- Project [f1#x]
   :     +- Filter (cast(f1#x as double) = cast(udf(cast(cast(udf(cast(f1#x as string)) as double) as string)) as double))
   :        +- Join Inner
   :           :- SubqueryAlias aa
   :           :  +- SubqueryAlias int4_tbl
   :           :     +- View (`INT4_TBL`, [f1#x])
   :           :        +- Project [cast(f1#x as int) AS f1#x]
   :           :           +- Project [f1#x]
   :           :              +- SubqueryAlias v
   :           :                 +- Project [col1#x AS f1#x]
   :           :                    +- LocalRelation [col1#x]
   :           +- SubqueryAlias bb
   :              +- SubqueryAlias float8_tbl
   :                 +- View (`FLOAT8_TBL`, [f1#x])
   :                    +- Project [cast(f1#x as double) AS f1#x]
   :                       +- Project [f1#x]
   :                          +- SubqueryAlias v
   :                             +- Project [col1#x AS f1#x]
   :                                +- LocalRelation [col1#x]
   +- SubqueryAlias x
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(udf(count(*))) from tenk1 x where
  udf(x.unique1) in (select udf(a.f1) from int4_tbl a,float8_tbl b where udf(udf(a.f1))=b.f1) and
  udf(x.unique1) = 0 and
  udf(udf(x.unique1)) in (select udf(aa.f1) from int4_tbl aa,float8_tbl bb where udf(aa.f1)=udf(udf(bb.f1)))
-- !query analysis
Aggregate [cast(udf(cast(cast(udf(cast(count(1) as string)) as bigint) as string)) as bigint) AS udf(udf(count(1)))#xL]
+- Filter ((cast(udf(cast(unique1#x as string)) as int) IN (list#x []) AND (cast(udf(cast(unique1#x as string)) as int) = 0)) AND cast(udf(cast(cast(udf(cast(unique1#x as string)) as int) as string)) as int) IN (list#x []))
   :  :- Project [cast(udf(cast(f1#x as string)) as int) AS udf(f1)#x]
   :  :  +- Filter (cast(cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int) as double) = f1#x)
   :  :     +- Join Inner
   :  :        :- SubqueryAlias a
   :  :        :  +- SubqueryAlias int4_tbl
   :  :        :     +- View (`INT4_TBL`, [f1#x])
   :  :        :        +- Project [cast(f1#x as int) AS f1#x]
   :  :        :           +- Project [f1#x]
   :  :        :              +- SubqueryAlias v
   :  :        :                 +- Project [col1#x AS f1#x]
   :  :        :                    +- LocalRelation [col1#x]
   :  :        +- SubqueryAlias b
   :  :           +- SubqueryAlias float8_tbl
   :  :              +- View (`FLOAT8_TBL`, [f1#x])
   :  :                 +- Project [cast(f1#x as double) AS f1#x]
   :  :                    +- Project [f1#x]
   :  :                       +- SubqueryAlias v
   :  :                          +- Project [col1#x AS f1#x]
   :  :                             +- LocalRelation [col1#x]
   :  +- Project [cast(udf(cast(f1#x as string)) as int) AS udf(f1)#x]
   :     +- Filter (cast(cast(udf(cast(f1#x as string)) as int) as double) = cast(udf(cast(cast(udf(cast(f1#x as string)) as double) as string)) as double))
   :        +- Join Inner
   :           :- SubqueryAlias aa
   :           :  +- SubqueryAlias int4_tbl
   :           :     +- View (`INT4_TBL`, [f1#x])
   :           :        +- Project [cast(f1#x as int) AS f1#x]
   :           :           +- Project [f1#x]
   :           :              +- SubqueryAlias v
   :           :                 +- Project [col1#x AS f1#x]
   :           :                    +- LocalRelation [col1#x]
   :           +- SubqueryAlias bb
   :              +- SubqueryAlias float8_tbl
   :                 +- View (`FLOAT8_TBL`, [f1#x])
   :                    +- Project [cast(f1#x as double) AS f1#x]
   :                       +- Project [f1#x]
   :                          +- SubqueryAlias v
   :                             +- Project [col1#x AS f1#x]
   :                                +- LocalRelation [col1#x]
   +- SubqueryAlias x
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select * from int8_tbl i1 left join (int8_tbl i2 join
  (select udf(123) as x) ss on udf(udf(i2.q1)) = udf(x)) on udf(udf(i1.q2)) = udf(udf(i2.q2))
order by udf(udf(1)), 2
-- !query analysis
Sort [cast(udf(cast(cast(udf(cast(1 as string)) as int) as string)) as int) ASC NULLS FIRST, q2#xL ASC NULLS FIRST], true
+- Project [q1#xL, q2#xL, q1#xL, q2#xL, x#x]
   +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint) = cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint))
      :- SubqueryAlias i1
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- Join Inner, (cast(udf(cast(cast(udf(cast(q1#xL as string)) as bigint) as string)) as bigint) = cast(cast(udf(cast(x#x as string)) as int) as bigint))
         :- SubqueryAlias i2
         :  +- SubqueryAlias int8_tbl
         :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
         :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
         :           +- Project [q1#xL, q2#xL]
         :              +- SubqueryAlias v
         :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
         :                    +- LocalRelation [col1#xL, col2#xL]
         +- SubqueryAlias ss
            +- Project [cast(udf(cast(123 as string)) as int) AS x#x]
               +- OneRowRelation


-- !query
select udf(count(*))
from
  (select udf(t3.tenthous) as x1, udf(coalesce(udf(t1.stringu1), udf(t2.stringu1))) as x2
   from tenk1 t1
   left join tenk1 t2 on udf(t1.unique1) = udf(t2.unique1)
   join tenk1 t3 on t1.unique2 = udf(t3.unique2)) ss,
  tenk1 t4,
  tenk1 t5
where udf(t4.thousand) = udf(t5.unique1) and udf(udf(ss.x1)) = t4.tenthous and udf(ss.x2) = udf(udf(t5.stringu1))
-- !query analysis
Aggregate [cast(udf(cast(count(1) as string)) as bigint) AS udf(count(1))#xL]
+- Filter (((cast(udf(cast(thousand#x as string)) as int) = cast(udf(cast(unique1#x as string)) as int)) AND (cast(udf(cast(cast(udf(cast(x1#x as string)) as int) as string)) as int) = tenthous#x)) AND (cast(udf(cast(x2#x as string)) as string) = cast(udf(cast(cast(udf(cast(stringu1#x as string)) as string) as string)) as string)))
   +- Join Inner
      :- Join Inner
      :  :- SubqueryAlias ss
      :  :  +- Project [cast(udf(cast(tenthous#x as string)) as int) AS x1#x, cast(udf(cast(coalesce(cast(udf(cast(stringu1#x as string)) as string), cast(udf(cast(stringu1#x as string)) as string)) as string)) as string) AS x2#x]
      :  :     +- Join Inner, (unique2#x = cast(udf(cast(unique2#x as string)) as int))
      :  :        :- Join LeftOuter, (cast(udf(cast(unique1#x as string)) as int) = cast(udf(cast(unique1#x as string)) as int))
      :  :        :  :- SubqueryAlias t1
      :  :        :  :  +- SubqueryAlias spark_catalog.default.tenk1
      :  :        :  :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  :        :  +- SubqueryAlias t2
      :  :        :     +- SubqueryAlias spark_catalog.default.tenk1
      :  :        :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  :        +- SubqueryAlias t3
      :  :           +- SubqueryAlias spark_catalog.default.tenk1
      :  :              +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  +- SubqueryAlias t4
      :     +- SubqueryAlias spark_catalog.default.tenk1
      :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      +- SubqueryAlias t5
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(a.f1), udf(b.f1), udf(t.thousand), udf(t.tenthous) from
  tenk1 t,
  (select udf(udf(sum(udf(f1))+1)) as f1 from int4_tbl i4a) a,
  (select udf(sum(udf(f1))) as f1 from int4_tbl i4b) b
where b.f1 = udf(t.thousand) and udf(a.f1) = udf(b.f1) and udf((udf(a.f1)+udf(b.f1)+999)) = udf(udf(t.tenthous))
-- !query analysis
Project [cast(udf(cast(f1#xL as string)) as bigint) AS udf(f1)#xL, cast(udf(cast(f1#xL as string)) as bigint) AS udf(f1)#xL, cast(udf(cast(thousand#x as string)) as int) AS udf(thousand)#x, cast(udf(cast(tenthous#x as string)) as int) AS udf(tenthous)#x]
+- Filter (((f1#xL = cast(cast(udf(cast(thousand#x as string)) as int) as bigint)) AND (cast(udf(cast(f1#xL as string)) as bigint) = cast(udf(cast(f1#xL as string)) as bigint))) AND (cast(udf(cast(((cast(udf(cast(f1#xL as string)) as bigint) + cast(udf(cast(f1#xL as string)) as bigint)) + cast(999 as bigint)) as string)) as bigint) = cast(cast(udf(cast(cast(udf(cast(tenthous#x as string)) as int) as string)) as int) as bigint)))
   +- Join Inner
      :- Join Inner
      :  :- SubqueryAlias t
      :  :  +- SubqueryAlias spark_catalog.default.tenk1
      :  :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  +- SubqueryAlias a
      :     +- Aggregate [cast(udf(cast(cast(udf(cast((sum(cast(udf(cast(f1#x as string)) as int)) + cast(1 as bigint)) as string)) as bigint) as string)) as bigint) AS f1#xL]
      :        +- SubqueryAlias i4a
      :           +- SubqueryAlias int4_tbl
      :              +- View (`INT4_TBL`, [f1#x])
      :                 +- Project [cast(f1#x as int) AS f1#x]
      :                    +- Project [f1#x]
      :                       +- SubqueryAlias v
      :                          +- Project [col1#x AS f1#x]
      :                             +- LocalRelation [col1#x]
      +- SubqueryAlias b
         +- Aggregate [cast(udf(cast(sum(cast(udf(cast(f1#x as string)) as int)) as string)) as bigint) AS f1#xL]
            +- SubqueryAlias i4b
               +- SubqueryAlias int4_tbl
                  +- View (`INT4_TBL`, [f1#x])
                     +- Project [cast(f1#x as int) AS f1#x]
                        +- Project [f1#x]
                           +- SubqueryAlias v
                              +- Project [col1#x AS f1#x]
                                 +- LocalRelation [col1#x]


-- !query
select * from
  j1_tbl full join
  (select * from j2_tbl order by udf(udf(j2_tbl.i)) desc, udf(j2_tbl.k) asc) j2_tbl
  on udf(j1_tbl.i) = udf(j2_tbl.i) and udf(j1_tbl.i) = udf(j2_tbl.k)
-- !query analysis
Project [i#x, j#x, t#x, i#x, k#x]
+- Join FullOuter, ((cast(udf(cast(i#x as string)) as int) = cast(udf(cast(i#x as string)) as int)) AND (cast(udf(cast(i#x as string)) as int) = cast(udf(cast(k#x as string)) as int)))
   :- SubqueryAlias spark_catalog.default.j1_tbl
   :  +- Relation spark_catalog.default.j1_tbl[i#x,j#x,t#x] parquet
   +- SubqueryAlias j2_tbl
      +- Sort [cast(udf(cast(cast(udf(cast(i#x as string)) as int) as string)) as int) DESC NULLS LAST, cast(udf(cast(k#x as string)) as int) ASC NULLS FIRST], true
         +- Project [i#x, k#x]
            +- SubqueryAlias spark_catalog.default.j2_tbl
               +- Relation spark_catalog.default.j2_tbl[i#x,k#x] parquet


-- !query
select udf(count(*)) from
  (select * from tenk1 x order by udf(x.thousand), udf(udf(x.twothousand)), x.fivethous) x
  left join
  (select * from tenk1 y order by udf(y.unique2)) y
  on udf(x.thousand) = y.unique2 and x.twothousand = udf(y.hundred) and x.fivethous = y.unique2
-- !query analysis
Aggregate [cast(udf(cast(count(1) as string)) as bigint) AS udf(count(1))#xL]
+- Join LeftOuter, (((cast(udf(cast(thousand#x as string)) as int) = unique2#x) AND (twothousand#x = cast(udf(cast(hundred#x as string)) as int))) AND (fivethous#x = unique2#x))
   :- SubqueryAlias x
   :  +- Sort [cast(udf(cast(thousand#x as string)) as int) ASC NULLS FIRST, cast(udf(cast(cast(udf(cast(twothousand#x as string)) as int) as string)) as int) ASC NULLS FIRST, fivethous#x ASC NULLS FIRST], true
   :     +- Project [unique1#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, fivethous#x, tenthous#x, odd#x, even#x, stringu1#x, stringu2#x, string4#x]
   :        +- SubqueryAlias x
   :           +- SubqueryAlias spark_catalog.default.tenk1
   :              +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   +- SubqueryAlias y
      +- Sort [cast(udf(cast(unique2#x as string)) as int) ASC NULLS FIRST], true
         +- Project [unique1#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, fivethous#x, tenthous#x, odd#x, even#x, stringu1#x, stringu2#x, string4#x]
            +- SubqueryAlias y
               +- SubqueryAlias spark_catalog.default.tenk1
                  +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
DROP TABLE t1
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t1


-- !query
DROP TABLE t2
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t2


-- !query
DROP TABLE t3
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.t3


-- !query
DROP TABLE J1_TBL
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.J1_TBL


-- !query
DROP TABLE J2_TBL
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.J2_TBL


-- !query
create or replace temporary view tt1 as select * from
  (values (1, 11), (2, NULL))
  as v(tt1_id, joincol)
-- !query analysis
CreateViewCommand `tt1`, select * from
  (values (1, 11), (2, NULL))
  as v(tt1_id, joincol), false, true, LocalTempView, true
   +- Project [tt1_id#x, joincol#x]
      +- SubqueryAlias v
         +- Project [col1#x AS tt1_id#x, col2#x AS joincol#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view tt2 as select * from
  (values (21, 11), (22, 11))
  as v(tt2_id, joincol)
-- !query analysis
CreateViewCommand `tt2`, select * from
  (values (21, 11), (22, 11))
  as v(tt2_id, joincol), false, true, LocalTempView, true
   +- Project [tt2_id#x, joincol#x]
      +- SubqueryAlias v
         +- Project [col1#x AS tt2_id#x, col2#x AS joincol#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select tt1.*, tt2.* from tt1 left join tt2 on udf(udf(tt1.joincol)) = udf(tt2.joincol)
-- !query analysis
Project [tt1_id#x, joincol#x, tt2_id#x, joincol#x]
+- Join LeftOuter, (cast(udf(cast(cast(udf(cast(joincol#x as string)) as int) as string)) as int) = cast(udf(cast(joincol#x as string)) as int))
   :- SubqueryAlias tt1
   :  +- View (`tt1`, [tt1_id#x, joincol#x])
   :     +- Project [cast(tt1_id#x as int) AS tt1_id#x, cast(joincol#x as int) AS joincol#x]
   :        +- Project [tt1_id#x, joincol#x]
   :           +- SubqueryAlias v
   :              +- Project [col1#x AS tt1_id#x, col2#x AS joincol#x]
   :                 +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias tt2
      +- View (`tt2`, [tt2_id#x, joincol#x])
         +- Project [cast(tt2_id#x as int) AS tt2_id#x, cast(joincol#x as int) AS joincol#x]
            +- Project [tt2_id#x, joincol#x]
               +- SubqueryAlias v
                  +- Project [col1#x AS tt2_id#x, col2#x AS joincol#x]
                     +- LocalRelation [col1#x, col2#x]


-- !query
select tt1.*, tt2.* from tt2 right join tt1 on udf(udf(tt1.joincol)) = udf(udf(tt2.joincol))
-- !query analysis
Project [tt1_id#x, joincol#x, tt2_id#x, joincol#x]
+- Join RightOuter, (cast(udf(cast(cast(udf(cast(joincol#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(joincol#x as string)) as int) as string)) as int))
   :- SubqueryAlias tt2
   :  +- View (`tt2`, [tt2_id#x, joincol#x])
   :     +- Project [cast(tt2_id#x as int) AS tt2_id#x, cast(joincol#x as int) AS joincol#x]
   :        +- Project [tt2_id#x, joincol#x]
   :           +- SubqueryAlias v
   :              +- Project [col1#x AS tt2_id#x, col2#x AS joincol#x]
   :                 +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias tt1
      +- View (`tt1`, [tt1_id#x, joincol#x])
         +- Project [cast(tt1_id#x as int) AS tt1_id#x, cast(joincol#x as int) AS joincol#x]
            +- Project [tt1_id#x, joincol#x]
               +- SubqueryAlias v
                  +- Project [col1#x AS tt1_id#x, col2#x AS joincol#x]
                     +- LocalRelation [col1#x, col2#x]


-- !query
select udf(count(*)) from tenk1 a, tenk1 b
  where udf(a.hundred) = b.thousand and udf(udf((b.fivethous % 10)) < 10)
-- !query analysis
Aggregate [cast(udf(cast(count(1) as string)) as bigint) AS udf(count(1))#xL]
+- Filter ((cast(udf(cast(hundred#x as string)) as int) = thousand#x) AND cast(udf(cast((cast(udf(cast((fivethous#x % 10) as string)) as int) < 10) as string)) as boolean))
   +- Join Inner
      :- SubqueryAlias a
      :  +- SubqueryAlias spark_catalog.default.tenk1
      :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      +- SubqueryAlias b
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
DROP TABLE IF EXISTS tt3
-- !query analysis
DropTable true, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.tt3


-- !query
CREATE TABLE tt3(f1 int, f2 string) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`tt3`, false


-- !query
INSERT INTO tt3 SELECT x.id, repeat('xyzzy', 100) FROM range(1,10001) x
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/tt3, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/tt3], Append, `spark_catalog`.`default`.`tt3`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/tt3), [f1, f2]
+- Project [cast(id#xL as int) AS f1#x, cast(repeat(xyzzy, 100)#x as string) AS f2#x]
   +- Project [id#xL, repeat(xyzzy, 100) AS repeat(xyzzy, 100)#x]
      +- SubqueryAlias x
         +- Range (1, 10001, step=1)


-- !query
DROP TABLE IF EXISTS tt4
-- !query analysis
DropTable true, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.tt4


-- !query
CREATE TABLE tt4(f1 int) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`tt4`, false


-- !query
INSERT INTO tt4 VALUES (0),(1),(9999)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/tt4, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/tt4], Append, `spark_catalog`.`default`.`tt4`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/tt4), [f1]
+- Project [cast(col1#x as int) AS f1#x]
   +- LocalRelation [col1#x]


-- !query
SELECT udf(udf(a.f1)) as f1
FROM tt4 a
LEFT JOIN (
        SELECT b.f1
        FROM tt3 b LEFT JOIN tt3 c ON udf(b.f1) = udf(c.f1)
        WHERE udf(c.f1) IS NULL
) AS d ON udf(a.f1) = d.f1
WHERE udf(udf(d.f1)) IS NULL
-- !query analysis
Project [cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int) AS f1#x]
+- Filter isnull(cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int))
   +- Join LeftOuter, (cast(udf(cast(f1#x as string)) as int) = f1#x)
      :- SubqueryAlias a
      :  +- SubqueryAlias spark_catalog.default.tt4
      :     +- Relation spark_catalog.default.tt4[f1#x] parquet
      +- SubqueryAlias d
         +- Project [f1#x]
            +- Filter isnull(cast(udf(cast(f1#x as string)) as int))
               +- Join LeftOuter, (cast(udf(cast(f1#x as string)) as int) = cast(udf(cast(f1#x as string)) as int))
                  :- SubqueryAlias b
                  :  +- SubqueryAlias spark_catalog.default.tt3
                  :     +- Relation spark_catalog.default.tt3[f1#x,f2#x] parquet
                  +- SubqueryAlias c
                     +- SubqueryAlias spark_catalog.default.tt3
                        +- Relation spark_catalog.default.tt3[f1#x,f2#x] parquet


-- !query
create or replace temporary view tt5 as select * from
  (values (1, 10), (1, 11))
  as v(f1, f2)
-- !query analysis
CreateViewCommand `tt5`, select * from
  (values (1, 10), (1, 11))
  as v(f1, f2), false, true, LocalTempView, true
   +- Project [f1#x, f2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x, col2#x AS f2#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view tt6 as select * from
  (values (1, 9), (1, 2), (2, 9))
  as v(f1, f2)
-- !query analysis
CreateViewCommand `tt6`, select * from
  (values (1, 9), (1, 2), (2, 9))
  as v(f1, f2), false, true, LocalTempView, true
   +- Project [f1#x, f2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x, col2#x AS f2#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select * from tt5,tt6 where udf(tt5.f1) = udf(tt6.f1) and udf(tt5.f1) = udf(udf(tt5.f2) - udf(tt6.f2))
-- !query analysis
Project [f1#x, f2#x, f1#x, f2#x]
+- Filter ((cast(udf(cast(f1#x as string)) as int) = cast(udf(cast(f1#x as string)) as int)) AND (cast(udf(cast(f1#x as string)) as int) = cast(udf(cast((cast(udf(cast(f2#x as string)) as int) - cast(udf(cast(f2#x as string)) as int)) as string)) as int)))
   +- Join Inner
      :- SubqueryAlias tt5
      :  +- View (`tt5`, [f1#x, f2#x])
      :     +- Project [cast(f1#x as int) AS f1#x, cast(f2#x as int) AS f2#x]
      :        +- Project [f1#x, f2#x]
      :           +- SubqueryAlias v
      :              +- Project [col1#x AS f1#x, col2#x AS f2#x]
      :                 +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias tt6
         +- View (`tt6`, [f1#x, f2#x])
            +- Project [cast(f1#x as int) AS f1#x, cast(f2#x as int) AS f2#x]
               +- Project [f1#x, f2#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x, col2#x AS f2#x]
                        +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view xx as select * from
  (values (1), (2), (3))
  as v(pkxx)
-- !query analysis
CreateViewCommand `xx`, select * from
  (values (1), (2), (3))
  as v(pkxx), false, true, LocalTempView, true
   +- Project [pkxx#x]
      +- SubqueryAlias v
         +- Project [col1#x AS pkxx#x]
            +- LocalRelation [col1#x]


-- !query
create or replace temporary view yy as select * from
  (values (101, 1), (201, 2), (301, NULL))
  as v(pkyy, pkxx)
-- !query analysis
CreateViewCommand `yy`, select * from
  (values (101, 1), (201, 2), (301, NULL))
  as v(pkyy, pkxx), false, true, LocalTempView, true
   +- Project [pkyy#x, pkxx#x]
      +- SubqueryAlias v
         +- Project [col1#x AS pkyy#x, col2#x AS pkxx#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select udf(udf(yy.pkyy)) as yy_pkyy, udf(yy.pkxx) as yy_pkxx, udf(yya.pkyy) as yya_pkyy,
       udf(xxa.pkxx) as xxa_pkxx, udf(xxb.pkxx) as xxb_pkxx
from yy
     left join (SELECT * FROM yy where pkyy = 101) as yya ON udf(yy.pkyy) = udf(yya.pkyy)
     left join xx xxa on udf(yya.pkxx) = udf(udf(xxa.pkxx))
     left join xx xxb on udf(udf(coalesce (xxa.pkxx, 1))) = udf(xxb.pkxx)
-- !query analysis
Project [cast(udf(cast(cast(udf(cast(pkyy#x as string)) as int) as string)) as int) AS yy_pkyy#x, cast(udf(cast(pkxx#x as string)) as int) AS yy_pkxx#x, cast(udf(cast(pkyy#x as string)) as int) AS yya_pkyy#x, cast(udf(cast(pkxx#x as string)) as int) AS xxa_pkxx#x, cast(udf(cast(pkxx#x as string)) as int) AS xxb_pkxx#x]
+- Join LeftOuter, (cast(udf(cast(cast(udf(cast(coalesce(pkxx#x, 1) as string)) as int) as string)) as int) = cast(udf(cast(pkxx#x as string)) as int))
   :- Join LeftOuter, (cast(udf(cast(pkxx#x as string)) as int) = cast(udf(cast(cast(udf(cast(pkxx#x as string)) as int) as string)) as int))
   :  :- Join LeftOuter, (cast(udf(cast(pkyy#x as string)) as int) = cast(udf(cast(pkyy#x as string)) as int))
   :  :  :- SubqueryAlias yy
   :  :  :  +- View (`yy`, [pkyy#x, pkxx#x])
   :  :  :     +- Project [cast(pkyy#x as int) AS pkyy#x, cast(pkxx#x as int) AS pkxx#x]
   :  :  :        +- Project [pkyy#x, pkxx#x]
   :  :  :           +- SubqueryAlias v
   :  :  :              +- Project [col1#x AS pkyy#x, col2#x AS pkxx#x]
   :  :  :                 +- LocalRelation [col1#x, col2#x]
   :  :  +- SubqueryAlias yya
   :  :     +- Project [pkyy#x, pkxx#x]
   :  :        +- Filter (pkyy#x = 101)
   :  :           +- SubqueryAlias yy
   :  :              +- View (`yy`, [pkyy#x, pkxx#x])
   :  :                 +- Project [cast(pkyy#x as int) AS pkyy#x, cast(pkxx#x as int) AS pkxx#x]
   :  :                    +- Project [pkyy#x, pkxx#x]
   :  :                       +- SubqueryAlias v
   :  :                          +- Project [col1#x AS pkyy#x, col2#x AS pkxx#x]
   :  :                             +- LocalRelation [col1#x, col2#x]
   :  +- SubqueryAlias xxa
   :     +- SubqueryAlias xx
   :        +- View (`xx`, [pkxx#x])
   :           +- Project [cast(pkxx#x as int) AS pkxx#x]
   :              +- Project [pkxx#x]
   :                 +- SubqueryAlias v
   :                    +- Project [col1#x AS pkxx#x]
   :                       +- LocalRelation [col1#x]
   +- SubqueryAlias xxb
      +- SubqueryAlias xx
         +- View (`xx`, [pkxx#x])
            +- Project [cast(pkxx#x as int) AS pkxx#x]
               +- Project [pkxx#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS pkxx#x]
                        +- LocalRelation [col1#x]


-- !query
create or replace temporary view zt1 as select * from
  (values (53))
  as v(f1)
-- !query analysis
CreateViewCommand `zt1`, select * from
  (values (53))
  as v(f1), false, true, LocalTempView, true
   +- Project [f1#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f1#x]
            +- LocalRelation [col1#x]


-- !query
create or replace temporary view zt2 as select * from
  (values (53))
  as v(f2)
-- !query analysis
CreateViewCommand `zt2`, select * from
  (values (53))
  as v(f2), false, true, LocalTempView, true
   +- Project [f2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS f2#x]
            +- LocalRelation [col1#x]


-- !query
create or replace temporary view zt3(f3 int) using parquet
-- !query analysis
CreateTempViewUsing [tableIdent:`zt3` StructType(StructField(f3,IntegerType,true)) replace:true provider:parquet Map()


-- !query
select * from
  zt2 left join zt3 on (udf(f2) = udf(udf(f3)))
      left join zt1 on (udf(udf(f3)) = udf(f1))
where udf(f2) = 53
-- !query analysis
Project [f2#x, f3#x, f1#x]
+- Filter (cast(udf(cast(f2#x as string)) as int) = 53)
   +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(f3#x as string)) as int) as string)) as int) = cast(udf(cast(f1#x as string)) as int))
      :- Join LeftOuter, (cast(udf(cast(f2#x as string)) as int) = cast(udf(cast(cast(udf(cast(f3#x as string)) as int) as string)) as int))
      :  :- SubqueryAlias zt2
      :  :  +- View (`zt2`, [f2#x])
      :  :     +- Project [cast(f2#x as int) AS f2#x]
      :  :        +- Project [f2#x]
      :  :           +- SubqueryAlias v
      :  :              +- Project [col1#x AS f2#x]
      :  :                 +- LocalRelation [col1#x]
      :  +- SubqueryAlias zt3
      :     +- View (`zt3`, [f3#x])
      :        +- Relation [f3#x] parquet
      +- SubqueryAlias zt1
         +- View (`zt1`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
create temp view zv1 as select *,'dummy' AS junk from zt1
-- !query analysis
CreateViewCommand `zv1`, select *,'dummy' AS junk from zt1, false, false, LocalTempView, true
   +- Project [f1#x, dummy AS junk#x]
      +- SubqueryAlias zt1
         +- View (`zt1`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select * from
  zt2 left join zt3 on (f2 = udf(f3))
      left join zv1 on (udf(f3) = f1)
where udf(udf(f2)) = 53
-- !query analysis
Project [f2#x, f3#x, f1#x, junk#x]
+- Filter (cast(udf(cast(cast(udf(cast(f2#x as string)) as int) as string)) as int) = 53)
   +- Join LeftOuter, (cast(udf(cast(f3#x as string)) as int) = f1#x)
      :- Join LeftOuter, (f2#x = cast(udf(cast(f3#x as string)) as int))
      :  :- SubqueryAlias zt2
      :  :  +- View (`zt2`, [f2#x])
      :  :     +- Project [cast(f2#x as int) AS f2#x]
      :  :        +- Project [f2#x]
      :  :           +- SubqueryAlias v
      :  :              +- Project [col1#x AS f2#x]
      :  :                 +- LocalRelation [col1#x]
      :  +- SubqueryAlias zt3
      :     +- View (`zt3`, [f3#x])
      :        +- Relation [f3#x] parquet
      +- SubqueryAlias zv1
         +- View (`zv1`, [f1#x, junk#x])
            +- Project [cast(f1#x as int) AS f1#x, cast(junk#x as string) AS junk#x]
               +- Project [f1#x, dummy AS junk#x]
                  +- SubqueryAlias zt1
                     +- View (`zt1`, [f1#x])
                        +- Project [cast(f1#x as int) AS f1#x]
                           +- Project [f1#x]
                              +- SubqueryAlias v
                                 +- Project [col1#x AS f1#x]
                                    +- LocalRelation [col1#x]


-- !query
select udf(a.unique2), udf(a.ten), udf(b.tenthous), udf(b.unique2), udf(b.hundred)
from tenk1 a left join tenk1 b on a.unique2 = udf(b.tenthous)
where udf(a.unique1) = 42 and
      ((udf(b.unique2) is null and udf(a.ten) = 2) or udf(udf(b.hundred)) = udf(udf(3)))
-- !query analysis
Project [cast(udf(cast(unique2#x as string)) as int) AS udf(unique2)#x, cast(udf(cast(ten#x as string)) as int) AS udf(ten)#x, cast(udf(cast(tenthous#x as string)) as int) AS udf(tenthous)#x, cast(udf(cast(unique2#x as string)) as int) AS udf(unique2)#x, cast(udf(cast(hundred#x as string)) as int) AS udf(hundred)#x]
+- Filter ((cast(udf(cast(unique1#x as string)) as int) = 42) AND ((isnull(cast(udf(cast(unique2#x as string)) as int)) AND (cast(udf(cast(ten#x as string)) as int) = 2)) OR (cast(udf(cast(cast(udf(cast(hundred#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(3 as string)) as int) as string)) as int))))
   +- Join LeftOuter, (unique2#x = cast(udf(cast(tenthous#x as string)) as int))
      :- SubqueryAlias a
      :  +- SubqueryAlias spark_catalog.default.tenk1
      :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      +- SubqueryAlias b
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
create or replace temporary view a (i integer) using parquet
-- !query analysis
CreateTempViewUsing [tableIdent:`a` StructType(StructField(i,IntegerType,true)) replace:true provider:parquet Map()


-- !query
create or replace temporary view b (x integer, y integer) using parquet
-- !query analysis
CreateTempViewUsing [tableIdent:`b` StructType(StructField(x,IntegerType,true),StructField(y,IntegerType,true)) replace:true provider:parquet Map()


-- !query
select * from a left join b on udf(i) = x and i = udf(y) and udf(x) = udf(i)
-- !query analysis
Project [i#x, x#x, y#x]
+- Join LeftOuter, (((cast(udf(cast(i#x as string)) as int) = x#x) AND (i#x = cast(udf(cast(y#x as string)) as int))) AND (cast(udf(cast(x#x as string)) as int) = cast(udf(cast(i#x as string)) as int)))
   :- SubqueryAlias a
   :  +- View (`a`, [i#x])
   :     +- Relation [i#x] parquet
   +- SubqueryAlias b
      +- View (`b`, [x#x, y#x])
         +- Relation [x#x,y#x] parquet


-- !query
select udf(t1.q2), udf(count(t2.q1, t2.q2))
from int8_tbl t1 left join int8_tbl t2 on (udf(udf(t1.q2)) = t2.q1)
group by udf(t1.q2) order by 1
-- !query analysis
Sort [udf(q2)#xL ASC NULLS FIRST], true
+- Aggregate [cast(udf(cast(q2#xL as string)) as bigint)], [cast(udf(cast(q2#xL as string)) as bigint) AS udf(q2)#xL, cast(udf(cast(count(q1#xL, q2#xL) as string)) as bigint) AS udf(count(q1, q2))#xL]
   +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint) = q1#xL)
      :- SubqueryAlias t1
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias t2
         +- SubqueryAlias int8_tbl
            +- View (`INT8_TBL`, [q1#xL, q2#xL])
               +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                  +- Project [q1#xL, q2#xL]
                     +- SubqueryAlias v
                        +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                           +- LocalRelation [col1#xL, col2#xL]


-- !query
select udf(udf(t1.q2)), udf(count(t2.q1, t2.q2))
from int8_tbl t1 left join (select * from int8_tbl) t2 on (udf(udf(t1.q2)) = udf(t2.q1))
group by udf(udf(t1.q2)) order by 1
-- !query analysis
Sort [udf(udf(q2))#xL ASC NULLS FIRST], true
+- Aggregate [cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint)], [cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint) AS udf(udf(q2))#xL, cast(udf(cast(count(q1#xL, q2#xL) as string)) as bigint) AS udf(count(q1, q2))#xL]
   +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint) = cast(udf(cast(q1#xL as string)) as bigint))
      :- SubqueryAlias t1
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias t2
         +- Project [q1#xL, q2#xL]
            +- SubqueryAlias int8_tbl
               +- View (`INT8_TBL`, [q1#xL, q2#xL])
                  +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                     +- Project [q1#xL, q2#xL]
                        +- SubqueryAlias v
                           +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                              +- LocalRelation [col1#xL, col2#xL]


-- !query
select udf(t1.q2) as q2, udf(udf(count(t2.q1, t2.q2)))
from int8_tbl t1 left join
  (select udf(q1) as q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2
  on (udf(t1.q2) = udf(t2.q1))
group by t1.q2 order by 1
-- !query analysis
Sort [q2#xL ASC NULLS FIRST], true
+- Aggregate [q2#xL], [cast(udf(cast(q2#xL as string)) as bigint) AS q2#xL, cast(udf(cast(cast(udf(cast(count(q1#xL, q2#xL) as string)) as bigint) as string)) as bigint) AS udf(udf(count(q1, q2)))#xL]
   +- Join LeftOuter, (cast(udf(cast(q2#xL as string)) as bigint) = cast(udf(cast(q1#xL as string)) as bigint))
      :- SubqueryAlias t1
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias t2
         +- Project [cast(udf(cast(q1#xL as string)) as bigint) AS q1#xL, CASE WHEN (q2#xL = cast(1 as bigint)) THEN cast(1 as bigint) ELSE q2#xL END AS q2#xL]
            +- SubqueryAlias int8_tbl
               +- View (`INT8_TBL`, [q1#xL, q2#xL])
                  +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                     +- Project [q1#xL, q2#xL]
                        +- SubqueryAlias v
                           +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                              +- LocalRelation [col1#xL, col2#xL]


-- !query
create or replace temporary view a as select * from
  (values ('p'), ('q'))
  as v(code)
-- !query analysis
CreateViewCommand `a`, select * from
  (values ('p'), ('q'))
  as v(code), false, true, LocalTempView, true
   +- Project [code#x]
      +- SubqueryAlias v
         +- Project [col1#x AS code#x]
            +- LocalRelation [col1#x]


-- !query
create or replace temporary view b as select * from
  (values ('p', 1), ('p', 2))
  as v(a, num)
-- !query analysis
CreateViewCommand `b`, select * from
  (values ('p', 1), ('p', 2))
  as v(a, num), false, true, LocalTempView, true
   +- Project [a#x, num#x]
      +- SubqueryAlias v
         +- Project [col1#x AS a#x, col2#x AS num#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view c as select * from
  (values ('A', 'p'), ('B', 'q'), ('C', null))
  as v(name, a)
-- !query analysis
CreateViewCommand `c`, select * from
  (values ('A', 'p'), ('B', 'q'), ('C', null))
  as v(name, a), false, true, LocalTempView, true
   +- Project [name#x, a#x]
      +- SubqueryAlias v
         +- Project [col1#x AS name#x, col2#x AS a#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select udf(c.name), udf(ss.code), udf(ss.b_cnt), udf(ss.const)
from c left join
  (select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const
   from a left join
     (select udf(count(1)) as cnt, b.a as a from b group by b.a) as b_grp
     on udf(a.code) = udf(udf(b_grp.a))
  ) as ss
  on (udf(udf(c.a)) = udf(ss.code))
order by c.name
-- !query analysis
Project [udf(name)#x, udf(code)#x, udf(b_cnt)#xL, udf(const)#x]
+- Sort [name#x ASC NULLS FIRST], true
   +- Project [cast(udf(cast(name#x as string)) as string) AS udf(name)#x, cast(udf(cast(code#x as string)) as string) AS udf(code)#x, cast(udf(cast(b_cnt#xL as string)) as bigint) AS udf(b_cnt)#xL, cast(udf(cast(const#x as string)) as int) AS udf(const)#x, name#x]
      +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(a#x as string)) as string) as string)) as string) = cast(udf(cast(code#x as string)) as string))
         :- SubqueryAlias c
         :  +- View (`c`, [name#x, a#x])
         :     +- Project [cast(name#x as string) AS name#x, cast(a#x as string) AS a#x]
         :        +- Project [name#x, a#x]
         :           +- SubqueryAlias v
         :              +- Project [col1#x AS name#x, col2#x AS a#x]
         :                 +- LocalRelation [col1#x, col2#x]
         +- SubqueryAlias ss
            +- Project [code#x, coalesce(cnt#xL, cast(0 as bigint)) AS b_cnt#xL, -1 AS const#x]
               +- Join LeftOuter, (cast(udf(cast(code#x as string)) as string) = cast(udf(cast(cast(udf(cast(a#x as string)) as string) as string)) as string))
                  :- SubqueryAlias a
                  :  +- View (`a`, [code#x])
                  :     +- Project [cast(code#x as string) AS code#x]
                  :        +- Project [code#x]
                  :           +- SubqueryAlias v
                  :              +- Project [col1#x AS code#x]
                  :                 +- LocalRelation [col1#x]
                  +- SubqueryAlias b_grp
                     +- Aggregate [a#x], [cast(udf(cast(count(1) as string)) as bigint) AS cnt#xL, a#x AS a#x]
                        +- SubqueryAlias b
                           +- View (`b`, [a#x, num#x])
                              +- Project [cast(a#x as string) AS a#x, cast(num#x as int) AS num#x]
                                 +- Project [a#x, num#x]
                                    +- SubqueryAlias v
                                       +- Project [col1#x AS a#x, col2#x AS num#x]
                                          +- LocalRelation [col1#x, col2#x]


-- !query
SELECT * FROM
( SELECT 1 as key1 ) sub1
LEFT JOIN
( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM
    ( SELECT 1 as key3 ) sub3
    LEFT JOIN
    ( SELECT udf(sub5.key5) as key5, udf(udf(COALESCE(sub6.value1, 1))) as value2 FROM
        ( SELECT 1 as key5 ) sub5
        LEFT JOIN
        ( SELECT 2 as key6, 42 as value1 ) sub6
        ON sub5.key5 = udf(sub6.key6)
    ) sub4
    ON udf(sub4.key5) = sub3.key3
) sub2
ON udf(udf(sub1.key1)) = udf(udf(sub2.key3))
-- !query analysis
Project [key1#x, key3#x, value2#x, value3#x]
+- Join LeftOuter, (cast(udf(cast(cast(udf(cast(key1#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(key3#x as string)) as int) as string)) as int))
   :- SubqueryAlias sub1
   :  +- Project [1 AS key1#x]
   :     +- OneRowRelation
   +- SubqueryAlias sub2
      +- Project [key3#x, value2#x, coalesce(value2#x, 66) AS value3#x]
         +- Join LeftOuter, (cast(udf(cast(key5#x as string)) as int) = key3#x)
            :- SubqueryAlias sub3
            :  +- Project [1 AS key3#x]
            :     +- OneRowRelation
            +- SubqueryAlias sub4
               +- Project [cast(udf(cast(key5#x as string)) as int) AS key5#x, cast(udf(cast(cast(udf(cast(coalesce(value1#x, 1) as string)) as int) as string)) as int) AS value2#x]
                  +- Join LeftOuter, (key5#x = cast(udf(cast(key6#x as string)) as int))
                     :- SubqueryAlias sub5
                     :  +- Project [1 AS key5#x]
                     :     +- OneRowRelation
                     +- SubqueryAlias sub6
                        +- Project [2 AS key6#x, 42 AS value1#x]
                           +- OneRowRelation


-- !query
SELECT * FROM
( SELECT 1 as key1 ) sub1
LEFT JOIN
( SELECT udf(sub3.key3) as key3, udf(value2), udf(COALESCE(value2, 66)) as value3 FROM
    ( SELECT 1 as key3 ) sub3
    LEFT JOIN
    ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM
        ( SELECT 1 as key5 ) sub5
        LEFT JOIN
        ( SELECT 2 as key6, 42 as value1 ) sub6
        ON udf(udf(sub5.key5)) = sub6.key6
    ) sub4
    ON sub4.key5 = sub3.key3
) sub2
ON sub1.key1 = udf(udf(sub2.key3))
-- !query analysis
Project [key1#x, key3#x, udf(value2)#x, value3#x]
+- Join LeftOuter, (key1#x = cast(udf(cast(cast(udf(cast(key3#x as string)) as int) as string)) as int))
   :- SubqueryAlias sub1
   :  +- Project [1 AS key1#x]
   :     +- OneRowRelation
   +- SubqueryAlias sub2
      +- Project [cast(udf(cast(key3#x as string)) as int) AS key3#x, cast(udf(cast(value2#x as string)) as int) AS udf(value2)#x, cast(udf(cast(coalesce(value2#x, 66) as string)) as int) AS value3#x]
         +- Join LeftOuter, (key5#x = key3#x)
            :- SubqueryAlias sub3
            :  +- Project [1 AS key3#x]
            :     +- OneRowRelation
            +- SubqueryAlias sub4
               +- Project [key5#x, coalesce(value1#x, 1) AS value2#x]
                  +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(key5#x as string)) as int) as string)) as int) = key6#x)
                     :- SubqueryAlias sub5
                     :  +- Project [1 AS key5#x]
                     :     +- OneRowRelation
                     +- SubqueryAlias sub6
                        +- Project [2 AS key6#x, 42 AS value1#x]
                           +- OneRowRelation


-- !query
SELECT udf(qq), udf(udf(unique1))
  FROM
  ( SELECT udf(COALESCE(q1, 0)) AS qq FROM int8_tbl a ) AS ss1
  FULL OUTER JOIN
  ( SELECT udf(udf(COALESCE(q2, -1))) AS qq FROM int8_tbl b ) AS ss2
  USING (qq)
  INNER JOIN tenk1 c ON udf(qq) = udf(unique2)
-- !query analysis
Project [cast(udf(cast(qq#xL as string)) as bigint) AS udf(qq)#xL, cast(udf(cast(cast(udf(cast(unique1#x as string)) as int) as string)) as int) AS udf(udf(unique1))#x]
+- Join Inner, (cast(udf(cast(qq#xL as string)) as bigint) = cast(cast(udf(cast(unique2#x as string)) as int) as bigint))
   :- Project [coalesce(qq#xL, qq#xL) AS qq#xL]
   :  +- Join FullOuter, (qq#xL = qq#xL)
   :     :- SubqueryAlias ss1
   :     :  +- Project [cast(udf(cast(coalesce(q1#xL, cast(0 as bigint)) as string)) as bigint) AS qq#xL]
   :     :     +- SubqueryAlias a
   :     :        +- SubqueryAlias int8_tbl
   :     :           +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :     :              +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :     :                 +- Project [q1#xL, q2#xL]
   :     :                    +- SubqueryAlias v
   :     :                       +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :     :                          +- LocalRelation [col1#xL, col2#xL]
   :     +- SubqueryAlias ss2
   :        +- Project [cast(udf(cast(cast(udf(cast(coalesce(q2#xL, cast(-1 as bigint)) as string)) as bigint) as string)) as bigint) AS qq#xL]
   :           +- SubqueryAlias b
   :              +- SubqueryAlias int8_tbl
   :                 +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :                    +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :                       +- Project [q1#xL, q2#xL]
   :                          +- SubqueryAlias v
   :                             +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :                                +- LocalRelation [col1#xL, col2#xL]
   +- SubqueryAlias c
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
create or replace temporary view nt1 as select * from
  (values(1,true,true), (2,true,false), (3,false,false))
  as v(id, a1, a2)
-- !query analysis
CreateViewCommand `nt1`, select * from
  (values(1,true,true), (2,true,false), (3,false,false))
  as v(id, a1, a2), false, true, LocalTempView, true
   +- Project [id#x, a1#x, a2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS id#x, col2#x AS a1#x, col3#x AS a2#x]
            +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
create or replace temporary view nt2 as select * from
  (values(1,1,true,true), (2,2,true,false), (3,3,false,false))
  as v(id, nt1_id, b1, b2)
-- !query analysis
CreateViewCommand `nt2`, select * from
  (values(1,1,true,true), (2,2,true,false), (3,3,false,false))
  as v(id, nt1_id, b1, b2), false, true, LocalTempView, true
   +- Project [id#x, nt1_id#x, b1#x, b2#x]
      +- SubqueryAlias v
         +- Project [col1#x AS id#x, col2#x AS nt1_id#x, col3#x AS b1#x, col4#x AS b2#x]
            +- LocalRelation [col1#x, col2#x, col3#x, col4#x]


-- !query
create or replace temporary view nt3 as select * from
  (values(1,1,true), (2,2,false), (3,3,true))
  as v(id, nt2_id, c1)
-- !query analysis
CreateViewCommand `nt3`, select * from
  (values(1,1,true), (2,2,false), (3,3,true))
  as v(id, nt2_id, c1), false, true, LocalTempView, true
   +- Project [id#x, nt2_id#x, c1#x]
      +- SubqueryAlias v
         +- Project [col1#x AS id#x, col2#x AS nt2_id#x, col3#x AS c1#x]
            +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
select udf(nt3.id)
from nt3 as nt3
  left join
    (select nt2.*, (udf(nt2.b1) and udf(ss1.a3)) AS b3
     from nt2 as nt2
       left join
         (select nt1.*, (udf(nt1.id) is not null) as a3 from nt1) as ss1
         on ss1.id = udf(udf(nt2.nt1_id))
    ) as ss2
    on udf(ss2.id) = nt3.nt2_id
where udf(nt3.id) = 1 and udf(ss2.b3)
-- !query analysis
Project [cast(udf(cast(id#x as string)) as int) AS udf(id)#x]
+- Filter ((cast(udf(cast(id#x as string)) as int) = 1) AND cast(udf(cast(b3#x as string)) as boolean))
   +- Join LeftOuter, (cast(udf(cast(id#x as string)) as int) = nt2_id#x)
      :- SubqueryAlias nt3
      :  +- SubqueryAlias nt3
      :     +- View (`nt3`, [id#x, nt2_id#x, c1#x])
      :        +- Project [cast(id#x as int) AS id#x, cast(nt2_id#x as int) AS nt2_id#x, cast(c1#x as boolean) AS c1#x]
      :           +- Project [id#x, nt2_id#x, c1#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS id#x, col2#x AS nt2_id#x, col3#x AS c1#x]
      :                    +- LocalRelation [col1#x, col2#x, col3#x]
      +- SubqueryAlias ss2
         +- Project [id#x, nt1_id#x, b1#x, b2#x, (cast(udf(cast(b1#x as string)) as boolean) AND cast(udf(cast(a3#x as string)) as boolean)) AS b3#x]
            +- Join LeftOuter, (id#x = cast(udf(cast(cast(udf(cast(nt1_id#x as string)) as int) as string)) as int))
               :- SubqueryAlias nt2
               :  +- SubqueryAlias nt2
               :     +- View (`nt2`, [id#x, nt1_id#x, b1#x, b2#x])
               :        +- Project [cast(id#x as int) AS id#x, cast(nt1_id#x as int) AS nt1_id#x, cast(b1#x as boolean) AS b1#x, cast(b2#x as boolean) AS b2#x]
               :           +- Project [id#x, nt1_id#x, b1#x, b2#x]
               :              +- SubqueryAlias v
               :                 +- Project [col1#x AS id#x, col2#x AS nt1_id#x, col3#x AS b1#x, col4#x AS b2#x]
               :                    +- LocalRelation [col1#x, col2#x, col3#x, col4#x]
               +- SubqueryAlias ss1
                  +- Project [id#x, a1#x, a2#x, isnotnull(cast(udf(cast(id#x as string)) as int)) AS a3#x]
                     +- SubqueryAlias nt1
                        +- View (`nt1`, [id#x, a1#x, a2#x])
                           +- Project [cast(id#x as int) AS id#x, cast(a1#x as boolean) AS a1#x, cast(a2#x as boolean) AS a2#x]
                              +- Project [id#x, a1#x, a2#x]
                                 +- SubqueryAlias v
                                    +- Project [col1#x AS id#x, col2#x AS a1#x, col3#x AS a2#x]
                                       +- LocalRelation [col1#x, col2#x, col3#x]


-- !query
select * from int4_tbl a full join int4_tbl b on true
-- !query analysis
Project [f1#x, f1#x]
+- Join FullOuter, true
   :- SubqueryAlias a
   :  +- SubqueryAlias int4_tbl
   :     +- View (`INT4_TBL`, [f1#x])
   :        +- Project [cast(f1#x as int) AS f1#x]
   :           +- Project [f1#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS f1#x]
   :                    +- LocalRelation [col1#x]
   +- SubqueryAlias b
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select * from int4_tbl a full join int4_tbl b on false
-- !query analysis
Project [f1#x, f1#x]
+- Join FullOuter, false
   :- SubqueryAlias a
   :  +- SubqueryAlias int4_tbl
   :     +- View (`INT4_TBL`, [f1#x])
   :        +- Project [cast(f1#x as int) AS f1#x]
   :           +- Project [f1#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS f1#x]
   :                    +- LocalRelation [col1#x]
   +- SubqueryAlias b
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select udf(count(*)) from
  tenk1 a join tenk1 b on udf(a.unique1) = udf(b.unique2)
  left join tenk1 c on udf(a.unique2) = udf(b.unique1) and udf(c.thousand) = udf(udf(a.thousand))
  join int4_tbl on udf(b.thousand) = f1
-- !query analysis
Aggregate [cast(udf(cast(count(1) as string)) as bigint) AS udf(count(1))#xL]
+- Join Inner, (cast(udf(cast(thousand#x as string)) as int) = f1#x)
   :- Join LeftOuter, ((cast(udf(cast(unique2#x as string)) as int) = cast(udf(cast(unique1#x as string)) as int)) AND (cast(udf(cast(thousand#x as string)) as int) = cast(udf(cast(cast(udf(cast(thousand#x as string)) as int) as string)) as int)))
   :  :- Join Inner, (cast(udf(cast(unique1#x as string)) as int) = cast(udf(cast(unique2#x as string)) as int))
   :  :  :- SubqueryAlias a
   :  :  :  +- SubqueryAlias spark_catalog.default.tenk1
   :  :  :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   :  :  +- SubqueryAlias b
   :  :     +- SubqueryAlias spark_catalog.default.tenk1
   :  :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   :  +- SubqueryAlias c
   :     +- SubqueryAlias spark_catalog.default.tenk1
   :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   +- SubqueryAlias int4_tbl
      +- View (`INT4_TBL`, [f1#x])
         +- Project [cast(f1#x as int) AS f1#x]
            +- Project [f1#x]
               +- SubqueryAlias v
                  +- Project [col1#x AS f1#x]
                     +- LocalRelation [col1#x]


-- !query
select udf(b.unique1) from
  tenk1 a join tenk1 b on udf(a.unique1) = udf(b.unique2)
  left join tenk1 c on udf(b.unique1) = 42 and c.thousand = udf(a.thousand)
  join int4_tbl i1 on udf(b.thousand) = udf(udf(f1))
  right join int4_tbl i2 on udf(udf(i2.f1)) = udf(b.tenthous)
  order by udf(1)
-- !query analysis
Sort [cast(udf(cast(1 as string)) as int) ASC NULLS FIRST], true
+- Project [cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x]
   +- Join RightOuter, (cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int) = cast(udf(cast(tenthous#x as string)) as int))
      :- Join Inner, (cast(udf(cast(thousand#x as string)) as int) = cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int))
      :  :- Join LeftOuter, ((cast(udf(cast(unique1#x as string)) as int) = 42) AND (thousand#x = cast(udf(cast(thousand#x as string)) as int)))
      :  :  :- Join Inner, (cast(udf(cast(unique1#x as string)) as int) = cast(udf(cast(unique2#x as string)) as int))
      :  :  :  :- SubqueryAlias a
      :  :  :  :  +- SubqueryAlias spark_catalog.default.tenk1
      :  :  :  :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  :  :  +- SubqueryAlias b
      :  :  :     +- SubqueryAlias spark_catalog.default.tenk1
      :  :  :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  :  +- SubqueryAlias c
      :  :     +- SubqueryAlias spark_catalog.default.tenk1
      :  :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  +- SubqueryAlias i1
      :     +- SubqueryAlias int4_tbl
      :        +- View (`INT4_TBL`, [f1#x])
      :           +- Project [cast(f1#x as int) AS f1#x]
      :              +- Project [f1#x]
      :                 +- SubqueryAlias v
      :                    +- Project [col1#x AS f1#x]
      :                       +- LocalRelation [col1#x]
      +- SubqueryAlias i2
         +- SubqueryAlias int4_tbl
            +- View (`INT4_TBL`, [f1#x])
               +- Project [cast(f1#x as int) AS f1#x]
                  +- Project [f1#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS f1#x]
                           +- LocalRelation [col1#x]


-- !query
select * from
(
  select udf(unique1), udf(q1), udf(udf(coalesce(unique1, -1)) + udf(q1)) as fault
  from int8_tbl left join tenk1 on (udf(q2) = udf(unique2))
) ss
where udf(fault) = udf(122)
order by udf(fault)
-- !query analysis
Sort [cast(udf(cast(fault#xL as string)) as bigint) ASC NULLS FIRST], true
+- Project [udf(unique1)#x, udf(q1)#xL, fault#xL]
   +- Filter (cast(udf(cast(fault#xL as string)) as bigint) = cast(cast(udf(cast(122 as string)) as int) as bigint))
      +- SubqueryAlias ss
         +- Project [cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x, cast(udf(cast(q1#xL as string)) as bigint) AS udf(q1)#xL, cast(udf(cast((cast(cast(udf(cast(coalesce(unique1#x, -1) as string)) as int) as bigint) + cast(udf(cast(q1#xL as string)) as bigint)) as string)) as bigint) AS fault#xL]
            +- Join LeftOuter, (cast(udf(cast(q2#xL as string)) as bigint) = cast(cast(udf(cast(unique2#x as string)) as int) as bigint))
               :- SubqueryAlias int8_tbl
               :  +- View (`INT8_TBL`, [q1#xL, q2#xL])
               :     +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
               :        +- Project [q1#xL, q2#xL]
               :           +- SubqueryAlias v
               :              +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
               :                 +- LocalRelation [col1#xL, col2#xL]
               +- SubqueryAlias spark_catalog.default.tenk1
                  +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(q1), udf(unique2), udf(thousand), udf(hundred)
  from int8_tbl a left join tenk1 b on udf(q1) = udf(unique2)
  where udf(coalesce(thousand,123)) = udf(q1) and udf(q1) = udf(udf(coalesce(hundred,123)))
-- !query analysis
Project [cast(udf(cast(q1#xL as string)) as bigint) AS udf(q1)#xL, cast(udf(cast(unique2#x as string)) as int) AS udf(unique2)#x, cast(udf(cast(thousand#x as string)) as int) AS udf(thousand)#x, cast(udf(cast(hundred#x as string)) as int) AS udf(hundred)#x]
+- Filter ((cast(cast(udf(cast(coalesce(thousand#x, 123) as string)) as int) as bigint) = cast(udf(cast(q1#xL as string)) as bigint)) AND (cast(udf(cast(q1#xL as string)) as bigint) = cast(cast(udf(cast(cast(udf(cast(coalesce(hundred#x, 123) as string)) as int) as string)) as int) as bigint)))
   +- Join LeftOuter, (cast(udf(cast(q1#xL as string)) as bigint) = cast(cast(udf(cast(unique2#x as string)) as int) as bigint))
      :- SubqueryAlias a
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias b
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(f1), udf(unique2), case when udf(udf(unique2)) is null then udf(f1) else 0 end
  from int4_tbl a left join tenk1 b on udf(f1) = udf(udf(unique2))
  where (case when udf(unique2) is null then udf(f1) else 0 end) = 0
-- !query analysis
Project [cast(udf(cast(f1#x as string)) as int) AS udf(f1)#x, cast(udf(cast(unique2#x as string)) as int) AS udf(unique2)#x, CASE WHEN isnull(cast(udf(cast(cast(udf(cast(unique2#x as string)) as int) as string)) as int)) THEN cast(udf(cast(f1#x as string)) as int) ELSE 0 END AS CASE WHEN (udf(udf(unique2)) IS NULL) THEN udf(f1) ELSE 0 END#x]
+- Filter (CASE WHEN isnull(cast(udf(cast(unique2#x as string)) as int)) THEN cast(udf(cast(f1#x as string)) as int) ELSE 0 END = 0)
   +- Join LeftOuter, (cast(udf(cast(f1#x as string)) as int) = cast(udf(cast(cast(udf(cast(unique2#x as string)) as int) as string)) as int))
      :- SubqueryAlias a
      :  +- SubqueryAlias int4_tbl
      :     +- View (`INT4_TBL`, [f1#x])
      :        +- Project [cast(f1#x as int) AS f1#x]
      :           +- Project [f1#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS f1#x]
      :                    +- LocalRelation [col1#x]
      +- SubqueryAlias b
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select udf(a.unique1), udf(b.unique1), udf(c.unique1), udf(coalesce(b.twothousand, a.twothousand))
  from tenk1 a left join tenk1 b on udf(b.thousand) = a.unique1                       left join tenk1 c on udf(c.unique2) = udf(coalesce(b.twothousand, a.twothousand))
  where a.unique2 < udf(10) and udf(udf(coalesce(b.twothousand, a.twothousand))) = udf(44)
-- !query analysis
Project [cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x, cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x, cast(udf(cast(unique1#x as string)) as int) AS udf(unique1)#x, cast(udf(cast(coalesce(twothousand#x, twothousand#x) as string)) as int) AS udf(coalesce(twothousand, twothousand))#x]
+- Filter ((unique2#x < cast(udf(cast(10 as string)) as int)) AND (cast(udf(cast(cast(udf(cast(coalesce(twothousand#x, twothousand#x) as string)) as int) as string)) as int) = cast(udf(cast(44 as string)) as int)))
   +- Join LeftOuter, (cast(udf(cast(unique2#x as string)) as int) = cast(udf(cast(coalesce(twothousand#x, twothousand#x) as string)) as int))
      :- Join LeftOuter, (cast(udf(cast(thousand#x as string)) as int) = unique1#x)
      :  :- SubqueryAlias a
      :  :  +- SubqueryAlias spark_catalog.default.tenk1
      :  :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      :  +- SubqueryAlias b
      :     +- SubqueryAlias spark_catalog.default.tenk1
      :        +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      +- SubqueryAlias c
         +- SubqueryAlias spark_catalog.default.tenk1
            +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select * from
  text_tbl t1
  inner join int8_tbl i8
  on udf(i8.q2) = udf(udf(456))
  right join text_tbl t2
  on udf(t1.f1) = udf(udf('doh!'))
  left join int4_tbl i4
  on udf(udf(i8.q1)) = i4.f1
-- !query analysis
Project [f1#x, q1#xL, q2#xL, f1#x, f1#x]
+- Join LeftOuter, (cast(udf(cast(cast(udf(cast(q1#xL as string)) as bigint) as string)) as bigint) = cast(f1#x as bigint))
   :- Join RightOuter, (cast(udf(cast(f1#x as string)) as string) = cast(udf(cast(cast(udf(cast(doh! as string)) as string) as string)) as string))
   :  :- Join Inner, (cast(udf(cast(q2#xL as string)) as bigint) = cast(cast(udf(cast(cast(udf(cast(456 as string)) as int) as string)) as int) as bigint))
   :  :  :- SubqueryAlias t1
   :  :  :  +- SubqueryAlias text_tbl
   :  :  :     +- View (`TEXT_TBL`, [f1#x])
   :  :  :        +- Project [cast(f1#x as string) AS f1#x]
   :  :  :           +- Project [f1#x]
   :  :  :              +- SubqueryAlias v
   :  :  :                 +- Project [col1#x AS f1#x]
   :  :  :                    +- LocalRelation [col1#x]
   :  :  +- SubqueryAlias i8
   :  :     +- SubqueryAlias int8_tbl
   :  :        +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :  :           +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :  :              +- Project [q1#xL, q2#xL]
   :  :                 +- SubqueryAlias v
   :  :                    +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :  :                       +- LocalRelation [col1#xL, col2#xL]
   :  +- SubqueryAlias t2
   :     +- SubqueryAlias text_tbl
   :        +- View (`TEXT_TBL`, [f1#x])
   :           +- Project [cast(f1#x as string) AS f1#x]
   :              +- Project [f1#x]
   :                 +- SubqueryAlias v
   :                    +- Project [col1#x AS f1#x]
   :                       +- LocalRelation [col1#x]
   +- SubqueryAlias i4
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select * from
  (select udf(udf(1)) as id) as xx
  left join
    (tenk1 as a1 full join (select udf(1) as id) as yy on (udf(a1.unique1) = udf(yy.id)))
  on (xx.id = udf(udf(coalesce(yy.id))))
-- !query analysis
Project [id#x, unique1#x, unique2#x, two#x, four#x, ten#x, twenty#x, hundred#x, thousand#x, twothousand#x, fivethous#x, tenthous#x, odd#x, even#x, stringu1#x, stringu2#x, string4#x, id#x]
+- Join LeftOuter, (id#x = cast(udf(cast(cast(udf(cast(coalesce(id#x) as string)) as int) as string)) as int))
   :- SubqueryAlias xx
   :  +- Project [cast(udf(cast(cast(udf(cast(1 as string)) as int) as string)) as int) AS id#x]
   :     +- OneRowRelation
   +- Join FullOuter, (cast(udf(cast(unique1#x as string)) as int) = cast(udf(cast(id#x as string)) as int))
      :- SubqueryAlias a1
      :  +- SubqueryAlias spark_catalog.default.tenk1
      :     +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
      +- SubqueryAlias yy
         +- Project [cast(udf(cast(1 as string)) as int) AS id#x]
            +- OneRowRelation


-- !query
select udf(a.q2), udf(b.q1)
  from int8_tbl a left join int8_tbl b on udf(a.q2) = coalesce(b.q1, 1)
  where udf(udf(coalesce(b.q1, 1)) > 0)
-- !query analysis
Project [cast(udf(cast(q2#xL as string)) as bigint) AS udf(q2)#xL, cast(udf(cast(q1#xL as string)) as bigint) AS udf(q1)#xL]
+- Filter cast(udf(cast((cast(udf(cast(coalesce(q1#xL, cast(1 as bigint)) as string)) as bigint) > cast(0 as bigint)) as string)) as boolean)
   +- Join LeftOuter, (cast(udf(cast(q2#xL as string)) as bigint) = coalesce(q1#xL, cast(1 as bigint)))
      :- SubqueryAlias a
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias b
         +- SubqueryAlias int8_tbl
            +- View (`INT8_TBL`, [q1#xL, q2#xL])
               +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                  +- Project [q1#xL, q2#xL]
                     +- SubqueryAlias v
                        +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                           +- LocalRelation [col1#xL, col2#xL]


-- !query
create or replace temporary view parent as select * from
  (values (1, 10), (2, 20), (3, 30))
  as v(k, pd)
-- !query analysis
CreateViewCommand `parent`, select * from
  (values (1, 10), (2, 20), (3, 30))
  as v(k, pd), false, true, LocalTempView, true
   +- Project [k#x, pd#x]
      +- SubqueryAlias v
         +- Project [col1#x AS k#x, col2#x AS pd#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view child as select * from
  (values (1, 100), (4, 400))
  as v(k, cd)
-- !query analysis
CreateViewCommand `child`, select * from
  (values (1, 100), (4, 400))
  as v(k, cd), false, true, LocalTempView, true
   +- Project [k#x, cd#x]
      +- SubqueryAlias v
         +- Project [col1#x AS k#x, col2#x AS cd#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
select p.* from parent p left join child c on (udf(p.k) = udf(c.k))
-- !query analysis
Project [k#x, pd#x]
+- Join LeftOuter, (cast(udf(cast(k#x as string)) as int) = cast(udf(cast(k#x as string)) as int))
   :- SubqueryAlias p
   :  +- SubqueryAlias parent
   :     +- View (`parent`, [k#x, pd#x])
   :        +- Project [cast(k#x as int) AS k#x, cast(pd#x as int) AS pd#x]
   :           +- Project [k#x, pd#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS k#x, col2#x AS pd#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias c
      +- SubqueryAlias child
         +- View (`child`, [k#x, cd#x])
            +- Project [cast(k#x as int) AS k#x, cast(cd#x as int) AS cd#x]
               +- Project [k#x, cd#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS k#x, col2#x AS cd#x]
                        +- LocalRelation [col1#x, col2#x]


-- !query
select p.*, linked from parent p
  left join (select c.*, udf(udf(true)) as linked from child c) as ss
  on (udf(p.k) = udf(udf(ss.k)))
-- !query analysis
Project [k#x, pd#x, linked#x]
+- Join LeftOuter, (cast(udf(cast(k#x as string)) as int) = cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int))
   :- SubqueryAlias p
   :  +- SubqueryAlias parent
   :     +- View (`parent`, [k#x, pd#x])
   :        +- Project [cast(k#x as int) AS k#x, cast(pd#x as int) AS pd#x]
   :           +- Project [k#x, pd#x]
   :              +- SubqueryAlias v
   :                 +- Project [col1#x AS k#x, col2#x AS pd#x]
   :                    +- LocalRelation [col1#x, col2#x]
   +- SubqueryAlias ss
      +- Project [k#x, cd#x, cast(udf(cast(cast(udf(cast(true as string)) as boolean) as string)) as boolean) AS linked#x]
         +- SubqueryAlias c
            +- SubqueryAlias child
               +- View (`child`, [k#x, cd#x])
                  +- Project [cast(k#x as int) AS k#x, cast(cd#x as int) AS cd#x]
                     +- Project [k#x, cd#x]
                        +- SubqueryAlias v
                           +- Project [col1#x AS k#x, col2#x AS cd#x]
                              +- LocalRelation [col1#x, col2#x]


-- !query
select p.* from
  parent p left join child c on (udf(p.k) = c.k)
  where p.k = udf(1) and udf(udf(p.k)) = udf(udf(2))
-- !query analysis
Project [k#x, pd#x]
+- Filter ((k#x = cast(udf(cast(1 as string)) as int)) AND (cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(2 as string)) as int) as string)) as int)))
   +- Join LeftOuter, (cast(udf(cast(k#x as string)) as int) = k#x)
      :- SubqueryAlias p
      :  +- SubqueryAlias parent
      :     +- View (`parent`, [k#x, pd#x])
      :        +- Project [cast(k#x as int) AS k#x, cast(pd#x as int) AS pd#x]
      :           +- Project [k#x, pd#x]
      :              +- SubqueryAlias v
      :                 +- Project [col1#x AS k#x, col2#x AS pd#x]
      :                    +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias c
         +- SubqueryAlias child
            +- View (`child`, [k#x, cd#x])
               +- Project [cast(k#x as int) AS k#x, cast(cd#x as int) AS cd#x]
                  +- Project [k#x, cd#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS k#x, col2#x AS cd#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
select p.* from
  (parent p left join child c on (udf(p.k) = c.k)) join parent x on p.k = udf(x.k)
  where udf(p.k) = udf(1) and udf(udf(p.k)) = udf(udf(2))
-- !query analysis
Project [k#x, pd#x]
+- Filter ((cast(udf(cast(k#x as string)) as int) = cast(udf(cast(1 as string)) as int)) AND (cast(udf(cast(cast(udf(cast(k#x as string)) as int) as string)) as int) = cast(udf(cast(cast(udf(cast(2 as string)) as int) as string)) as int)))
   +- Join Inner, (k#x = cast(udf(cast(k#x as string)) as int))
      :- Join LeftOuter, (cast(udf(cast(k#x as string)) as int) = k#x)
      :  :- SubqueryAlias p
      :  :  +- SubqueryAlias parent
      :  :     +- View (`parent`, [k#x, pd#x])
      :  :        +- Project [cast(k#x as int) AS k#x, cast(pd#x as int) AS pd#x]
      :  :           +- Project [k#x, pd#x]
      :  :              +- SubqueryAlias v
      :  :                 +- Project [col1#x AS k#x, col2#x AS pd#x]
      :  :                    +- LocalRelation [col1#x, col2#x]
      :  +- SubqueryAlias c
      :     +- SubqueryAlias child
      :        +- View (`child`, [k#x, cd#x])
      :           +- Project [cast(k#x as int) AS k#x, cast(cd#x as int) AS cd#x]
      :              +- Project [k#x, cd#x]
      :                 +- SubqueryAlias v
      :                    +- Project [col1#x AS k#x, col2#x AS cd#x]
      :                       +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias x
         +- SubqueryAlias parent
            +- View (`parent`, [k#x, pd#x])
               +- Project [cast(k#x as int) AS k#x, cast(pd#x as int) AS pd#x]
                  +- Project [k#x, pd#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS k#x, col2#x AS pd#x]
                           +- LocalRelation [col1#x, col2#x]


-- !query
create or replace temporary view a as select * from
  (values (0), (1))
  as v(id)
-- !query analysis
CreateViewCommand `a`, select * from
  (values (0), (1))
  as v(id), false, true, LocalTempView, true
   +- Project [id#x]
      +- SubqueryAlias v
         +- Project [col1#x AS id#x]
            +- LocalRelation [col1#x]


-- !query
create or replace temporary view b as select * from
  (values (0, 0), (1, NULL))
  as v(id, a_id)
-- !query analysis
CreateViewCommand `b`, select * from
  (values (0, 0), (1, NULL))
  as v(id, a_id), false, true, LocalTempView, true
   +- Project [id#x, a_id#x]
      +- SubqueryAlias v
         +- Project [col1#x AS id#x, col2#x AS a_id#x]
            +- LocalRelation [col1#x, col2#x]


-- !query
SELECT * FROM b LEFT JOIN a ON (udf(b.a_id) = udf(a.id)) WHERE (udf(udf(a.id)) IS NULL OR udf(a.id) > 0)
-- !query analysis
Project [id#x, a_id#x, id#x]
+- Filter (isnull(cast(udf(cast(cast(udf(cast(id#x as string)) as int) as string)) as int)) OR (cast(udf(cast(id#x as string)) as int) > 0))
   +- Join LeftOuter, (cast(udf(cast(a_id#x as string)) as int) = cast(udf(cast(id#x as string)) as int))
      :- SubqueryAlias b
      :  +- View (`b`, [id#x, a_id#x])
      :     +- Project [cast(id#x as int) AS id#x, cast(a_id#x as int) AS a_id#x]
      :        +- Project [id#x, a_id#x]
      :           +- SubqueryAlias v
      :              +- Project [col1#x AS id#x, col2#x AS a_id#x]
      :                 +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias a
         +- View (`a`, [id#x])
            +- Project [cast(id#x as int) AS id#x]
               +- Project [id#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS id#x]
                        +- LocalRelation [col1#x]


-- !query
SELECT b.* FROM b LEFT JOIN a ON (udf(b.a_id) = udf(a.id)) WHERE (udf(a.id) IS NULL OR udf(udf(a.id)) > 0)
-- !query analysis
Project [id#x, a_id#x]
+- Filter (isnull(cast(udf(cast(id#x as string)) as int)) OR (cast(udf(cast(cast(udf(cast(id#x as string)) as int) as string)) as int) > 0))
   +- Join LeftOuter, (cast(udf(cast(a_id#x as string)) as int) = cast(udf(cast(id#x as string)) as int))
      :- SubqueryAlias b
      :  +- View (`b`, [id#x, a_id#x])
      :     +- Project [cast(id#x as int) AS id#x, cast(a_id#x as int) AS a_id#x]
      :        +- Project [id#x, a_id#x]
      :           +- SubqueryAlias v
      :              +- Project [col1#x AS id#x, col2#x AS a_id#x]
      :                 +- LocalRelation [col1#x, col2#x]
      +- SubqueryAlias a
         +- View (`a`, [id#x])
            +- Project [cast(id#x as int) AS id#x]
               +- Project [id#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS id#x]
                        +- LocalRelation [col1#x]


-- !query
create or replace temporary view innertab as select * from
  (values (123L, 42L))
  as v(id, dat1)
-- !query analysis
CreateViewCommand `innertab`, select * from
  (values (123L, 42L))
  as v(id, dat1), false, true, LocalTempView, true
   +- Project [id#xL, dat1#xL]
      +- SubqueryAlias v
         +- Project [col1#xL AS id#xL, col2#xL AS dat1#xL]
            +- LocalRelation [col1#xL, col2#xL]


-- !query
SELECT * FROM
    (SELECT udf(1) AS x) ss1
  LEFT JOIN
    (SELECT udf(q1), udf(q2), udf(COALESCE(dat1, q1)) AS y
     FROM int8_tbl LEFT JOIN innertab ON udf(udf(q2)) = id) ss2
  ON true
-- !query analysis
Project [x#x, udf(q1)#xL, udf(q2)#xL, y#xL]
+- Join LeftOuter, true
   :- SubqueryAlias ss1
   :  +- Project [cast(udf(cast(1 as string)) as int) AS x#x]
   :     +- OneRowRelation
   +- SubqueryAlias ss2
      +- Project [cast(udf(cast(q1#xL as string)) as bigint) AS udf(q1)#xL, cast(udf(cast(q2#xL as string)) as bigint) AS udf(q2)#xL, cast(udf(cast(coalesce(dat1#xL, q1#xL) as string)) as bigint) AS y#xL]
         +- Join LeftOuter, (cast(udf(cast(cast(udf(cast(q2#xL as string)) as bigint) as string)) as bigint) = id#xL)
            :- SubqueryAlias int8_tbl
            :  +- View (`INT8_TBL`, [q1#xL, q2#xL])
            :     +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
            :        +- Project [q1#xL, q2#xL]
            :           +- SubqueryAlias v
            :              +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
            :                 +- LocalRelation [col1#xL, col2#xL]
            +- SubqueryAlias innertab
               +- View (`innertab`, [id#xL, dat1#xL])
                  +- Project [cast(id#xL as bigint) AS id#xL, cast(dat1#xL as bigint) AS dat1#xL]
                     +- Project [id#xL, dat1#xL]
                        +- SubqueryAlias v
                           +- Project [col1#xL AS id#xL, col2#xL AS dat1#xL]
                              +- LocalRelation [col1#xL, col2#xL]


-- !query
select * from
  int8_tbl x join (int4_tbl x cross join int4_tbl y) j on udf(q1) = udf(f1)
-- !query analysis
org.apache.spark.sql.AnalysisException
{
  "errorClass" : "AMBIGUOUS_REFERENCE",
  "sqlState" : "42704",
  "messageParameters" : {
    "name" : "`f1`",
    "referenceNames" : "[`j`.`f1`, `j`.`f1`]"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 87,
    "stopIndex" : 88,
    "fragment" : "f1"
  } ]
}


-- !query
select * from
  int8_tbl x join (int4_tbl x cross join int4_tbl y) j on udf(q1) = udf(y.f1)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`y`.`f1`",
    "proposal" : "`j`.`f1`, `j`.`f1`, `x`.`q1`, `x`.`q2`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 87,
    "stopIndex" : 90,
    "fragment" : "y.f1"
  } ]
}


-- !query
select * from
  int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on udf(q1) = udf(udf(f1))
-- !query analysis
Project [q1#xL, q2#xL, f1#x, ff#x]
+- Join Inner, (cast(udf(cast(q1#xL as string)) as bigint) = cast(cast(udf(cast(cast(udf(cast(f1#x as string)) as int) as string)) as int) as bigint))
   :- SubqueryAlias x
   :  +- SubqueryAlias int8_tbl
   :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :           +- Project [q1#xL, q2#xL]
   :              +- SubqueryAlias v
   :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :                    +- LocalRelation [col1#xL, col2#xL]
   +- SubqueryAlias j
      +- Join Cross
         :- SubqueryAlias x
         :  +- SubqueryAlias int4_tbl
         :     +- View (`INT4_TBL`, [f1#x])
         :        +- Project [cast(f1#x as int) AS f1#x]
         :           +- Project [f1#x]
         :              +- SubqueryAlias v
         :                 +- Project [col1#x AS f1#x]
         :                    +- LocalRelation [col1#x]
         +- SubqueryAlias y
            +- Project [f1#x AS ff#x]
               +- SubqueryAlias int4_tbl
                  +- View (`INT4_TBL`, [f1#x])
                     +- Project [cast(f1#x as int) AS f1#x]
                        +- Project [f1#x]
                           +- SubqueryAlias v
                              +- Project [col1#x AS f1#x]
                                 +- LocalRelation [col1#x]


-- !query
select udf(t1.uunique1) from
  tenk1 t1 join tenk2 t2 on t1.two = udf(t2.two)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`t1`.`uunique1`",
    "proposal" : "`t1`.`unique1`, `t2`.`unique1`, `t1`.`unique2`, `t2`.`unique2`, `t1`.`four`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 12,
    "stopIndex" : 22,
    "fragment" : "t1.uunique1"
  } ]
}


-- !query
select udf(udf(t2.uunique1)) from
  tenk1 t1 join tenk2 t2 on udf(t1.two) = t2.two
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`t2`.`uunique1`",
    "proposal" : "`t2`.`unique1`, `t1`.`unique1`, `t2`.`unique2`, `t1`.`unique2`, `t2`.`four`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 16,
    "stopIndex" : 26,
    "fragment" : "t2.uunique1"
  } ]
}


-- !query
select udf(uunique1) from
  tenk1 t1 join tenk2 t2 on udf(t1.two) = udf(t2.two)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITH_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`uunique1`",
    "proposal" : "`t1`.`unique1`, `t2`.`unique1`, `t1`.`unique2`, `t2`.`unique2`, `t1`.`ten`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 12,
    "stopIndex" : 19,
    "fragment" : "uunique1"
  } ]
}


-- !query
select unique2, x.*
from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x
-- !query analysis
Project [unique2#x, f1#x]
+- LateralJoin lateral-subquery#x [unique1#x], Inner
   :  +- SubqueryAlias x
   :     +- Project [f1#x]
   :        +- Filter (f1#x = outer(unique1#x))
   :           +- SubqueryAlias b
   :              +- SubqueryAlias int4_tbl
   :                 +- View (`INT4_TBL`, [f1#x])
   :                    +- Project [cast(f1#x as int) AS f1#x]
   :                       +- Project [f1#x]
   :                          +- SubqueryAlias v
   :                             +- Project [col1#x AS f1#x]
   :                                +- LocalRelation [col1#x]
   +- SubqueryAlias a
      +- SubqueryAlias spark_catalog.default.tenk1
         +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet


-- !query
select unique2, x.*
from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss
-- !query analysis
Project [unique2#x, f1#x]
+- LateralJoin lateral-subquery#x [f1#x], Inner
   :  +- SubqueryAlias ss
   :     +- Project [unique2#x]
   :        +- Filter (outer(f1#x) = unique1#x)
   :           +- SubqueryAlias spark_catalog.default.tenk1
   :              +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   +- SubqueryAlias x
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select unique2, x.*
from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true
-- !query analysis
Project [unique2#x, f1#x]
+- LateralJoin lateral-subquery#x [f1#x], LeftOuter, true
   :  +- SubqueryAlias ss
   :     +- Project [unique1#x, unique2#x]
   :        +- Filter (outer(f1#x) = unique1#x)
   :           +- SubqueryAlias spark_catalog.default.tenk1
   :              +- Relation spark_catalog.default.tenk1[unique1#x,unique2#x,two#x,four#x,ten#x,twenty#x,hundred#x,thousand#x,twothousand#x,fivethous#x,tenthous#x,odd#x,even#x,stringu1#x,stringu2#x,string4#x] parquet
   +- SubqueryAlias x
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
  lateral (select x) ss2(y)
-- !query analysis
Project [x#x, f1#x, y#x]
+- LateralJoin lateral-subquery#x [x#x], Inner
   :  +- SubqueryAlias ss2
   :     +- Project [x#x AS y#x]
   :        +- Project [outer(x#x)]
   :           +- OneRowRelation
   +- Join Inner, (x#x = cast(f1#x as double))
      :- SubqueryAlias ss1
      :  +- Project [(cast(f1#x as double) / cast(2 as double)) AS x#x]
      :     +- SubqueryAlias int4_tbl
      :        +- View (`INT4_TBL`, [f1#x])
      :           +- Project [cast(f1#x as int) AS f1#x]
      :              +- Project [f1#x]
      :                 +- SubqueryAlias v
      :                    +- Project [col1#x AS f1#x]
      :                       +- LocalRelation [col1#x]
      +- SubqueryAlias i4
         +- SubqueryAlias int4_tbl
            +- View (`INT4_TBL`, [f1#x])
               +- Project [cast(f1#x as int) AS f1#x]
                  +- Project [f1#x]
                     +- SubqueryAlias v
                        +- Project [col1#x AS f1#x]
                           +- LocalRelation [col1#x]


-- !query
select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j,
  lateral (select x) ss2(y)
-- !query analysis
Project [x#x, f1#x, y#x]
+- LateralJoin lateral-subquery#x [x#x], Inner
   :  +- SubqueryAlias ss2
   :     +- Project [x#x AS y#x]
   :        +- Project [outer(x#x)]
   :           +- OneRowRelation
   +- SubqueryAlias j
      +- Join Inner, (x#x = cast(f1#x as double))
         :- SubqueryAlias ss1
         :  +- Project [(cast(f1#x as double) / cast(2 as double)) AS x#x]
         :     +- SubqueryAlias int4_tbl
         :        +- View (`INT4_TBL`, [f1#x])
         :           +- Project [cast(f1#x as int) AS f1#x]
         :              +- Project [f1#x]
         :                 +- SubqueryAlias v
         :                    +- Project [col1#x AS f1#x]
         :                       +- LocalRelation [col1#x]
         +- SubqueryAlias i4
            +- SubqueryAlias int4_tbl
               +- View (`INT4_TBL`, [f1#x])
                  +- Project [cast(f1#x as int) AS f1#x]
                     +- Project [f1#x]
                        +- SubqueryAlias v
                           +- Project [col1#x AS f1#x]
                              +- LocalRelation [col1#x]


-- !query
select * from
  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
  lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2)
-- !query analysis
Project [q1#xL, q2#xL, q1#xL, q2#xL, xq1#xL, yq1#xL, yq2#xL]
+- LateralJoin lateral-subquery#x [q1#xL && q1#xL && q2#xL], Inner
   :  +- SubqueryAlias v
   :     +- Project [q1#xL AS xq1#xL, q1#xL AS yq1#xL, q2#xL AS yq2#xL]
   :        +- Project [outer(q1#xL), outer(q1#xL), outer(q2#xL)]
   :           +- OneRowRelation
   +- Join LeftOuter, (q2#xL = q1#xL)
      :- SubqueryAlias x
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias y
         +- Project [q1#xL, coalesce(q2#xL, cast(0 as bigint)) AS q2#xL]
            +- SubqueryAlias int8_tbl
               +- View (`INT8_TBL`, [q1#xL, q2#xL])
                  +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                     +- Project [q1#xL, q2#xL]
                        +- SubqueryAlias v
                           +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                              +- LocalRelation [col1#xL, col2#xL]


-- !query
select x.* from
  int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
  lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2)
-- !query analysis
Project [q1#xL, q2#xL]
+- LateralJoin lateral-subquery#x [q1#xL && q1#xL && q2#xL], Inner
   :  +- SubqueryAlias v
   :     +- Project [q1#xL AS xq1#xL, q1#xL AS yq1#xL, q2#xL AS yq2#xL]
   :        +- Project [outer(q1#xL), outer(q1#xL), outer(q2#xL)]
   :           +- OneRowRelation
   +- Join LeftOuter, (q2#xL = q1#xL)
      :- SubqueryAlias x
      :  +- SubqueryAlias int8_tbl
      :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
      :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
      :           +- Project [q1#xL, q2#xL]
      :              +- SubqueryAlias v
      :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
      :                    +- LocalRelation [col1#xL, col2#xL]
      +- SubqueryAlias y
         +- Project [q1#xL, coalesce(q2#xL, cast(0 as bigint)) AS q2#xL]
            +- SubqueryAlias int8_tbl
               +- View (`INT8_TBL`, [q1#xL, q2#xL])
                  +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
                     +- Project [q1#xL, q2#xL]
                        +- SubqueryAlias v
                           +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                              +- LocalRelation [col1#xL, col2#xL]


-- !query
select * from
  int8_tbl a left join
  lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1
-- !query analysis
Project [q1#xL, q2#xL, q1#xL, q2#xL, x#xL]
+- LateralJoin lateral-subquery#x [q2#xL], LeftOuter, (q2#xL = q1#xL)
   :  +- SubqueryAlias ss
   :     +- Project [q1#xL, q2#xL, outer(q2#xL) AS x#xL]
   :        +- SubqueryAlias b
   :           +- SubqueryAlias int8_tbl
   :              +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :                 +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :                    +- Project [q1#xL, q2#xL]
   :                       +- SubqueryAlias v
   :                          +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :                             +- LocalRelation [col1#xL, col2#xL]
   +- SubqueryAlias a
      +- SubqueryAlias int8_tbl
         +- View (`INT8_TBL`, [q1#xL, q2#xL])
            +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
               +- Project [q1#xL, q2#xL]
                  +- SubqueryAlias v
                     +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                        +- LocalRelation [col1#xL, col2#xL]


-- !query
select * from
  int8_tbl a left join
  lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1
-- !query analysis
Project [q1#xL, q2#xL, q1#xL, q2#xL, x#xL]
+- LateralJoin lateral-subquery#x [q2#xL], LeftOuter, (q2#xL = q1#xL)
   :  +- SubqueryAlias ss
   :     +- Project [q1#xL, q2#xL, coalesce(outer(q2#xL), cast(42 as bigint)) AS x#xL]
   :        +- SubqueryAlias b
   :           +- SubqueryAlias int8_tbl
   :              +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :                 +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :                    +- Project [q1#xL, q2#xL]
   :                       +- SubqueryAlias v
   :                          +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :                             +- LocalRelation [col1#xL, col2#xL]
   +- SubqueryAlias a
      +- SubqueryAlias int8_tbl
         +- View (`INT8_TBL`, [q1#xL, q2#xL])
            +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
               +- Project [q1#xL, q2#xL]
                  +- SubqueryAlias v
                     +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                        +- LocalRelation [col1#xL, col2#xL]


-- !query
select * from int4_tbl i left join
  lateral (select * from int2_tbl j where i.f1 = j.f1) k on true
-- !query analysis
Project [f1#x, f1#x]
+- LateralJoin lateral-subquery#x [f1#x], LeftOuter, true
   :  +- SubqueryAlias k
   :     +- Project [f1#x]
   :        +- Filter (outer(f1#x) = cast(f1#x as int))
   :           +- SubqueryAlias j
   :              +- SubqueryAlias int2_tbl
   :                 +- View (`INT2_TBL`, [f1#x])
   :                    +- Project [cast(col1#x as smallint) AS f1#x]
   :                       +- LocalRelation [col1#x]
   +- SubqueryAlias i
      +- SubqueryAlias int4_tbl
         +- View (`INT4_TBL`, [f1#x])
            +- Project [cast(f1#x as int) AS f1#x]
               +- Project [f1#x]
                  +- SubqueryAlias v
                     +- Project [col1#x AS f1#x]
                        +- LocalRelation [col1#x]


-- !query
select * from
  int8_tbl a left join lateral
  (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
   int8_tbl b cross join int8_tbl c) ss
  on a.q2 = ss.bq1
-- !query analysis
Project [q1#xL, q2#xL, bq1#xL, cq1#xL, least(outer(a.q1), q1, q1)#xL]
+- LateralJoin lateral-subquery#x [q1#xL], LeftOuter, (q2#xL = bq1#xL)
   :  +- SubqueryAlias ss
   :     +- Project [q1#xL AS bq1#xL, q1#xL AS cq1#xL, least(outer(q1#xL), q1#xL, q1#xL) AS least(outer(a.q1), q1, q1)#xL]
   :        +- Join Cross
   :           :- SubqueryAlias b
   :           :  +- SubqueryAlias int8_tbl
   :           :     +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :           :        +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :           :           +- Project [q1#xL, q2#xL]
   :           :              +- SubqueryAlias v
   :           :                 +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :           :                    +- LocalRelation [col1#xL, col2#xL]
   :           +- SubqueryAlias c
   :              +- SubqueryAlias int8_tbl
   :                 +- View (`INT8_TBL`, [q1#xL, q2#xL])
   :                    +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
   :                       +- Project [q1#xL, q2#xL]
   :                          +- SubqueryAlias v
   :                             +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
   :                                +- LocalRelation [col1#xL, col2#xL]
   +- SubqueryAlias a
      +- SubqueryAlias int8_tbl
         +- View (`INT8_TBL`, [q1#xL, q2#xL])
            +- Project [cast(q1#xL as bigint) AS q1#xL, cast(q2#xL as bigint) AS q2#xL]
               +- Project [q1#xL, q2#xL]
                  +- SubqueryAlias v
                     +- Project [col1#xL AS q1#xL, col2#xL AS q2#xL]
                        +- LocalRelation [col1#xL, col2#xL]


-- !query
select udf(udf(f1,g)) from int4_tbl a, (select udf(udf(f1)) as g) ss
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITHOUT_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`f1`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 56,
    "stopIndex" : 57,
    "fragment" : "f1"
  } ]
}


-- !query
select udf(f1,g) from int4_tbl a, (select a.f1 as g) ss
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITHOUT_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`a`.`f1`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 43,
    "stopIndex" : 46,
    "fragment" : "a.f1"
  } ]
}


-- !query
select udf(udf(f1,g)) from int4_tbl a cross join (select udf(f1) as g) ss
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITHOUT_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`f1`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 62,
    "stopIndex" : 63,
    "fragment" : "f1"
  } ]
}


-- !query
select udf(f1,g) from int4_tbl a cross join (select udf(udf(a.f1)) as g) ss
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
  "errorClass" : "UNRESOLVED_COLUMN.WITHOUT_SUGGESTION",
  "sqlState" : "42703",
  "messageParameters" : {
    "objectName" : "`a`.`f1`"
  },
  "queryContext" : [ {
    "objectType" : "",
    "objectName" : "",
    "startIndex" : 61,
    "stopIndex" : 64,
    "fragment" : "a.f1"
  } ]
}


-- !query
CREATE TABLE j1 (id1 int, id2 int) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`j1`, false


-- !query
CREATE TABLE j2 (id1 int, id2 int) USING parquet
-- !query analysis
CreateDataSourceTableCommand `spark_catalog`.`default`.`j2`, false


-- !query
INSERT INTO j1 values(1,1),(1,2)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j1, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j1], Append, `spark_catalog`.`default`.`j1`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j1), [id1, id2]
+- Project [cast(col1#x as int) AS id1#x, cast(col2#x as int) AS id2#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO j2 values(1,1)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2], Append, `spark_catalog`.`default`.`j2`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2), [id1, id2]
+- Project [cast(col1#x as int) AS id1#x, cast(col2#x as int) AS id2#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
INSERT INTO j2 values(1,2)
-- !query analysis
InsertIntoHadoopFsRelationCommand file:[not included in comparison]/{warehouse_dir}/j2, false, Parquet, [path=file:[not included in comparison]/{warehouse_dir}/j2], Append, `spark_catalog`.`default`.`j2`, org.apache.spark.sql.execution.datasources.InMemoryFileIndex(file:[not included in comparison]/{warehouse_dir}/j2), [id1, id2]
+- Project [cast(col1#x as int) AS id1#x, cast(col2#x as int) AS id2#x]
   +- LocalRelation [col1#x, col2#x]


-- !query
select * from j1
inner join j2 on udf(j1.id1) = udf(j2.id1) and udf(udf(j1.id2)) = udf(j2.id2)
where udf(j1.id1) % 1000 = 1 and udf(udf(j2.id1) % 1000) = 1
-- !query analysis
Project [id1#x, id2#x, id1#x, id2#x]
+- Filter (((cast(udf(cast(id1#x as string)) as int) % 1000) = 1) AND (cast(udf(cast((cast(udf(cast(id1#x as string)) as int) % 1000) as string)) as int) = 1))
   +- Join Inner, ((cast(udf(cast(id1#x as string)) as int) = cast(udf(cast(id1#x as string)) as int)) AND (cast(udf(cast(cast(udf(cast(id2#x as string)) as int) as string)) as int) = cast(udf(cast(id2#x as string)) as int)))
      :- SubqueryAlias spark_catalog.default.j1
      :  +- Relation spark_catalog.default.j1[id1#x,id2#x] parquet
      +- SubqueryAlias spark_catalog.default.j2
         +- Relation spark_catalog.default.j2[id1#x,id2#x] parquet


-- !query
drop table j1
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.j1


-- !query
drop table j2
-- !query analysis
DropTable false, false
+- ResolvedIdentifier V2SessionCatalog(spark_catalog), default.j2