| -- Automatically generated by SQLQueryTestSuite |
| -- !query |
| create temporary view t1 as select * from values |
| ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'), |
| ('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'), |
| ('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'), |
| ('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'), |
| ('val1d', null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null), |
| ('val1d', null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null), |
| ('val1e', 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'), |
| ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'), |
| ('val1d', 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'), |
| ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'), |
| ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, 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, 20E2, timestamp '2014-04-04 00:00:00.000', date '2014-04-04'), |
| ('val1b', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1a', 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'), |
| ('val1a', 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'), |
| ('val1c', 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'), |
| ('val1d', null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null), |
| ('val1d', null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null), |
| ('val1e', 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'), |
| ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'), |
| ('val1d', 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'), |
| ('val1a', 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'), |
| ('val1e', 10S, null, 19L, float(17.0), 25D, 26E2, 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, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1b', 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'), |
| ('val1c', 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'), |
| ('val1b', null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null), |
| ('val2e', 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'), |
| ('val1f', 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'), |
| ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'), |
| ('val1c', 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'), |
| ('val1e', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'), |
| ('val1f', 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'), |
| ('val1b', null, 16, 19L, float(17), 25D, 26E2, 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, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1b', 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'), |
| ('val1c', 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'), |
| ('val1b', null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null), |
| ('val2e', 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'), |
| ('val1f', 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'), |
| ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'), |
| ('val1c', 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'), |
| ('val1e', 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'), |
| ('val1f', 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'), |
| ('val1b', null, 16, 19L, float(17), 25D, 26E2, 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, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'), |
| ('val3a', 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'), |
| ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'), |
| ('val3c', 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'), |
| ('val3c', 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'), |
| ('val1b', null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null), |
| ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null), |
| ('val3b', 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val3b', 8S, null, 19L, float(17), 25D, 26E2, 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, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'), |
| ('val3a', 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val1b', 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'), |
| ('val1b', 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'), |
| ('val3c', 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'), |
| ('val3c', 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'), |
| ('val1b', null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null), |
| ('val1b', null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null), |
| ('val3b', 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'), |
| ('val3b', 8S, null, 19L, float(17), 25D, 26E2, 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 (SELECT min(t3d) FROM t3) min_t3d, |
| (SELECT max(t2h) FROM t2) max_t2h |
| FROM t1 |
| WHERE t1a = 'val1c' |
| -- !query analysis |
| Project [scalar-subquery#x [] AS min_t3d#xL, scalar-subquery#x [] AS max_t2h#x] |
| : :- Aggregate [min(t3d#xL) AS min(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 double) 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 [max(t2h#x) AS max(t2h)#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 double) 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] |
| +- Filter (t1a#x = val1c) |
| +- 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 double) 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, count(*) |
| FROM t1 |
| WHERE t1c IN (SELECT (SELECT min(t3c) FROM t3) |
| FROM t2 |
| GROUP BY t2g |
| HAVING count(*) > 1) |
| GROUP BY t1a |
| -- !query analysis |
| Aggregate [t1a#x], [t1a#x, count(1) AS count(1)#xL] |
| +- Filter t1c#x IN (list#x []) |
| : +- Project [scalarsubquery()#x] |
| : +- Filter (count(1)#xL > cast(1 as bigint)) |
| : +- Aggregate [t2g#x], [scalar-subquery#x [] AS scalarsubquery()#x, count(1) AS count(1)#xL] |
| : : +- Aggregate [min(t3c#x) AS min(t3c)#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 double) 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 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 double) 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 double) 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 (SELECT min(t3d) FROM t3) min_t3d, |
| null |
| FROM t1 |
| WHERE t1a = 'val1c' |
| UNION |
| SELECT null, |
| (SELECT max(t2h) FROM t2) max_t2h |
| FROM t1 |
| WHERE t1a = 'val1c' |
| -- !query analysis |
| Distinct |
| +- Union false, false |
| :- Project [min_t3d#xL, cast(NULL#x as timestamp) AS NULL#x] |
| : +- Project [scalar-subquery#x [] AS min_t3d#xL, null AS NULL#x] |
| : : +- Aggregate [min(t3d#xL) AS min(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 double) 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] |
| : +- Filter (t1a#x = val1c) |
| : +- 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 double) 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 [cast(NULL#x as bigint) AS NULL#xL, max_t2h#x] |
| +- Project [null AS NULL#x, scalar-subquery#x [] AS max_t2h#x] |
| : +- Aggregate [max(t2h#x) AS max(t2h)#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 double) 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] |
| +- Filter (t1a#x = val1c) |
| +- 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 double) 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 (SELECT min(t3c) FROM t3) min_t3d |
| FROM t1 |
| WHERE t1a = 'val1a' |
| INTERSECT |
| SELECT (SELECT min(t2c) FROM t2) min_t2d |
| FROM t1 |
| WHERE t1a = 'val1d' |
| -- !query analysis |
| Intersect false |
| :- Project [scalar-subquery#x [] AS min_t3d#x] |
| : : +- Aggregate [min(t3c#x) AS min(t3c)#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 double) 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] |
| : +- Filter (t1a#x = val1a) |
| : +- 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 double) 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 [scalar-subquery#x [] AS min_t2d#x] |
| : +- Aggregate [min(t2c#x) AS min(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 double) 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] |
| +- Filter (t1a#x = val1d) |
| +- 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 double) 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 q1.t1a, q2.t2a, q1.min_t3d, q2.avg_t3d |
| FROM (SELECT t1a, (SELECT min(t3d) FROM t3) min_t3d |
| FROM t1 |
| WHERE t1a IN ('val1e', 'val1c')) q1 |
| FULL OUTER JOIN |
| (SELECT t2a, (SELECT avg(t3d) FROM t3) avg_t3d |
| FROM t2 |
| WHERE t2a IN ('val1c', 'val2a')) q2 |
| ON q1.t1a = q2.t2a |
| AND q1.min_t3d < q2.avg_t3d |
| -- !query analysis |
| Project [t1a#x, t2a#x, min_t3d#xL, avg_t3d#x] |
| +- Join FullOuter, ((t1a#x = t2a#x) AND (cast(min_t3d#xL as double) < avg_t3d#x)) |
| :- SubqueryAlias q1 |
| : +- Project [t1a#x, scalar-subquery#x [] AS min_t3d#xL] |
| : : +- Aggregate [min(t3d#xL) AS min(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 double) 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] |
| : +- Filter t1a#x IN (val1e,val1c) |
| : +- 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 double) 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 q2 |
| +- Project [t2a#x, scalar-subquery#x [] AS avg_t3d#x] |
| : +- Aggregate [avg(t3d#xL) AS avg(t3d)#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 double) 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] |
| +- Filter t2a#x IN (val1c,val2a) |
| +- 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 double) 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 (SELECT min(t3d) FROM t3 WHERE t3.t3a = t1.t1a) min_t3d, |
| (SELECT max(t2h) FROM t2 WHERE t2.t2a = t1.t1a) max_t2h |
| FROM t1 |
| WHERE t1a = 'val1b' |
| -- !query analysis |
| Project [scalar-subquery#x [t1a#x] AS min_t3d#xL, scalar-subquery#x [t1a#x] AS max_t2h#x] |
| : :- Aggregate [min(t3d#xL) AS min(t3d)#xL] |
| : : +- Filter (t3a#x = outer(t1a#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 double) 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 [max(t2h#x) AS max(t2h)#x] |
| : +- Filter (t2a#x = outer(t1a#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 double) 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] |
| +- Filter (t1a#x = val1b) |
| +- 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 double) 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 (SELECT min(t3d) FROM t3 WHERE t3a = t1a) min_t3d |
| FROM t1 |
| WHERE t1a = 'val1b' |
| MINUS |
| SELECT (SELECT min(t3d) FROM t3) abs_min_t3d |
| FROM t1 |
| WHERE t1a = 'val1b' |
| -- !query analysis |
| Except false |
| :- Project [scalar-subquery#x [t1a#x] AS min_t3d#xL] |
| : : +- Aggregate [min(t3d#xL) AS min(t3d)#xL] |
| : : +- Filter (t3a#x = outer(t1a#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 double) 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] |
| : +- Filter (t1a#x = val1b) |
| : +- 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 double) 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 [scalar-subquery#x [] AS abs_min_t3d#xL] |
| : +- Aggregate [min(t3d#xL) AS min(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 double) 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] |
| +- Filter (t1a#x = val1b) |
| +- 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 double) 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 NOT EXISTS (SELECT (SELECT max(t2b) |
| FROM t2 LEFT JOIN t1 |
| ON t2a = t1a |
| WHERE t2c = t3c) dummy |
| FROM t3 |
| WHERE t3b < (SELECT max(t2b) |
| FROM t2 LEFT JOIN t1 |
| ON t2a = t1a |
| WHERE t2c = t3c) |
| AND t3a = t1a) |
| -- !query analysis |
| Project [t1a#x, t1b#x] |
| +- Filter NOT exists#x [t1a#x] |
| : +- Project [scalar-subquery#x [t3c#x] AS dummy#x] |
| : : +- Aggregate [max(t2b#x) AS max(t2b)#x] |
| : : +- Filter (t2c#x = outer(t3c#x)) |
| : : +- Join LeftOuter, (t2a#x = t1a#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 double) 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 double) 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] |
| : +- Filter ((t3b#x < scalar-subquery#x [t3c#x]) AND (t3a#x = outer(t1a#x))) |
| : : +- Aggregate [max(t2b#x) AS max(t2b)#x] |
| : : +- Filter (t2c#x = outer(t3c#x)) |
| : : +- Join LeftOuter, (t2a#x = t1a#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 double) 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 double) 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 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 double) 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 double) 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, |
| (SELECT count(t2d) FROM t2 WHERE t2a = t1a) count_t2, |
| (SELECT count_if(t2d > 0) FROM t2 WHERE t2a = t1a) count_if_t2, |
| (SELECT approx_count_distinct(t2d) FROM t2 WHERE t2a = t1a) approx_count_distinct_t2, |
| (SELECT collect_list(t2d) FROM t2 WHERE t2a = t1a) collect_list_t2, |
| (SELECT sort_array(collect_set(t2d)) FROM t2 WHERE t2a = t1a) collect_set_t2, |
| (SELECT hex(count_min_sketch(t2d, 0.5d, 0.5d, 1)) FROM t2 WHERE t2a = t1a) collect_set_t2 |
| FROM t1 |
| -- !query analysis |
| Project [t1a#x, scalar-subquery#x [t1a#x] AS count_t2#xL, scalar-subquery#x [t1a#x] AS count_if_t2#xL, scalar-subquery#x [t1a#x] AS approx_count_distinct_t2#xL, scalar-subquery#x [t1a#x] AS collect_list_t2#x, scalar-subquery#x [t1a#x] AS collect_set_t2#x, scalar-subquery#x [t1a#x] AS collect_set_t2#x] |
| : :- Aggregate [count(t2d#xL) AS count(t2d)#xL] |
| : : +- Filter (t2a#x = outer(t1a#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 double) 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 [count_if((t2d#xL > cast(0 as bigint))) AS count_if((t2d > 0))#xL] |
| : : +- Filter (t2a#x = outer(t1a#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 double) 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 [approx_count_distinct(t2d#xL, 0.05, 0, 0) AS approx_count_distinct(t2d)#xL] |
| : : +- Filter (t2a#x = outer(t1a#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 double) 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 [collect_list(t2d#xL, 0, 0) AS collect_list(t2d)#x] |
| : : +- Filter (t2a#x = outer(t1a#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 double) 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 [sort_array(collect_set(t2d#xL, 0, 0), true) AS sort_array(collect_set(t2d), true)#x] |
| : : +- Filter (t2a#x = outer(t1a#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 double) 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 [hex(count_min_sketch(t2d#xL, 0.5, 0.5, 1, 0, 0)) AS hex(count_min_sketch(t2d, 0.5, 0.5, 1))#x] |
| : +- Filter (t2a#x = outer(t1a#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 double) 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 double) 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 t1c, (SELECT t1c) FROM t1 |
| -- !query analysis |
| Project [t1c#x, scalar-subquery#x [t1c#x] AS scalarsubquery(t1c)#x] |
| : +- Project [outer(t1c#x)] |
| : +- OneRowRelation |
| +- 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 double) 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 t1c, (SELECT t1c WHERE t1c = 8) FROM t1 |
| -- !query analysis |
| Project [t1c#x, scalar-subquery#x [t1c#x && t1c#x] AS scalarsubquery(t1c, t1c)#x] |
| : +- Project [outer(t1c#x)] |
| : +- Filter (outer(t1c#x) = 8) |
| : +- OneRowRelation |
| +- 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 double) 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 t1c, t1d, (SELECT c + d FROM (SELECT t1c AS c, t1d AS d)) FROM t1 |
| -- !query analysis |
| Project [t1c#x, t1d#xL, scalar-subquery#x [t1c#x && t1d#xL] AS scalarsubquery(t1c, t1d)#xL] |
| : +- Project [(cast(c#x as bigint) + d#xL) AS (c + d)#xL] |
| : +- SubqueryAlias __auto_generated_subquery_name |
| : +- Project [outer(t1c#x) AS c#x, outer(t1d#xL) AS d#xL] |
| : +- OneRowRelation |
| +- 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 double) 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 t1c, (SELECT SUM(c) FROM (SELECT t1c AS c)) FROM t1 |
| -- !query analysis |
| Project [t1c#x, scalar-subquery#x [t1c#x] AS scalarsubquery(t1c)#xL] |
| : +- Aggregate [sum(c#x) AS sum(c)#xL] |
| : +- SubqueryAlias __auto_generated_subquery_name |
| : +- Project [outer(t1c#x) AS c#x] |
| : +- OneRowRelation |
| +- 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 double) 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, (SELECT SUM(t2b) FROM t2 JOIN (SELECT t1a AS a) ON t2a = a) FROM t1 |
| -- !query analysis |
| Project [t1a#x, scalar-subquery#x [t1a#x] AS scalarsubquery(t1a)#xL] |
| : +- Aggregate [sum(t2b#x) AS sum(t2b)#xL] |
| : +- Join Inner, (t2a#x = a#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 double) 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 __auto_generated_subquery_name |
| : +- Project [outer(t1a#x) AS a#x] |
| : +- OneRowRelation |
| +- 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 double) 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 TEMPORARY VIEW t1 AS VALUES (0, 1), (1, 2) t1(c1, c2) |
| -- !query analysis |
| CreateViewCommand `t1`, VALUES (0, 1), (1, 2) t1(c1, c2), false, true, LocalTempView, true |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| CREATE OR REPLACE TEMPORARY VIEW t2 AS VALUES (0, 2), (0, 3) t2(c1, c2) |
| -- !query analysis |
| CreateViewCommand `t2`, VALUES (0, 2), (0, 3) t2(c1, c2), false, true, LocalTempView, true |
| +- SubqueryAlias t2 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| SELECT c1, (WITH t AS (SELECT 1 AS a) SELECT a + c1 FROM t) FROM t1 |
| -- !query analysis |
| Project [c1#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#x] |
| : +- WithCTE |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t |
| : : +- Project [1 AS a#x] |
| : : +- OneRowRelation |
| : +- Project [(a#x + outer(c1#x)) AS (a + outer(t1.c1))#x] |
| : +- SubqueryAlias t |
| : +- CTERelationRef xxxx, true, [a#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| SELECT c1, (WITH t AS (SELECT * FROM t2 WHERE c1 = t1.c1) SELECT SUM(c2) FROM t) FROM t1 |
| -- !query analysis |
| Project [c1#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#xL] |
| : +- WithCTE |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t |
| : : +- Project [c1#x, c2#x] |
| : : +- Filter (c1#x = outer(c1#x)) |
| : : +- SubqueryAlias t2 |
| : : +- View (`t2`, [c1#x, c2#x]) |
| : : +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- LocalRelation [c1#x, c2#x] |
| : +- Aggregate [sum(c2#x) AS sum(c2)#xL] |
| : +- SubqueryAlias t |
| : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| SELECT c1, ( |
| WITH t3 AS (SELECT c1 + 1 AS c1, c2 + 1 AS c2 FROM t2), |
| t4 AS (SELECT * FROM t3 WHERE t1.c1 = c1) |
| SELECT SUM(c2) FROM t4 |
| ) FROM t1 |
| -- !query analysis |
| Project [c1#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#xL] |
| : +- WithCTE |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t3 |
| : : +- Project [(c1#x + 1) AS c1#x, (c2#x + 1) AS c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- View (`t2`, [c1#x, c2#x]) |
| : : +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- LocalRelation [c1#x, c2#x] |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t4 |
| : : +- Project [c1#x, c2#x] |
| : : +- Filter (outer(c1#x) = c1#x) |
| : : +- SubqueryAlias t3 |
| : : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| : +- Aggregate [sum(c2#x) AS sum(c2)#xL] |
| : +- SubqueryAlias t4 |
| : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| SELECT c1, ( |
| WITH t AS (SELECT * FROM t2) |
| SELECT SUM(c2) FROM (SELECT c1, c2 FROM t UNION SELECT c2, c1 FROM t) r(c1, c2) |
| WHERE c1 = t1.c1 |
| ) FROM t1 |
| -- !query analysis |
| Project [c1#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#xL] |
| : +- WithCTE |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t |
| : : +- Project [c1#x, c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- View (`t2`, [c1#x, c2#x]) |
| : : +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- LocalRelation [c1#x, c2#x] |
| : +- Aggregate [sum(c2#x) AS sum(c2)#xL] |
| : +- Filter (c1#x = outer(c1#x)) |
| : +- SubqueryAlias r |
| : +- Project [c1#x AS c1#x, c2#x AS c2#x] |
| : +- Distinct |
| : +- Union false, false |
| : :- Project [c1#x, c2#x] |
| : : +- SubqueryAlias t |
| : : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| : +- Project [c2#x, c1#x] |
| : +- SubqueryAlias t |
| : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| WITH v AS (SELECT * FROM t2) |
| SELECT * FROM t1 WHERE c1 > ( |
| WITH t AS (SELECT * FROM t2) |
| SELECT COUNT(*) FROM v WHERE c1 = t1.c1 AND c1 > (SELECT SUM(c2) FROM t WHERE c1 = v.c1) |
| ) |
| -- !query analysis |
| WithCTE |
| :- CTERelationDef xxxx, false |
| : +- SubqueryAlias v |
| : +- Project [c1#x, c2#x] |
| : +- SubqueryAlias t2 |
| : +- View (`t2`, [c1#x, c2#x]) |
| : +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| : +- SubqueryAlias t2 |
| : +- LocalRelation [c1#x, c2#x] |
| +- Project [c1#x, c2#x] |
| +- Filter (cast(c1#x as bigint) > scalar-subquery#x [c1#x]) |
| : +- WithCTE |
| : :- CTERelationDef xxxx, false |
| : : +- SubqueryAlias t |
| : : +- Project [c1#x, c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- View (`t2`, [c1#x, c2#x]) |
| : : +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| : : +- SubqueryAlias t2 |
| : : +- LocalRelation [c1#x, c2#x] |
| : +- Aggregate [count(1) AS count(1)#xL] |
| : +- Filter ((c1#x = outer(c1#x)) AND (cast(c1#x as bigint) > scalar-subquery#x [c1#x])) |
| : : +- Aggregate [sum(c2#x) AS sum(c2)#xL] |
| : : +- Filter (c1#x = outer(c1#x)) |
| : : +- SubqueryAlias t |
| : : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| : +- SubqueryAlias v |
| : +- CTERelationRef xxxx, true, [c1#x, c2#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| WITH t AS (SELECT 1 AS a) |
| SELECT c1, (SELECT a FROM t WHERE a = c1) FROM t1 |
| -- !query analysis |
| WithCTE |
| :- CTERelationDef xxxx, false |
| : +- SubqueryAlias t |
| : +- Project [1 AS a#x] |
| : +- OneRowRelation |
| +- Project [c1#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#x] |
| : +- Project [a#x] |
| : +- Filter (a#x = outer(c1#x)) |
| : +- SubqueryAlias t |
| : +- CTERelationRef xxxx, true, [a#x], false |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(c1#x as int) AS c1#x, cast(c2#x as int) AS c2#x] |
| +- SubqueryAlias t1 |
| +- LocalRelation [c1#x, c2#x] |
| |
| |
| -- !query |
| WITH |
| v1 AS (SELECT c1, c2, rand(0) c3 FROM t1), |
| v2 AS (SELECT c1, c2, rand(0) c4 FROM v1 WHERE c3 IN (SELECT c3 FROM v1)) |
| SELECT c1, ( |
| WITH v3 AS (SELECT c1, c2, rand(0) c5 FROM t2) |
| SELECT COUNT(*) FROM ( |
| SELECT * FROM v2 WHERE c1 > 0 |
| UNION SELECT * FROM v2 WHERE c2 > 0 |
| UNION SELECT * FROM v3 WHERE c2 > 0 |
| ) WHERE c1 = v1.c1 |
| ) FROM v1 |
| -- !query analysis |
| [Analyzer test output redacted due to nondeterminism] |
| |
| |
| -- !query |
| SELECT (SELECT a FROM (SELECT 1 AS a UNION ALL SELECT 2 AS a) t) AS b |
| -- !query analysis |
| Project [scalar-subquery#x [] AS b#x] |
| : +- Project [a#x] |
| : +- SubqueryAlias t |
| : +- Union false, false |
| : :- Project [1 AS a#x] |
| : : +- OneRowRelation |
| : +- Project [2 AS a#x] |
| : +- OneRowRelation |
| +- OneRowRelation |
| |
| |
| -- !query |
| CREATE OR REPLACE TEMP VIEW t1(c1, c2) AS (VALUES (0, 1), (1, 2)) |
| -- !query analysis |
| CreateViewCommand `t1`, [(c1,None), (c2,None)], (VALUES (0, 1), (1, 2)), false, true, LocalTempView, true |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| CREATE OR REPLACE TEMP VIEW t2(c1, c2) AS (VALUES (0, 2), (0, 3)) |
| -- !query analysis |
| CreateViewCommand `t2`, [(c1,None), (c2,None)], (VALUES (0, 2), (0, 3)), false, true, LocalTempView, true |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| CREATE OR REPLACE TEMP VIEW students(id, name, major, year) AS (VALUES |
| (0, 'A', 'CS', 2022), |
| (1, 'B', 'CS', 2022), |
| (2, 'C', 'Math', 2022)) |
| -- !query analysis |
| CreateViewCommand `students`, [(id,None), (name,None), (major,None), (year,None)], (VALUES |
| (0, 'A', 'CS', 2022), |
| (1, 'B', 'CS', 2022), |
| (2, 'C', 'Math', 2022)), false, true, LocalTempView, true |
| +- LocalRelation [col1#x, col2#x, col3#x, col4#x] |
| |
| |
| -- !query |
| CREATE OR REPLACE TEMP VIEW exams(sid, course, curriculum, grade, date) AS (VALUES |
| (0, 'C1', 'CS', 4, 2020), |
| (0, 'C2', 'CS', 3, 2021), |
| (1, 'C1', 'CS', 2, 2020), |
| (1, 'C2', 'CS', 1, 2021)) |
| -- !query analysis |
| CreateViewCommand `exams`, [(sid,None), (course,None), (curriculum,None), (grade,None), (date,None)], (VALUES |
| (0, 'C1', 'CS', 4, 2020), |
| (0, 'C2', 'CS', 3, 2021), |
| (1, 'C1', 'CS', 2, 2020), |
| (1, 'C2', 'CS', 1, 2021)), false, true, LocalTempView, true |
| +- LocalRelation [col1#x, col2#x, col3#x, col4#x, col5#x] |
| |
| |
| -- !query |
| SELECT students.name, exams.course |
| FROM students, exams |
| WHERE students.id = exams.sid |
| AND (students.major = 'CS' OR students.major = 'Games Eng') |
| AND exams.grade >= ( |
| SELECT avg(exams.grade) + 1 |
| FROM exams |
| WHERE students.id = exams.sid |
| OR (exams.curriculum = students.major AND students.year > exams.date)) |
| -- !query analysis |
| Project [name#x, course#x] |
| +- Filter (((id#x = sid#x) AND ((major#x = CS) OR (major#x = Games Eng))) AND (cast(grade#x as double) >= scalar-subquery#x [id#x && major#x && year#x])) |
| : +- Aggregate [(avg(grade#x) + cast(1 as double)) AS (avg(grade) + 1)#x] |
| : +- Filter ((outer(id#x) = sid#x) OR ((curriculum#x = outer(major#x)) AND (outer(year#x) > date#x))) |
| : +- SubqueryAlias exams |
| : +- View (`exams`, [sid#x, course#x, curriculum#x, grade#x, date#x]) |
| : +- Project [cast(col1#x as int) AS sid#x, cast(col2#x as string) AS course#x, cast(col3#x as string) AS curriculum#x, cast(col4#x as int) AS grade#x, cast(col5#x as int) AS date#x] |
| : +- LocalRelation [col1#x, col2#x, col3#x, col4#x, col5#x] |
| +- Join Inner |
| :- SubqueryAlias students |
| : +- View (`students`, [id#x, name#x, major#x, year#x]) |
| : +- Project [cast(col1#x as int) AS id#x, cast(col2#x as string) AS name#x, cast(col3#x as string) AS major#x, cast(col4#x as int) AS year#x] |
| : +- LocalRelation [col1#x, col2#x, col3#x, col4#x] |
| +- SubqueryAlias exams |
| +- View (`exams`, [sid#x, course#x, curriculum#x, grade#x, date#x]) |
| +- Project [cast(col1#x as int) AS sid#x, cast(col2#x as string) AS course#x, cast(col3#x as string) AS curriculum#x, cast(col4#x as int) AS grade#x, cast(col5#x as int) AS date#x] |
| +- LocalRelation [col1#x, col2#x, col3#x, col4#x, col5#x] |
| |
| |
| -- !query |
| SELECT (SELECT min(c2) FROM t2 WHERE t1.c1 > t2.c1) FROM t1 |
| -- !query analysis |
| Project [scalar-subquery#x [c1#x] AS scalarsubquery(c1)#x] |
| : +- Aggregate [min(c2#x) AS min(c2)#x] |
| : +- Filter (outer(c1#x) > c1#x) |
| : +- SubqueryAlias t2 |
| : +- View (`t2`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| SELECT (SELECT min(c2) FROM t2 WHERE t1.c1 >= t2.c1 AND t1.c2 < t2.c2) FROM t1 |
| -- !query analysis |
| Project [scalar-subquery#x [c1#x && c2#x] AS scalarsubquery(c1, c2)#x] |
| : +- Aggregate [min(c2#x) AS min(c2)#x] |
| : +- Filter ((outer(c1#x) >= c1#x) AND (outer(c2#x) < c2#x)) |
| : +- SubqueryAlias t2 |
| : +- View (`t2`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| SELECT (SELECT count(*) FROM t2 WHERE t1.c1 > t2.c1) FROM t1 |
| -- !query analysis |
| Project [scalar-subquery#x [c1#x] AS scalarsubquery(c1)#xL] |
| : +- Aggregate [count(1) AS count(1)#xL] |
| : +- Filter (outer(c1#x) > c1#x) |
| : +- SubqueryAlias t2 |
| : +- View (`t2`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| SELECT c, ( |
| SELECT count(*) |
| FROM (VALUES ('ab'), ('abc'), ('bc')) t2(c) |
| WHERE t1.c = substring(t2.c, 1, 1) |
| ) FROM (VALUES ('a'), ('b')) t1(c) |
| -- !query analysis |
| Project [c#x, scalar-subquery#x [c#x] AS scalarsubquery(c)#xL] |
| : +- Aggregate [count(1) AS count(1)#xL] |
| : +- Filter (outer(c#x) = substring(c#x, 1, 1)) |
| : +- SubqueryAlias t2 |
| : +- Project [col1#x AS c#x] |
| : +- LocalRelation [col1#x] |
| +- SubqueryAlias t1 |
| +- Project [col1#x AS c#x] |
| +- LocalRelation [col1#x] |
| |
| |
| -- !query |
| SELECT c, ( |
| SELECT count(*) |
| FROM (VALUES (0, 6), (1, 5), (2, 4), (3, 3)) t1(a, b) |
| WHERE a + b = c |
| ) FROM (VALUES (6)) t2(c) |
| -- !query analysis |
| Project [c#x, scalar-subquery#x [c#x] AS scalarsubquery(c)#xL] |
| : +- Aggregate [count(1) AS count(1)#xL] |
| : +- Filter ((a#x + b#x) = outer(c#x)) |
| : +- SubqueryAlias t1 |
| : +- Project [col1#x AS a#x, col2#x AS b#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t2 |
| +- Project [col1#x AS c#x] |
| +- LocalRelation [col1#x] |
| |
| |
| -- !query |
| SELECT *, (SELECT count(1) is null FROM t2 WHERE t1.c1 = t2.c1) FROM t1 |
| -- !query analysis |
| Project [c1#x, c2#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#x] |
| : +- Aggregate [isnull(count(1)) AS (count(1) IS NULL)#x] |
| : +- Filter (outer(c1#x) = c1#x) |
| : +- SubqueryAlias t2 |
| : +- View (`t2`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| select (select f from (select false as f, max(c2) from t1 where t1.c1 = t1.c1)) from t2 |
| -- !query analysis |
| Project [scalar-subquery#x [] AS scalarsubquery()#x] |
| : +- Project [f#x] |
| : +- SubqueryAlias __auto_generated_subquery_name |
| : +- Aggregate [false AS f#x, max(c2#x) AS max(c2)#x] |
| : +- Filter (c1#x = c1#x) |
| : +- SubqueryAlias t1 |
| : +- View (`t1`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t2 |
| +- View (`t2`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
| |
| |
| -- !query |
| set spark.sql.optimizer.optimizeOneRowRelationSubquery.alwaysInline=false |
| -- !query analysis |
| SetCommand (spark.sql.optimizer.optimizeOneRowRelationSubquery.alwaysInline,Some(false)) |
| |
| |
| -- !query |
| WITH T AS (SELECT 1 AS a) |
| SELECT (SELECT sum(1) FROM T WHERE a = col OR upper(col)= 'Y') |
| FROM (SELECT null as col) as foo |
| -- !query analysis |
| WithCTE |
| :- CTERelationDef xxxx, false |
| : +- SubqueryAlias T |
| : +- Project [1 AS a#x] |
| : +- OneRowRelation |
| +- Project [scalar-subquery#x [col#x && col#x] AS scalarsubquery(col, col)#xL] |
| : +- Aggregate [sum(1) AS sum(1)#xL] |
| : +- Filter ((a#x = cast(outer(col#x) as int)) OR (upper(cast(outer(col#x) as string)) = Y)) |
| : +- SubqueryAlias T |
| : +- CTERelationRef xxxx, true, [a#x], false |
| +- SubqueryAlias foo |
| +- Project [null AS col#x] |
| +- OneRowRelation |
| |
| |
| -- !query |
| set spark.sql.optimizer.optimizeOneRowRelationSubquery.alwaysInline=true |
| -- !query analysis |
| SetCommand (spark.sql.optimizer.optimizeOneRowRelationSubquery.alwaysInline,Some(true)) |
| |
| |
| -- !query |
| select * from ( |
| select t1.id c1, ( |
| select t2.id c from range (1, 2) t2 |
| where t1.id = t2.id ) c2 |
| from range (1, 3) t1 ) t |
| where t.c2 is not null |
| -- !query analysis |
| Project [c1#xL, c2#xL] |
| +- Filter isnotnull(c2#xL) |
| +- SubqueryAlias t |
| +- Project [id#xL AS c1#xL, scalar-subquery#x [id#xL] AS c2#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 c1, c2, (SELECT count(*) cnt FROM t1 t2 WHERE t1.c1 = t2.c1 HAVING cnt = 0) FROM t1 |
| -- !query analysis |
| Project [c1#x, c2#x, scalar-subquery#x [c1#x] AS scalarsubquery(c1)#xL] |
| : +- Filter (cnt#xL = cast(0 as bigint)) |
| : +- Aggregate [count(1) AS cnt#xL] |
| : +- Filter (outer(c1#x) = c1#x) |
| : +- SubqueryAlias t2 |
| : +- SubqueryAlias t1 |
| : +- View (`t1`, [c1#x, c2#x]) |
| : +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| : +- LocalRelation [col1#x, col2#x] |
| +- SubqueryAlias t1 |
| +- View (`t1`, [c1#x, c2#x]) |
| +- Project [cast(col1#x as int) AS c1#x, cast(col2#x as int) AS c2#x] |
| +- LocalRelation [col1#x, col2#x] |
