diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 8e524016c6f..b836af6fb55 100644 --- a/src/backend/optimizer/path/indxpath.c +++ b/src/backend/optimizer/path/indxpath.c @@ -20,6 +20,7 @@ #include "access/stratnum.h" #include "access/sysattr.h" #include "catalog/pg_am.h" +#include "catalog/pg_amop.h" #include "catalog/pg_operator.h" #include "catalog/pg_opfamily.h" #include "catalog/pg_type.h" @@ -3158,6 +3159,7 @@ match_orclause_to_indexcol(PlannerInfo *root, Oid inputcollid = InvalidOid; bool firstTime = true; bool have_param = false; + HeapTuple tp; Assert(IsA(orclause, BoolExpr)); Assert(orclause->boolop == OR_EXPR); @@ -3193,7 +3195,7 @@ match_orclause_to_indexcol(PlannerInfo *root, if (list_length(subClause->args) != 2) return NULL; - /* RestrictInfo parameters must match parent */ + /* RestrictInfo parameters dmust match parent */ if (subRinfo->is_pushed_down != rinfo->is_pushed_down || subRinfo->is_clone != rinfo->is_clone || subRinfo->security_level != rinfo->security_level || @@ -3234,6 +3236,15 @@ match_orclause_to_indexcol(PlannerInfo *root, return NULL; } + tp = SearchSysCache3(AMOPOPID, + ObjectIdGetDatum(opno), + CharGetDatum(AMOP_SEARCH), + ObjectIdGetDatum(index->opfamily[indexcol])); + if (!HeapTupleIsValid(tp)) + return NULL; + + ReleaseSysCache(tp); + /* * Ignore any RelabelType node above the operands. This is needed to * be able to apply indexscanning in binary-compatible-operator cases. diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index c6feef03810..cac076abb88 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -1913,6 +1913,27 @@ SELECT * FROM tenk1 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx (1 row) +EXPLAIN (COSTS OFF) +SELECT * FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric); + QUERY PLAN +------------------------------------------------------------------------------------------------------------- + Bitmap Heap Scan on tenk1 + Recheck Cond: (thousand = 42) + Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR ((tenthous)::numeric = '42'::numeric)) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (thousand = 42) +(5 rows) + +EXPLAIN (COSTS OFF) +SELECT * FROM tenk1 + WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric; + QUERY PLAN +------------------------------------------------------------------------------------------------------------- + Seq Scan on tenk1 + Filter: (((tenthous)::numeric = '1'::numeric) OR (tenthous = 3) OR ((tenthous)::numeric = '42'::numeric)) +(2 rows) + EXPLAIN (COSTS OFF) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); @@ -2034,6 +2055,78 @@ SELECT count(*) FROM tenk1 10 (1 row) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1, tenk2 + WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Aggregate + -> Nested Loop + Join Filter: ((tenk2.thousand = 42) OR (tenk1.thousand = 41) OR (tenk2.tenthous = 2)) + -> Bitmap Heap Scan on tenk1 + Recheck Cond: (hundred = 42) + -> Bitmap Index Scan on tenk1_hundred + Index Cond: (hundred = 42) + -> Materialize + -> Bitmap Heap Scan on tenk2 + Recheck Cond: (hundred = 42) + -> Bitmap Index Scan on tenk2_hundred + Index Cond: (hundred = 42) +(12 rows) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1, tenk2 + WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Nested Loop + -> Bitmap Heap Scan on tenk2 + Recheck Cond: (hundred = 42) + Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2)) + -> Bitmap Index Scan on tenk2_hundred + Index Cond: (hundred = 42) + -> Index Only Scan using tenk1_hundred on tenk1 + Index Cond: (hundred = 42) +(9 rows) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 JOIN tenk2 ON + tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Nested Loop + -> Bitmap Heap Scan on tenk2 + Recheck Cond: (hundred = 42) + Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2)) + -> Bitmap Index Scan on tenk2_hundred + Index Cond: (hundred = 42) + -> Index Only Scan using tenk1_hundred on tenk1 + Index Cond: (hundred = 42) +(9 rows) + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON + tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + QUERY PLAN +------------------------------------------------------------------------------------ + Aggregate + -> Nested Loop Left Join + Join Filter: (tenk1.hundred = 42) + -> Index Only Scan using tenk1_hundred on tenk1 + -> Memoize + Cache Key: tenk1.hundred + Cache Mode: logical + -> Index Scan using tenk2_hundred on tenk2 + Index Cond: (hundred = tenk1.hundred) + Filter: ((thousand = 42) OR (thousand = 41) OR (tenthous = 2)) +(10 rows) + -- -- Check behavior with duplicate index column contents -- diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index 319190855bd..ef890b96cc6 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -4492,6 +4492,13 @@ SELECT * FROM rls_tbl WHERE a <<< 1000; --- (0 rows) +EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 8c4da955084..a4c7be487ef 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3254,6 +3254,8 @@ CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int, restrict = scalarltsel); SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied ERROR: permission denied for table priv_test_tbl +SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0; +ERROR: permission denied for table priv_test_tbl DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied ERROR: permission denied for table priv_test_tbl -- Grant access via a security barrier view, but hide all data @@ -3268,6 +3270,11 @@ SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not l ---+--- (0 rows) +SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not leak + a | b +---+--- +(0 rows) + DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak -- Grant table access, but hide all data with RLS RESET SESSION AUTHORIZATION; @@ -3280,6 +3287,11 @@ SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not le ---+--- (0 rows) +SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0; + a | b +---+--- +(0 rows) + DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak -- privilege checks for pg_stats_ext and pg_stats_ext_exprs RESET SESSION AUTHORIZATION; diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out index 6026e15ed31..8f4ef0d7a6a 100644 --- a/src/test/regress/expected/uuid.out +++ b/src/test/regress/expected/uuid.out @@ -129,6 +129,37 @@ CREATE INDEX guid1_btree ON guid1 USING BTREE (guid_field); CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field); -- unique index test CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field); +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111' OR + guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e'; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------ + Aggregate + -> Seq Scan on guid1 + Filter: ((guid_field <> '11111111-1111-1111-1111-111111111111'::uuid) OR (guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid)) +(3 rows) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222' OR + guid_field <= '11111111111111111111111111111111' OR + guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e'; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Seq Scan on guid1 + Filter: ((guid_field <= '22222222-2222-2222-2222-222222222222'::uuid) OR (guid_field <= '11111111-1111-1111-1111-111111111111'::uuid) OR (guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid)) +(3 rows) + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e' OR + guid_field = '11111111111111111111111111111111'; + QUERY PLAN +---------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + -> Seq Scan on guid1 + Filter: ((guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e'::uuid) OR (guid_field = '11111111-1111-1111-1111-111111111111'::uuid)) +(3 rows) + -- should fail INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111'); ERROR: duplicate key value violates unique constraint "guid1_unique_btree" diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index f74ad415fbf..7e108f9b283 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -750,6 +750,14 @@ SELECT * FROM tenk1 SELECT * FROM tenk1 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42); +EXPLAIN (COSTS OFF) +SELECT * FROM tenk1 + WHERE thousand = 42 AND (tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric); + +EXPLAIN (COSTS OFF) +SELECT * FROM tenk1 + WHERE tenthous = 1::numeric OR tenthous = 3::int4 OR tenthous = 42::numeric; + EXPLAIN (COSTS OFF) SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 99); @@ -780,6 +788,25 @@ SELECT count(*) FROM tenk1 SELECT count(*) FROM tenk1 WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2); +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1, tenk2 + WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk1.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1, tenk2 + WHERE tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 JOIN tenk2 ON + tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; + +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 LEFT JOIN tenk2 ON + tenk1.hundred = 42 AND (tenk2.thousand = 42 OR tenk2.thousand = 41 OR tenk2.tenthous = 2) AND + tenk2.hundred = tenk1.hundred; -- -- Check behavior with duplicate index column contents -- diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql index 3011d71b12b..6d2414b6044 100644 --- a/src/test/regress/sql/rowsecurity.sql +++ b/src/test/regress/sql/rowsecurity.sql @@ -2177,6 +2177,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int, restrict = scalarltsel); SELECT * FROM rls_tbl WHERE a <<< 1000; +EXPLAIN (COSTS OFF) SELECT * FROM rls_tbl WHERE a <<< 1000 or a <<< 900; DROP OPERATOR <<< (int, int); DROP FUNCTION op_leak(int, int); RESET SESSION AUTHORIZATION; diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 0c08a6cc42e..5c786b16c6f 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -1634,6 +1634,7 @@ CREATE FUNCTION op_leak(int, int) RETURNS bool CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int, restrict = scalarltsel); SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied +SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0; DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Permission denied -- Grant access via a security barrier view, but hide all data @@ -1645,6 +1646,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_view TO regress_stats_user1; -- Should now have access via the view, but see nothing and leak nothing SET SESSION AUTHORIZATION regress_stats_user1; SELECT * FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak +SELECT * FROM tststats.priv_test_view WHERE a <<< 0 OR b <<< 0; -- Should not leak DELETE FROM tststats.priv_test_view WHERE a <<< 0 AND b <<< 0; -- Should not leak -- Grant table access, but hide all data with RLS @@ -1655,6 +1657,7 @@ GRANT SELECT, DELETE ON tststats.priv_test_tbl TO regress_stats_user1; -- Should now have direct table access, but see nothing and leak nothing SET SESSION AUTHORIZATION regress_stats_user1; SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak +SELECT * FROM tststats.priv_test_tbl WHERE a <<< 0 OR b <<< 0; DELETE FROM tststats.priv_test_tbl WHERE a <<< 0 AND b <<< 0; -- Should not leak -- privilege checks for pg_stats_ext and pg_stats_ext_exprs diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql index c88f6d087a7..75ee966ded0 100644 --- a/src/test/regress/sql/uuid.sql +++ b/src/test/regress/sql/uuid.sql @@ -63,6 +63,18 @@ CREATE INDEX guid1_hash ON guid1 USING HASH (guid_field); -- unique index test CREATE UNIQUE INDEX guid1_unique_BTREE ON guid1 USING BTREE (guid_field); + +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field <> '11111111111111111111111111111111' OR + guid_field <> '3f3e3c3b-3a30-3938-3736-353433a2313e'; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field <= '22222222-2222-2222-2222-222222222222' OR + guid_field <= '11111111111111111111111111111111' OR + guid_field <= '3f3e3c3b-3a30-3938-3736-353433a2313e'; +EXPLAIN (COSTS OFF) +SELECT COUNT(*) FROM guid1 WHERE guid_field = '3f3e3c3b-3a30-3938-3736-353433a2313e' OR + guid_field = '11111111111111111111111111111111'; + -- should fail INSERT INTO guid1(guid_field) VALUES('11111111-1111-1111-1111-111111111111');