subquery/in-subquery/in-with-cte.sql

0001 -- A test suite for in with cte 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 -- outside CTE
0052 -- TC 01.01
0053 WITH cte1
0054      AS (SELECT t1a,
0055                 t1b
0056          FROM   t1
0057          WHERE  t1a = "val1a")
0058 SELECT t1a,
0059        t1b,
0060        t1c,
0061        t1d,
0062        t1h
0063 FROM   t1
0064 WHERE  t1b IN (SELECT cte1.t1b
0065                FROM   cte1
0066                WHERE  cte1.t1b > 0);
0067
0068 -- TC 01.02
0069 WITH cte1 AS
0070 (
0071        SELECT t1a,
0072               t1b
0073        FROM   t1)
0074 SELECT count(distinct(t1a)), t1b, t1c
0075 FROM   t1
0076 WHERE  t1b IN
0077        (
0078               SELECT cte1.t1b
0079               FROM   cte1
0080               WHERE  cte1.t1b > 0
0081               UNION
0082               SELECT cte1.t1b
0083               FROM   cte1
0084               WHERE  cte1.t1b > 5
0085               UNION ALL
0086               SELECT cte1.t1b
0087               FROM   cte1
0088               INTERSECT
0089               SELECT cte1.t1b
0090               FROM   cte1
0091               UNION
0092               SELECT cte1.t1b
0093               FROM   cte1 )
0094 GROUP BY t1a, t1b, t1c
0095 HAVING t1c IS NOT NULL;
0096
0097 -- TC 01.03
0098 WITH cte1 AS
0099 (
0100        SELECT t1a,
0101               t1b,
0102               t1c,
0103               t1d,
0104               t1e
0105        FROM   t1)
0106 SELECT t1a,
0107        t1b,
0108        t1c,
0109        t1h
0110 FROM   t1
0111 WHERE  t1c IN
0112        (
0113               SELECT          cte1.t1c
0114               FROM            cte1
0115               JOIN            cte1 cte2
0116               on              cte1.t1b > cte2.t1b
0117               FULL OUTER JOIN cte1 cte3
0118               ON              cte1.t1c = cte3.t1c
0119               LEFT JOIN       cte1 cte4
0120               ON              cte1.t1d = cte4.t1d
0121               INNER JOIN  cte1 cte5
0122               ON              cte1.t1b < cte5.t1b
0123               LEFT OUTER JOIN  cte1 cte6
0124               ON              cte1.t1d > cte6.t1d);
0125
0126 -- CTE inside and outside
0127 -- TC 01.04
0128 WITH cte1
0129      AS (SELECT t1a,
0130                 t1b
0131          FROM   t1
0132          WHERE  t1b IN (SELECT t2b
0133                         FROM   t2
0134                                RIGHT JOIN t1
0135                                        ON t1c = t2c
0136                                LEFT JOIN t3
0137                                       ON t2d = t3d)
0138                 AND t1a = "val1b")
0139 SELECT *
0140 FROM   (SELECT *
0141         FROM   cte1
0142                JOIN cte1 cte2
0143                  ON cte1.t1b > 5
0144                     AND cte1.t1a = cte2.t1a
0145                FULL OUTER JOIN cte1 cte3
0146                             ON cte1.t1a = cte3.t1a
0147                INNER JOIN cte1 cte4
0148                        ON cte1.t1b = cte4.t1b) s;
0149
0150 -- TC 01.05
0151 WITH cte1 AS
0152 (
0153        SELECT t1a,
0154               t1b,
0155               t1h
0156        FROM   t1
0157        WHERE  t1a IN
0158               (
0159                      SELECT t2a
0160                      FROM   t2
0161                      WHERE  t1b < t2b))
0162 SELECT   Count(DISTINCT t1a),
0163          t1b
0164 FROM     (
0165                     SELECT     cte1.t1a,
0166                                cte1.t1b
0167                     FROM       cte1
0168                     JOIN       cte1 cte2
0169                     on         cte1.t1h >= cte2.t1h) s
0170 WHERE    t1b IN
0171          (
0172                 SELECT t1b
0173                 FROM   t1)
0174 GROUP BY t1b;
0175
0176 -- TC 01.06
0177 WITH cte1 AS
0178 (
0179        SELECT t1a,
0180               t1b,
0181               t1c
0182        FROM   t1
0183        WHERE  t1b IN
0184               (
0185                      SELECT t2b
0186                      FROM   t2 FULL OUTER JOIN T3 on t2a = t3a
0187                      WHERE  t1c = t2c) AND
0188               t1a = "val1b")
0189 SELECT *
0190 FROM            (
0191                        SELECT *
0192                        FROM   cte1
0193                        INNER JOIN   cte1 cte2 ON cte1.t1a = cte2.t1a
0194                        RIGHT OUTER JOIN cte1 cte3  ON cte1.t1b = cte3.t1b
0195                        LEFT OUTER JOIN cte1 cte4 ON cte1.t1c = cte4.t1c
0196                        ) s
0197 ;
0198
0199 -- TC 01.07
0200 WITH cte1
0201      AS (SELECT t1a,
0202                 t1b
0203          FROM   t1
0204          WHERE  t1b IN (SELECT t2b
0205                         FROM   t2
0206                         WHERE  t1c = t2c))
0207 SELECT Count(DISTINCT( s.t1a )),
0208        s.t1b
0209 FROM   (SELECT cte1.t1a,
0210                cte1.t1b
0211         FROM   cte1
0212                RIGHT OUTER JOIN cte1 cte2
0213                              ON cte1.t1a = cte2.t1a) s
0214 GROUP  BY s.t1b;
0215
0216 -- TC 01.08
0217 WITH cte1 AS
0218 (
0219        SELECT t1a,
0220               t1b
0221        FROM   t1
0222        WHERE  t1b IN
0223               (
0224                      SELECT t2b
0225                      FROM   t2
0226                      WHERE  t1c = t2c))
0227 SELECT DISTINCT(s.t1b)
0228 FROM            (
0229                                 SELECT          cte1.t1b
0230                                 FROM            cte1
0231                                 LEFT OUTER JOIN cte1 cte2
0232                                 ON              cte1.t1b = cte2.t1b) s
0233 WHERE           s.t1b IN
0234                 (
0235                        SELECT t1.t1b
0236                        FROM   t1 INNER
0237                        JOIN   cte1
0238                        ON     t1.t1a = cte1.t1a);
0239
0240 -- CTE with NOT IN
0241 -- TC 01.09
0242 WITH cte1
0243      AS (SELECT t1a,
0244                 t1b
0245          FROM   t1
0246          WHERE  t1a = "val1d")
0247 SELECT t1a,
0248        t1b,
0249        t1c,
0250        t1h
0251 FROM   t1
0252 WHERE  t1b NOT IN (SELECT cte1.t1b
0253                    FROM   cte1
0254                    WHERE  cte1.t1b < 0) AND
0255        t1c > 10;
0256
0257 -- TC 01.10
0258 WITH cte1 AS
0259 (
0260        SELECT t1a,
0261               t1b,
0262               t1c,
0263               t1d,
0264               t1h
0265        FROM   t1
0266        WHERE  t1d NOT IN
0267               (
0268                               SELECT          t2d
0269                               FROM            t2
0270                               FULL OUTER JOIN t3 ON t2a = t3a
0271                               JOIN t1 on t1b = t2b))
0272 SELECT   t1a,
0273          t1b,
0274          t1c,
0275          t1d,
0276          t1h
0277 FROM     t1
0278 WHERE    t1b NOT IN
0279          (
0280                     SELECT     cte1.t1b
0281                     FROM       cte1 INNER
0282                     JOIN       cte1 cte2 ON cte1.t1a = cte2.t1a
0283                     RIGHT JOIN cte1 cte3 ON cte1.t1b = cte3.t1b
0284                     JOIN cte1 cte4 ON cte1.t1c = cte4.t1c) AND
0285          t1c IS NOT NULL
0286 ORDER BY t1c DESC;
0287