1-- 2-- Tests for the planner's "equivalence class" mechanism 3-- 4 5-- One thing that's not tested well during normal querying is the logic 6-- for handling "broken" ECs. This is because an EC can only become broken 7-- if its underlying btree operator family doesn't include a complete set 8-- of cross-type equality operators. There are not (and should not be) 9-- any such families built into Postgres; so we have to hack things up 10-- to create one. We do this by making two alias types that are really 11-- int8 (so we need no new C code) and adding only some operators for them 12-- into the standard integer_ops opfamily. 13 14create type int8alias1; 15create function int8alias1in(cstring) returns int8alias1 16 strict immutable language internal as 'int8in'; 17create function int8alias1out(int8alias1) returns cstring 18 strict immutable language internal as 'int8out'; 19create type int8alias1 ( 20 input = int8alias1in, 21 output = int8alias1out, 22 like = int8 23); 24 25create type int8alias2; 26create function int8alias2in(cstring) returns int8alias2 27 strict immutable language internal as 'int8in'; 28create function int8alias2out(int8alias2) returns cstring 29 strict immutable language internal as 'int8out'; 30create type int8alias2 ( 31 input = int8alias2in, 32 output = int8alias2out, 33 like = int8 34); 35 36create cast (int8 as int8alias1) without function; 37create cast (int8 as int8alias2) without function; 38create cast (int8alias1 as int8) without function; 39create cast (int8alias2 as int8) without function; 40 41create function int8alias1eq(int8alias1, int8alias1) returns bool 42 strict immutable language internal as 'int8eq'; 43create operator = ( 44 procedure = int8alias1eq, 45 leftarg = int8alias1, rightarg = int8alias1, 46 commutator = =, 47 restrict = eqsel, join = eqjoinsel, 48 merges 49); 50alter operator family integer_ops using btree add 51 operator 3 = (int8alias1, int8alias1); 52 53create function int8alias2eq(int8alias2, int8alias2) returns bool 54 strict immutable language internal as 'int8eq'; 55create operator = ( 56 procedure = int8alias2eq, 57 leftarg = int8alias2, rightarg = int8alias2, 58 commutator = =, 59 restrict = eqsel, join = eqjoinsel, 60 merges 61); 62alter operator family integer_ops using btree add 63 operator 3 = (int8alias2, int8alias2); 64 65create function int8alias1eq(int8, int8alias1) returns bool 66 strict immutable language internal as 'int8eq'; 67create operator = ( 68 procedure = int8alias1eq, 69 leftarg = int8, rightarg = int8alias1, 70 restrict = eqsel, join = eqjoinsel, 71 merges 72); 73alter operator family integer_ops using btree add 74 operator 3 = (int8, int8alias1); 75 76create function int8alias1eq(int8alias1, int8alias2) returns bool 77 strict immutable language internal as 'int8eq'; 78create operator = ( 79 procedure = int8alias1eq, 80 leftarg = int8alias1, rightarg = int8alias2, 81 restrict = eqsel, join = eqjoinsel, 82 merges 83); 84alter operator family integer_ops using btree add 85 operator 3 = (int8alias1, int8alias2); 86 87create function int8alias1lt(int8alias1, int8alias1) returns bool 88 strict immutable language internal as 'int8lt'; 89create operator < ( 90 procedure = int8alias1lt, 91 leftarg = int8alias1, rightarg = int8alias1 92); 93alter operator family integer_ops using btree add 94 operator 1 < (int8alias1, int8alias1); 95 96create function int8alias1cmp(int8, int8alias1) returns int 97 strict immutable language internal as 'btint8cmp'; 98alter operator family integer_ops using btree add 99 function 1 int8alias1cmp (int8, int8alias1); 100 101create table ec0 (ff int8 primary key, f1 int8, f2 int8); 102create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); 103create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); 104 105-- for the moment we only want to look at nestloop plans 106set enable_hashjoin = off; 107set enable_mergejoin = off; 108 109-- 110-- Note that for cases where there's a missing operator, we don't care so 111-- much whether the plan is ideal as that we don't fail or generate an 112-- outright incorrect plan. 113-- 114 115explain (costs off) 116 select * from ec0 where ff = f1 and f1 = '42'::int8; 117explain (costs off) 118 select * from ec0 where ff = f1 and f1 = '42'::int8alias1; 119explain (costs off) 120 select * from ec1 where ff = f1 and f1 = '42'::int8alias1; 121explain (costs off) 122 select * from ec1 where ff = f1 and f1 = '42'::int8alias2; 123 124explain (costs off) 125 select * from ec1, ec2 where ff = x1 and ff = '42'::int8; 126explain (costs off) 127 select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1; 128explain (costs off) 129 select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; 130explain (costs off) 131 select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1; 132explain (costs off) 133 select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2; 134 135create unique index ec1_expr1 on ec1((ff + 1)); 136create unique index ec1_expr2 on ec1((ff + 2 + 1)); 137create unique index ec1_expr3 on ec1((ff + 3 + 1)); 138create unique index ec1_expr4 on ec1((ff + 4)); 139 140explain (costs off) 141 select * from ec1, 142 (select ff + 1 as x from 143 (select ff + 2 as ff from ec1 144 union all 145 select ff + 3 as ff from ec1) ss0 146 union all 147 select ff + 4 as x from ec1) as ss1 148 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 149 150explain (costs off) 151 select * from ec1, 152 (select ff + 1 as x from 153 (select ff + 2 as ff from ec1 154 union all 155 select ff + 3 as ff from ec1) ss0 156 union all 157 select ff + 4 as x from ec1) as ss1 158 where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; 159 160explain (costs off) 161 select * from ec1, 162 (select ff + 1 as x from 163 (select ff + 2 as ff from ec1 164 union all 165 select ff + 3 as ff from ec1) ss0 166 union all 167 select ff + 4 as x from ec1) as ss1, 168 (select ff + 1 as x from 169 (select ff + 2 as ff from ec1 170 union all 171 select ff + 3 as ff from ec1) ss0 172 union all 173 select ff + 4 as x from ec1) as ss2 174 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; 175 176-- let's try that as a mergejoin 177set enable_mergejoin = on; 178set enable_nestloop = off; 179 180explain (costs off) 181 select * from ec1, 182 (select ff + 1 as x from 183 (select ff + 2 as ff from ec1 184 union all 185 select ff + 3 as ff from ec1) ss0 186 union all 187 select ff + 4 as x from ec1) as ss1, 188 (select ff + 1 as x from 189 (select ff + 2 as ff from ec1 190 union all 191 select ff + 3 as ff from ec1) ss0 192 union all 193 select ff + 4 as x from ec1) as ss2 194 where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; 195 196-- check partially indexed scan 197set enable_nestloop = on; 198set enable_mergejoin = off; 199 200drop index ec1_expr3; 201 202explain (costs off) 203 select * from ec1, 204 (select ff + 1 as x from 205 (select ff + 2 as ff from ec1 206 union all 207 select ff + 3 as ff from ec1) ss0 208 union all 209 select ff + 4 as x from ec1) as ss1 210 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 211 212-- let's try that as a mergejoin 213set enable_mergejoin = on; 214set enable_nestloop = off; 215 216explain (costs off) 217 select * from ec1, 218 (select ff + 1 as x from 219 (select ff + 2 as ff from ec1 220 union all 221 select ff + 3 as ff from ec1) ss0 222 union all 223 select ff + 4 as x from ec1) as ss1 224 where ss1.x = ec1.f1 and ec1.ff = 42::int8; 225 226-- check effects of row-level security 227set enable_nestloop = on; 228set enable_mergejoin = off; 229 230alter table ec1 enable row level security; 231create policy p1 on ec1 using (f1 < '5'::int8alias1); 232 233create user regress_user_ectest; 234grant select on ec0 to regress_user_ectest; 235grant select on ec1 to regress_user_ectest; 236 237-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass 238explain (costs off) 239 select * from ec0 a, ec1 b 240 where a.ff = b.ff and a.ff = 43::bigint::int8alias1; 241 242set session authorization regress_user_ectest; 243 244-- with RLS active, the non-leakproof a.ff = 43 clause is not treated 245-- as a suitable source for an EquivalenceClass; currently, this is true 246-- even though the RLS clause has nothing to do directly with the EC 247explain (costs off) 248 select * from ec0 a, ec1 b 249 where a.ff = b.ff and a.ff = 43::bigint::int8alias1; 250 251reset session authorization; 252 253revoke select on ec0 from regress_user_ectest; 254revoke select on ec1 from regress_user_ectest; 255 256drop user regress_user_ectest; 257 258-- check that X=X is converted to X IS NOT NULL when appropriate 259explain (costs off) 260 select * from tenk1 where unique1 = unique1 and unique2 = unique2; 261 262-- this could be converted, but isn't at present 263explain (costs off) 264 select * from tenk1 where unique1 = unique1 or unique2 = unique2; 265 266-- check that we recognize equivalence with dummy domains in the way 267create temp table undername (f1 name, f2 int); 268create temp view overview as 269 select f1::information_schema.sql_identifier as sqli, f2 from undername; 270explain (costs off) -- this should not require a sort 271 select * from overview where sqli = 'foo' order by sqli; 272