Github user dilipbiswal commented on a diff in the pull request:
https://github.com/apache/spark/pull/16798#discussion_r99454377
--- Diff:
sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
---
@@ -0,0 +1,407 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 25
+
+
+-- !query 0
+CREATE OR REPLACE TEMPORARY VIEW p AS VALUES (1, 1) AS T(pk, pv)
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+CREATE OR REPLACE TEMPORARY VIEW c AS VALUES (1, 1) AS T(ck, cv)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+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 2 schema
+struct<pk:int,cv:int>
+-- !query 2 output
+1 1
+
+
+-- !query 3
+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 3 schema
+struct<pk:int,cv:int>
+-- !query 3 output
+1 1
+
+
+-- !query 4
+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 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+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 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+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 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+SELECT t1a, t1b
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+-- !query 7 schema
+struct<t1a:string,t1b:smallint>
+-- !query 7 output
+val1b 8
+val1c 8
+val1d NULL
+val1d NULL
+
+
+-- !query 8
+SELECT t1a, t1d, t1f
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+AND t1b > (SELECT min(t3b)
+ FROM t3)
+-- !query 8 schema
+struct<t1a:string,t1d:bigint,t1f:double>
+-- !query 8 output
+val1b 19 25.0
+val1c 19 25.0
+
+
+-- !query 9
+SELECT t1a, t1h
+FROM t1
+WHERE t1c = (SELECT max(t2c)
+ FROM t2)
+OR t1b = (SELECT min(t3b)
+ FROM t3
+ WHERE t3b > 10)
+-- !query 9 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 9 output
+val1b 2014-05-04 01:01:00
+val1c 2014-05-04 01:02:00.001
+val1d 2014-06-04 01:01:00
+val1d 2014-07-04 01:02:00.001
+
+
+-- !query 10
+SELECT t1a, t1b, t2d
+FROM t1 LEFT JOIN t2
+ ON t1a = t2a
+WHERE t1b = (SELECT min(t3b)
+ FROM t3)
+-- !query 10 schema
+struct<t1a:string,t1b:smallint,t2d:bigint>
+-- !query 10 output
+val1a 6 NULL
+val1a 6 NULL
+
+
+-- !query 11
+SELECT t1a, t1b, t1g
+FROM t1
+WHERE t1c + 5 = (SELECT max(t2e)
+ FROM t2)
+-- !query 11 schema
+struct<t1a:string,t1b:smallint,t1g:decimal(2,-2)>
+-- !query 11 output
+val1a 16 2000
+val1a 16 2000
+
+
+-- !query 12
+SELECT t1a, t1h
+FROM t1
+WHERE date(t1h) = (SELECT min(t2i)
+ FROM t2)
+-- !query 12 schema
+struct<t1a:string,t1h:timestamp>
+-- !query 12 output
+val1a 2014-04-04 00:00:00
+val1a 2014-04-04 01:02:00.001
+
+
+-- !query 13
+SELECT t2d, t1a
+FROM t1, t2
+WHERE t1b = t2b
+AND t2c + 1 = (SELECT max(t2c) + 1
+ FROM t2, t1
+ WHERE t2b = t1b)
+-- !query 13 schema
+struct<t2d:bigint,t1a:string>
+-- !query 13 output
+119 val1b
+119 val1c
+19 val1b
+19 val1c
+
+
+-- !query 14
+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 14 schema
+struct<t2a:string,max_t1g:decimal(2,-2)>
+-- !query 14 output
+val1b 2600
+val1c 2600
+val1e 2600
+
+
+-- !query 15
+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 15 schema
+struct<t3b:smallint,t3c:int>
+-- !query 15 output
+8 NULL
+8 NULL
+NULL 16
+NULL 16
+
+
+-- !query 16
+SELECT t1a
+FROM t1
+WHERE t1a < (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 16 schema
+struct<t1a:string>
+-- !query 16 output
+val1a
+val1a
+val1b
+
+
+-- !query 17
+SELECT t1a, t1c
+FROM t1
+WHERE (SELECT max(t2a)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c) IS NULL
+-- !query 17 schema
+struct<t1a:string,t1c:int>
+-- !query 17 output
+val1a 8
+val1a 8
+val1d NULL
+val1e NULL
+val1e NULL
+val1e NULL
+
+
+-- !query 18
+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 18 schema
+struct<t1a:string>
+-- !query 18 output
+val1c
+val1d
+
+
+-- !query 19
+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 19 schema
+struct<count(t1a):bigint>
+-- !query 19 output
+7
+
+
+-- !query 20
+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 20 schema
+struct<t1a:string>
+-- !query 20 output
+val1b
+val1c
+
+
+-- !query 21
+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 21 schema
+struct<t1a:string>
+-- !query 21 output
+val1b
+val1c
+
+
+-- !query 22
+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 22 schema
+struct<t1a:string>
+-- !query 22 output
+val1a
+val1a
+val1b
+val1b
+val1c
+val1c
+val1d
+val1d
+
+
+-- !query 23
+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 23 schema
+struct<t1a:string>
+-- !query 23 output
+val1a
+
+
+-- !query 24
+SELECT t1a
+FROM t1
+GROUP BY t1a, t1c
+HAVING max(t1b) <= (SELECT max(t2b)
+ FROM t2
+ WHERE t2c = t1c
+ GROUP BY t2c)
+-- !query 24 schema
+struct<t1a:string>
+-- !query 24 output
+val1b
+val1c
--- End diff --
I have compared the result set matched with the result from DB2.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at [email protected] or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
