subquery/in-subquery/not-in-joins.sql

0001 -- A test suite for not-in-joins in parent side, subquery, and both predicate subquery
0002 -- It includes correlated cases.
0003
0004 create temporary view t1 as select * from values
0005   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:00:00.000', date '2014-04-04'),
0006   ("val1b", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0007   ("val1a", 16S, 12, 21L, float(15.0), 20D, 20E2, timestamp '2014-06-04 01:02:00.001', date '2014-06-04'),
0008   ("val1a", 16S, 12, 10L, float(15.0), 20D, 20E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0009   ("val1c", 8S, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:02:00.001', date '2014-05-05'),
0010   ("val1d", null, 16, 22L, float(17.0), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', null),
0011   ("val1d", null, 16, 19L, float(17.0), 25D, 26E2, timestamp '2014-07-04 01:02:00.001', null),
0012   ("val1e", 10S, null, 25L, float(17.0), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-04'),
0013   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-09-04 01:02:00.001', date '2014-09-04'),
0014   ("val1d", 10S, null, 12L, float(17.0), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0015   ("val1a", 6S, 8, 10L, float(15.0), 20D, 20E2, timestamp '2014-04-04 01:02:00.001', date '2014-04-04'),
0016   ("val1e", 10S, null, 19L, float(17.0), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04')
0017   as t1(t1a, t1b, t1c, t1d, t1e, t1f, t1g, t1h, t1i);
0018
0019 create temporary view t2 as select * from values
0020   ("val2a", 6S, 12, 14L, float(15), 20D, 20E2, timestamp '2014-04-04 01:01:00.000', date '2014-04-04'),
0021   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0022   ("val1b", 8S, 16, 119L, float(17), 25D, 26E2, timestamp '2015-05-04 01:01:00.000', date '2015-05-04'),
0023   ("val1c", 12S, 16, 219L, float(17), 25D, 26E2, timestamp '2016-05-04 01:01:00.000', date '2016-05-04'),
0024   ("val1b", null, 16, 319L, float(17), 25D, 26E2, timestamp '2017-05-04 01:01:00.000', null),
0025   ("val2e", 8S, null, 419L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0026   ("val1f", 19S, null, 519L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', date '2014-05-04'),
0027   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-06-04 01:01:00.000', date '2014-06-04'),
0028   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:01:00.000', date '2014-07-04'),
0029   ("val1c", 12S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-08-04 01:01:00.000', date '2014-08-05'),
0030   ("val1e", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:01:00.000', date '2014-09-04'),
0031   ("val1f", 19S, null, 19L, float(17), 25D, 26E2, timestamp '2014-10-04 01:01:00.000', date '2014-10-04'),
0032   ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:01:00.000', null)
0033   as t2(t2a, t2b, t2c, t2d, t2e, t2f, t2g, t2h, t2i);
0034
0035 create temporary view t3 as select * from values
0036   ("val3a", 6S, 12, 110L, float(15), 20D, 20E2, timestamp '2014-04-04 01:02:00.000', date '2014-04-04'),
0037   ("val3a", 6S, 12, 10L, float(15), 20D, 20E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0038   ("val1b", 10S, 12, 219L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0039   ("val1b", 10S, 12, 19L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0040   ("val1b", 8S, 16, 319L, float(17), 25D, 26E2, timestamp '2014-06-04 01:02:00.000', date '2014-06-04'),
0041   ("val1b", 8S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-07-04 01:02:00.000', date '2014-07-04'),
0042   ("val3c", 17S, 16, 519L, float(17), 25D, 26E2, timestamp '2014-08-04 01:02:00.000', date '2014-08-04'),
0043   ("val3c", 17S, 16, 19L, float(17), 25D, 26E2, timestamp '2014-09-04 01:02:00.000', date '2014-09-05'),
0044   ("val1b", null, 16, 419L, float(17), 25D, 26E2, timestamp '2014-10-04 01:02:00.000', null),
0045   ("val1b", null, 16, 19L, float(17), 25D, 26E2, timestamp '2014-11-04 01:02:00.000', null),
0046   ("val3b", 8S, null, 719L, float(17), 25D, 26E2, timestamp '2014-05-04 01:02:00.000', date '2014-05-04'),
0047   ("val3b", 8S, null, 19L, float(17), 25D, 26E2, timestamp '2015-05-04 01:02:00.000', date '2015-05-04')
0048   as t3(t3a, t3b, t3c, t3d, t3e, t3f, t3g, t3h, t3i);
0049
0050 -- correlated IN subquery
0051 -- different not JOIN in parent side
0052 -- TC 01.01
0053 SELECT t1a,
0054        t1b,
0055        t1c,
0056        t3a,
0057        t3b,
0058        t3c
0059 FROM   t1
0060        JOIN t3
0061 WHERE  t1a NOT IN (SELECT t2a
0062                    FROM   t2)
0063        AND t1b = t3b;
0064
0065 -- TC 01.02
0066 SELECT          t1a,
0067                 t1b,
0068                 t1c,
0069                 count(distinct(t3a)),
0070                 t3b,
0071                 t3c
0072 FROM            t1
0073 FULL OUTER JOIN t3 on t1b != t3b
0074 RIGHT JOIN      t2 on t1c = t2c
0075 where           t1a NOT IN
0076                 (
0077                        SELECT t2a
0078                        FROM   t2
0079                        WHERE  t2c NOT IN
0080                               (
0081                                      SELECT t1c
0082                                      FROM   t1
0083                                      WHERE  t1a = t2a))
0084 AND             t1b != t3b
0085 AND             t1d = t2d
0086 GROUP BY        t1a, t1b, t1c, t3a, t3b, t3c
0087 HAVING          count(distinct(t3a)) >= 1
0088 ORDER BY        t1a, t3b;
0089
0090 -- TC 01.03
0091 SELECT t1a,
0092        t1b,
0093        t1c,
0094        t1d,
0095        t1h
0096 FROM   t1
0097 WHERE  t1a NOT IN
0098        (
0099                  SELECT    t2a
0100                  FROM      t2
0101                  LEFT JOIN t3 on t2b = t3b
0102                  WHERE t1d = t2d
0103                   )
0104 AND    t1d NOT IN
0105        (
0106               SELECT t2d
0107               FROM   t2
0108               RIGHT JOIN t1 on t2e = t1e
0109               WHERE t1a = t2a);
0110
0111 -- TC 01.04
0112 SELECT Count(DISTINCT( t1a )),
0113        t1b,
0114        t1c,
0115        t1d
0116 FROM   t1
0117 WHERE  t1a NOT IN (SELECT t2a
0118                    FROM   t2
0119                    JOIN t1
0120                    WHERE  t2b <> t1b)
0121 GROUP  BY t1b,
0122           t1c,
0123           t1d
0124 HAVING t1d NOT IN (SELECT t2d
0125                    FROM   t2
0126                    WHERE  t1d = t2d)
0127 ORDER BY t1b DESC, t1d ASC;
0128
0129 -- TC 01.05
0130 SELECT   COUNT(DISTINCT(t1a)),
0131          t1b,
0132          t1c,
0133          t1d
0134 FROM     t1
0135 WHERE    t1a NOT IN
0136          (
0137                 SELECT t2a
0138                 FROM   t2 INNER
0139                 JOIN   t1 ON t1a = t2a)
0140 GROUP BY t1b,
0141          t1c,
0142          t1d
0143 HAVING   t1b < sum(t1c);
0144
0145 -- TC 01.06
0146 SELECT   COUNT(DISTINCT(t1a)),
0147          t1b,
0148          t1c,
0149          t1d
0150 FROM     t1
0151 WHERE    t1a NOT IN
0152          (
0153                 SELECT t2a
0154                 FROM   t2 INNER
0155                 JOIN   t1
0156                 ON     t1a = t2a)
0157 AND      t1d NOT IN
0158          (
0159                     SELECT     t2d
0160                     FROM       t2
0161                     INNER JOIN t3
0162                     ON         t2b = t3b )
0163 GROUP BY t1b,
0164          t1c,
0165          t1d
0166 HAVING   t1b < sum(t1c);
0167