1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185
|
--
-- Tests for predicate handling
--
--
-- Test that restrictions that are always true are ignored, and that are always
-- false are replaced with constant-FALSE
--
-- Currently we only check for NullTest quals and OR clauses that include
-- NullTest quals. We may extend it in the future.
--
CREATE TABLE pred_tab (a int NOT NULL, b int, c int NOT NULL);
--
-- Test restriction clauses
--
-- Ensure the IS_NOT_NULL qual is ignored when the column is non-nullable
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.a IS NOT NULL;
-- Ensure the IS_NOT_NULL qual is not ignored on a nullable column
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.b IS NOT NULL;
-- Ensure the IS_NULL qual is reduced to constant-FALSE for non-nullable
-- columns
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.a IS NULL;
-- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable
-- columns
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.b IS NULL;
--
-- Tests for OR clauses in restriction clauses
--
-- Ensure the OR clause is ignored when an OR branch is always true
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.a IS NOT NULL OR t.b = 1;
-- Ensure the OR clause is not ignored for NullTests that can't be proven
-- always true
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.b IS NOT NULL OR t.a = 1;
-- Ensure the OR clause is reduced to constant-FALSE when all branches are
-- provably false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL;
-- Ensure the OR clause is not reduced to constant-FALSE when not all branches
-- are provably false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
--
-- Test join clauses
--
-- Ensure the IS_NOT_NULL qual is ignored, since a) it's on a NOT NULL column,
-- and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL;
-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
-- by an outer join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
-- NULL column, and b) its Var is not nullable by any outer joins
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a IS NULL;
-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
-- nullable by an outer join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a = 1
LEFT JOIN pred_tab t3 ON t2.a IS NULL;
--
-- Tests for OR clauses in join clauses
--
-- Ensure the OR clause is ignored when an OR branch is provably always true
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a IS NOT NULL OR t2.b = 1;
-- Ensure the NullTest is not ignored when the column is nullable by an outer
-- join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
FULL JOIN pred_tab t2 ON t1.a = t2.a
LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
-- provably false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON (t1.a IS NULL OR t1.c IS NULL);
-- Ensure the OR clause is not reduced to constant-FALSE when a column is
-- made nullable from an outer join
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON t1.a = 1
LEFT JOIN pred_tab t3 ON t2.a IS NULL OR t2.c IS NULL;
DROP TABLE pred_tab;
-- Validate we handle IS NULL and IS NOT NULL quals correctly with inheritance
-- parents.
CREATE TABLE pred_parent (a int);
CREATE TABLE pred_child () INHERITS (pred_parent);
ALTER TABLE ONLY pred_parent ALTER a SET NOT NULL;
-- Ensure that the scan on pred_child contains the IS NOT NULL qual.
EXPLAIN (COSTS OFF)
SELECT * FROM pred_parent WHERE a IS NOT NULL;
-- Ensure we only scan pred_child and not pred_parent
EXPLAIN (COSTS OFF)
SELECT * FROM pred_parent WHERE a IS NULL;
ALTER TABLE pred_parent ALTER a DROP NOT NULL;
ALTER TABLE pred_child ALTER a SET NOT NULL;
-- Ensure the IS NOT NULL qual is removed from the pred_child scan.
EXPLAIN (COSTS OFF)
SELECT * FROM pred_parent WHERE a IS NOT NULL;
-- Ensure we only scan pred_parent and not pred_child
EXPLAIN (COSTS OFF)
SELECT * FROM pred_parent WHERE a IS NULL;
DROP TABLE pred_parent, pred_child;
-- Validate we do not reduce a clone clause to a constant true or false
CREATE TABLE pred_tab (a int, b int);
CREATE TABLE pred_tab_notnull (a int, b int NOT NULL);
INSERT INTO pred_tab VALUES (1, 1);
INSERT INTO pred_tab VALUES (2, 2);
INSERT INTO pred_tab_notnull VALUES (2, 2);
INSERT INTO pred_tab_notnull VALUES (3, 3);
ANALYZE pred_tab;
ANALYZE pred_tab_notnull;
-- Ensure the IS_NOT_NULL qual is not reduced to constant true and removed
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
LEFT JOIN pred_tab t4 ON t3.b IS NOT NULL;
-- Ensure the IS_NULL qual is not reduced to constant false
EXPLAIN (COSTS OFF)
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
SELECT * FROM pred_tab t1
LEFT JOIN pred_tab t2 ON TRUE
LEFT JOIN pred_tab_notnull t3 ON t2.a = t3.a
LEFT JOIN pred_tab t4 ON t3.b IS NULL AND t3.a IS NOT NULL;
DROP TABLE pred_tab;
DROP TABLE pred_tab_notnull;
|