Google Git
Sign in
apache / spark / 5f883117203d823cb9914f483e314633845ecaa5 / . / sql / core / src / test / resources / sql-tests / analyzer-results / subquery / scalar-subquery / scalar-subquery-predicate.sql.out
blob: 48769f90faffd786443549390661540a2fcb641e [file] [log] [blame]
-- Automatically generated by SQLQueryTestSuite
-- !query
CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv)
-- !query analysis
CreateViewCommand `p`, VALUES (1, 1) AS T(pk, pv), false, true, LocalTempView, true
+- SubqueryAlias T
+- LocalRelation [pk#x, pv#x]
-- !query
CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv)
-- !query analysis
CreateViewCommand `c`, VALUES (1, 1) AS T(ck, cv), false, true, LocalTempView, true
+- SubqueryAlias T
+- LocalRelation [ck#x, cv#x]
-- !query
SELECT pk, cv
FROM p, c
WHERE p.pk = c.ck
AND c.cv = (SELECT avg(c1.cv)
FROM c c1
WHERE c1.ck = p.pk)
-- !query analysis
Project [pk#x, cv#x]
+- Filter ((pk#x = ck#x) AND (cast(cv#x as double) = scalar-subquery#x [pk#x]))
: +- Aggregate [avg(cv#x) AS avg(cv)#x]
: +- Filter (ck#x = outer(pk#x))
: +- SubqueryAlias c1
: +- SubqueryAlias c
: +- View (`c`, [ck#x, cv#x])
: +- Project [cast(ck#x as int) AS ck#x, cast(cv#x as int) AS cv#x]
: +- SubqueryAlias T
: +- LocalRelation [ck#x, cv#x]
+- Join Inner
:- SubqueryAlias p
: +- View (`p`, [pk#x, pv#x])
: +- Project [cast(pk#x as int) AS pk#x, cast(pv#x as int) AS pv#x]
: +- SubqueryAlias T
: +- LocalRelation [pk#x, pv#x]
+- SubqueryAlias c
+- View (`c`, [ck#x, cv#x])
+- Project [cast(ck#x as int) AS ck#x, cast(cv#x as int) AS cv#x]
+- SubqueryAlias T
+- LocalRelation [ck#x, cv#x]
-- !query
SELECT pk, cv
FROM p, c
WHERE p.pk = c.ck
AND c.cv = (SELECT max(avg)
FROM (SELECT c1.cv, avg(c1.cv) avg
FROM c c1
WHERE c1.ck = p.pk
GROUP BY c1.cv))
-- !query analysis
Project [pk#x, cv#x]
+- Filter ((pk#x = ck#x) AND (cast(cv#x as double) = scalar-subquery#x [pk#x]))
: +- Aggregate [max(avg#x) AS max(avg)#x]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Aggregate [cv#x], [cv#x, avg(cv#x) AS avg#x]
: +- Filter (ck#x = outer(pk#x))
: +- SubqueryAlias c1
: +- SubqueryAlias c
: +- View (`c`, [ck#x, cv#x])
: +- Project [cast(ck#x as int) AS ck#x, cast(cv#x as int) AS cv#x]
: +- SubqueryAlias T
: +- LocalRelation [ck#x, cv#x]
+- Join Inner
:- SubqueryAlias p
: +- View (`p`, [pk#x, pv#x])
: +- Project [cast(pk#x as int) AS pk#x, cast(pv#x as int) AS pv#x]
: +- SubqueryAlias T
: +- LocalRelation [pk#x, pv#x]
+- SubqueryAlias c
+- View (`c`, [ck#x, cv#x])
+- Project [cast(ck#x as int) AS ck#x, cast(cv#x as int) AS cv#x]
+- SubqueryAlias T
+- LocalRelation [ck#x, cv#x]
-- !query
create temporary view t1 as select * from values
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2BD, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
('val1d', null, 16, 22L, float(17.0), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', null),
('val1d', null, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.001', null),
('val1e', 10S, null, 25L, float(17.0), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
('val1d', 10S, null, 12L, float(17.0), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i)
-- !query analysis
CreateViewCommand `t1`, select * from values
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'),
('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2BD, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
('val1d', null, 16, 22L, float(17.0), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', null),
('val1d', null, 16, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.001', null),
('val1e', 10S, null, 25L, float(17.0), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
('val1d', 10S, null, 12L, float(17.0), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
('val1e', 10S, null, 19L, float(17.0), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i), false, false, LocalTempView, true
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
create temporary view t2 as select * from values
('val2a', 6S, 12, 14L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1b', 8S, 16, 119L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
('val1c', 12S, 16, 219L, float(17), 25D, 26E2BD, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
('val1b', null, 16, 319L, float(17), 25D, 26E2BD, timestamp '2017-05-04 01:01:00.000', null),
('val2e', 8S, null, 419L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
('val1f', 19S, null, 519L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
('val1b', 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
('val1c', 12S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
('val1e', 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
('val1f', 19S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
('val1b', null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', null)
as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i)
-- !query analysis
CreateViewCommand `t2`, select * from values
('val2a', 6S, 12, 14L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1b', 8S, 16, 119L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
('val1c', 12S, 16, 219L, float(17), 25D, 26E2BD, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
('val1b', null, 16, 319L, float(17), 25D, 26E2BD, timestamp '2017-05-04 01:01:00.000', null),
('val2e', 8S, null, 419L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
('val1f', 19S, null, 519L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
('val1b', 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
('val1c', 12S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
('val1e', 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
('val1f', 19S, null, 19L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
('val1b', null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:01:00.000', null)
as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i), false, false, LocalTempView, true
+- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t2
+- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
-- !query
create temporary view t3 as select * from values
('val3a', 6S, 12, 110L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
('val3a', 6S, 12, 10L, float(15), 20D, 20E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 10S, 12, 219L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 8S, 16, 319L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
('val1b', 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
('val3c', 17S, 16, 519L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
('val3c', 17S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
('val1b', null, 16, 419L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:02:00.000', null),
('val1b', null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-11-04 01:02:00.000', null),
('val3b', 8S, null, 719L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val3b', 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i)
-- !query analysis
CreateViewCommand `t3`, select * from values
('val3a', 6S, 12, 110L, float(15), 20D, 20E2BD, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
('val3a', 6S, 12, 10L, float(15), 20D, 20E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 10S, 12, 219L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 10S, 12, 19L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val1b', 8S, 16, 319L, float(17), 25D, 26E2BD, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
('val1b', 8S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
('val3c', 17S, 16, 519L, float(17), 25D, 26E2BD, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
('val3c', 17S, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
('val1b', null, 16, 419L, float(17), 25D, 26E2BD, timestamp '2014-10-04 01:02:00.000', null),
('val1b', null, 16, 19L, float(17), 25D, 26E2BD, timestamp '2014-11-04 01:02:00.000', null),
('val3b', 8S, null, 719L, float(17), 25D, 26E2BD, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
('val3b', 8S, null, 19L, float(17), 25D, 26E2BD, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i), false, false, LocalTempView, true
+- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t3
+- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT max(t2c)
FROM t2)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [])
: +- Aggregate [max(t2c#x) AS max(t2c)#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1d, t1f
FROM t1
WHERE t1c = (SELECT max(t2c)
FROM t2)
AND t1b > (SELECT min(t3b)
FROM t3)
-- !query analysis
Project [t1a#x, t1d#xL, t1f#x]
+- Filter ((t1c#x = scalar-subquery#x []) AND (t1b#x > scalar-subquery#x []))
: :- Aggregate [max(t2c#x) AS max(t2c)#x]
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- Aggregate [min(t3b#x) AS min(t3b)#x]
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1h
FROM t1
WHERE t1c = (SELECT max(t2c)
FROM t2)
OR t1b = (SELECT min(t3b)
FROM t3
WHERE t3b > 10)
-- !query analysis
Project [t1a#x, t1h#x]
+- Filter ((t1c#x = scalar-subquery#x []) OR (t1b#x = scalar-subquery#x []))
: :- Aggregate [max(t2c#x) AS max(t2c)#x]
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- Aggregate [min(t3b#x) AS min(t3b)#x]
: +- Filter (cast(t3b#x as int) > 10)
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b, t2d
FROM t1 LEFT JOIN t2
ON t1a = t2a
WHERE t1b = (SELECT min(t3b)
FROM t3)
-- !query analysis
Project [t1a#x, t1b#x, t2d#xL]
+- Filter (t1b#x = scalar-subquery#x [])
: +- Aggregate [min(t3b#x) AS min(t3b)#x]
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- Join LeftOuter, (t1a#x = t2a#x)
:- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t2
+- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t2
+- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
-- !query
SELECT t1a, t1b, t1g
FROM t1
WHERE t1c + 5 = (SELECT max(t2e)
FROM t2)
-- !query analysis
Project [t1a#x, t1b#x, t1g#x]
+- Filter (cast((t1c#x + 5) as float) = scalar-subquery#x [])
: +- Aggregate [max(t2e#x) AS max(t2e)#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1h
FROM t1
WHERE date(t1h) = (SELECT min(t2i)
FROM t2)
-- !query analysis
Project [t1a#x, t1h#x]
+- Filter (cast(t1h#x as date) = scalar-subquery#x [])
: +- Aggregate [min(t2i#x) AS min(t2i)#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t2d, t1a
FROM t1, t2
WHERE t1b = t2b
AND t2c + 1 = (SELECT max(t2c) + 1
FROM t2, t1
WHERE t2b = t1b)
-- !query analysis
Project [t2d#xL, t1a#x]
+- Filter ((t1b#x = t2b#x) AND ((t2c#x + 1) = scalar-subquery#x []))
: +- Aggregate [(max(t2c#x) + 1) AS (max(t2c) + 1)#x]
: +- Filter (t2b#x = t1b#x)
: +- Join Inner
: :- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Join Inner
:- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t2
+- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t2
+- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
-- !query
SELECT DISTINCT t2a, max_t1g
FROM t2, (SELECT max(t1g) max_t1g, t1a
FROM t1
GROUP BY t1a) t1
WHERE t2a = t1a
AND max_t1g = (SELECT max(t1g)
FROM t1)
-- !query analysis
Distinct
+- Project [t2a#x, max_t1g#x]
+- Filter ((t2a#x = t1a#x) AND (max_t1g#x = scalar-subquery#x []))
: +- Aggregate [max(t1g#x) AS max(t1g)#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Join Inner
:- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- Aggregate [t1a#x], [max(t1g#x) AS max_t1g#x, t1a#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t3b, t3c
FROM t3
WHERE (SELECT max(t3c)
FROM t3
WHERE t3b > 10) >=
(SELECT min(t3b)
FROM t3
WHERE t3c > 0)
AND (t3b is null or t3c is null)
-- !query analysis
Project [t3b#x, t3c#x]
+- Filter ((scalar-subquery#x [] >= cast(scalar-subquery#x [] as int)) AND (isnull(t3b#x) OR isnull(t3c#x)))
: :- Aggregate [max(t3c#x) AS max(t3c)#x]
: : +- Filter (cast(t3b#x as int) > 10)
: : +- SubqueryAlias t3
: : +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: : +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: : +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: : +- SubqueryAlias t3
: : +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- Aggregate [min(t3b#x) AS min(t3b)#x]
: +- Filter (t3c#x > 0)
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t3
+- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
+- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
+- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t3
+- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a < (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Project [t1a#x]
+- Filter (t1a#x < scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1c
FROM t1
WHERE (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c) IS NULL
-- !query analysis
Project [t1a#x, t1c#x]
+- Filter isnull(scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a = (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c
HAVING count(*) >= 0)
OR t1i > '2014-12-31'
-- !query analysis
Project [t1a#x]
+- Filter ((t1a#x = scalar-subquery#x [t1c#x]) OR (t1i#x > cast(2014-12-31 as date)))
: +- Project [max(t2a)#x]
: +- Filter (count(1)#xL >= cast(0 as bigint))
: +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x, count(1) AS count(1)#xL]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a = (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c
HAVING count(*) >= 1)
OR t1i > '2014-12-31'
-- !query analysis
Project [t1a#x]
+- Filter ((t1a#x = scalar-subquery#x [t1c#x]) OR (t1i#x > cast(2014-12-31 as date)))
: +- Project [max(t2a)#x]
: +- Filter (count(1)#xL >= cast(1 as bigint))
: +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x, count(1) AS count(1)#xL]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT count(t1a)
FROM t1 RIGHT JOIN t2
ON t1d = t2d
WHERE t1a < (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Aggregate [count(t1a#x) AS count(t1a)#xL]
+- Filter (t1a#x < scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- Join RightOuter, (t1d#xL = t2d#xL)
:- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t2
+- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
+- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
+- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t2
+- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1b <= (SELECT max(t2b)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
AND t1b >= (SELECT min(t2b)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Project [t1a#x]
+- Filter ((t1b#x <= scalar-subquery#x [t1c#x]) AND (t1b#x >= scalar-subquery#x [t1c#x]))
: :- Aggregate [t2c#x], [max(t2b#x) AS max(t2b)#x]
: : +- Filter (t2c#x = outer(t1c#x))
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- Aggregate [t2c#x], [min(t2b#x) AS min(t2b)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a <= (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
INTERSECT
SELECT t1a
FROM t1
WHERE t1a >= (SELECT min(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Intersect false
:- Project [t1a#x]
: +- Filter (t1a#x <= scalar-subquery#x [t1c#x])
: : +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: : +- Filter (t2c#x = outer(t1c#x))
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Project [t1a#x]
+- Filter (t1a#x >= scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [min(t2a#x) AS min(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a <= (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
UNION ALL
SELECT t1a
FROM t1
WHERE t1a >= (SELECT min(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Union false, false
:- Project [t1a#x]
: +- Filter (t1a#x <= scalar-subquery#x [t1c#x])
: : +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: : +- Filter (t2c#x = outer(t1c#x))
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Project [t1a#x]
+- Filter (t1a#x >= scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [min(t2a#x) AS min(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a <= (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
UNION DISTINCT
SELECT t1a
FROM t1
WHERE t1a >= (SELECT min(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Distinct
+- Union false, false
:- Project [t1a#x]
: +- Filter (t1a#x <= scalar-subquery#x [t1c#x])
: : +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: : +- Filter (t2c#x = outer(t1c#x))
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Project [t1a#x]
+- Filter (t1a#x >= scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [min(t2a#x) AS min(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
WHERE t1a <= (SELECT max(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
MINUS
SELECT t1a
FROM t1
WHERE t1a >= (SELECT min(t2a)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Except false
:- Project [t1a#x]
: +- Filter (t1a#x <= scalar-subquery#x [t1c#x])
: : +- Aggregate [t2c#x], [max(t2a#x) AS max(t2a)#x]
: : +- Filter (t2c#x = outer(t1c#x))
: : +- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t1
: +- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
: +- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
: +- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
: +- SubqueryAlias t1
: +- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- Project [t1a#x]
+- Filter (t1a#x >= scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [min(t2a#x) AS min(t2a)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a
FROM t1
GROUP BY t1a, t1c
HAVING max(t1b) <= (SELECT max(t2b)
FROM t2
WHERE t2c = t1c
GROUP BY t2c)
-- !query analysis
Project [t1a#x]
+- Filter (max(t1b#x)#x <= scalar-subquery#x [t1c#x])
: +- Aggregate [t2c#x], [max(t2b#x) AS max(t2b)#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- Aggregate [t1a#x, t1c#x], [t1a#x, max(t1b#x) AS max(t1b#x)#x, t1c#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT 1
FROM t1
WHERE t1b < (SELECT MAX(tmp.s) FROM (
SELECT SUM(t2b) OVER (partition by t2c order by t2d) as s
FROM t2 WHERE t2.t2d = t1.t1d) as tmp)
-- !query analysis
Project [1 AS 1#x]
+- Filter (cast(t1b#x as bigint) < scalar-subquery#x [t1d#xL])
: +- Aggregate [max(s#xL) AS max(s)#xL]
: +- SubqueryAlias tmp
: +- Project [s#xL]
: +- Project [t2b#x, t2c#x, t2d#xL, s#xL, s#xL]
: +- Window [sum(t2b#x) windowspecdefinition(t2c#x, t2d#xL ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS s#xL], [t2c#x], [t2d#xL ASC NULLS FIRST]
: +- Project [t2b#x, t2c#x, t2d#xL]
: +- Filter (t2d#xL = outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT 1
FROM t1
WHERE t1b < (SELECT SUM(t2b) OVER (partition by t2c order by t2d) as s
FROM t2 WHERE t2.t2d = t1.t1d
ORDER BY s DESC
LIMIT 1)
-- !query analysis
Project [1 AS 1#x]
+- Filter (cast(t1b#x as bigint) < scalar-subquery#x [t1d#xL])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Sort [s#xL DESC NULLS LAST], true
: +- Project [s#xL]
: +- Project [t2b#x, t2c#x, t2d#xL, s#xL, s#xL]
: +- Window [sum(t2b#x) windowspecdefinition(t2c#x, t2d#xL ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS s#xL], [t2c#x], [t2d#xL ASC NULLS FIRST]
: +- Project [t2b#x, t2c#x, t2d#xL]
: +- Filter (t2d#xL = outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT 1
FROM t1
WHERE t1b < (SELECT MAX(tmp.s) FROM (
SELECT SUM(t2b) OVER (partition by t2c order by t2d) as s
FROM t2 WHERE t2.t2d <= t1.t1d) as tmp)
-- !query analysis
Project [1 AS 1#x]
+- Filter (cast(t1b#x as bigint) < scalar-subquery#x [t1d#xL])
: +- Aggregate [max(s#xL) AS max(s)#xL]
: +- SubqueryAlias tmp
: +- Project [s#xL]
: +- Project [t2b#x, t2c#x, t2d#xL, s#xL, s#xL]
: +- Window [sum(t2b#x) windowspecdefinition(t2c#x, t2d#xL ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS s#xL], [t2c#x], [t2d#xL ASC NULLS FIRST]
: +- Project [t2b#x, t2c#x, t2d#xL]
: +- Filter (t2d#xL <= outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT 1
FROM t1
WHERE t1b < (SELECT SUM(t2b) OVER (partition by t2c order by t2d) as s
FROM t2 WHERE t2.t2d <= t1.t1d
ORDER BY s DESC
LIMIT 1)
-- !query analysis
Project [1 AS 1#x]
+- Filter (cast(t1b#x as bigint) < scalar-subquery#x [t1d#xL])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Sort [s#xL DESC NULLS LAST], true
: +- Project [s#xL]
: +- Project [t2b#x, t2c#x, t2d#xL, s#xL, s#xL]
: +- Window [sum(t2b#x) windowspecdefinition(t2c#x, t2d#xL ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS s#xL], [t2c#x], [t2d#xL ASC NULLS FIRST]
: +- Project [t2b#x, t2c#x, t2d#xL]
: +- Filter (t2d#xL <= outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1b
FROM t1
WHERE t1b > (SELECT MAX(tmp.s) FROM (
SELECT RANK() OVER (partition by t3c, t2b order by t3c) as s
FROM t2, t3 where t2.t2c = t3.t3c AND t2.t2a = t1.t1a) as tmp)
-- !query analysis
Project [t1b#x]
+- Filter (cast(t1b#x as int) > scalar-subquery#x [t1a#x])
: +- Aggregate [max(s#x) AS max(s)#x]
: +- SubqueryAlias tmp
: +- Project [s#x]
: +- Project [t3c#x, t2b#x, s#x, s#x]
: +- Window [rank(t3c#x) windowspecdefinition(t3c#x, t2b#x, t3c#x ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS s#x], [t3c#x, t2b#x], [t3c#x ASC NULLS FIRST]
: +- Project [t3c#x, t2b#x]
: +- Filter ((t2c#x = t3c#x) AND (t2a#x = outer(t1a#x)))
: +- Join Inner
: :- SubqueryAlias t2
: : +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: : +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: : +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: : +- SubqueryAlias t2
: : +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1b
FROM t1
WHERE t1b > (SELECT MAX(tmp.s) FROM (
SELECT RANK() OVER (partition by t3c, t3d order by t3c) as s
FROM (SELECT t3b, t3c, max(t3d) as t3d FROM t3 GROUP BY t3b, t3c) as g) as tmp)
ORDER BY t1b
-- !query analysis
Sort [t1b#x ASC NULLS FIRST], true
+- Project [t1b#x]
+- Filter (cast(t1b#x as int) > scalar-subquery#x [])
: +- Aggregate [max(s#x) AS max(s)#x]
: +- SubqueryAlias tmp
: +- Project [s#x]
: +- Project [t3c#x, t3d#xL, s#x, s#x]
: +- Window [rank(t3c#x) windowspecdefinition(t3c#x, t3d#xL, t3c#x ASC NULLS FIRST, specifiedwindowframe(RowFrame, unboundedpreceding$(), currentrow$())) AS s#x], [t3c#x, t3d#xL], [t3c#x ASC NULLS FIRST]
: +- Project [t3c#x, t3d#xL]
: +- SubqueryAlias g
: +- Aggregate [t3b#x, t3c#x], [t3b#x, t3c#x, max(t3d#xL) AS t3d#xL]
: +- SubqueryAlias t3
: +- View (`t3`, [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x])
: +- Project [cast(t3a#x as string) AS t3a#x, cast(t3b#x as smallint) AS t3b#x, cast(t3c#x as int) AS t3c#x, cast(t3d#xL as bigint) AS t3d#xL, cast(t3e#x as float) AS t3e#x, cast(t3f#x as double) AS t3f#x, cast(t3g#x as decimal(4,0)) AS t3g#x, cast(t3h#x as timestamp) AS t3h#x, cast(t3i#x as date) AS t3i#x]
: +- Project [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
: +- SubqueryAlias t3
: +- LocalRelation [t3a#x, t3b#x, t3c#x, t3d#xL, t3e#x, t3f#x, t3g#x, t3h#x, t3i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT 1
FROM t1
WHERE t1b = (SELECT MAX(tmp.s) FROM (
SELECT SUM(t2c) OVER (partition by t2c order by t1.t1d + t2d) as s
FROM t2) as tmp)
-- !query analysis
Project [1 AS 1#x]
+- Filter (cast(t1b#x as bigint) = scalar-subquery#x [t1d#xL])
: +- Aggregate [max(s#xL) AS max(s)#xL]
: +- SubqueryAlias tmp
: +- Project [s#xL]
: +- Project [t2c#x, _w1#xL, s#xL, s#xL]
: +- Window [sum(t2c#x) windowspecdefinition(t2c#x, _w1#xL ASC NULLS FIRST, specifiedwindowframe(RangeFrame, unboundedpreceding$(), currentrow$())) AS s#xL], [t2c#x], [_w1#xL ASC NULLS FIRST]
: +- Project [t2c#x, (outer(t1d#xL) + t2d#xL) AS _w1#xL]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT t2c
FROM t2
WHERE t2b < t1b
ORDER BY t2d LIMIT 1)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [t1b#x])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Project [t2c#x]
: +- Sort [t2d#xL ASC NULLS FIRST], true
: +- Project [t2c#x, t2d#xL]
: +- Filter (t2b#x < outer(t1b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT t2c
FROM t2
WHERE t2c = t1c
ORDER BY t2c LIMIT 1)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [t1c#x])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Sort [t2c#x ASC NULLS FIRST], true
: +- Project [t2c#x]
: +- Filter (t2c#x = outer(t1c#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT t2c
FROM t2
WHERE t1b < t1d
ORDER BY t2c LIMIT 1)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [t1b#x && t1d#xL])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Sort [t2c#x ASC NULLS FIRST], true
: +- Project [t2c#x]
: +- Filter (cast(outer(t1b#x) as bigint) < outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT MAX(t2c)
FROM t2
WHERE t1b < t1d
ORDER BY min(t2c) LIMIT 1)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [t1b#x && t1d#xL])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Project [max(t2c)#x]
: +- Sort [min(t2c#x)#x ASC NULLS FIRST], true
: +- Aggregate [max(t2c#x) AS max(t2c)#x, min(t2c#x) AS min(t2c#x)#x]
: +- Filter (cast(outer(t1b#x) as bigint) < outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
SELECT t1a, t1b
FROM t1
WHERE t1c = (SELECT DISTINCT t2c
FROM t2
WHERE t1b < t1d
ORDER BY t2c LIMIT 1)
-- !query analysis
Project [t1a#x, t1b#x]
+- Filter (t1c#x = scalar-subquery#x [t1b#x && t1d#xL])
: +- GlobalLimit 1
: +- LocalLimit 1
: +- Sort [t2c#x ASC NULLS FIRST], true
: +- Distinct
: +- Project [t2c#x]
: +- Filter (cast(outer(t1b#x) as bigint) < outer(t1d#xL))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x])
: +- Project [cast(t2a#x as string) AS t2a#x, cast(t2b#x as smallint) AS t2b#x, cast(t2c#x as int) AS t2c#x, cast(t2d#xL as bigint) AS t2d#xL, cast(t2e#x as float) AS t2e#x, cast(t2f#x as double) AS t2f#x, cast(t2g#x as decimal(4,0)) AS t2g#x, cast(t2h#x as timestamp) AS t2h#x, cast(t2i#x as date) AS t2i#x]
: +- Project [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
: +- SubqueryAlias t2
: +- LocalRelation [t2a#x, t2b#x, t2c#x, t2d#xL, t2e#x, t2f#x, t2g#x, t2h#x, t2i#x]
+- SubqueryAlias t1
+- View (`t1`, [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x])
+- Project [cast(t1a#x as string) AS t1a#x, cast(t1b#x as smallint) AS t1b#x, cast(t1c#x as int) AS t1c#x, cast(t1d#xL as bigint) AS t1d#xL, cast(t1e#x as float) AS t1e#x, cast(t1f#x as double) AS t1f#x, cast(t1g#x as decimal(4,0)) AS t1g#x, cast(t1h#x as timestamp) AS t1h#x, cast(t1i#x as date) AS t1i#x]
+- Project [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
+- SubqueryAlias t1
+- LocalRelation [t1a#x, t1b#x, t1c#x, t1d#xL, t1e#x, t1f#x, t1g#x, t1h#x, t1i#x]
-- !query
CREATE OR REPLACE TEMP VIEW t0(t0a, t0b) AS VALUES (1, 1), (2, 0)
-- !query analysis
CreateViewCommand `t0`, [(t0a,None), (t0b,None)], VALUES (1, 1), (2, 0), false, true, LocalTempView, true
+- LocalRelation [col1#x, col2#x]
-- !query
CREATE OR REPLACE TEMP VIEW t1(t1a, t1b, t1c) AS VALUES (1, 1, 3)
-- !query analysis
CreateViewCommand `t1`, [(t1a,None), (t1b,None), (t1c,None)], VALUES (1, 1, 3), false, true, LocalTempView, true
+- LocalRelation [col1#x, col2#x, col3#x]
-- !query
CREATE OR REPLACE TEMP VIEW t2(t2a, t2b, t2c) AS VALUES (1, 1, 5), (2, 2, 7)
-- !query analysis
CreateViewCommand `t2`, [(t2a,None), (t2b,None), (t2c,None)], VALUES (1, 1, 5), (2, 2, 7), false, true, LocalTempView, true
+- LocalRelation [col1#x, col2#x, col3#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(c) FROM
(SELECT t1c as c
FROM t1
WHERE t1a = t0a
UNION ALL
SELECT t2c as c
FROM t2
WHERE t2b = t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(c#x) AS sum(c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x AS c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x AS c#x]
: +- Filter (t2b#x = outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(c) FROM
(SELECT t1c as c
FROM t1
WHERE t1a = t0a
UNION ALL
SELECT t2c as c
FROM t2
WHERE t2a = t0a)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0a#x])
: +- Aggregate [sum(c#x) AS sum(c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x AS c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x AS c#x]
: +- Filter (t2a#x = outer(t0a#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(c) FROM
(SELECT t1c as c
FROM t1
WHERE t1a > t0a
UNION ALL
SELECT t2c as c
FROM t2
WHERE t2b <= t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(c#x) AS sum(c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x AS c#x]
: : +- Filter (t1a#x > outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x AS c#x]
: +- Filter (t2b#x <= outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1c) FROM
(SELECT t1c
FROM t1
WHERE t1a = t0a
UNION ALL
SELECT t2c
FROM t2
WHERE t2b = t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(t1c#x) AS sum(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x]
: +- Filter (t2b#x = outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1c) FROM
(SELECT t1c
FROM t1
WHERE t1a = t0a
UNION DISTINCT
SELECT t2c
FROM t2
WHERE t2b = t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(t1c#x) AS sum(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Distinct
: +- Union false, false
: :- Project [t1c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x]
: +- Filter (t2b#x = outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1a + 3 * t1b + 5 * t1c) FROM
(SELECT t1c as t1a, t1a as t1b, t0a as t1c
FROM t1
WHERE t1a = t0a
UNION ALL
SELECT t0a as t2b, t2c as t1a, t0b as t2c
FROM t2
WHERE t2b = t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0a#x && t0a#x && t0b#x && t0b#x])
: +- Aggregate [sum(((t1a#x + (3 * t1b#x)) + (5 * t1c#x))) AS sum(((t1a + (3 * t1b)) + (5 * t1c)))#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x AS t1a#x, t1a#x AS t1b#x, outer(t0a#x) AS t1c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [outer(t0a#x) AS t2b#x, t2c#x AS t1a#x, outer(t0b#x) AS t2c#x]
: +- Filter (t2b#x = outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT count(t1c) FROM
(SELECT t1c
FROM t1
WHERE t1a = t0a
UNION DISTINCT
SELECT t2c
FROM t2
WHERE t2b = t0b)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [count(t1c#x) AS count(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Distinct
: +- Union false, false
: :- Project [t1c#x]
: : +- Filter (t1a#x = outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x]
: +- Filter (t2b#x = outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(d) FROM
(SELECT t1a - t0a as d
FROM t1
UNION ALL
SELECT t2a - t0a as d
FROM t2)
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0a#x])
: +- Aggregate [sum(d#x) AS sum(d)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [(t1a#x - outer(t0a#x)) AS d#x]
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [(t2a#x - outer(t0a#x)) AS d#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(d) FROM
(SELECT sum(t0a) as d
FROM t1
UNION ALL
SELECT sum(t2a) + t0a as d
FROM t2)
)
-- !query analysis
org.apache.spark.sql.catalyst.ExtendedAnalysisException
{
"errorClass" : "UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE",
"sqlState" : "0A000",
"messageParameters" : {
"sqlExprs" : "\"sum(t0a) AS d\""
},
"queryContext" : [ {
"objectType" : "",
"objectName" : "",
"startIndex" : 53,
"stopIndex" : 84,
"fragment" : "SELECT sum(t0a) as d\n FROM t1"
} ]
}
-- !query
SELECT t0a, t0b FROM t0
GROUP BY t0a, t0b
HAVING t0a <
(SELECT sum(c) FROM
(SELECT t1c as c
FROM t1
WHERE t1a > t0a
UNION ALL
SELECT t2c as c
FROM t2
WHERE t2b <= t0b)
)
-- !query analysis
Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(c#x) AS sum(c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Union false, false
: :- Project [t1c#x AS c#x]
: : +- Filter (t1a#x > outer(t0a#x))
: : +- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- Project [t2c#x AS c#x]
: +- Filter (t2b#x <= outer(t0b#x))
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- Aggregate [t0a#x, t0b#x], [t0a#x, t0b#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
select *
from range(1, 3) t1
where (select t2.id c
from range (1, 2) t2 where t1.id = t2.id
) is not null
-- !query analysis
Project [id#xL]
+- Filter isnotnull(scalar-subquery#x [id#xL])
: +- Project [id#xL AS c#xL]
: +- Filter (outer(id#xL) = id#xL)
: +- SubqueryAlias t2
: +- Range (1, 2, step=1)
+- SubqueryAlias t1
+- Range (1, 3, step=1)
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1c) FROM
(SELECT t1c
FROM t1 JOIN t2 ON (t1a = t0a AND t2b = t1b))
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x])
: +- Aggregate [sum(t1c#x) AS sum(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Project [t1c#x]
: +- Join Inner, ((t1a#x = outer(t0a#x)) AND (t2b#x = t1b#x))
: :- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1c) FROM
(SELECT t1c
FROM t1 JOIN t2 ON (t1a < t0a AND t2b >= t1b))
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x])
: +- Aggregate [sum(t1c#x) AS sum(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Project [t1c#x]
: +- Join Inner, ((t1a#x < outer(t0a#x)) AND (t2b#x >= t1b#x))
: :- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]
-- !query
SELECT * FROM t0 WHERE t0a <
(SELECT sum(t1c) FROM
(SELECT t1c
FROM t1 LEFT JOIN t2 ON (t1a = t0a AND t2b = t0b))
)
-- !query analysis
Project [t0a#x, t0b#x]
+- Filter (cast(t0a#x as bigint) < scalar-subquery#x [t0a#x && t0b#x])
: +- Aggregate [sum(t1c#x) AS sum(t1c)#xL]
: +- SubqueryAlias __auto_generated_subquery_name
: +- Project [t1c#x]
: +- Join LeftOuter, ((t1a#x = outer(t0a#x)) AND (t2b#x = outer(t0b#x)))
: :- SubqueryAlias t1
: : +- View (`t1`, [t1a#x, t1b#x, t1c#x])
: : +- Project [cast(col1#x as int) AS t1a#x, cast(col2#x as int) AS t1b#x, cast(col3#x as int) AS t1c#x]
: : +- LocalRelation [col1#x, col2#x, col3#x]
: +- SubqueryAlias t2
: +- View (`t2`, [t2a#x, t2b#x, t2c#x])
: +- Project [cast(col1#x as int) AS t2a#x, cast(col2#x as int) AS t2b#x, cast(col3#x as int) AS t2c#x]
: +- LocalRelation [col1#x, col2#x, col3#x]
+- SubqueryAlias t0
+- View (`t0`, [t0a#x, t0b#x])
+- Project [cast(col1#x as int) AS t0a#x, cast(col2#x as int) AS t0b#x]
+- LocalRelation [col1#x, col2#x]