diff options
| -rw-r--r-- | src/test/regress/expected/join.out | 33 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 9 |
2 files changed, 24 insertions, 18 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index f59a921cdc6..446959e3c5f 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -6729,30 +6729,33 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code; -- Shuffle self-joined relations. Only in the case of iterative deletion -- attempts explains of these queries will be identical. CREATE UNIQUE INDEX ON emp1((id*id)); -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id; - QUERY PLAN ------------------------------------------------------------------ - Aggregate (cost=43.84..43.85 rows=1 width=8) - -> Seq Scan on emp1 c3 (cost=0.00..38.25 rows=2237 width=0) + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Seq Scan on emp1 c3 Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL)) (3 rows) -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id; - QUERY PLAN ------------------------------------------------------------------ - Aggregate (cost=43.84..43.85 rows=1 width=8) - -> Seq Scan on emp1 c3 (cost=0.00..38.25 rows=2237 width=0) + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Seq Scan on emp1 c3 Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL)) (3 rows) -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id; - QUERY PLAN ------------------------------------------------------------------ - Aggregate (cost=43.84..43.85 rows=1 width=8) - -> Seq Scan on emp1 c3 (cost=0.00..38.25 rows=2237 width=0) + QUERY PLAN +---------------------------------------------------------------- + Aggregate + -> Seq Scan on emp1 c3 Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL)) (3 rows) diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index f8b02601775..66dd03dd88b 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -2563,11 +2563,14 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code; -- Shuffle self-joined relations. Only in the case of iterative deletion -- attempts explains of these queries will be identical. CREATE UNIQUE INDEX ON emp1((id*id)); -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id; -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id; -explain SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 +explain (costs off) +SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3 WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id; -- We can remove the join even if we find the join can't duplicate rows and |