-- Automatically generated by SQLQueryTestSuite
-- Number of queries: 8
-- !query 0
create temporary view t1 as select * from values
("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01: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 0 schema
struct<>
-- !query 0 output
-- !query 1
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 1 schema
struct<>
-- !query 1 output
-- !query 2
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 2 schema
struct<>
-- !query 2 output
-- !query 3
SELECT t1a,
t1b,
t1h
FROM t1
WHERE ( t1a, t1h ) NOT IN (SELECT t2a,
t2h
FROM t2
WHERE t2a = t1a
ORDER BY t2a)
AND t1a = 'val1a'
-- !query 3 schema
struct<t1a:string,t1b:smallint,t1h:timestamp>
-- !query 3 output
val1a 16 2014-06-04 01:02:00.001
val1a 16 2014-07-04 01:01:00
val1a 6 2014-04-04 01:00:00
val1a 6 2014-04-04 01:02:00.001
-- !query 4
SELECT t1a,
t1b,
t1d
FROM t1
WHERE ( t1b, t1d ) IN (SELECT t2b,
t2d
FROM t2
WHERE t2i IN (SELECT t3i
FROM t3
WHERE t2b > t3b))
-- !query 4 schema
struct<t1a:string,t1b:smallint,t1d:bigint>
-- !query 4 output
val1e 10 19
val1e 10 19
-- !query 5
SELECT t1a,
t1b,
t1d
FROM t1
WHERE ( t1b, t1d ) NOT IN (SELECT t2b,
t2d
FROM t2
WHERE t2h IN (SELECT t3h
FROM t3
WHERE t2b > t3b))
AND t1a = 'val1a'
-- !query 5 schema
struct<t1a:string,t1b:smallint,t1d:bigint>
-- !query 5 output
val1a 16 10
val1a 16 21
val1a 6 10
val1a 6 10
-- !query 6
SELECT t2a
FROM (SELECT t2a
FROM t2
WHERE ( t2a, t2b ) IN (SELECT t1a,
t1b
FROM t1)
UNION ALL
SELECT t2a
FROM t2
WHERE ( t2a, t2b ) IN (SELECT t1a,
t1b
FROM t1)
UNION DISTINCT
SELECT t2a
FROM t2
WHERE ( t2a, t2b ) IN (SELECT t3a,
t3b
FROM t3)) AS t4
-- !query 6 schema
struct<t2a:string>
-- !query 6 output
val1b
-- !query 7
WITH cte1 AS
(
SELECT t1a,
t1b
FROM t1
WHERE (
t1b, t1d) IN
(
SELECT t2b,
t2d
FROM t2
WHERE t1c = t2c))
SELECT *
FROM (
SELECT *
FROM cte1
JOIN cte1 cte2
on cte1.t1b = cte2.t1b) s
-- !query 7 schema
struct<t1a:string,t1b:smallint,t1a:string,t1b:smallint>
-- !query 7 output
val1b 8 val1b 8
val1b 8 val1c 8
val1c 8 val1b 8
val1c 8 val1c 8
