|
||||
0001 -- 0002 -- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group 0003 -- 0004 -- 0005 -- JOIN 0006 -- Test JOIN clauses 0007 -- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/join.sql 0008 -- 0009 -- This test file was converted from postgreSQL/join.sql. 0010 0011 -- Disable BroadcastHashJoin optimization to avoid changing result order when we enable AQE 0012 --SET spark.sql.autoBroadcastJoinThreshold = -1 0013 0014 CREATE OR REPLACE TEMPORARY VIEW INT4_TBL AS SELECT * FROM 0015 (VALUES (0), (123456), (-123456), (2147483647), (-2147483647)) 0016 AS v(f1); 0017 CREATE OR REPLACE TEMPORARY VIEW INT8_TBL AS SELECT * FROM 0018 (VALUES 0019 (123, 456), 0020 (123, 4567890123456789), 0021 (4567890123456789, 123), 0022 (4567890123456789, 4567890123456789), 0023 (4567890123456789, -4567890123456789)) 0024 AS v(q1, q2); 0025 CREATE OR REPLACE TEMPORARY VIEW FLOAT8_TBL AS SELECT * FROM 0026 (VALUES (0.0), (1004.30), (-34.84), 0027 (cast('1.2345678901234e+200' as double)), (cast('1.2345678901234e-200' as double))) 0028 AS v(f1); 0029 CREATE OR REPLACE TEMPORARY VIEW TEXT_TBL AS SELECT * FROM 0030 (VALUES ('doh!'), ('hi de ho neighbor')) 0031 AS v(f1); 0032 CREATE OR REPLACE TEMPORARY VIEW tenk2 AS SELECT * FROM tenk1; 0033 0034 CREATE TABLE J1_TBL ( 0035 i integer, 0036 j integer, 0037 t string 0038 ) USING parquet; 0039 0040 CREATE TABLE J2_TBL ( 0041 i integer, 0042 k integer 0043 ) USING parquet; 0044 0045 0046 INSERT INTO J1_TBL VALUES (1, 4, 'one'); 0047 INSERT INTO J1_TBL VALUES (2, 3, 'two'); 0048 INSERT INTO J1_TBL VALUES (3, 2, 'three'); 0049 INSERT INTO J1_TBL VALUES (4, 1, 'four'); 0050 INSERT INTO J1_TBL VALUES (5, 0, 'five'); 0051 INSERT INTO J1_TBL VALUES (6, 6, 'six'); 0052 INSERT INTO J1_TBL VALUES (7, 7, 'seven'); 0053 INSERT INTO J1_TBL VALUES (8, 8, 'eight'); 0054 INSERT INTO J1_TBL VALUES (0, NULL, 'zero'); 0055 INSERT INTO J1_TBL VALUES (NULL, NULL, 'null'); 0056 INSERT INTO J1_TBL VALUES (NULL, 0, 'zero'); 0057 0058 INSERT INTO J2_TBL VALUES (1, -1); 0059 INSERT INTO J2_TBL VALUES (2, 2); 0060 INSERT INTO J2_TBL VALUES (3, -3); 0061 INSERT INTO J2_TBL VALUES (2, 4); 0062 INSERT INTO J2_TBL VALUES (5, -5); 0063 INSERT INTO J2_TBL VALUES (5, -5); 0064 INSERT INTO J2_TBL VALUES (0, NULL); 0065 INSERT INTO J2_TBL VALUES (NULL, NULL); 0066 INSERT INTO J2_TBL VALUES (NULL, 0); 0067 0068 -- [SPARK-20856] Do not need onerow because it only used for test statement using nested joins 0069 -- useful in some tests below 0070 -- create temp table onerow(); 0071 -- insert into onerow default values; 0072 -- analyze onerow; 0073 0074 0075 -- 0076 -- CORRELATION NAMES 0077 -- Make sure that table/column aliases are supported 0078 -- before diving into more complex join syntax. 0079 -- 0080 0081 SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t) 0082 FROM J1_TBL AS tx; 0083 0084 SELECT udf(udf('')) AS `xxx`, udf(udf(i)), udf(j), udf(t) 0085 FROM J1_TBL tx; 0086 0087 SELECT udf('') AS `xxx`, a, udf(udf(b)), c 0088 FROM J1_TBL AS t1 (a, b, c); 0089 0090 SELECT udf('') AS `xxx`, udf(a), udf(b), udf(udf(c)) 0091 FROM J1_TBL t1 (a, b, c); 0092 0093 SELECT udf('') AS `xxx`, udf(a), b, udf(c), udf(d), e 0094 FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e); 0095 0096 -- [SPARK-28377] Fully support correlation names in the FROM clause 0097 -- SELECT '' AS "xxx", t1.a, t2.e 0098 -- FROM J1_TBL t1 (a, b, c), J2_TBL t2 (d, e) 0099 -- WHERE t1.a = t2.d; 0100 0101 0102 -- 0103 -- CROSS JOIN 0104 -- Qualifications are not allowed on cross joins, 0105 -- which degenerate into a standard unqualified inner join. 0106 -- 0107 0108 SELECT udf('') AS `xxx`, * 0109 FROM J1_TBL CROSS JOIN J2_TBL; 0110 0111 -- ambiguous column 0112 SELECT udf('') AS `xxx`, udf(i) AS i, udf(k), udf(t) AS t 0113 FROM J1_TBL CROSS JOIN J2_TBL; 0114 0115 -- resolve previous ambiguity by specifying the table name 0116 SELECT udf('') AS `xxx`, udf(t1.i) AS i, udf(k), udf(t) 0117 FROM J1_TBL t1 CROSS JOIN J2_TBL t2; 0118 0119 SELECT udf(udf('')) AS `xxx`, udf(udf(ii)) AS ii, udf(udf(tt)) AS tt, udf(udf(kk)) 0120 FROM (J1_TBL CROSS JOIN J2_TBL) 0121 AS tx (ii, jj, tt, ii2, kk); 0122 0123 -- [SPARK-28377] Fully support correlation names in the FROM clause 0124 -- SELECT '' AS `xxx`, tx.ii, tx.jj, tx.kk 0125 -- FROM (J1_TBL t1 (a, b, c) CROSS JOIN J2_TBL t2 (d, e)) 0126 -- AS tx (ii, jj, tt, ii2, kk); 0127 0128 SELECT udf('') AS `xxx`, udf(udf(j1_tbl.i)), udf(j), udf(t), udf(a.i), udf(a.k), udf(b.i), udf(b.k) 0129 FROM J1_TBL CROSS JOIN J2_TBL a CROSS JOIN J2_TBL b; 0130 0131 0132 -- 0133 -- 0134 -- Inner joins (equi-joins) 0135 -- 0136 -- 0137 0138 -- 0139 -- Inner joins (equi-joins) with USING clause 0140 -- The USING syntax changes the shape of the resulting table 0141 -- by including a column in the USING clause only once in the result. 0142 -- 0143 0144 -- Inner equi-join on specified column 0145 SELECT udf('') AS `xxx`, udf(i) AS i, udf(j), udf(t) AS t, udf(k) 0146 FROM J1_TBL INNER JOIN J2_TBL USING (i); 0147 0148 -- Same as above, slightly different syntax 0149 SELECT udf(udf('')) AS `xxx`, udf(i), udf(j) AS j, udf(t), udf(k) AS k 0150 FROM J1_TBL JOIN J2_TBL USING (i); 0151 0152 SELECT udf('') AS `xxx`, * 0153 FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, d) USING (a) 0154 ORDER BY udf(udf(a)), udf(d); 0155 0156 -- [SPARK-28377] Fully support correlation names in the FROM clause 0157 -- SELECT '' AS `xxx`, * 0158 -- FROM J1_TBL t1 (a, b, c) JOIN J2_TBL t2 (a, b) USING (b) 0159 -- ORDER BY b, t1.a; 0160 0161 0162 -- 0163 -- NATURAL JOIN 0164 -- Inner equi-join on all columns with the same name 0165 -- 0166 0167 SELECT udf(udf('')) AS `xxx`, udf(i), udf(j), udf(t), udf(k) 0168 FROM J1_TBL NATURAL JOIN J2_TBL; 0169 0170 SELECT udf('') AS `xxx`, udf(udf(udf(a))) AS a, udf(b), udf(c), udf(d) 0171 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (a, d); 0172 0173 SELECT udf('') AS `xxx`, udf(udf(a)), udf(udf(b)), udf(udf(c)) AS c, udf(udf(udf(d))) AS d 0174 FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a); 0175 0176 -- [SPARK-28377] Fully support correlation names in the FROM clause 0177 -- mismatch number of columns 0178 -- currently, Postgres will fill in with underlying names 0179 -- SELECT '' AS `xxx`, * 0180 -- FROM J1_TBL t1 (a, b) NATURAL JOIN J2_TBL t2 (a); 0181 0182 0183 -- 0184 -- Inner joins (equi-joins) 0185 -- 0186 0187 SELECT udf('') AS `xxx`, udf(J1_TBL.i), udf(udf(J1_TBL.j)), udf(J1_TBL.t), udf(J2_TBL.i), udf(J2_TBL.k) 0188 FROM J1_TBL JOIN J2_TBL ON (udf(J1_TBL.i) = J2_TBL.i); 0189 0190 SELECT udf('') AS `xxx`, udf(udf(J1_TBL.i)), udf(udf(J1_TBL.j)), udf(udf(J1_TBL.t)), J2_TBL.i, J2_TBL.k 0191 FROM J1_TBL JOIN J2_TBL ON (J1_TBL.i = udf(J2_TBL.k)); 0192 0193 0194 -- 0195 -- Non-equi-joins 0196 -- 0197 0198 SELECT udf('') AS `xxx`, udf(J1_TBL.i), udf(J1_TBL.j), udf(J1_TBL.t), udf(J2_TBL.i), udf(J2_TBL.k) 0199 FROM J1_TBL JOIN J2_TBL ON (udf(J1_TBL.i) <= udf(udf(J2_TBL.k))); 0200 0201 0202 -- 0203 -- Outer joins 0204 -- Note that OUTER is a noise word 0205 -- 0206 0207 SELECT udf(udf('')) AS `xxx`, udf(i), udf(j), udf(t), udf(k) 0208 FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i) 0209 ORDER BY udf(udf(i)), udf(k), udf(t); 0210 0211 SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(k) 0212 FROM J1_TBL LEFT JOIN J2_TBL USING (i) 0213 ORDER BY udf(i), udf(udf(k)), udf(t); 0214 0215 SELECT udf('') AS `xxx`, udf(udf(i)), udf(j), udf(t), udf(k) 0216 FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); 0217 0218 SELECT udf('') AS `xxx`, udf(i), udf(udf(j)), udf(t), udf(k) 0219 FROM J1_TBL RIGHT JOIN J2_TBL USING (i); 0220 0221 SELECT udf('') AS `xxx`, udf(i), udf(j), udf(udf(t)), udf(k) 0222 FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i) 0223 ORDER BY udf(udf(i)), udf(k), udf(t); 0224 0225 SELECT udf('') AS `xxx`, udf(i), udf(j), t, udf(udf(k)) 0226 FROM J1_TBL FULL JOIN J2_TBL USING (i) 0227 ORDER BY udf(udf(i)), udf(k), udf(udf(t)); 0228 0229 SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(udf(k)) 0230 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (udf(k) = 1); 0231 0232 SELECT udf('') AS `xxx`, udf(i), udf(j), udf(t), udf(k) 0233 FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (udf(udf(i)) = udf(1)); 0234 0235 -- 0236 -- semijoin selectivity for <> 0237 -- 0238 -- explain (costs off) 0239 -- select * from int4_tbl i4, tenk1 a 0240 -- where exists(select * from tenk1 b 0241 -- where a.twothousand = b.twothousand and a.fivethous <> b.fivethous) 0242 -- and i4.f1 = a.tenthous; 0243 0244 0245 -- 0246 -- More complicated constructs 0247 -- 0248 0249 -- 0250 -- Multiway full join 0251 -- 0252 0253 CREATE TABLE t1 (name STRING, n INTEGER) USING parquet; 0254 CREATE TABLE t2 (name STRING, n INTEGER) USING parquet; 0255 CREATE TABLE t3 (name STRING, n INTEGER) USING parquet; 0256 0257 INSERT INTO t1 VALUES ( 'bb', 11 ); 0258 INSERT INTO t2 VALUES ( 'bb', 12 ); 0259 INSERT INTO t2 VALUES ( 'cc', 22 ); 0260 INSERT INTO t2 VALUES ( 'ee', 42 ); 0261 INSERT INTO t3 VALUES ( 'bb', 13 ); 0262 INSERT INTO t3 VALUES ( 'cc', 23 ); 0263 INSERT INTO t3 VALUES ( 'dd', 33 ); 0264 0265 SELECT * FROM t1 FULL JOIN t2 USING (name) FULL JOIN t3 USING (name); 0266 0267 -- 0268 -- Test interactions of join syntax and subqueries 0269 -- 0270 0271 -- Basic cases (we expect planner to pull up the subquery here) 0272 SELECT * FROM 0273 (SELECT udf(name) as name, t2.n FROM t2) as s2 0274 INNER JOIN 0275 (SELECT udf(udf(name)) as name, t3.n FROM t3) s3 0276 USING (name); 0277 0278 SELECT * FROM 0279 (SELECT udf(udf(name)) as name, t2.n FROM t2) as s2 0280 LEFT JOIN 0281 (SELECT udf(name) as name, t3.n FROM t3) s3 0282 USING (name); 0283 0284 SELECT udf(name), udf(udf(s2.n)), udf(s3.n) FROM 0285 (SELECT * FROM t2) as s2 0286 FULL JOIN 0287 (SELECT * FROM t3) s3 0288 USING (name); 0289 0290 -- Cases with non-nullable expressions in subquery results; 0291 -- make sure these go to null as expected 0292 SELECT * FROM 0293 (SELECT udf(udf(name)) as name, udf(n) as s2_n, udf(2) as s2_2 FROM t2) as s2 0294 NATURAL INNER JOIN 0295 (SELECT udf(name) as name, udf(udf(n)) as s3_n, udf(3) as s3_2 FROM t3) s3; 0296 0297 SELECT * FROM 0298 (SELECT udf(name) as name, udf(udf(n)) as s2_n, 2 as s2_2 FROM t2) as s2 0299 NATURAL LEFT JOIN 0300 (SELECT udf(udf(name)) as name, udf(n) as s3_n, 3 as s3_2 FROM t3) s3; 0301 0302 SELECT * FROM 0303 (SELECT udf(name) as name, udf(n) as s2_n, 2 as s2_2 FROM t2) as s2 0304 NATURAL FULL JOIN 0305 (SELECT udf(udf(name)) as name, udf(udf(n)) as s3_n, 3 as s3_2 FROM t3) s3; 0306 0307 SELECT * FROM 0308 (SELECT udf(udf(name)) as name, udf(n) as s1_n, 1 as s1_1 FROM t1) as s1 0309 NATURAL INNER JOIN 0310 (SELECT udf(name) as name, udf(n) as s2_n, 2 as s2_2 FROM t2) as s2 0311 NATURAL INNER JOIN 0312 (SELECT udf(udf(udf(name))) as name, udf(n) as s3_n, 3 as s3_2 FROM t3) s3; 0313 0314 SELECT * FROM 0315 (SELECT udf(name) as name, udf(n) as s1_n, udf(udf(1)) as s1_1 FROM t1) as s1 0316 NATURAL FULL JOIN 0317 (SELECT udf(name) as name, udf(udf(n)) as s2_n, udf(2) as s2_2 FROM t2) as s2 0318 NATURAL FULL JOIN 0319 (SELECT udf(udf(name)) as name, udf(n) as s3_n, udf(3) as s3_2 FROM t3) s3; 0320 0321 SELECT name, udf(udf(s1_n)), udf(s2_n), udf(s3_n) FROM 0322 (SELECT name, udf(udf(n)) as s1_n FROM t1) as s1 0323 NATURAL FULL JOIN 0324 (SELECT * FROM 0325 (SELECT name, udf(n) as s2_n FROM t2) as s2 0326 NATURAL FULL JOIN 0327 (SELECT name, udf(udf(n)) as s3_n FROM t3) as s3 0328 ) ss2; 0329 0330 SELECT * FROM 0331 (SELECT name, n as s1_n FROM t1) as s1 0332 NATURAL FULL JOIN 0333 (SELECT * FROM 0334 (SELECT name, udf(udf(n)) as s2_n, 2 as s2_2 FROM t2) as s2 0335 NATURAL FULL JOIN 0336 (SELECT name, udf(n) as s3_n FROM t3) as s3 0337 ) ss2; 0338 0339 -- Constants as join keys can also be problematic 0340 SELECT s1.name, udf(s1_n), s2.name, udf(udf(s2_n)) FROM 0341 (SELECT name, udf(n) as s1_n FROM t1) as s1 0342 FULL JOIN 0343 (SELECT name, 2 as s2_n FROM t2) as s2 0344 ON (udf(udf(s1_n)) = udf(s2_n)); 0345 0346 0347 -- Test for propagation of nullability constraints into sub-joins 0348 0349 create or replace temporary view x as select * from 0350 (values (1,11), (2,22), (3,null), (4,44), (5,null)) 0351 as v(x1, x2); 0352 0353 create or replace temporary view y as select * from 0354 (values (1,111), (2,222), (3,333), (4,null)) 0355 as v(y1, y2); 0356 0357 select udf(udf(x1)), udf(x2) from x; 0358 select udf(y1), udf(udf(y2)) from y; 0359 0360 select * from x left join y on (udf(x1) = udf(udf(y1)) and udf(x2) is not null); 0361 select * from x left join y on (udf(udf(x1)) = udf(y1) and udf(y2) is not null); 0362 0363 select * from (x left join y on (udf(x1) = udf(udf(y1)))) left join x xx(xx1,xx2) 0364 on (udf(udf(x1)) = udf(xx1)); 0365 select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2) 0366 on (udf(x1) = xx1 and udf(x2) is not null); 0367 select * from (x left join y on (x1 = udf(y1))) left join x xx(xx1,xx2) 0368 on (udf(x1) = udf(udf(xx1)) and udf(y2) is not null); 0369 select * from (x left join y on (udf(x1) = y1)) left join x xx(xx1,xx2) 0370 on (udf(udf(x1)) = udf(xx1) and udf(udf(xx2)) is not null); 0371 -- these should NOT give the same answers as above 0372 select * from (x left join y on (udf(udf(x1)) = udf(udf(y1)))) left join x xx(xx1,xx2) 0373 on (udf(x1) = udf(xx1)) where (udf(x2) is not null); 0374 select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2) 0375 on (udf(x1) = xx1) where (udf(y2) is not null); 0376 select * from (x left join y on (udf(x1) = udf(y1))) left join x xx(xx1,xx2) 0377 on (x1 = udf(xx1)) where (xx2 is not null); 0378 0379 -- 0380 -- regression test: check for bug with propagation of implied equality 0381 -- to outside an IN 0382 -- 0383 select udf(udf(count(*))) from tenk1 a where udf(udf(unique1)) in 0384 (select udf(unique1) from tenk1 b join tenk1 c using (unique1) 0385 where udf(udf(b.unique2)) = udf(42)); 0386 0387 -- 0388 -- regression test: check for failure to generate a plan with multiple 0389 -- degenerate IN clauses 0390 -- 0391 select udf(count(*)) from tenk1 x where 0392 udf(x.unique1) in (select udf(a.f1) from int4_tbl a,float8_tbl b where udf(udf(a.f1))=b.f1) and 0393 udf(x.unique1) = 0 and 0394 udf(x.unique1) in (select aa.f1 from int4_tbl aa,float8_tbl bb where aa.f1=udf(udf(bb.f1))); 0395 0396 -- try that with GEQO too 0397 -- begin; 0398 -- set geqo = on; 0399 -- set geqo_threshold = 2; 0400 select udf(udf(count(*))) from tenk1 x where 0401 udf(x.unique1) in (select udf(a.f1) from int4_tbl a,float8_tbl b where udf(udf(a.f1))=b.f1) and 0402 udf(x.unique1) = 0 and 0403 udf(udf(x.unique1)) in (select udf(aa.f1) from int4_tbl aa,float8_tbl bb where udf(aa.f1)=udf(udf(bb.f1))); 0404 -- rollback; 0405 0406 -- Skip this test because table b inherits from table a and we do not support this feature, see inherits.sql 0407 -- 0408 -- regression test: be sure we cope with proven-dummy append rels 0409 -- 0410 -- explain (costs off) 0411 -- select aa, bb, unique1, unique1 0412 -- from tenk1 right join b on aa = unique1 0413 -- where bb < bb and bb is null; 0414 0415 -- select aa, bb, unique1, unique1 0416 -- from tenk1 right join b on aa = unique1 0417 -- where bb < bb and bb is null; 0418 0419 -- 0420 -- regression test: check handling of empty-FROM subquery underneath outer join 0421 -- 0422 -- explain (costs off) 0423 -- select * from int8_tbl i1 left join (int8_tbl i2 join 0424 -- (select 123 as x) ss on i2.q1 = x) on i1.q2 = i2.q2 0425 -- order by 1, 2; 0426 0427 select * from int8_tbl i1 left join (int8_tbl i2 join 0428 (select udf(123) as x) ss on udf(udf(i2.q1)) = udf(x)) on udf(udf(i1.q2)) = udf(udf(i2.q2)) 0429 order by udf(udf(1)), 2; 0430 0431 -- 0432 -- regression test: check a case where join_clause_is_movable_into() gives 0433 -- an imprecise result, causing an assertion failure 0434 -- 0435 select udf(count(*)) 0436 from 0437 (select udf(t3.tenthous) as x1, udf(coalesce(udf(t1.stringu1), udf(t2.stringu1))) as x2 0438 from tenk1 t1 0439 left join tenk1 t2 on udf(t1.unique1) = udf(t2.unique1) 0440 join tenk1 t3 on t1.unique2 = udf(t3.unique2)) ss, 0441 tenk1 t4, 0442 tenk1 t5 0443 where udf(t4.thousand) = udf(t5.unique1) and udf(udf(ss.x1)) = t4.tenthous and udf(ss.x2) = udf(udf(t5.stringu1)); 0444 0445 -- 0446 -- regression test: check a case where we formerly missed including an EC 0447 -- enforcement clause because it was expected to be handled at scan level 0448 -- 0449 -- explain (costs off) 0450 -- select a.f1, b.f1, t.thousand, t.tenthous from 0451 -- tenk1 t, 0452 -- (select sum(f1)+1 as f1 from int4_tbl i4a) a, 0453 -- (select sum(f1) as f1 from int4_tbl i4b) b 0454 -- where b.f1 = t.thousand and a.f1 = b.f1 and (a.f1+b.f1+999) = t.tenthous; 0455 0456 select udf(a.f1), udf(b.f1), udf(t.thousand), udf(t.tenthous) from 0457 tenk1 t, 0458 (select udf(udf(sum(udf(f1))+1)) as f1 from int4_tbl i4a) a, 0459 (select udf(sum(udf(f1))) as f1 from int4_tbl i4b) b 0460 where b.f1 = udf(t.thousand) and udf(a.f1) = udf(b.f1) and udf((udf(a.f1)+udf(b.f1)+999)) = udf(udf(t.tenthous)); 0461 0462 -- 0463 -- check a case where we formerly got confused by conflicting sort orders 0464 -- in redundant merge join path keys 0465 -- 0466 -- explain (costs off) 0467 -- select * from 0468 -- j1_tbl full join 0469 -- (select * from j2_tbl order by j2_tbl.i desc, j2_tbl.k asc) j2_tbl 0470 -- on j1_tbl.i = j2_tbl.i and j1_tbl.i = j2_tbl.k; 0471 0472 select * from 0473 j1_tbl full join 0474 (select * from j2_tbl order by udf(udf(j2_tbl.i)) desc, udf(j2_tbl.k) asc) j2_tbl 0475 on udf(j1_tbl.i) = udf(j2_tbl.i) and udf(j1_tbl.i) = udf(j2_tbl.k); 0476 0477 -- 0478 -- a different check for handling of redundant sort keys in merge joins 0479 -- 0480 -- explain (costs off) 0481 -- select count(*) from 0482 -- (select * from tenk1 x order by x.thousand, x.twothousand, x.fivethous) x 0483 -- left join 0484 -- (select * from tenk1 y order by y.unique2) y 0485 -- on x.thousand = y.unique2 and x.twothousand = y.hundred and x.fivethous = y.unique2; 0486 0487 select udf(count(*)) from 0488 (select * from tenk1 x order by udf(x.thousand), udf(udf(x.twothousand)), x.fivethous) x 0489 left join 0490 (select * from tenk1 y order by udf(y.unique2)) y 0491 on udf(x.thousand) = y.unique2 and x.twothousand = udf(y.hundred) and x.fivethous = y.unique2; 0492 0493 0494 -- 0495 -- Clean up 0496 -- 0497 0498 DROP TABLE t1; 0499 DROP TABLE t2; 0500 DROP TABLE t3; 0501 0502 DROP TABLE J1_TBL; 0503 DROP TABLE J2_TBL; 0504 0505 -- Both DELETE and UPDATE allow the specification of additional tables 0506 -- to "join" against to determine which rows should be modified. 0507 0508 -- CREATE TEMP TABLE t1 (a int, b int); 0509 -- CREATE TEMP TABLE t2 (a int, b int); 0510 -- CREATE TEMP TABLE t3 (x int, y int); 0511 0512 -- INSERT INTO t1 VALUES (5, 10); 0513 -- INSERT INTO t1 VALUES (15, 20); 0514 -- INSERT INTO t1 VALUES (100, 100); 0515 -- INSERT INTO t1 VALUES (200, 1000); 0516 -- INSERT INTO t2 VALUES (200, 2000); 0517 -- INSERT INTO t3 VALUES (5, 20); 0518 -- INSERT INTO t3 VALUES (6, 7); 0519 -- INSERT INTO t3 VALUES (7, 8); 0520 -- INSERT INTO t3 VALUES (500, 100); 0521 0522 -- DELETE FROM t3 USING t1 table1 WHERE t3.x = table1.a; 0523 -- SELECT * FROM t3; 0524 -- DELETE FROM t3 USING t1 JOIN t2 USING (a) WHERE t3.x > t1.a; 0525 -- SELECT * FROM t3; 0526 -- DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; 0527 -- SELECT * FROM t3; 0528 0529 -- Test join against inheritance tree 0530 0531 -- create temp table t2a () inherits (t2); 0532 0533 -- insert into t2a values (200, 2001); 0534 0535 -- select * from t1 left join t2 on (t1.a = t2.a); 0536 0537 -- Test matching of column name with wrong alias 0538 0539 -- select t1.x from t1 join t3 on (t1.a = t3.x); 0540 0541 -- 0542 -- regression test for 8.1 merge right join bug 0543 -- 0544 0545 create or replace temporary view tt1 as select * from 0546 (values (1, 11), (2, NULL)) 0547 as v(tt1_id, joincol); 0548 0549 create or replace temporary view tt2 as select * from 0550 (values (21, 11), (22, 11)) 0551 as v(tt2_id, joincol); 0552 0553 -- set enable_hashjoin to off; 0554 -- set enable_nestloop to off; 0555 0556 -- these should give the same results 0557 0558 select tt1.*, tt2.* from tt1 left join tt2 on udf(udf(tt1.joincol)) = udf(tt2.joincol); 0559 0560 select tt1.*, tt2.* from tt2 right join tt1 on udf(udf(tt1.joincol)) = udf(udf(tt2.joincol)); 0561 0562 -- reset enable_hashjoin; 0563 -- reset enable_nestloop; 0564 0565 -- 0566 -- regression test for bug #13908 (hash join with skew tuples & nbatch increase) 0567 -- 0568 0569 -- set work_mem to '64kB'; 0570 -- set enable_mergejoin to off; 0571 0572 -- explain (costs off) 0573 -- select count(*) from tenk1 a, tenk1 b 0574 -- where a.hundred = b.thousand and (b.fivethous % 10) < 10; 0575 select udf(count(*)) from tenk1 a, tenk1 b 0576 where udf(a.hundred) = b.thousand and udf(udf((b.fivethous % 10)) < 10); 0577 0578 -- reset work_mem; 0579 -- reset enable_mergejoin; 0580 0581 -- 0582 -- regression test for 8.2 bug with improper re-ordering of left joins 0583 -- 0584 0585 DROP TABLE IF EXISTS tt3; 0586 CREATE TABLE tt3(f1 int, f2 string) USING parquet; 0587 INSERT INTO tt3 SELECT x.id, repeat('xyzzy', 100) FROM range(1,10001) x; 0588 -- create index tt3i on tt3(f1); 0589 -- analyze tt3; 0590 0591 DROP TABLE IF EXISTS tt4; 0592 CREATE TABLE tt4(f1 int) USING parquet; 0593 INSERT INTO tt4 VALUES (0),(1),(9999); 0594 -- analyze tt4; 0595 0596 SELECT udf(udf(a.f1)) as f1 0597 FROM tt4 a 0598 LEFT JOIN ( 0599 SELECT b.f1 0600 FROM tt3 b LEFT JOIN tt3 c ON udf(b.f1) = udf(c.f1) 0601 WHERE udf(c.f1) IS NULL 0602 ) AS d ON udf(a.f1) = d.f1 0603 WHERE udf(udf(d.f1)) IS NULL; 0604 0605 -- 0606 -- regression test for proper handling of outer joins within antijoins 0607 -- 0608 0609 -- create temp table tt4x(c1 int, c2 int, c3 int); 0610 0611 -- explain (costs off) 0612 -- select * from tt4x t1 0613 -- where not exists ( 0614 -- select 1 from tt4x t2 0615 -- left join tt4x t3 on t2.c3 = t3.c1 0616 -- left join ( select t5.c1 as c1 0617 -- from tt4x t4 left join tt4x t5 on t4.c2 = t5.c1 0618 -- ) a1 on t3.c2 = a1.c1 0619 -- where t1.c1 = t2.c2 0620 -- ); 0621 0622 -- 0623 -- regression test for problems of the sort depicted in bug #3494 0624 -- 0625 0626 create or replace temporary view tt5 as select * from 0627 (values (1, 10), (1, 11)) 0628 as v(f1, f2); 0629 create or replace temporary view tt6 as select * from 0630 (values (1, 9), (1, 2), (2, 9)) 0631 as v(f1, f2); 0632 0633 select * from tt5,tt6 where udf(tt5.f1) = udf(tt6.f1) and udf(tt5.f1) = udf(udf(tt5.f2) - udf(tt6.f2)); 0634 0635 -- 0636 -- regression test for problems of the sort depicted in bug #3588 0637 -- 0638 0639 create or replace temporary view xx as select * from 0640 (values (1), (2), (3)) 0641 as v(pkxx); 0642 create or replace temporary view yy as select * from 0643 (values (101, 1), (201, 2), (301, NULL)) 0644 as v(pkyy, pkxx); 0645 0646 select udf(udf(yy.pkyy)) as yy_pkyy, udf(yy.pkxx) as yy_pkxx, udf(yya.pkyy) as yya_pkyy, 0647 udf(xxa.pkxx) as xxa_pkxx, udf(xxb.pkxx) as xxb_pkxx 0648 from yy 0649 left join (SELECT * FROM yy where pkyy = 101) as yya ON udf(yy.pkyy) = udf(yya.pkyy) 0650 left join xx xxa on udf(yya.pkxx) = udf(udf(xxa.pkxx)) 0651 left join xx xxb on udf(udf(coalesce (xxa.pkxx, 1))) = udf(xxb.pkxx); 0652 0653 -- 0654 -- regression test for improper pushing of constants across outer-join clauses 0655 -- (as seen in early 8.2.x releases) 0656 -- 0657 0658 create or replace temporary view zt1 as select * from 0659 (values (53)) 0660 as v(f1); 0661 create or replace temporary view zt2 as select * from 0662 (values (53)) 0663 as v(f2); 0664 create or replace temporary view zt3(f3 int) using parquet; 0665 0666 select * from 0667 zt2 left join zt3 on (udf(f2) = udf(udf(f3))) 0668 left join zt1 on (udf(udf(f3)) = udf(f1)) 0669 where udf(f2) = 53; 0670 0671 create temp view zv1 as select *,'dummy' AS junk from zt1; 0672 0673 select * from 0674 zt2 left join zt3 on (f2 = udf(f3)) 0675 left join zv1 on (udf(f3) = f1) 0676 where udf(udf(f2)) = 53; 0677 0678 -- 0679 -- regression test for improper extraction of OR indexqual conditions 0680 -- (as seen in early 8.3.x releases) 0681 -- 0682 0683 select udf(a.unique2), udf(a.ten), udf(b.tenthous), udf(b.unique2), udf(b.hundred) 0684 from tenk1 a left join tenk1 b on a.unique2 = udf(b.tenthous) 0685 where udf(a.unique1) = 42 and 0686 ((udf(b.unique2) is null and udf(a.ten) = 2) or udf(udf(b.hundred)) = udf(udf(3))); 0687 0688 -- 0689 -- test proper positioning of one-time quals in EXISTS (8.4devel bug) 0690 -- 0691 -- prepare foo(bool) as 0692 -- select count(*) from tenk1 a left join tenk1 b 0693 -- on (a.unique2 = b.unique1 and exists 0694 -- (select 1 from tenk1 c where c.thousand = b.unique2 and $1)); 0695 -- execute foo(true); 0696 -- execute foo(false); 0697 0698 -- 0699 -- test for sane behavior with noncanonical merge clauses, per bug #4926 0700 -- 0701 0702 -- begin; 0703 0704 -- set enable_mergejoin = 1; 0705 -- set enable_hashjoin = 0; 0706 -- set enable_nestloop = 0; 0707 0708 create or replace temporary view a (i integer) using parquet; 0709 create or replace temporary view b (x integer, y integer) using parquet; 0710 0711 select * from a left join b on udf(i) = x and i = udf(y) and udf(x) = udf(i); 0712 0713 -- rollback; 0714 0715 -- 0716 -- test handling of merge clauses using record_ops 0717 -- 0718 -- begin; 0719 0720 -- create type mycomptype as (id int, v bigint); 0721 0722 -- create temp table tidv (idv mycomptype); 0723 -- create index on tidv (idv); 0724 0725 -- explain (costs off) 0726 -- select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 0727 0728 -- set enable_mergejoin = 0; 0729 0730 -- explain (costs off) 0731 -- select a.idv, b.idv from tidv a, tidv b where a.idv = b.idv; 0732 0733 -- rollback; 0734 0735 -- 0736 -- test NULL behavior of whole-row Vars, per bug #5025 0737 -- 0738 select udf(t1.q2), udf(count(t2.*)) 0739 from int8_tbl t1 left join int8_tbl t2 on (udf(udf(t1.q2)) = t2.q1) 0740 group by udf(t1.q2) order by 1; 0741 0742 select udf(udf(t1.q2)), udf(count(t2.*)) 0743 from int8_tbl t1 left join (select * from int8_tbl) t2 on (udf(udf(t1.q2)) = udf(t2.q1)) 0744 group by udf(udf(t1.q2)) order by 1; 0745 0746 -- [SPARK-28330] Enhance query limit 0747 -- select t1.q2, count(t2.*) 0748 -- from int8_tbl t1 left join (select * from int8_tbl offset 0) t2 on (t1.q2 = t2.q1) 0749 -- group by t1.q2 order by 1; 0750 0751 select udf(t1.q2) as q2, udf(udf(count(t2.*))) 0752 from int8_tbl t1 left join 0753 (select udf(q1) as q1, case when q2=1 then 1 else q2 end as q2 from int8_tbl) t2 0754 on (udf(t1.q2) = udf(t2.q1)) 0755 group by t1.q2 order by 1; 0756 0757 -- 0758 -- test incorrect failure to NULL pulled-up subexpressions 0759 -- 0760 -- begin; 0761 create or replace temporary view a as select * from 0762 (values ('p'), ('q')) 0763 as v(code); 0764 create or replace temporary view b as select * from 0765 (values ('p', 1), ('p', 2)) 0766 as v(a, num); 0767 create or replace temporary view c as select * from 0768 (values ('A', 'p'), ('B', 'q'), ('C', null)) 0769 as v(name, a); 0770 0771 select udf(c.name), udf(ss.code), udf(ss.b_cnt), udf(ss.const) 0772 from c left join 0773 (select a.code, coalesce(b_grp.cnt, 0) as b_cnt, -1 as const 0774 from a left join 0775 (select udf(count(1)) as cnt, b.a as a from b group by b.a) as b_grp 0776 on udf(a.code) = udf(udf(b_grp.a)) 0777 ) as ss 0778 on (udf(udf(c.a)) = udf(ss.code)) 0779 order by c.name; 0780 0781 -- rollback; 0782 0783 -- 0784 -- test incorrect handling of placeholders that only appear in targetlists, 0785 -- per bug #6154 0786 -- 0787 SELECT * FROM 0788 ( SELECT 1 as key1 ) sub1 0789 LEFT JOIN 0790 ( SELECT sub3.key3, sub4.value2, COALESCE(sub4.value2, 66) as value3 FROM 0791 ( SELECT 1 as key3 ) sub3 0792 LEFT JOIN 0793 ( SELECT udf(sub5.key5) as key5, udf(udf(COALESCE(sub6.value1, 1))) as value2 FROM 0794 ( SELECT 1 as key5 ) sub5 0795 LEFT JOIN 0796 ( SELECT 2 as key6, 42 as value1 ) sub6 0797 ON sub5.key5 = udf(sub6.key6) 0798 ) sub4 0799 ON udf(sub4.key5) = sub3.key3 0800 ) sub2 0801 ON udf(udf(sub1.key1)) = udf(udf(sub2.key3)); 0802 0803 -- test the path using join aliases, too 0804 SELECT * FROM 0805 ( SELECT 1 as key1 ) sub1 0806 LEFT JOIN 0807 ( SELECT udf(sub3.key3) as key3, udf(value2), udf(COALESCE(value2, 66)) as value3 FROM 0808 ( SELECT 1 as key3 ) sub3 0809 LEFT JOIN 0810 ( SELECT sub5.key5, COALESCE(sub6.value1, 1) as value2 FROM 0811 ( SELECT 1 as key5 ) sub5 0812 LEFT JOIN 0813 ( SELECT 2 as key6, 42 as value1 ) sub6 0814 ON udf(udf(sub5.key5)) = sub6.key6 0815 ) sub4 0816 ON sub4.key5 = sub3.key3 0817 ) sub2 0818 ON sub1.key1 = udf(udf(sub2.key3)); 0819 0820 -- 0821 -- test case where a PlaceHolderVar is used as a nestloop parameter 0822 -- 0823 0824 -- EXPLAIN (COSTS OFF) 0825 -- SELECT qq, unique1 0826 -- FROM 0827 -- ( SELECT COALESCE(q1, 0) AS qq FROM int8_tbl a ) AS ss1 0828 -- FULL OUTER JOIN 0829 -- ( SELECT COALESCE(q2, -1) AS qq FROM int8_tbl b ) AS ss2 0830 -- USING (qq) 0831 -- INNER JOIN tenk1 c ON qq = unique2; 0832 0833 SELECT udf(qq), udf(udf(unique1)) 0834 FROM 0835 ( SELECT udf(COALESCE(q1, 0)) AS qq FROM int8_tbl a ) AS ss1 0836 FULL OUTER JOIN 0837 ( SELECT udf(udf(COALESCE(q2, -1))) AS qq FROM int8_tbl b ) AS ss2 0838 USING (qq) 0839 INNER JOIN tenk1 c ON udf(qq) = udf(unique2); 0840 0841 -- 0842 -- nested nestloops can require nested PlaceHolderVars 0843 -- 0844 0845 create or replace temporary view nt1 as select * from 0846 (values(1,true,true), (2,true,false), (3,false,false)) 0847 as v(id, a1, a2); 0848 create or replace temporary view nt2 as select * from 0849 (values(1,1,true,true), (2,2,true,false), (3,3,false,false)) 0850 as v(id, nt1_id, b1, b2); 0851 create or replace temporary view nt3 as select * from 0852 (values(1,1,true), (2,2,false), (3,3,true)) 0853 as v(id, nt2_id, c1); 0854 -- explain (costs off) 0855 -- select nt3.id 0856 -- from nt3 as nt3 0857 -- left join 0858 -- (select nt2.*, (nt2.b1 and ss1.a3) AS b3 0859 -- from nt2 as nt2 0860 -- left join 0861 -- (select nt1.*, (nt1.id is not null) as a3 from nt1) as ss1 0862 -- on ss1.id = nt2.nt1_id 0863 -- ) as ss2 0864 -- on ss2.id = nt3.nt2_id 0865 -- where nt3.id = 1 and ss2.b3; 0866 0867 select udf(nt3.id) 0868 from nt3 as nt3 0869 left join 0870 (select nt2.*, (udf(nt2.b1) and udf(ss1.a3)) AS b3 0871 from nt2 as nt2 0872 left join 0873 (select nt1.*, (udf(nt1.id) is not null) as a3 from nt1) as ss1 0874 on ss1.id = udf(udf(nt2.nt1_id)) 0875 ) as ss2 0876 on udf(ss2.id) = nt3.nt2_id 0877 where udf(nt3.id) = 1 and udf(ss2.b3); 0878 0879 -- [SPARK-28379] Correlated scalar subqueries must be aggregated 0880 -- 0881 -- test case where a PlaceHolderVar is propagated into a subquery 0882 -- 0883 0884 -- explain (costs off) 0885 -- select * from 0886 -- int8_tbl t1 left join 0887 -- (select q1 as x, 42 as y from int8_tbl t2) ss 0888 -- on t1.q2 = ss.x 0889 -- where 0890 -- 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 0891 -- order by 1,2; 0892 0893 -- select * from 0894 -- int8_tbl t1 left join 0895 -- (select q1 as x, 42 as y from int8_tbl t2) ss 0896 -- on t1.q2 = ss.x 0897 -- where 0898 -- 1 = (select 1 from int8_tbl t3 where ss.y is not null limit 1) 0899 -- order by 1,2; 0900 0901 -- 0902 -- test the corner cases FULL JOIN ON TRUE and FULL JOIN ON FALSE 0903 -- 0904 select * from int4_tbl a full join int4_tbl b on true; 0905 select * from int4_tbl a full join int4_tbl b on false; 0906 0907 -- 0908 -- test for ability to use a cartesian join when necessary 0909 -- 0910 0911 -- explain (costs off) 0912 -- select * from 0913 -- tenk1 join int4_tbl on f1 = twothousand, 0914 -- int4(sin(1)) q1, 0915 -- int4(sin(0)) q2 0916 -- where q1 = thousand or q2 = thousand; 0917 0918 -- explain (costs off) 0919 -- select * from 0920 -- tenk1 join int4_tbl on f1 = twothousand, 0921 -- int4(sin(1)) q1, 0922 -- int4(sin(0)) q2 0923 -- where thousand = (q1 + q2); 0924 0925 -- 0926 -- test ability to generate a suitable plan for a star-schema query 0927 -- 0928 0929 -- explain (costs off) 0930 -- select * from 0931 -- tenk1, int8_tbl a, int8_tbl b 0932 -- where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2; 0933 0934 -- 0935 -- test a corner case in which we shouldn't apply the star-schema optimization 0936 -- 0937 0938 -- explain (costs off) 0939 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0940 -- tenk1 t1 0941 -- inner join int4_tbl i1 0942 -- left join (select v1.x2, v2.y1, 11 AS d1 0943 -- from (select 1,0 from onerow) v1(x1,x2) 0944 -- left join (select 3,1 from onerow) v2(y1,y2) 0945 -- on v1.x1 = v2.y2) subq1 0946 -- on (i1.f1 = subq1.x2) 0947 -- on (t1.unique2 = subq1.d1) 0948 -- left join tenk1 t2 0949 -- on (subq1.y1 = t2.unique1) 0950 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 0951 0952 -- [SPARK-20856] support statement using nested joins 0953 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0954 -- tenk1 t1 0955 -- inner join int4_tbl i1 0956 -- left join (select v1.x2, v2.y1, 11 AS d1 0957 -- from (select 1,0 from onerow) v1(x1,x2) 0958 -- left join (select 3,1 from onerow) v2(y1,y2) 0959 -- on v1.x1 = v2.y2) subq1 0960 -- on (i1.f1 = subq1.x2) 0961 -- on (t1.unique2 = subq1.d1) 0962 -- left join tenk1 t2 0963 -- on (subq1.y1 = t2.unique1) 0964 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 0965 0966 -- variant that isn't quite a star-schema case 0967 0968 -- [SPARK-16452] basic INFORMATION_SCHEMA support 0969 -- select ss1.d1 from 0970 -- tenk1 as t1 0971 -- inner join tenk1 as t2 0972 -- on t1.tenthous = t2.ten 0973 -- inner join 0974 -- int8_tbl as i8 0975 -- left join int4_tbl as i4 0976 -- inner join (select 64::information_schema.cardinal_number as d1 0977 -- from tenk1 t3, 0978 -- lateral (select abs(t3.unique1) + random()) ss0(x) 0979 -- where t3.fivethous < 0) as ss1 0980 -- on i4.f1 = ss1.d1 0981 -- on i8.q1 = i4.f1 0982 -- on t1.tenthous = ss1.d1 0983 -- where t1.unique1 < i4.f1; 0984 0985 -- this variant is foldable by the remove-useless-RESULT-RTEs code 0986 0987 -- explain (costs off) 0988 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 0989 -- tenk1 t1 0990 -- inner join int4_tbl i1 0991 -- left join (select v1.x2, v2.y1, 11 AS d1 0992 -- from (values(1,0)) v1(x1,x2) 0993 -- left join (values(3,1)) v2(y1,y2) 0994 -- on v1.x1 = v2.y2) subq1 0995 -- on (i1.f1 = subq1.x2) 0996 -- on (t1.unique2 = subq1.d1) 0997 -- left join tenk1 t2 0998 -- on (subq1.y1 = t2.unique1) 0999 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1000 1001 -- [SPARK-20856] support statement using nested joins 1002 -- select t1.unique2, t1.stringu1, t2.unique1, t2.stringu2 from 1003 -- tenk1 t1 1004 -- inner join int4_tbl i1 1005 -- left join (select v1.x2, v2.y1, 11 AS d1 1006 -- from (values(1,0)) v1(x1,x2) 1007 -- left join (values(3,1)) v2(y1,y2) 1008 -- on v1.x1 = v2.y2) subq1 1009 -- on (i1.f1 = subq1.x2) 1010 -- on (t1.unique2 = subq1.d1) 1011 -- left join tenk1 t2 1012 -- on (subq1.y1 = t2.unique1) 1013 -- where t1.unique2 < 42 and t1.stringu1 > t2.stringu2; 1014 1015 -- 1016 -- test extraction of restriction OR clauses from join OR clause 1017 -- (we used to only do this for indexable clauses) 1018 -- 1019 1020 -- explain (costs off) 1021 -- select * from tenk1 a join tenk1 b on 1022 -- (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.hundred = 4); 1023 -- explain (costs off) 1024 -- select * from tenk1 a join tenk1 b on 1025 -- (a.unique1 = 1 and b.unique1 = 2) or (a.unique2 = 3 and b.ten = 4); 1026 -- explain (costs off) 1027 -- select * from tenk1 a join tenk1 b on 1028 -- (a.unique1 = 1 and b.unique1 = 2) or 1029 -- ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4); 1030 1031 -- 1032 -- test placement of movable quals in a parameterized join tree 1033 -- 1034 1035 -- explain (costs off) 1036 -- select * from tenk1 t1 left join 1037 -- (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1038 -- on t1.hundred = t2.hundred and t1.ten = t3.ten 1039 -- where t1.unique1 = 1; 1040 1041 -- explain (costs off) 1042 -- select * from tenk1 t1 left join 1043 -- (tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2) 1044 -- on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten 1045 -- where t1.unique1 = 1; 1046 1047 -- explain (costs off) 1048 -- select count(*) from 1049 -- tenk1 a join tenk1 b on a.unique1 = b.unique2 1050 -- left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand 1051 -- join int4_tbl on b.thousand = f1; 1052 1053 select udf(count(*)) from 1054 tenk1 a join tenk1 b on udf(a.unique1) = udf(b.unique2) 1055 left join tenk1 c on udf(a.unique2) = udf(b.unique1) and udf(c.thousand) = udf(udf(a.thousand)) 1056 join int4_tbl on udf(b.thousand) = f1; 1057 1058 -- explain (costs off) 1059 -- select b.unique1 from 1060 -- tenk1 a join tenk1 b on a.unique1 = b.unique2 1061 -- left join tenk1 c on b.unique1 = 42 and c.thousand = a.thousand 1062 -- join int4_tbl i1 on b.thousand = f1 1063 -- right join int4_tbl i2 on i2.f1 = b.tenthous 1064 -- order by 1; 1065 1066 select udf(b.unique1) from 1067 tenk1 a join tenk1 b on udf(a.unique1) = udf(b.unique2) 1068 left join tenk1 c on udf(b.unique1) = 42 and c.thousand = udf(a.thousand) 1069 join int4_tbl i1 on udf(b.thousand) = udf(udf(f1)) 1070 right join int4_tbl i2 on udf(udf(i2.f1)) = udf(b.tenthous) 1071 order by udf(1); 1072 1073 -- explain (costs off) 1074 -- select * from 1075 -- ( 1076 -- select unique1, q1, coalesce(unique1, -1) + q1 as fault 1077 -- from int8_tbl left join tenk1 on (q2 = unique2) 1078 -- ) ss 1079 -- where fault = 122 1080 -- order by fault; 1081 1082 select * from 1083 ( 1084 select udf(unique1), udf(q1), udf(udf(coalesce(unique1, -1)) + udf(q1)) as fault 1085 from int8_tbl left join tenk1 on (udf(q2) = udf(unique2)) 1086 ) ss 1087 where udf(fault) = udf(122) 1088 order by udf(fault); 1089 1090 -- explain (costs off) 1091 -- select * from 1092 -- (values (1, array[10,20]), (2, array[20,30])) as v1(v1x,v1ys) 1093 -- left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1094 -- left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1095 1096 -- [SPARK-28382] Array Functions: unnest 1097 -- select * from 1098 -- (values (1, array(10,20)), (2, array(20,30))) as v1(v1x,v1ys) 1099 -- left join (values (1, 10), (2, 20)) as v2(v2x,v2y) on v2x = v1x 1100 -- left join unnest(v1ys) as u1(u1y) on u1y = v2y; 1101 1102 -- 1103 -- test handling of potential equivalence clauses above outer joins 1104 -- 1105 1106 -- explain (costs off) 1107 -- select q1, unique2, thousand, hundred 1108 -- from int8_tbl a left join tenk1 b on q1 = unique2 1109 -- where coalesce(thousand,123) = q1 and q1 = coalesce(hundred,123); 1110 1111 select udf(q1), udf(unique2), udf(thousand), udf(hundred) 1112 from int8_tbl a left join tenk1 b on udf(q1) = udf(unique2) 1113 where udf(coalesce(thousand,123)) = udf(q1) and udf(q1) = udf(udf(coalesce(hundred,123))); 1114 1115 -- explain (costs off) 1116 -- select f1, unique2, case when unique2 is null then f1 else 0 end 1117 -- from int4_tbl a left join tenk1 b on f1 = unique2 1118 -- where (case when unique2 is null then f1 else 0 end) = 0; 1119 1120 select udf(f1), udf(unique2), case when udf(udf(unique2)) is null then udf(f1) else 0 end 1121 from int4_tbl a left join tenk1 b on udf(f1) = udf(udf(unique2)) 1122 where (case when udf(unique2) is null then udf(f1) else 0 end) = 0; 1123 1124 -- 1125 -- another case with equivalence clauses above outer joins (bug #8591) 1126 -- 1127 1128 -- explain (costs off) 1129 -- select a.unique1, b.unique1, c.unique1, coalesce(b.twothousand, a.twothousand) 1130 -- from tenk1 a left join tenk1 b on b.thousand = a.unique1 left join tenk1 c on c.unique2 = coalesce(b.twothousand, a.twothousand) 1131 -- where a.unique2 < 10 and coalesce(b.twothousand, a.twothousand) = 44; 1132 1133 select udf(a.unique1), udf(b.unique1), udf(c.unique1), udf(coalesce(b.twothousand, a.twothousand)) 1134 from tenk1 a left join tenk1 b on udf(b.thousand) = a.unique1 left join tenk1 c on udf(c.unique2) = udf(coalesce(b.twothousand, a.twothousand)) 1135 where a.unique2 < udf(10) and udf(udf(coalesce(b.twothousand, a.twothousand))) = udf(44); 1136 1137 -- 1138 -- check handling of join aliases when flattening multiple levels of subquery 1139 -- 1140 1141 -- explain (verbose, costs off) 1142 -- select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1143 -- (values (0),(1)) foo1(join_key) 1144 -- left join 1145 -- (select join_key, bug_field from 1146 -- (select ss1.join_key, ss1.bug_field from 1147 -- (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1148 -- ) foo2 1149 -- left join 1150 -- (select unique2 as join_key from tenk1 i2) ss2 1151 -- using (join_key) 1152 -- ) foo3 1153 -- using (join_key); 1154 1155 1156 -- [SPARK-28377] Fully support correlation names in the FROM clause 1157 -- select foo1.join_key as foo1_id, foo3.join_key AS foo3_id, bug_field from 1158 -- (values (0),(1)) foo1(join_key) 1159 -- left join 1160 -- (select join_key, bug_field from 1161 -- (select ss1.join_key, ss1.bug_field from 1162 -- (select f1 as join_key, 666 as bug_field from int4_tbl i1) ss1 1163 -- ) foo2 1164 -- left join 1165 -- (select unique2 as join_key from tenk1 i2) ss2 1166 -- using (join_key) 1167 -- ) foo3 1168 -- using (join_key); 1169 1170 -- [SPARK-20856] Support statement using nested joins 1171 -- 1172 -- test successful handling of nested outer joins with degenerate join quals 1173 -- 1174 1175 -- explain (verbose, costs off) 1176 -- select t1.* from 1177 -- text_tbl t1 1178 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1179 -- left join int8_tbl i8 1180 -- left join (select *, null::int as d2 from int8_tbl i8b2) b2 1181 -- on (i8.q1 = b2.q1) 1182 -- on (b2.d2 = b1.q2) 1183 -- on (t1.f1 = b1.d1) 1184 -- left join int4_tbl i4 1185 -- on (i8.q2 = i4.f1); 1186 1187 -- select t1.* from 1188 -- text_tbl t1 1189 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1190 -- left join int8_tbl i8 1191 -- left join (select *, int(null) as d2 from int8_tbl i8b2) b2 1192 -- on (i8.q1 = b2.q1) 1193 -- on (b2.d2 = b1.q2) 1194 -- on (t1.f1 = b1.d1) 1195 -- left join int4_tbl i4 1196 -- on (i8.q2 = i4.f1); 1197 1198 -- explain (verbose, costs off) 1199 -- select t1.* from 1200 -- text_tbl t1 1201 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1202 -- left join int8_tbl i8 1203 -- left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1204 -- on (i8.q1 = b2.q1) 1205 -- on (b2.d2 = b1.q2) 1206 -- on (t1.f1 = b1.d1) 1207 -- left join int4_tbl i4 1208 -- on (i8.q2 = i4.f1); 1209 1210 -- select t1.* from 1211 -- text_tbl t1 1212 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1213 -- left join int8_tbl i8 1214 -- left join (select *, int(null) as d2 from int8_tbl i8b2, int4_tbl i4b2) b2 1215 -- on (i8.q1 = b2.q1) 1216 -- on (b2.d2 = b1.q2) 1217 -- on (t1.f1 = b1.d1) 1218 -- left join int4_tbl i4 1219 -- on (i8.q2 = i4.f1); 1220 1221 -- explain (verbose, costs off) 1222 -- select t1.* from 1223 -- text_tbl t1 1224 -- left join (select *, '***'::text as d1 from int8_tbl i8b1) b1 1225 -- left join int8_tbl i8 1226 -- left join (select *, null::int as d2 from int8_tbl i8b2, int4_tbl i4b2 1227 -- where q1 = f1) b2 1228 -- on (i8.q1 = b2.q1) 1229 -- on (b2.d2 = b1.q2) 1230 -- on (t1.f1 = b1.d1) 1231 -- left join int4_tbl i4 1232 -- on (i8.q2 = i4.f1); 1233 1234 -- select t1.* from 1235 -- text_tbl t1 1236 -- left join (select *, string('***') as d1 from int8_tbl i8b1) b1 1237 -- left join int8_tbl i8 1238 -- left join (select *, int(null) as d2 from int8_tbl i8b2, int4_tbl i4b2 1239 -- where q1 = f1) b2 1240 -- on (i8.q1 = b2.q1) 1241 -- on (b2.d2 = b1.q2) 1242 -- on (t1.f1 = b1.d1) 1243 -- left join int4_tbl i4 1244 -- on (i8.q2 = i4.f1); 1245 1246 -- explain (verbose, costs off) 1247 -- select * from 1248 -- text_tbl t1 1249 -- inner join int8_tbl i8 1250 -- on i8.q2 = 456 1251 -- right join text_tbl t2 1252 -- on t1.f1 = 'doh!' 1253 -- left join int4_tbl i4 1254 -- on i8.q1 = i4.f1; 1255 1256 select * from 1257 text_tbl t1 1258 inner join int8_tbl i8 1259 on udf(i8.q2) = udf(udf(456)) 1260 right join text_tbl t2 1261 on udf(t1.f1) = udf(udf('doh!')) 1262 left join int4_tbl i4 1263 on udf(udf(i8.q1)) = i4.f1; 1264 1265 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1266 -- 1267 -- test for appropriate join order in the presence of lateral references 1268 -- 1269 1270 -- explain (verbose, costs off) 1271 -- select * from 1272 -- text_tbl t1 1273 -- left join int8_tbl i8 1274 -- on i8.q2 = 123, 1275 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1276 -- where t1.f1 = ss.f1; 1277 1278 -- select * from 1279 -- text_tbl t1 1280 -- left join int8_tbl i8 1281 -- on i8.q2 = 123, 1282 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss 1283 -- where t1.f1 = ss.f1; 1284 1285 -- explain (verbose, costs off) 1286 -- select * from 1287 -- text_tbl t1 1288 -- left join int8_tbl i8 1289 -- on i8.q2 = 123, 1290 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1291 -- lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1292 -- where t1.f1 = ss2.f1; 1293 1294 -- select * from 1295 -- text_tbl t1 1296 -- left join int8_tbl i8 1297 -- on i8.q2 = 123, 1298 -- lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1, 1299 -- lateral (select ss1.* from text_tbl t3 limit 1) as ss2 1300 -- where t1.f1 = ss2.f1; 1301 1302 -- explain (verbose, costs off) 1303 -- select 1 from 1304 -- text_tbl as tt1 1305 -- inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1306 -- left join text_tbl as tt3 on (tt3.f1 = 'foo') 1307 -- left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1308 -- lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1309 -- where tt1.f1 = ss1.c0; 1310 1311 -- select 1 from 1312 -- text_tbl as tt1 1313 -- inner join text_tbl as tt2 on (tt1.f1 = 'foo') 1314 -- left join text_tbl as tt3 on (tt3.f1 = 'foo') 1315 -- left join text_tbl as tt4 on (tt3.f1 = tt4.f1), 1316 -- lateral (select tt4.f1 as c0 from text_tbl as tt5 limit 1) as ss1 1317 -- where tt1.f1 = ss1.c0; 1318 1319 -- 1320 -- check a case in which a PlaceHolderVar forces join order 1321 -- 1322 1323 -- explain (verbose, costs off) 1324 -- select ss2.* from 1325 -- int4_tbl i41 1326 -- left join int8_tbl i8 1327 -- join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1328 -- from int4_tbl i42, int4_tbl i43) ss1 1329 -- on i8.q1 = ss1.c2 1330 -- on i41.f1 = ss1.c1, 1331 -- lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1332 -- where ss1.c2 = 0; 1333 1334 -- select ss2.* from 1335 -- int4_tbl i41 1336 -- left join int8_tbl i8 1337 -- join (select i42.f1 as c1, i43.f1 as c2, 42 as c3 1338 -- from int4_tbl i42, int4_tbl i43) ss1 1339 -- on i8.q1 = ss1.c2 1340 -- on i41.f1 = ss1.c1, 1341 -- lateral (select i41.*, i8.*, ss1.* from text_tbl limit 1) ss2 1342 -- where ss1.c2 = 0; 1343 1344 -- 1345 -- test successful handling of full join underneath left join (bug #14105) 1346 -- 1347 1348 -- explain (costs off) 1349 -- select * from 1350 -- (select 1 as id) as xx 1351 -- left join 1352 -- (tenk1 as a1 full join (select 1 as id) as yy on (a1.unique1 = yy.id)) 1353 -- on (xx.id = coalesce(yy.id)); 1354 1355 select * from 1356 (select udf(udf(1)) as id) as xx 1357 left join 1358 (tenk1 as a1 full join (select udf(1) as id) as yy on (udf(a1.unique1) = udf(yy.id))) 1359 on (xx.id = udf(udf(coalesce(yy.id)))); 1360 1361 -- 1362 -- test ability to push constants through outer join clauses 1363 -- 1364 1365 -- explain (costs off) 1366 -- select * from int4_tbl a left join tenk1 b on f1 = unique2 where f1 = 0; 1367 1368 -- explain (costs off) 1369 -- select * from tenk1 a full join tenk1 b using(unique2) where unique2 = 42; 1370 1371 -- 1372 -- test that quals attached to an outer join have correct semantics, 1373 -- specifically that they don't re-use expressions computed below the join; 1374 -- we force a mergejoin so that coalesce(b.q1, 1) appears as a join input 1375 -- 1376 1377 -- set enable_hashjoin to off; 1378 -- set enable_nestloop to off; 1379 1380 -- explain (verbose, costs off) 1381 -- select a.q2, b.q1 1382 -- from int8_tbl a left join int8_tbl b on a.q2 = coalesce(b.q1, 1) 1383 -- where coalesce(b.q1, 1) > 0; 1384 select udf(a.q2), udf(b.q1) 1385 from int8_tbl a left join int8_tbl b on udf(a.q2) = coalesce(b.q1, 1) 1386 where udf(udf(coalesce(b.q1, 1)) > 0); 1387 1388 -- reset enable_hashjoin; 1389 -- reset enable_nestloop; 1390 1391 -- Skip these test because it only test explain 1392 -- 1393 -- test join removal 1394 -- 1395 1396 -- begin; 1397 1398 -- CREATE TEMP TABLE a (id int PRIMARY KEY, b_id int); 1399 -- CREATE TEMP TABLE b (id int PRIMARY KEY, c_id int); 1400 -- CREATE TEMP TABLE c (id int PRIMARY KEY); 1401 -- CREATE TEMP TABLE d (a int, b int); 1402 -- INSERT INTO a VALUES (0, 0), (1, NULL); 1403 -- INSERT INTO b VALUES (0, 0), (1, NULL); 1404 -- INSERT INTO c VALUES (0), (1); 1405 -- INSERT INTO d VALUES (1,3), (2,2), (3,1); 1406 1407 -- all three cases should be optimizable into a simple seqscan 1408 -- explain (costs off) SELECT a.* FROM a LEFT JOIN b ON a.b_id = b.id; 1409 -- explain (costs off) SELECT b.* FROM b LEFT JOIN c ON b.c_id = c.id; 1410 -- explain (costs off) 1411 -- SELECT a.* FROM a LEFT JOIN (b left join c on b.c_id = c.id) 1412 -- ON (a.b_id = b.id); 1413 1414 -- check optimization of outer join within another special join 1415 -- explain (costs off) 1416 -- select id from a where id in ( 1417 -- select b.id from b left join c on b.id = c.id 1418 -- ); 1419 1420 -- check that join removal works for a left join when joining a subquery 1421 -- that is guaranteed to be unique by its GROUP BY clause 1422 -- explain (costs off) 1423 -- select d.* from d left join (select * from b group by b.id, b.c_id) s 1424 -- on d.a = s.id and d.b = s.c_id; 1425 1426 -- similarly, but keying off a DISTINCT clause 1427 -- explain (costs off) 1428 -- select d.* from d left join (select distinct * from b) s 1429 -- on d.a = s.id and d.b = s.c_id; 1430 1431 -- join removal is not possible when the GROUP BY contains a column that is 1432 -- not in the join condition. (Note: as of 9.6, we notice that b.id is a 1433 -- primary key and so drop b.c_id from the GROUP BY of the resulting plan; 1434 -- but this happens too late for join removal in the outer plan level.) 1435 -- explain (costs off) 1436 -- select d.* from d left join (select * from b group by b.id, b.c_id) s 1437 -- on d.a = s.id; 1438 1439 -- similarly, but keying off a DISTINCT clause 1440 -- explain (costs off) 1441 -- select d.* from d left join (select distinct * from b) s 1442 -- on d.a = s.id; 1443 1444 -- check join removal works when uniqueness of the join condition is enforced 1445 -- by a UNION 1446 -- explain (costs off) 1447 -- select d.* from d left join (select id from a union select id from b) s 1448 -- on d.a = s.id; 1449 1450 -- check join removal with a cross-type comparison operator 1451 -- explain (costs off) 1452 -- select i8.* from int8_tbl i8 left join (select f1 from int4_tbl group by f1) i4 1453 -- on i8.q1 = i4.f1; 1454 1455 -- check join removal with lateral references 1456 -- explain (costs off) 1457 -- select 1 from (select a.id FROM a left join b on a.b_id = b.id) q, 1458 -- lateral generate_series(1, q.id) gs(i) where q.id = gs.i; 1459 1460 -- rollback; 1461 1462 create or replace temporary view parent as select * from 1463 (values (1, 10), (2, 20), (3, 30)) 1464 as v(k, pd); 1465 create or replace temporary view child as select * from 1466 (values (1, 100), (4, 400)) 1467 as v(k, cd); 1468 1469 -- this case is optimizable 1470 select p.* from parent p left join child c on (udf(p.k) = udf(c.k)); 1471 -- explain (costs off) 1472 -- select p.* from parent p left join child c on (p.k = c.k); 1473 1474 -- this case is not 1475 select p.*, linked from parent p 1476 left join (select c.*, udf(udf(true)) as linked from child c) as ss 1477 on (udf(p.k) = udf(udf(ss.k))); 1478 -- explain (costs off) 1479 -- select p.*, linked from parent p 1480 -- left join (select c.*, true as linked from child c) as ss 1481 -- on (p.k = ss.k); 1482 1483 -- check for a 9.0rc1 bug: join removal breaks pseudoconstant qual handling 1484 select p.* from 1485 parent p left join child c on (udf(p.k) = c.k) 1486 where p.k = udf(1) and udf(udf(p.k)) = udf(udf(2)); 1487 -- explain (costs off) 1488 -- select p.* from 1489 -- parent p left join child c on (p.k = c.k) 1490 -- where p.k = 1 and p.k = 2; 1491 1492 select p.* from 1493 (parent p left join child c on (udf(p.k) = c.k)) join parent x on p.k = udf(x.k) 1494 where udf(p.k) = udf(1) and udf(udf(p.k)) = udf(udf(2)); 1495 -- explain (costs off) 1496 -- select p.* from 1497 -- (parent p left join child c on (p.k = c.k)) join parent x on p.k = x.k 1498 -- where p.k = 1 and p.k = 2; 1499 1500 -- bug 5255: this is not optimizable by join removal 1501 -- begin; 1502 1503 create or replace temporary view a as select * from 1504 (values (0), (1)) 1505 as v(id); 1506 create or replace temporary view b as select * from 1507 (values (0, 0), (1, NULL)) 1508 as v(id, a_id); 1509 1510 SELECT * FROM b LEFT JOIN a ON (udf(b.a_id) = udf(a.id)) WHERE (udf(udf(a.id)) IS NULL OR udf(a.id) > 0); 1511 SELECT b.* FROM b LEFT JOIN a ON (udf(b.a_id) = udf(a.id)) WHERE (udf(a.id) IS NULL OR udf(udf(a.id)) > 0); 1512 1513 -- rollback; 1514 1515 -- another join removal bug: this is not optimizable, either 1516 -- begin; 1517 1518 create or replace temporary view innertab as select * from 1519 (values (123L, 42L)) 1520 as v(id, dat1); 1521 1522 SELECT * FROM 1523 (SELECT udf(1) AS x) ss1 1524 LEFT JOIN 1525 (SELECT udf(q1), udf(q2), udf(COALESCE(dat1, q1)) AS y 1526 FROM int8_tbl LEFT JOIN innertab ON udf(udf(q2)) = id) ss2 1527 ON true; 1528 1529 -- rollback; 1530 1531 -- another join removal bug: we must clean up correctly when removing a PHV 1532 -- begin; 1533 1534 -- create temp table uniquetbl (f1 text unique); 1535 1536 -- explain (costs off) 1537 -- select t1.* from 1538 -- uniquetbl as t1 1539 -- left join (select *, '***'::text as d1 from uniquetbl) t2 1540 -- on t1.f1 = t2.f1 1541 -- left join uniquetbl t3 1542 -- on t2.d1 = t3.f1; 1543 1544 -- explain (costs off) 1545 -- select t0.* 1546 -- from 1547 -- text_tbl t0 1548 -- left join 1549 -- (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1550 -- t1.stringu2 1551 -- from tenk1 t1 1552 -- join int4_tbl i4 ON i4.f1 = t1.unique2 1553 -- left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1554 -- on t0.f1 = ss.case1 1555 -- where ss.stringu2 !~* ss.case1; 1556 1557 -- [SPARK-27987] Support POSIX Regular Expressions 1558 -- select t0.* 1559 -- from 1560 -- text_tbl t0 1561 -- left join 1562 -- (select case t1.ten when 0 then 'doh!'::text else null::text end as case1, 1563 -- t1.stringu2 1564 -- from tenk1 t1 1565 -- join int4_tbl i4 ON i4.f1 = t1.unique2 1566 -- left join uniquetbl u1 ON u1.f1 = t1.string4) ss 1567 -- on t0.f1 = ss.case1 1568 -- where ss.stringu2 !~* ss.case1; 1569 1570 -- rollback; 1571 1572 -- bug #8444: we've historically allowed duplicate aliases within aliased JOINs 1573 1574 select * from 1575 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on udf(q1) = udf(f1); -- error 1576 select * from 1577 int8_tbl x join (int4_tbl x cross join int4_tbl y) j on udf(q1) = udf(y.f1); -- error 1578 select * from 1579 int8_tbl x join (int4_tbl x cross join int4_tbl y(ff)) j on udf(q1) = udf(udf(f1)); -- ok 1580 1581 -- 1582 -- Test hints given on incorrect column references are useful 1583 -- 1584 1585 select udf(t1.uunique1) from 1586 tenk1 t1 join tenk2 t2 on t1.two = udf(t2.two); -- error, prefer "t1" suggestion 1587 select udf(udf(t2.uunique1)) from 1588 tenk1 t1 join tenk2 t2 on udf(t1.two) = t2.two; -- error, prefer "t2" suggestion 1589 select udf(uunique1) from 1590 tenk1 t1 join tenk2 t2 on udf(t1.two) = udf(t2.two); -- error, suggest both at once 1591 1592 -- Skip this test because it is a PostgreSQL specific case 1593 -- 1594 -- Take care to reference the correct RTE 1595 -- 1596 1597 -- -- select atts.relid::regclass, s.* from pg_stats s join 1598 -- -- pg_attribute a on s.attname = a.attname and s.tablename = 1599 -- -- a.attrelid::regclass::text join (select unnest(indkey) attnum, 1600 -- -- indexrelid from pg_index i) atts on atts.attnum = a.attnum where 1601 -- schemaname != 'pg_catalog'; 1602 1603 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1604 -- 1605 -- Test LATERAL 1606 -- 1607 1608 -- select unique2, x.* 1609 -- from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1610 -- explain (costs off) 1611 -- select unique2, x.* 1612 -- from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; 1613 -- select unique2, x.* 1614 -- from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1615 -- explain (costs off) 1616 -- select unique2, x.* 1617 -- from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; 1618 -- explain (costs off) 1619 -- select unique2, x.* 1620 -- from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss; 1621 -- select unique2, x.* 1622 -- from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1623 -- explain (costs off) 1624 -- select unique2, x.* 1625 -- from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; 1626 1627 -- [SPARK-27877] ANSI SQL: LATERAL derived table(T491) 1628 -- check scoping of lateral versus parent references 1629 -- the first of these should return int8_tbl.q2, the second int8_tbl.q1 1630 -- select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl; 1631 -- select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl; 1632 1633 -- lateral with function in FROM 1634 -- select count(*) from tenk1 a, lateral generate_series(1,two) g; 1635 -- explain (costs off) 1636 -- select count(*) from tenk1 a, lateral generate_series(1,two) g; 1637 -- explain (costs off) 1638 -- select count(*) from tenk1 a cross join lateral generate_series(1,two) g; 1639 -- don't need the explicit LATERAL keyword for functions 1640 -- explain (costs off) 1641 -- select count(*) from tenk1 a, generate_series(1,two) g; 1642 1643 -- lateral with UNION ALL subselect 1644 -- explain (costs off) 1645 -- select * from generate_series(100,200) g, 1646 -- lateral (select * from int8_tbl a where g = q1 union all 1647 -- select * from int8_tbl b where g = q2) ss; 1648 -- select * from generate_series(100,200) g, 1649 -- lateral (select * from int8_tbl a where g = q1 union all 1650 -- select * from int8_tbl b where g = q2) ss; 1651 1652 -- lateral with VALUES 1653 -- explain (costs off) 1654 -- select count(*) from tenk1 a, 1655 -- tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1656 -- select count(*) from tenk1 a, 1657 -- tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; 1658 1659 -- lateral with VALUES, no flattening possible 1660 -- explain (costs off) 1661 -- select count(*) from tenk1 a, 1662 -- tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1663 -- select count(*) from tenk1 a, 1664 -- tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; 1665 1666 -- lateral injecting a strange outer join condition 1667 -- explain (costs off) 1668 -- select * from int8_tbl a, 1669 -- int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1670 -- on x.q2 = ss.z 1671 -- order by a.q1, a.q2, x.q1, x.q2, ss.z; 1672 -- select * from int8_tbl a, 1673 -- int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z) 1674 -- on x.q2 = ss.z 1675 -- order by a.q1, a.q2, x.q1, x.q2, ss.z; 1676 1677 -- lateral reference to a join alias variable 1678 -- select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1679 -- lateral (select x) ss2(y); 1680 -- select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, 1681 -- lateral (values(x)) ss2(y); 1682 -- select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j, 1683 -- lateral (select x) ss2(y); 1684 1685 -- lateral references requiring pullup 1686 -- select * from (values(1)) x(lb), 1687 -- lateral generate_series(lb,4) x4; 1688 -- select * from (select f1/1000000000 from int4_tbl) x(lb), 1689 -- lateral generate_series(lb,4) x4; 1690 -- select * from (values(1)) x(lb), 1691 -- lateral (values(lb)) y(lbcopy); 1692 -- select * from (values(1)) x(lb), 1693 -- lateral (select lb from int4_tbl) y(lbcopy); 1694 -- select * from 1695 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1696 -- lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2); 1697 -- select * from 1698 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1699 -- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1700 -- select x.* from 1701 -- int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, 1702 -- lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2); 1703 -- select v.* from 1704 -- (int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1) 1705 -- left join int4_tbl z on z.f1 = x.q2, 1706 -- lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1707 -- select v.* from 1708 -- (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1709 -- left join int4_tbl z on z.f1 = x.q2, 1710 -- lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy); 1711 -- select v.* from 1712 -- (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1) 1713 -- left join int4_tbl z on z.f1 = x.q2, 1714 -- lateral (select x.q1,y.q1 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy); 1715 1716 -- explain (verbose, costs off) 1717 -- select * from 1718 -- int8_tbl a left join 1719 -- lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1720 -- select * from 1721 -- int8_tbl a left join 1722 -- lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; 1723 -- explain (verbose, costs off) 1724 -- select * from 1725 -- int8_tbl a left join 1726 -- lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1727 -- select * from 1728 -- int8_tbl a left join 1729 -- lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1; 1730 1731 -- lateral can result in join conditions appearing below their 1732 -- real semantic level 1733 -- explain (verbose, costs off) 1734 -- select * from int4_tbl i left join 1735 -- lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1736 -- select * from int4_tbl i left join 1737 -- lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; 1738 -- explain (verbose, costs off) 1739 -- select * from int4_tbl i left join 1740 -- lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1741 -- select * from int4_tbl i left join 1742 -- lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true; 1743 -- explain (verbose, costs off) 1744 -- select * from int4_tbl a, 1745 -- lateral ( 1746 -- select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1747 -- ) ss; 1748 -- select * from int4_tbl a, 1749 -- lateral ( 1750 -- select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2) 1751 -- ) ss; 1752 1753 -- lateral reference in a PlaceHolderVar evaluated at join level 1754 -- explain (verbose, costs off) 1755 -- select * from 1756 -- int8_tbl a left join lateral 1757 -- (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1758 -- int8_tbl b cross join int8_tbl c) ss 1759 -- on a.q2 = ss.bq1; 1760 -- select * from 1761 -- int8_tbl a left join lateral 1762 -- (select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from 1763 -- int8_tbl b cross join int8_tbl c) ss 1764 -- on a.q2 = ss.bq1; 1765 1766 -- case requiring nested PlaceHolderVars 1767 -- explain (verbose, costs off) 1768 -- select * from 1769 -- int8_tbl c left join ( 1770 -- int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1 1771 -- on a.q2 = ss1.q1 1772 -- cross join 1773 -- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1774 -- ) on c.q2 = ss2.q1, 1775 -- lateral (select ss2.y offset 0) ss3; 1776 1777 -- case that breaks the old ph_may_need optimization 1778 -- explain (verbose, costs off) 1779 -- select c.*,a.*,ss1.q1,ss2.q1,ss3.* from 1780 -- int8_tbl c left join ( 1781 -- int8_tbl a left join 1782 -- (select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2 1783 -- where q1 < f1) ss1 1784 -- on a.q2 = ss1.q1 1785 -- cross join 1786 -- lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2 1787 -- ) on c.q2 = ss2.q1, 1788 -- lateral (select * from int4_tbl i where ss2.y > f1) ss3; 1789 1790 -- check processing of postponed quals (bug #9041) 1791 -- explain (verbose, costs off) 1792 -- select * from 1793 -- (select 1 as x offset 0) x cross join (select 2 as y offset 0) y 1794 -- left join lateral ( 1795 -- select * from (select 3 as z offset 0) z where z.z = x.x 1796 -- ) zz on zz.z = y.y; 1797 1798 -- check dummy rels with lateral references (bug #15694) 1799 -- explain (verbose, costs off) 1800 -- select * from int8_tbl i8 left join lateral 1801 -- (select *, i8.q2 from int4_tbl where false) ss on true; 1802 -- explain (verbose, costs off) 1803 -- select * from int8_tbl i8 left join lateral 1804 -- (select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true; 1805 1806 -- check handling of nested appendrels inside LATERAL 1807 -- select * from 1808 -- ((select 2 as v) union all (select 3 as v)) as q1 1809 -- cross join lateral 1810 -- ((select * from 1811 -- ((select 4 as v) union all (select 5 as v)) as q3) 1812 -- union all 1813 -- (select q1.v) 1814 -- ) as q2; 1815 1816 -- check we don't try to do a unique-ified semijoin with LATERAL 1817 -- explain (verbose, costs off) 1818 -- select * from 1819 -- (values (0,9998), (1,1000)) v(id,x), 1820 -- lateral (select f1 from int4_tbl 1821 -- where f1 = any (select unique1 from tenk1 1822 -- where unique2 = v.x offset 0)) ss; 1823 -- select * from 1824 -- (values (0,9998), (1,1000)) v(id,x), 1825 -- lateral (select f1 from int4_tbl 1826 -- where f1 = any (select unique1 from tenk1 1827 -- where unique2 = v.x offset 0)) ss; 1828 1829 -- check proper extParam/allParam handling (this isn't exactly a LATERAL issue, 1830 -- but we can make the test case much more compact with LATERAL) 1831 -- explain (verbose, costs off) 1832 -- select * from (values (0), (1)) v(id), 1833 -- lateral (select * from int8_tbl t1, 1834 -- lateral (select * from 1835 -- (select * from int8_tbl t2 1836 -- where q1 = any (select q2 from int8_tbl t3 1837 -- where q2 = (select greatest(t1.q1,t2.q2)) 1838 -- and (select v.id=0)) offset 0) ss2) ss 1839 -- where t1.q1 = ss.q2) ss0; 1840 1841 -- select * from (values (0), (1)) v(id), 1842 -- lateral (select * from int8_tbl t1, 1843 -- lateral (select * from 1844 -- (select * from int8_tbl t2 1845 -- where q1 = any (select q2 from int8_tbl t3 1846 -- where q2 = (select greatest(t1.q1,t2.q2)) 1847 -- and (select v.id=0)) offset 0) ss2) ss 1848 -- where t1.q1 = ss.q2) ss0; 1849 1850 -- test some error cases where LATERAL should have been used but wasn't 1851 select udf(udf(f1,g)) from int4_tbl a, (select udf(udf(f1)) as g) ss; 1852 select udf(f1,g) from int4_tbl a, (select a.f1 as g) ss; 1853 select udf(udf(f1,g)) from int4_tbl a cross join (select udf(f1) as g) ss; 1854 select udf(f1,g) from int4_tbl a cross join (select udf(udf(a.f1)) as g) ss; 1855 -- SQL:2008 says the left table is in scope but illegal to access here 1856 -- select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true; 1857 -- select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true; 1858 -- check we complain about ambiguous table references 1859 -- select * from 1860 -- int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss); 1861 -- LATERAL can be used to put an aggregate into the FROM clause of its query 1862 -- select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss; 1863 1864 -- check behavior of LATERAL in UPDATE/DELETE 1865 1866 -- create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl; 1867 1868 -- error, can't do this: 1869 -- update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss; 1870 -- update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss; 1871 -- can't do it even with LATERAL: 1872 -- update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss; 1873 -- we might in future allow something like this, but for now it's an error: 1874 -- update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss; 1875 1876 -- also errors: 1877 -- delete from xx1 using (select * from int4_tbl where f1 = x1) ss; 1878 -- delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss; 1879 -- delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss; 1880 1881 -- [SPARK-25411] Implement range partition in Spark 1882 -- 1883 -- test LATERAL reference propagation down a multi-level inheritance hierarchy 1884 -- produced for a multi-level partitioned table hierarchy. 1885 -- 1886 -- create table join_pt1 (a int, b int, c varchar) partition by range(a); 1887 -- create table join_pt1p1 partition of join_pt1 for values from (0) to (100) partition by range(b); 1888 -- create table join_pt1p2 partition of join_pt1 for values from (100) to (200); 1889 -- create table join_pt1p1p1 partition of join_pt1p1 for values from (0) to (100); 1890 -- insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y'); 1891 -- create table join_ut1 (a int, b int, c varchar); 1892 -- insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z'); 1893 -- explain (verbose, costs off) 1894 -- select t1.b, ss.phv from join_ut1 t1 left join lateral 1895 -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 1896 -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 1897 -- on t1.a = ss.t2a order by t1.a; 1898 -- select t1.b, ss.phv from join_ut1 t1 left join lateral 1899 -- (select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv 1900 -- from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss 1901 -- on t1.a = ss.t2a order by t1.a; 1902 -- 1903 -- drop table join_pt1; 1904 -- drop table join_ut1; 1905 -- 1906 -- test that foreign key join estimation performs sanely for outer joins 1907 -- 1908 1909 -- begin; 1910 1911 -- create table fkest (a int, b int, c int unique, primary key(a,b)); 1912 -- create table fkest1 (a int, b int, primary key(a,b)); 1913 1914 -- insert into fkest select x/10, x%10, x from generate_series(1,1000) x; 1915 -- insert into fkest1 select x/10, x%10 from generate_series(1,1000) x; 1916 1917 -- alter table fkest1 1918 -- add constraint fkest1_a_b_fkey foreign key (a,b) references fkest; 1919 1920 -- analyze fkest; 1921 -- analyze fkest1; 1922 1923 -- explain (costs off) 1924 -- select * 1925 -- from fkest f 1926 -- left join fkest1 f1 on f.a = f1.a and f.b = f1.b 1927 -- left join fkest1 f2 on f.a = f2.a and f.b = f2.b 1928 -- left join fkest1 f3 on f.a = f3.a and f.b = f3.b 1929 -- where f.c = 1; 1930 1931 -- rollback; 1932 1933 -- Skip these test because it only test explain 1934 -- 1935 -- test planner's ability to mark joins as unique 1936 -- 1937 1938 -- create table j1 (id int primary key); 1939 -- create table j2 (id int primary key); 1940 -- create table j3 (id int); 1941 1942 -- insert into j1 values(1),(2),(3); 1943 -- insert into j2 values(1),(2),(3); 1944 -- insert into j3 values(1),(1); 1945 1946 -- analyze j1; 1947 -- analyze j2; 1948 -- analyze j3; 1949 1950 -- ensure join is properly marked as unique 1951 -- explain (verbose, costs off) 1952 -- select * from j1 inner join j2 on j1.id = j2.id; 1953 1954 -- ensure join is not unique when not an equi-join 1955 -- explain (verbose, costs off) 1956 -- select * from j1 inner join j2 on j1.id > j2.id; 1957 1958 -- ensure non-unique rel is not chosen as inner 1959 -- explain (verbose, costs off) 1960 -- select * from j1 inner join j3 on j1.id = j3.id; 1961 1962 -- ensure left join is marked as unique 1963 -- explain (verbose, costs off) 1964 -- select * from j1 left join j2 on j1.id = j2.id; 1965 1966 -- ensure right join is marked as unique 1967 -- explain (verbose, costs off) 1968 -- select * from j1 right join j2 on j1.id = j2.id; 1969 1970 -- ensure full join is marked as unique 1971 -- explain (verbose, costs off) 1972 -- select * from j1 full join j2 on j1.id = j2.id; 1973 1974 -- a clauseless (cross) join can't be unique 1975 -- explain (verbose, costs off) 1976 -- select * from j1 cross join j2; 1977 1978 -- ensure a natural join is marked as unique 1979 -- explain (verbose, costs off) 1980 -- select * from j1 natural join j2; 1981 1982 -- ensure a distinct clause allows the inner to become unique 1983 -- explain (verbose, costs off) 1984 -- select * from j1 1985 -- inner join (select distinct id from j3) j3 on j1.id = j3.id; 1986 1987 -- ensure group by clause allows the inner to become unique 1988 -- explain (verbose, costs off) 1989 -- select * from j1 1990 -- inner join (select id from j3 group by id) j3 on j1.id = j3.id; 1991 1992 -- drop table if exists j1; 1993 -- drop table if exists j2; 1994 -- drop table if exists j3; 1995 1996 -- test more complex permutations of unique joins 1997 1998 CREATE TABLE j1 (id1 int, id2 int) USING parquet; 1999 CREATE TABLE j2 (id1 int, id2 int) USING parquet; 2000 -- create table j3 (id1 int, id2 int) using parquet; 2001 2002 INSERT INTO j1 values(1,1),(1,2); 2003 INSERT INTO j2 values(1,1); 2004 -- insert into j3 values(1,1); 2005 2006 -- analyze j1; 2007 -- analyze j2; 2008 -- analyze j3; 2009 2010 -- ensure there's no unique join when not all columns which are part of the 2011 -- unique index are seen in the join clause 2012 -- explain (verbose, costs off) 2013 -- select * from j1 2014 -- inner join j2 on j1.id1 = j2.id1; 2015 2016 -- ensure proper unique detection with multiple join quals 2017 -- explain (verbose, costs off) 2018 -- select * from j1 2019 -- inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2; 2020 2021 -- ensure we don't detect the join to be unique when quals are not part of the 2022 -- join condition 2023 -- explain (verbose, costs off) 2024 -- select * from j1 2025 -- inner join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2026 2027 -- as above, but for left joins. 2028 -- explain (verbose, costs off) 2029 -- select * from j1 2030 -- left join j2 on j1.id1 = j2.id1 where j1.id2 = 1; 2031 2032 -- validate logic in merge joins which skips mark and restore. 2033 -- it should only do this if all quals which were used to detect the unique 2034 -- are present as join quals, and not plain quals. 2035 -- set enable_nestloop to 0; 2036 -- set enable_hashjoin to 0; 2037 -- set enable_sort to 0; 2038 2039 -- create indexes that will be preferred over the PKs to perform the join 2040 -- create index j1_id1_idx on j1 (id1) where id1 % 1000 = 1; 2041 -- create index j2_id1_idx on j2 (id1) where id1 % 1000 = 1; 2042 2043 -- need an additional row in j2, if we want j2_id1_idx to be preferred 2044 INSERT INTO j2 values(1,2); 2045 -- analyze j2; 2046 2047 -- explain (costs off) select * from j1 2048 -- inner join j2 on j1.id1 = j2.id1 and j1.id2 = j2.id2 2049 -- where j1.id1 % 1000 = 1 and j2.id1 % 1000 = 1; 2050 2051 select * from j1 2052 inner join j2 on udf(j1.id1) = udf(j2.id1) and udf(udf(j1.id2)) = udf(j2.id2) 2053 where udf(j1.id1) % 1000 = 1 and udf(udf(j2.id1) % 1000) = 1; 2054 2055 -- reset enable_nestloop; 2056 -- reset enable_hashjoin; 2057 -- reset enable_sort; 2058 2059 drop table j1; 2060 drop table j2; 2061 -- drop table j3; 2062 2063 -- Skip these tests because it only test explain 2064 -- check that semijoin inner is not seen as unique for a portion of the outerrel 2065 -- explain (verbose, costs off) 2066 -- select t1.unique1, t2.hundred 2067 -- from onek t1, tenk1 t2 2068 -- where exists (select 1 from tenk1 t3 2069 -- where t3.thousand = t1.unique1 and t3.tenthous = t2.hundred) 2070 -- and t1.unique1 < 1; 2071 2072 -- ... unless it actually is unique 2073 -- create table j3 as select unique1, tenthous from onek; 2074 -- vacuum analyze j3; 2075 -- create unique index on j3(unique1, tenthous); 2076 2077 -- explain (verbose, costs off) 2078 -- select t1.unique1, t2.hundred 2079 -- from onek t1, tenk1 t2 2080 -- where exists (select 1 from j3 2081 -- where j3.unique1 = t1.unique1 and j3.tenthous = t2.hundred) 2082 -- and t1.unique1 < 1; 2083 2084 -- drop table j3;
[ Source navigation ] | [ Diff markup ] | [ Identifier search ] | [ general search ] |
This page was automatically generated by the 2.1.0 LXR engine. The LXR team |