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.sqlindex 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.rsindex 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()]),