diff options
Diffstat (limited to 'src/test/regress/sql/equivclass.sql')
| -rw-r--r-- | src/test/regress/sql/equivclass.sql | 271 |
1 files changed, 271 insertions, 0 deletions
diff --git a/src/test/regress/sql/equivclass.sql b/src/test/regress/sql/equivclass.sql new file mode 100644 index 0000000..247b0a3 --- /dev/null +++ b/src/test/regress/sql/equivclass.sql @@ -0,0 +1,271 @@ +-- +-- Tests for the planner's "equivalence class" mechanism +-- + +-- One thing that's not tested well during normal querying is the logic +-- for handling "broken" ECs. This is because an EC can only become broken +-- if its underlying btree operator family doesn't include a complete set +-- of cross-type equality operators. There are not (and should not be) +-- any such families built into Postgres; so we have to hack things up +-- to create one. We do this by making two alias types that are really +-- int8 (so we need no new C code) and adding only some operators for them +-- into the standard integer_ops opfamily. + +create type int8alias1; +create function int8alias1in(cstring) returns int8alias1 + strict immutable language internal as 'int8in'; +create function int8alias1out(int8alias1) returns cstring + strict immutable language internal as 'int8out'; +create type int8alias1 ( + input = int8alias1in, + output = int8alias1out, + like = int8 +); + +create type int8alias2; +create function int8alias2in(cstring) returns int8alias2 + strict immutable language internal as 'int8in'; +create function int8alias2out(int8alias2) returns cstring + strict immutable language internal as 'int8out'; +create type int8alias2 ( + input = int8alias2in, + output = int8alias2out, + like = int8 +); + +create cast (int8 as int8alias1) without function; +create cast (int8 as int8alias2) without function; +create cast (int8alias1 as int8) without function; +create cast (int8alias2 as int8) without function; + +create function int8alias1eq(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias1, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias1); + +create function int8alias2eq(int8alias2, int8alias2) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias2eq, + leftarg = int8alias2, rightarg = int8alias2, + commutator = =, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias2, int8alias2); + +create function int8alias1eq(int8, int8alias1) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8, rightarg = int8alias1, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8, int8alias1); + +create function int8alias1eq(int8alias1, int8alias2) returns bool + strict immutable language internal as 'int8eq'; +create operator = ( + procedure = int8alias1eq, + leftarg = int8alias1, rightarg = int8alias2, + restrict = eqsel, join = eqjoinsel, + merges +); +alter operator family integer_ops using btree add + operator 3 = (int8alias1, int8alias2); + +create function int8alias1lt(int8alias1, int8alias1) returns bool + strict immutable language internal as 'int8lt'; +create operator < ( + procedure = int8alias1lt, + leftarg = int8alias1, rightarg = int8alias1 +); +alter operator family integer_ops using btree add + operator 1 < (int8alias1, int8alias1); + +create function int8alias1cmp(int8, int8alias1) returns int + strict immutable language internal as 'btint8cmp'; +alter operator family integer_ops using btree add + function 1 int8alias1cmp (int8, int8alias1); + +create table ec0 (ff int8 primary key, f1 int8, f2 int8); +create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2); +create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2); + +-- for the moment we only want to look at nestloop plans +set enable_hashjoin = off; +set enable_mergejoin = off; + +-- +-- Note that for cases where there's a missing operator, we don't care so +-- much whether the plan is ideal as that we don't fail or generate an +-- outright incorrect plan. +-- + +explain (costs off) + select * from ec0 where ff = f1 and f1 = '42'::int8; +explain (costs off) + select * from ec0 where ff = f1 and f1 = '42'::int8alias1; +explain (costs off) + select * from ec1 where ff = f1 and f1 = '42'::int8alias1; +explain (costs off) + select * from ec1 where ff = f1 and f1 = '42'::int8alias2; + +explain (costs off) + select * from ec1, ec2 where ff = x1 and ff = '42'::int8; +explain (costs off) + select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1; +explain (costs off) + select * from ec1, ec2 where ff = x1 and '42'::int8 = x1; +explain (costs off) + select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1; +explain (costs off) + select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2; + +create unique index ec1_expr1 on ec1((ff + 1)); +create unique index ec1_expr2 on ec1((ff + 2 + 1)); +create unique index ec1_expr3 on ec1((ff + 3 + 1)); +create unique index ec1_expr4 on ec1((ff + 4)); + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1; + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss2 + where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss2 + where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8; + +-- check partially indexed scan +set enable_nestloop = on; +set enable_mergejoin = off; + +drop index ec1_expr3; + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + +-- let's try that as a mergejoin +set enable_mergejoin = on; +set enable_nestloop = off; + +explain (costs off) + select * from ec1, + (select ff + 1 as x from + (select ff + 2 as ff from ec1 + union all + select ff + 3 as ff from ec1) ss0 + union all + select ff + 4 as x from ec1) as ss1 + where ss1.x = ec1.f1 and ec1.ff = 42::int8; + +-- check effects of row-level security +set enable_nestloop = on; +set enable_mergejoin = off; + +alter table ec1 enable row level security; +create policy p1 on ec1 using (f1 < '5'::int8alias1); + +create user regress_user_ectest; +grant select on ec0 to regress_user_ectest; +grant select on ec1 to regress_user_ectest; + +-- without any RLS, we'll treat {a.ff, b.ff, 43} as an EquivalenceClass +explain (costs off) + select * from ec0 a, ec1 b + where a.ff = b.ff and a.ff = 43::bigint::int8alias1; + +set session authorization regress_user_ectest; + +-- with RLS active, the non-leakproof a.ff = 43 clause is not treated +-- as a suitable source for an EquivalenceClass; currently, this is true +-- even though the RLS clause has nothing to do directly with the EC +explain (costs off) + select * from ec0 a, ec1 b + where a.ff = b.ff and a.ff = 43::bigint::int8alias1; + +reset session authorization; + +revoke select on ec0 from regress_user_ectest; +revoke select on ec1 from regress_user_ectest; + +drop user regress_user_ectest; + +-- check that X=X is converted to X IS NOT NULL when appropriate +explain (costs off) + select * from tenk1 where unique1 = unique1 and unique2 = unique2; + +-- this could be converted, but isn't at present +explain (costs off) + select * from tenk1 where unique1 = unique1 or unique2 = unique2; + +-- check that we recognize equivalence with dummy domains in the way +create temp table undername (f1 name, f2 int); +create temp view overview as + select f1::information_schema.sql_identifier as sqli, f2 from undername; +explain (costs off) -- this should not require a sort + select * from overview where sqli = 'foo' order by sqli; |