pg.ddx.io  pgdogdev/pgdog GitHub issues and pull requests  
help / color / mirror / Atom feed
From: jkaczman (@jkaczman) <noreply+jkaczman@github.com>
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
Subject: [pgdogdev/pgdog] PR #2066: fix: support cross-shard subqueries that only return constants (e.g., Rails .limit(N).count)
Date: Wed, 18 Nov 2026 19:36:30 +0000
Message-ID: <gh-pgdogdev-pgdog-2066@github.com> (raw)

When we re-write a cross-shard subquery (#381) into a new `SELECT` statement to replace the subquery with:
- we keep constants as-is
- we re-write columns as `unnest(..)` arrays (#2003)

However, there was a previous assumption of there always being a column returned; we didn't have a way to handle only constants. If no columns were returned, the subquery would be rewritten as `unnest()` with no args (which Postgres errors on, returning `unnest()` does not exist error)

Thus, this PR implements functionality to properly handle subqueries that only return constants. 

Why is this important / common? When calling `.limit(N).count` with Rails, Rails sends a subquery that returns the constant `1` for each row that it matches (no columns in SELECT list) ([Rails source](https://github.com/rails/rails/blob/v7.1.4/activerecord/lib/active_record/relation/calculations.rb#L...)). E.g., for `FollowRequest.where(..).limit(40).count`, Rails sends

> `SELECT COUNT(*) FROM (SELECT 1 AS one FROM follow_requests WHERE target_account_id = $1 LIMIT $2) subquery_for_count`

Now, as a bit of a workaround, we send an array with a NULL for each row that the subquery returned. E.g., if the subquery returns 3 rows, we send `{NULL,NULL,NULL}`, so `unnest(..)` returns 3 rows, and, thus, COUNT(*) correctly returns 3.

> `SELECT count(*) FROM (SELECT 1 FROM unnest($1::pg_catalog._int4) u(c1)) subquery_for_count(one)`

This is motivated by Mastodon (written in Ruby on Rails). When a user logs in, Mastodon counts the pending follow requests sent to the user with `FollowRequest.where(..).limit(40).count` ([Mastodon source](https://github.com/mastodon/mastodon/blob/v4.3.0/app/serializers/rest/credential_account_serializer....)).


inline patch 1: integration/rust/tests/integration/subqueries/tests/select.sql, pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs (+41/-0 lines) download inline-1.patchdiff --git a/integration/rust/tests/integration/subqueries/tests/select.sql b/integration/rust/tests/integration/subqueries/tests/select.sql index 084658f80..75a9d5487 100644 --- a/integration/rust/tests/integration/subqueries/tests/select.sql +++ b/integration/rust/tests/integration/subqueries/tests/select.sql @@ -215,6 +215,29 @@ SELECT * FROM ( -- sql: SELECT * FROM (SELECT u.c1, ($2::pg_catalog._varchar)[u.c2 + 1:u.c2 + u.c3] FROM unnest($1::pg_catalog._int8, $3::pg_catalog._int4, $4::pg_catalog._int4) u(c1, c2, c3)) s(id, tags) ORDER BY id SELECT * FROM (SELECT id, tags FROM subusers) s ORDER BY id; +-- case +-- what: SELECT COUNT(*) FROM (<sq>) s, <sq> only returns constants +-- used by: Rails, which counts at most N rows for .limit(N).count (e.g., FollowRequest.where(..).limit(40).count) with +-- SELECT COUNT(*) FROM (SELECT 1 AS one FROM .. LIMIT N) subquery_for_count +-- - https://github.com/rails/rails/blob/v7.1.4/activerecord/lib/active_record/relation/calculations.rb#L... +-- - https://github.com/rails/rails/blob/v7.1.4/activerecord/lib/active_record/relation/calculations.rb#L... +-- used by: Mastodon, which counts the pending follow requests sent to accounts when the accounts log in with FollowRequest.where(target_account: ..).limit(40).count +-- - https://github.com/mastodon/mastodon/blob/v4.3.0/app/serializers/rest/credential_account_serializer.... +-- route select: 0,1 +-- route select: any +-- sql: SELECT count(*) FROM (SELECT 1 FROM unnest($1::pg_catalog._int4) u(c1)) subquery_for_count(one) +SELECT COUNT(*) FROM (SELECT 1 AS one FROM subusers WHERE status = 'VIP' LIMIT 40) subquery_for_count; + +-- case +-- what: SELECT COUNT(*) FROM (<sq>) s, <sq> only returns constants & returns zero rows +-- used by: Mastodon, which counts the pending follow requests sent to accounts when the accounts log in +-- For accounts without pending follow requests (most accounts), the derived table (SELECT 1 AS one FROM follow_requests WHERE target_account_id = .. LIMIT 40) doesn't return any rows +-- - https://github.com/mastodon/mastodon/blob/v4.3.0/app/serializers/rest/credential_account_serializer.... +-- route select: 0,1 +-- route select: any +-- sql: SELECT count(*) FROM (SELECT 1 FROM unnest($1::pg_catalog._int4) u(c1)) subquery_for_count(one) WHERE false +SELECT COUNT(*) FROM (SELECT 1 AS one FROM subusers WHERE status = 'nobody' LIMIT 40) subquery_for_count; + -- case -- what: Derived table with an inner-table that returns an empty response. -- Want to make sure that the OIDs returned are correct (if present) diff --git a/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs b/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs index 7279ab6f9..b932efb09 100644 --- a/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs +++ b/pgdog/src/frontend/client/query_engine/multi_step/subquery/clause/from_clause.rs @@ -269,6 +269,24 @@ impl<'a> SubqueryMulti<'a> { )); } + // If the subquery only returns constants, we don't have any columns to send, however unnest() needs at least one argument + // E.g., to count at most 40 rows (.limit(40).count), Rails sends SELECT COUNT(*) FROM (SELECT 1 AS one FROM users WHERE .. LIMIT 40) subquery_for_count, where the subquery returns the constant 1 for each row that it matches (up to 40) + // + // To solve this, we send an array with a NULL per row that the subquery returned to ensure that unnest(..) returns as many rows as the subquery + // E.g., if the subquery returns 3 rows, we send {NULL,NULL,NULL}, and SELECT 1 FROM unnest($1::pg_catalog._int4) u(c1), which + // returns the same exact response: 1 for each of the 3 rows + if param_refs.is_empty() { + param_refs.push(push_unnest_argument( + mem, + old_bind, + INT4_OID, + "_int4", + std::iter::repeat_n(None, data_rows.len()), + )); + + unnest_column_names.push(mem.make_string(Some("c1")).uncast()); + } + let list_of_param_refs = mem.make_list(param_refs.as_slice()); let unnest_func_call = mem.make_func_call( mem.make_list(&[mem.make_string(Some("unnest")).uncast()]),

view thread (6+ messages)  latest in thread

Message-ID: <gh-pgdogdev-pgdog-2066@github.com>
Permalink:  ../gh-pgdogdev-pgdog-2066@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox