Github user nsyca commented on a diff in the pull request:
https://github.com/apache/spark/pull/16841#discussion_r100076749
--- Diff:
sql/core/src/test/resources/sql-tests/results/subquery/in-subquery/in-having.sql.out
---
@@ -0,0 +1,217 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 12
+
+
+-- !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 t1b IN (SELECT t2b
+ FROM t2
+ GROUP BY t2b
+ HAVING t2b < 10)
+-- !query 3 schema
+struct<t1a:string,t1b:smallint,t1h:timestamp>
+-- !query 3 output
+val1a 6 2014-04-04 01:00:00
+val1a 6 2014-04-04 01:02:00.001
+val1b 8 2014-05-04 01:01:00
+val1c 8 2014-05-04 01:02:00.001
+
+
+-- !query 4
+SELECT t1a,
+ t1b,
+ t1c
+FROM t1
+WHERE t1b IN (SELECT Min(t2b)
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2b
+ HAVING t2b > 1)
+-- !query 4 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 4 output
+val1b 8 16
+
+
+-- !query 5
+SELECT t1a, t1b, t1c
+FROM t1
+WHERE t1b IN (SELECT t2b
+ FROM t2
+ WHERE t1c < t2c)
+GROUP BY t1a, t1b, t1c
+HAVING t1b < 10
+-- !query 5 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 5 output
+val1a 6 8
+
+
+-- !query 6
+SELECT t1a, t1b, t1c
+FROM t1
+WHERE t1b IN (SELECT t2b
+ FROM t2
+ WHERE t1c = t2c)
+GROUP BY t1a, t1b, t1c
+HAVING COUNT (DISTINCT t1b) < 10
+-- !query 6 schema
+struct<t1a:string,t1b:smallint,t1c:int>
+-- !query 6 output
+val1b 8 16
+val1c 8 16
+
+
+-- !query 7
+SELECT Count(DISTINCT( t1a )),
+ t1b
+FROM t1
+WHERE t1c IN (SELECT t2c
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2c
+ HAVING t2c > 10)
+GROUP BY t1b
+HAVING t1b >= 8
+-- !query 7 schema
+struct<count(DISTINCT t1a):bigint,t1b:smallint>
+-- !query 7 output
+2 8
+
+
+-- !query 8
+SELECT t1a,
+ Max(t1b)
+FROM t1
+WHERE t1b > 0
+GROUP BY t1a
+HAVING t1a IN (SELECT t2a
+ FROM t2
+ WHERE t2b IN (SELECT t3b
+ FROM t3
+ WHERE t2c = t3c)
+ )
+-- !query 8 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 8 output
+val1b 8
+
+
+-- !query 9
+SELECT t1a,
+ t1c,
+ Min(t1d)
+FROM t1
+WHERE t1a NOT IN (SELECT t2a
+ FROM t2
+ GROUP BY t2a
+ HAVING t2a > 'val2a')
+GROUP BY t1a, t1c
+HAVING Min(t1d) > t1c
+-- !query 9 schema
+struct<t1a:string,t1c:int,min(t1d):bigint>
+-- !query 9 output
+val1a 8 10
+val1b 16 19
+val1c 16 19
+val1d 16 19
+
+
+-- !query 10
+SELECT t1a,
+ t1b
+FROM t1
+WHERE t1d NOT IN (SELECT t2d
+ FROM t2
+ WHERE t1a = t2a
+ GROUP BY t2c, t2d
+ HAVING t2c > 8)
+GROUP BY t1a, t1b
+HAVING t1b < 10
+-- !query 10 schema
+struct<t1a:string,t1b:smallint>
+-- !query 10 output
+val1a 6
+
+
+-- !query 11
+SELECT t1a,
+ Max(t1b)
+FROM t1
+WHERE t1b > 0
+GROUP BY t1a
+HAVING t1a NOT IN (SELECT t2a
+ FROM t2
+ WHERE t2b > 3)
+-- !query 11 schema
+struct<t1a:string,max(t1b):smallint>
+-- !query 11 output
+val1a 16
+val1d 10
--- End diff --
The results match with the ones 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 infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
