From: murex971 (@murex971) <noreply+murex971@github.com>
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
Subject: [pgdogdev/pgdog] PR #2050: feat: cross-shard GROUP BY on SELECT aliases, and alias/expression keys with HAVING
Date: Tue, 17 Nov 2026 16:58:39 +0000
Message-ID: <gh-pgdogdev-pgdog-2050@github.com> (raw)
### Summary
Cross-shard `GROUP BY` now accepts a SELECT alias, and queries with `HAVING` accept
alias and expression group keys. All three were rejected before.
### How
In `GROUP BY name`, Postgres prefers a table column called `name` over a SELECT alias.
PgDog uses its loaded schema to decide which one it is:
- No table in `FROM` has that column: it's the alias, and groups merge on that SELECT
list position.
- A table has that column: the column wins and is sent to shards as a hidden key.
- PgDog can't tell (subquery, function or CTE in `FROM`, table not in the schema,
`AS t(a, b)` renames, system columns): still rejected, with a clearer error.
For `HAVING`, the rewrite now returns the position of every group key, and keys that
aren't in the SELECT list are projected as hidden columns.
### Limits
- Alias grouping needs the schema loaded; without it the query is rejected as before.
- Unqualified tables are matched by name across all schemas, since the search path
isn't available here. This only errs towards the hidden-column path.
- `HAVING` that references a column inside an expression key
(`GROUP BY lower(label) HAVING label = 'x'`) is not handled.
inline patch 1: integration/sql/cases/957_group_by_alias_expression_case.sql, integration/sql/cases/957_group_by_alias_expression_setup.sql, integration/sql/cases/957_group_by_alias_expression_teardown.sql and 5 more (+355/-77 lines) download inline-1.patchdiff --git a/integration/sql/cases/957_group_by_alias_expression_case.sql b/integration/sql/cases/957_group_by_alias_expression_case.sqlnew file mode 100644index 000000000..b35b95ac1--- /dev/null+++ b/integration/sql/cases/957_group_by_alias_expression_case.sql@@ -0,0 +1,24 @@+-- description: GROUP BY on a SELECT alias or an expression merges across shards+-- tags: standard sharded+-- transactional: true++SELECT value % 2 AS parity, COUNT(*) FROM sql_regression_samples GROUP BY parity ORDER BY parity;++SELECT COUNT(*), lower(label) AS l, value % 2 AS parity FROM sql_regression_samples+GROUP BY parity, l ORDER BY parity, l;++-- The table column wins over the alias, so 'a' and 'A' stay separate groups.+SELECT lower(label) AS label, COUNT(*), SUM(value) FROM sql_regression_samples+GROUP BY label ORDER BY 1, 3;++SELECT value % 2 AS parity, COUNT(*) FROM sql_regression_samples+GROUP BY parity HAVING COUNT(*) > 3 ORDER BY parity;++SELECT lower(label), COUNT(*) FROM sql_regression_samples+GROUP BY lower(label) HAVING COUNT(*) > 1 ORDER BY 1;++SELECT COUNT(*), SUM(value) FROM sql_regression_samples+GROUP BY value % 3 HAVING SUM(value) > 4 ORDER BY 2;++SELECT lower(label) AS label, COUNT(*) FROM sql_regression_samples+GROUP BY label HAVING COUNT(*) >= 1 ORDER BY 1, 2;diff --git a/integration/sql/cases/957_group_by_alias_expression_setup.sql b/integration/sql/cases/957_group_by_alias_expression_setup.sqlnew file mode 100644index 000000000..9ccde7b03--- /dev/null+++ b/integration/sql/cases/957_group_by_alias_expression_setup.sql@@ -0,0 +1,10 @@+DROP TABLE IF EXISTS sql_regression_samples;+CREATE TABLE sql_regression_samples (id BIGINT PRIMARY KEY, value INTEGER, label TEXT);+INSERT INTO sql_regression_samples VALUES (1, 1, 'a');+INSERT INTO sql_regression_samples VALUES (101, 1, 'A');+INSERT INTO sql_regression_samples VALUES (2, 2, 'b');+INSERT INTO sql_regression_samples VALUES (102, 3, 'B');+INSERT INTO sql_regression_samples VALUES (3, 4, 'c');+INSERT INTO sql_regression_samples VALUES (103, 5, 'a');+INSERT INTO sql_regression_samples VALUES (4, 6, NULL);+INSERT INTO sql_regression_samples VALUES (104, NULL, NULL);diff --git a/integration/sql/cases/957_group_by_alias_expression_teardown.sql b/integration/sql/cases/957_group_by_alias_expression_teardown.sqlnew file mode 100644index 000000000..f14a9af47--- /dev/null+++ b/integration/sql/cases/957_group_by_alias_expression_teardown.sql@@ -0,0 +1 @@+DROP TABLE IF EXISTS sql_regression_samples;diff --git a/pgdog/src/frontend/router/parser/aggregate.rs b/pgdog/src/frontend/router/parser/aggregate.rsindex 22eb3716d..5f831b2b3 100644--- a/pgdog/src/frontend/router/parser/aggregate.rs+++ b/pgdog/src/frontend/router/parser/aggregate.rs@@ -1,6 +1,8 @@
use pg_raw_parse::{Node, nodes};
use std::fmt;
+use pgdog_stats::Relation as StatsRelation;+
use super::Function;
use crate::backend::schema::Schema;
@@ -93,6 +95,108 @@ fn index_of_column(stmt: &nodes::SelectStmt, qualified_column_name: &[&str]) ->
})
}
+/// Columns every table has without listing them in its definition.+const SYSTEM_COLUMNS: [&str; 6] = ["tableoid", "xmin", "cmin", "xmax", "cmax", "ctid"];++/// What a bare `GROUP BY` name that matches a `SELECT` alias refers to.+///+/// Postgres looks for a column of the `FROM` tables first and falls back to+/// the `SELECT` alias, so the schema decides which one it is:+///+/// ```sql+/// SELECT lower(name) AS name, count(*) FROM users GROUP BY name -- users.name+/// SELECT lower(name) AS n, count(*) FROM users GROUP BY n -- lower(name)+/// ```+#[derive(Debug, Clone, Copy, PartialEq)]+pub(crate) enum GroupByAlias {+ /// A table in `FROM` has a column with this name.+ InputColumn,+ /// No table has one. The name is the `SELECT` list item at this position.+ Output(usize),+ /// `FROM` has something other than tables found in the schema.+ Unknown,+}++impl GroupByAlias {+ /// Returns `None` if the column isn't a bare name matching a `SELECT` alias.+ pub(crate) fn resolve(+ stmt: &nodes::SelectStmt,+ column: &nodes::ColumnRef,+ schema: &Schema,+ ) -> Option<Self> {+ let mut fields = column.fields().into_iter();+ let (Some(name), None) = (fields.next().and_then(Node::as_str), fields.next()) else {+ return None;+ };+ let index = stmt+ .target_list()+ .iter()+ .position(|target| target.name() == Some(name))?;++ if SYSTEM_COLUMNS.contains(&name) {+ return Some(Self::Unknown);+ }++ let ctes: Vec<&str> = stmt+ .with_clause()+ .map(|with| with.ctes().iter().filter_map(|cte| cte.ctename()).collect())+ .unwrap_or_default();+ let mut is_input_column = false;+ for from in stmt.from_clause().iter() {+ match from_has_column(from, name, schema, &ctes) {+ Some(has_column) => is_input_column |= has_column,+ None => return Some(Self::Unknown),+ }+ }++ Some(if is_input_column {+ Self::InputColumn+ } else {+ Self::Output(index)+ })+ }+}++/// Whether a `FROM` item has a column with this name. `None` if it isn't made+/// of tables found in the schema, e.g., a subquery, a function or a CTE.+fn from_has_column(from: Node<'_>, name: &str, schema: &Schema, ctes: &[&str]) -> Option<bool> {+ // `AS t(a, b)` renames the columns.+ let renames_columns =+ |alias: Option<&nodes::Alias>| alias.is_some_and(|alias| !alias.colnames().is_empty());++ match from {+ Node::RangeVar(table) => {+ let relname = table.relname()?;+ if renames_columns(table.alias()) {+ return None;+ }+ let has_column = |relation: &StatsRelation| relation.columns.contains_key(name);+ match table.schemaname() {+ Some(schemaname) => schema.get(schemaname, relname).map(has_column),+ None if ctes.contains(&relname) => None,+ // The search path isn't known here. A column in any table+ // with this name counts, which only ever picks the hidden+ // column the shards check themselves.+ None => schema+ .relations+ .values()+ .filter_map(|relations| relations.get(relname))+ .map(has_column)+ .reduce(|a, b| a || b),+ }+ }+ Node::JoinExpr(join) => {+ if renames_columns(join.alias()) {+ return None;+ }+ let left = from_has_column(join.larg(), name, schema, ctes)?;+ let right = from_has_column(join.rarg(), name, schema, ctes)?;+ Some(left || right)+ }+ _ => None,+ }+}+
fn columns_match(group_by_names: &[&str], select_names: &[&str]) -> bool {
if group_by_names == select_names {
return true;
@@ -127,7 +231,7 @@ impl Aggregate {
let group_by = stmt
.group_clause()
.iter()
- .filter_map(|node| Self::group_by_column(stmt, node))+ .filter_map(|node| Self::group_by_column(stmt, node, schema))
.collect();
let targets = stmt
@@ -156,9 +260,13 @@ impl Aggregate {
Self { group_by, targets }
}
- /// Position of a GROUP BY item in the SELECT list, if it's an ordinal- /// or a selected column.- pub(crate) fn group_by_column(stmt: &nodes::SelectStmt, node: Node<'_>) -> Option<usize> {+ /// Position of a GROUP BY item in the SELECT list, if it's an ordinal,+ /// a selected column or a SELECT alias.+ pub(crate) fn group_by_column(+ stmt: &nodes::SelectStmt,+ node: Node<'_>,+ schema: &Schema,+ ) -> Option<usize> {
match node {
// We use 0-indexed arrays, Postgres uses 1-indexed.
Node::A_Const(c) => c
@@ -171,7 +279,11 @@ impl Aggregate {
.iter()
.filter_map(Node::as_str)
.collect::<Vec<_>>(),
- ),+ )+ .or_else(|| match GroupByAlias::resolve(stmt, c, schema) {+ Some(GroupByAlias::Output(index)) => Some(index),+ _ => None,+ }),
_ => None,
}
}
diff --git a/pgdog/src/frontend/router/parser/mod.rs b/pgdog/src/frontend/router/parser/mod.rsindex b6dbbd6db..672a88cfd 100644--- a/pgdog/src/frontend/router/parser/mod.rs+++ b/pgdog/src/frontend/router/parser/mod.rs@@ -33,7 +33,7 @@ mod table;
pub(crate) mod value;
mod where_clause;
-pub(crate) use aggregate::{Aggregate, AggregateFunction, AggregateTarget};+pub(crate) use aggregate::{Aggregate, AggregateFunction, AggregateTarget, GroupByAlias};
pub(crate) use binary::BinaryStream;
pub(crate) use cache::{Ast, AstContext, Cache};
pub(crate) use column::Column;
diff --git a/pgdog/src/frontend/router/parser/rewrite/statement/group_by.rs b/pgdog/src/frontend/router/parser/rewrite/statement/group_by.rsindex 1247971ff..62d4189ce 100644--- a/pgdog/src/frontend/router/parser/rewrite/statement/group_by.rs+++ b/pgdog/src/frontend/router/parser/rewrite/statement/group_by.rs@@ -4,12 +4,18 @@ use pg_raw_parse::{Node, make, nodes};
use super::{
Error,
- projection::{HiddenAliases, column_name, expands_star},+ projection::{HiddenAliases, expands_star},+};+use crate::{+ backend::schema::Schema,+ frontend::router::parser::{Aggregate, GroupByAlias},
};
-use crate::frontend::router::parser::Aggregate;
const ALIAS_PREFIX: &str = "__pgdog_group_";
+pub(super) const UNRESOLVED_ALIAS: &str = "GROUP BY on a SELECT alias is only supported when FROM has just tables \+ PgDog has loaded the schema for";+
/// GROUP BY keys added to the shard query as hidden columns.
#[derive(Debug, PartialEq)]
pub(crate) struct GroupByRewrite {
@@ -50,6 +56,7 @@ pub(super) fn rewrite_select<'a>(
select: &mut make::Unique<'a, &'a nodes::SelectStmt>,
mem: make::MemoryToken<'a>,
aggregate: &Aggregate,
+ schema: &Schema,
) -> Result<Option<GroupByRewrite>, Error> {
if aggregate.group_by().len() == select.group_clause().len() {
return Ok(None);
@@ -67,7 +74,7 @@ pub(super) fn rewrite_select<'a>(
let mut group_by = vec![];
for node in select.group_clause().iter() {
- if let Some(column) = Aggregate::group_by_column(select, node) {+ if let Some(column) = Aggregate::group_by_column(select, node, schema) {
group_by.push(column);
continue;
}
@@ -78,11 +85,12 @@ pub(super) fn rewrite_select<'a>(
"GROUPING SETS, ROLLUP and CUBE are unsupported",
));
}
- // Shards can't resolve a SELECT alias inside the hidden column.- Node::ColumnRef(column) if groups_by_output_alias(select, column) => {- return Err(Error::UnsupportedGroupBy(- "GROUP BY on a SELECT alias is unsupported",- ));+ // Shards can't resolve a SELECT alias inside the hidden column,+ // so the name has to be a known column of the FROM tables.+ Node::ColumnRef(column)+ if GroupByAlias::resolve(select, column, schema) == Some(GroupByAlias::Unknown) =>+ {+ return Err(Error::UnsupportedGroupBy(UNRESOLVED_ALIAS));
}
_ => {}
}
@@ -102,12 +110,3 @@ pub(super) fn rewrite_select<'a>(
aggregate.clone().with_group_by(group_by),
)))
}
--fn groups_by_output_alias(select: &nodes::SelectStmt, column: &nodes::ColumnRef) -> bool {- column.fields().len() == 1- && select.target_list().iter().any(|target| {- target- .name()- .is_some_and(|name| Some(name) == column_name(column))- })-}diff --git a/pgdog/src/frontend/router/parser/rewrite/statement/having.rs b/pgdog/src/frontend/router/parser/rewrite/statement/having.rsindex f053056db..46735a884 100644--- a/pgdog/src/frontend/router/parser/rewrite/statement/having.rs+++ b/pgdog/src/frontend/router/parser/rewrite/statement/having.rs@@ -7,11 +7,13 @@ use pg_raw_parse::{
};
use crate::backend::schema::Schema;
-use crate::frontend::router::parser::{AggregateFunction, HavingRewritePlan};+use crate::frontend::router::parser::{+ Aggregate, AggregateFunction, GroupByAlias, HavingRewritePlan,+};
use super::{
- Error, offset,- projection::{HiddenAliases, column_name, is_star},+ Error, group_by, offset,+ projection::{HiddenAliases, is_star},
};
const ALIAS_PREFIX: &str = "__pgdog_having_";
@@ -49,11 +51,14 @@ const TABLE_ALIAS: &str = "__pgdog_having";
///
/// which returns the positions of the groups to keep. LIMIT and OFFSET are
/// removed from the shard query, since they apply to the groups that pass.
+///+/// Returns the plan and the position of every GROUP BY key in the rewritten+/// SELECT list.
pub(super) fn rewrite_select<'a>(
select: &mut nodes::SelectStmtMut<'a, '_>,
mem: make::MemoryToken<'a>,
schema: &Schema,
-) -> Result<HavingRewritePlan, Error> {+) -> Result<(HavingRewritePlan, Vec<usize>), Error> {
debug_assert!(
!matches!(select.having_clause(), Node::None),
"HAVING rewrite requires a HAVING clause"
@@ -123,25 +128,39 @@ pub(super) fn rewrite_select<'a>(
.iter()
.map(|group| mem.make_unique(group))
.collect::<Vec<_>>();
+ let selected = select.target_list().len();+ let mut group_by = Vec::with_capacity(group_sources.len());
for group_source in group_sources {
- match group_source.as_ref() {- // Shards can't resolve a SELECT alias inside the hidden projection.- Node::ColumnRef(column) if groups_by_output_alias(select, column) => {- return Err(Error::UnsupportedHaving(- "GROUP BY on a SELECT alias is unsupported",- ));- }- Node::ColumnRef(_) => {- let alias = operands.aliases.next_alias();- operands.projected.push((alias, group_source));- }- Node::A_Const(_) => {}- _ => {+ let selected_column = match group_source.as_ref() {+ // A merged row can't tell which grouping set it came from.+ Node::GroupingSet(_) => {
return Err(Error::UnsupportedHaving(
- "GROUP BY expressions other than columns and ordinals are unsupported",+ "GROUPING SETS, ROLLUP and CUBE are unsupported",
));
}
- }+ ordinal @ Node::A_Const(_) => Some(+ Aggregate::group_by_column(select, ordinal, schema).ok_or_else(|| {+ Error::UnsupportedHaving(+ "GROUP BY constants other than ordinals are unsupported",+ )+ })?,+ ),+ Node::ColumnRef(column) => match GroupByAlias::resolve(select, column, schema) {+ Some(GroupByAlias::Output(index)) => Some(index),+ // Shards can't resolve a SELECT alias inside the hidden projection.+ Some(GroupByAlias::Unknown) => {+ return Err(Error::UnsupportedHaving(group_by::UNRESOLVED_ALIAS));+ }+ Some(GroupByAlias::InputColumn) | None => None,+ },+ _ => None,+ };+ // Keys that aren't a SELECT list position are projected as hidden columns.+ group_by.push(selected_column.unwrap_or_else(|| {+ let alias = operands.aliases.next_alias();+ operands.projected.push((alias, group_source));+ selected + operands.projected.len() - 1+ }));
}
let hidden_aliases = operands
@@ -174,26 +193,12 @@ pub(super) fn rewrite_select<'a>(
// Shards return every group; PgDog applies LIMIT and OFFSET after the filter.
offset::remove_select(select, mem);
- Ok(HavingRewritePlan::new(- filter_sql,- operand_aliases,- hidden_aliases,- operands.params,+ Ok((+ HavingRewritePlan::new(filter_sql, operand_aliases, hidden_aliases, operands.params),+ group_by,
))
}
-fn groups_by_output_alias(- select: &nodes::SelectStmtMut<'_, '_>,- column: &nodes::ColumnRef,-) -> bool {- column.fields().len() == 1- && select.target_list().iter().any(|target| {- target- .name()- .is_some_and(|name| Some(name) == column_name(column))- })-}-
struct Operands<'a, 's> {
schema: &'s Schema,
aliases: HiddenAliases,
@@ -285,11 +290,7 @@ mod tests {
let mut plan = None;
let rewritten = make::try_owned(|mem| {
let mut select = mem.make_unique(select);
- plan = Some(rewrite_select(- &mut select.as_mut(),- mem,- &Schema::default(),- )?);+ plan = Some(rewrite_select(&mut select.as_mut(), mem, &Schema::default())?.0);
Ok::<_, Error>(select)
})?;
Ok((
diff --git a/pgdog/src/frontend/router/parser/rewrite/statement/projection.rs b/pgdog/src/frontend/router/parser/rewrite/statement/projection.rsindex b2a75417e..47d669999 100644--- a/pgdog/src/frontend/router/parser/rewrite/statement/projection.rs+++ b/pgdog/src/frontend/router/parser/rewrite/statement/projection.rs@@ -326,26 +326,23 @@ fn build(
let rewritten = make::try_owned(|mem| {
let mut select = mem.make_unique(select);
if has_having {
- let having = having::rewrite_select(&mut select.as_mut(), mem, schema)?;- let rewritten_aggregate = Aggregate::parse(&select, schema);+ let (having, group_by) = having::rewrite_select(&mut select.as_mut(), mem, schema)?;+ // Aggregate::parse skips GROUP BY items it cannot resolve. The+ // rewrite has a position for every one of them, so unrelated+ // groups are never merged together.+ let rewritten_aggregate = Aggregate::parse(&select, schema).with_group_by(group_by);
if rewritten_aggregate.targets().is_empty() {
return Err(Error::UnsupportedHaving(
"at least one aggregate function is required",
));
}
- // Aggregate::parse skips GROUP BY items it cannot resolve. Require- // every item here so unrelated groups are never merged together.- if rewritten_aggregate.group_by().len() != select.group_clause().len() {- return Err(Error::UnsupportedHaving(- "every GROUP BY expression must be a selected column or ordinal",- ));- }
plan.having = Some(Box::new(HavingRewriteResult::new(
having,
rewritten_aggregate,
)));
} else if let Some(aggregate) = &aggregate {
- plan.group_by = group_by::rewrite_select(&mut select, mem, aggregate)?.map(Box::new);+ plan.group_by =+ group_by::rewrite_select(&mut select, mem, aggregate, schema)?.map(Box::new);
}
let aggregate = plan
.aggregate()
@@ -448,7 +445,10 @@ impl HiddenAliases {
#[cfg(test)]
mod tests {
+ use std::collections::HashMap;+
use super::*;
+ use crate::backend::schema::relation::Relation;
#[test]
fn rejects_count_sort_key() {
@@ -750,12 +750,143 @@ mod tests {
}
}
+ /// A schema with `items (id, category, region)`.+ fn items_schema() -> Schema {+ let columns = ["id", "category", "region"]+ .into_iter()+ .map(|name| {+ let column = pgdog_stats::Column {+ column_name: name.into(),+ ..Default::default()+ };+ (name.to_owned(), column.into())+ })+ .collect();+ let relation = Relation::test_table("public", "items", columns);+ Schema::from_parts(+ vec!["public".into()],+ HashMap::from([(("public".into(), "items".into()), relation)]),+ )+ }++ #[test]+ fn group_by_select_alias_is_merged_by_position() {+ let schema = items_schema();+ // (query, shard query if rewritten, merged GROUP BY columns)+ for (sql, shard_sql, group_by) in [+ // No table has a column `c`, so it's the SELECT alias.+ (+ "SELECT lower(category) AS c, count(*) FROM items GROUP BY c",+ None,+ vec![0],+ ),+ (+ "SELECT count(*), category AS c, region AS r FROM items i JOIN items j USING (id) GROUP BY r, c",+ None,+ vec![2, 1],+ ),+ // `items.category` wins over the alias, so the key is hidden.+ (+ "SELECT lower(category) AS category, count(*) FROM items GROUP BY category",+ Some(+ "SELECT lower(category) AS category, count(*), category AS __pgdog_group_0 \+ FROM items GROUP BY category",+ ),+ vec![2],+ ),+ ] {+ let ast = pg_raw_parse::parse(sql).unwrap();+ let Some(Node::SelectStmt(select)) = ast.stmts().next() else {+ panic!("expected SELECT")+ };+ let rewrite = build(&ast, &schema, false).unwrap();+ assert_eq!(+ rewrite.as_ref().map(|rewrite| rewrite.sql.as_ref()),+ shard_sql,+ "{sql}"+ );+ let aggregate = match rewrite.as_ref().and_then(|rewrite| rewrite.plan.as_ref()) {+ Some(plan) => plan.aggregate().expect("GROUP BY plan").clone(),+ None => Aggregate::parse(select, &schema),+ };+ assert_eq!(aggregate.group_by(), group_by, "{sql}");+ }+ }++ #[test]+ fn group_by_select_alias_is_rejected_when_from_is_not_known() {+ let loaded = items_schema();+ let not_loaded = Schema::default();+ for (sql, schema) in [+ (+ "SELECT lower(category) AS c, count(*) FROM unknown GROUP BY c",+ &loaded,+ ),+ (+ "SELECT lower(category) AS c, count(*) FROM (SELECT * FROM items) i GROUP BY c",+ &loaded,+ ),+ (+ "WITH items AS (SELECT 1 AS c) SELECT c + 1 AS c, count(*) FROM items GROUP BY c",+ &loaded,+ ),+ (+ "SELECT lower(category) AS c, count(*) FROM items AS i (c, d, e) GROUP BY c",+ &loaded,+ ),+ (+ "SELECT lower(category) AS ctid, count(*) FROM items GROUP BY ctid",+ &loaded,+ ),+ (+ "SELECT lower(category) AS c, count(*) FROM items GROUP BY c HAVING count(*) > 1",+ ¬_loaded,+ ),+ ] {+ let ast = pg_raw_parse::parse(sql).unwrap();+ let error = build(&ast, schema, false).unwrap_err();+ assert!(+ matches!(+ error,+ Error::UnsupportedGroupBy(_) | Error::UnsupportedHaving(_)+ ),+ "{sql}"+ );+ }+ }++ #[test]+ fn having_groups_by_alias_and_expression() {+ let schema = items_schema();+ // (query, shard query, merged GROUP BY columns)+ for (sql, shard_sql, group_by) in [+ (+ "SELECT lower(category) AS c, count(*) FROM items GROUP BY c HAVING count(*) > 1",+ "SELECT lower(category) AS c, count(*), count(*) AS __pgdog_having_0 \+ FROM items GROUP BY c HAVING count(*) > 1 OR true",+ vec![0],+ ),+ (+ "SELECT count(*) FROM items GROUP BY lower(category), 1 HAVING count(*) > 1",+ "SELECT count(*), count(*) AS __pgdog_having_0, lower(category) AS __pgdog_having_1 \+ FROM items GROUP BY lower(category), 1 HAVING count(*) > 1 OR true",+ vec![2, 0],+ ),+ ] {+ let ast = pg_raw_parse::parse(sql).unwrap();+ let rewrite = build(&ast, &schema, false)+ .unwrap()+ .expect("HAVING requires a rewrite");+ assert_eq!(rewrite.sql.as_ref(), shard_sql, "{sql}");+ let plan = rewrite.plan.expect("projection plan");+ let aggregate = plan.having.as_ref().expect("HAVING plan").aggregate();+ assert_eq!(aggregate.group_by(), group_by, "{sql}");+ }+ }+
#[test]
fn unsupported_having_queries_fail_closed() {
for sql in [
- // Groups can only be matched across shards on columns and ordinals.- "SELECT lower(customer_id), COUNT(*) FROM purchases \- GROUP BY lower(customer_id) HAVING COUNT(*) > 2",
// Star expansion shifts the positions aggregates are merged by.
"SELECT * FROM purchases GROUP BY customer_id HAVING COUNT(*) > 2",
"SELECT p.*, COUNT(*) FROM purchases p GROUP BY customer_id HAVING COUNT(*) > 2",