Google Git
Sign in
apache / spark / 5f883117203d823cb9914f483e314633845ecaa5 / . / sql / core / src / test / resources / sql-tests / analyzer-results / udf / postgreSQL / udf-join.sql.out
blob: 909ba8ce8656f2ad121f16f6c0161ebe3ee7ffa9 [file] [log] [blame]
-- 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