On 09.02.2025 18:14, Alena Rybakina wrote:
Hi! I found another example where the transformation worked
incorrectly and reconsidered the idea.
As for conversion of exists_sublink_to_ANY, we need to get the
flattened implicit-AND list of clauses and pull out the chunks of the
WHERE clause that belong to the parent query,
since we are called halfway through the parent's
preprocess_expression() and earlier steps of preprocess_expression()
wouldn't get applied to the pulled-up stuff unless we do them here.
We also do some processing for vars depending on which side the var is
on - if it's in a subquery, we only need to lower its level (varlevel)
because subquery will be flatted, while
for other vars that belong to the parent query, we need to do
preparation to pull up the sub-select into top range table.
For those expressions that we couldn't assign to either list, we
define newWhere and apply both cases.
When I run 'make -C contrib/ check', tests of postgres_fdw extension
failed. I might be wrong, but you should be careful with LIMIT.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.
diff -U3 /home/ilia/postgres/contrib/postgres_fdw/expected/postgres_fdw.out
/home/ilia/postgres/contrib/postgres_fdw/results/postgres_fdw.out
--- /home/ilia/postgres/contrib/postgres_fdw/expected/postgres_fdw.out
2025-02-10 23:31:19.353059650 +0300
+++ /home/ilia/postgres/contrib/postgres_fdw/results/postgres_fdw.out
2025-02-10 23:46:43.249745683 +0300
@@ -4835,13 +4835,15 @@
EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2))
AND ft2.c1 > 900
ORDER BY ft2.c1 LIMIT 10;
-
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Foreign Scan
+
QUERY
PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Limit
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8
- Relations: (public.ft2) SEMI JOIN ((public.ft2 ft2_1) SEMI JOIN
(public.ft4))
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7,
r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND EXISTS (SELECT NULL FROM
"S 1"."T 1" r3 WHERE ((r1."C 1" = r3."C 1")) AND EXISTS (SELECT NULL FROM "S
1"."T 3" r4 WHERE ((r3.c2 = r4.c2)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT
10::bigint
-(4 rows)
+ -> Foreign Scan
+ Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8
+ Relations: (public.ft2) SEMI JOIN ((public.ft2 ft2_1) SEMI JOIN
(public.ft4))
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6,
r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND EXISTS (SELECT
NULL FROM "S 1"."T 1" r3 WHERE ((r1."C 1" = r3."C 1")) AND EXISTS (SELECT NULL
FROM "S 1"."T 3" r4 WHERE ((r3.c2 = r4.c2)))) ORDER BY r1."C 1" ASC NULLS LAST
+(6 rows)
SELECT ft2.* FROM ft2 WHERE
c1 = ANY (
@@ -4871,13 +4873,20 @@
EXISTS (SELECT 1 FROM ft4 WHERE ft4.c2 = ft2.c2) AND c1 = ftupper.c1 )
AND ftupper.c1 > 900
ORDER BY ftupper.c1 LIMIT 10;
-
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Foreign Scan
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------
+ Limit
Output: ftupper.c1, ftupper.c2, ftupper.c3, ftupper.c4, ftupper.c5,
ftupper.c6, ftupper.c7, ftupper.c8
- Relations: (public.ft2 ftupper) SEMI JOIN ((public.ft2) SEMI JOIN
(public.ft4))
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7,
r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" > 900)) AND EXISTS (SELECT NULL FROM
"S 1"."T 1" r2 WHERE ((r1."C 1" = r2."C 1")) AND EXISTS (SELECT NULL FROM "S
1"."T 3" r3 WHERE ((r2.c2 = r3.c2)))) ORDER BY r1."C 1" ASC NULLS LAST LIMIT
10::bigint
-(4 rows)
+ -> Foreign Scan on public.ft2 ftupper
+ Output: ftupper.c1, ftupper.c2, ftupper.c3, ftupper.c4, ftupper.c5,
ftupper.c6, ftupper.c7, ftupper.c8
+ Filter: (ANY (ftupper.c1 = (hashed SubPlan 2).col1))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
WHERE (("C 1" > 900)) ORDER BY "C 1" ASC NULLS LAST
+ SubPlan 2
+ -> Foreign Scan
+ Output: ft2.c1
+ Relations: (public.ft2) SEMI JOIN (public.ft4)
+ Remote SQL: SELECT r1."C 1" FROM "S 1"."T 1" r1 WHERE EXISTS
(SELECT NULL FROM "S 1"."T 3" r2 WHERE ((r1.c2 = r2.c2)))
+(11 rows)
SELECT * FROM ft2 ftupper WHERE
EXISTS (
