pg.ddx.io  pgdogdev/pgdog GitHub issues and pull requests  
help / color / mirror / Atom feed
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.sql new file mode 100644 index 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.sql new file mode 100644 index 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.sql new file mode 100644 index 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.rs index 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.rs index 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.rs index 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.rs index 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.rs index 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", + &not_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",

view thread (8+ messages)  latest in thread

Message-ID: <gh-pgdogdev-pgdog-2050@github.com>
Permalink:  ../gh-pgdogdev-pgdog-2050@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