public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: GROUP BY ALL
Date: Sun, 18 Dec 2022 20:19:10 -0800
Message-ID: <CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com> (raw)
Hi hackers!
I saw a thread in a social network[0] about GROUP BY ALL. The idea seems useful.
I always was writing something like
select datname, usename, count(*) from pg_stat_activity group by 1,2;
and then rewriting to
select datname, usename, query, count(*) from pg_stat_activity group by 1,2;
and then "aaahhhh, add a number at the end".
With the proposed feature I can write just
select datname, usename, count(*) from pg_stat_activity group by all;
PFA very dummy implementation just for a discussion. I think we can
add all non-aggregating targets.
What do you think?
Best regards, Andrey Borodin.
[0] https://www.linkedin.com/posts/mosha_duckdb-firebolt-snowflake-activity-7009615821006131200-VQ0o/
Attachments:
[application/octet-stream] v1-0001-Implement-GROUP-BY-ALL.patch (5.6K, 2-v1-0001-Implement-GROUP-BY-ALL.patch)
download | inline diff:
From e5f9ca89d577926155cc94e0ea5b5bbfefbd331d Mon Sep 17 00:00:00 2001
From: Andrey Borodin <[email protected]>
Date: Sun, 18 Dec 2022 19:52:48 -0800
Subject: [PATCH v1] Implement GROUP BY ALL
---
src/backend/parser/analyze.c | 1 +
src/backend/parser/gram.y | 14 ++++++++++++++
src/backend/parser/parse_agg.c | 23 ++++++++++++++++++++++-
src/backend/utils/adt/ruleutils.c | 3 +++
src/backend/utils/misc/queryjumble.c | 1 +
src/include/nodes/parsenodes.h | 2 ++
6 files changed, 43 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6688c2a865..71d12ead79 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1347,6 +1347,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_GROUP_BY,
false /* allow SQL92 rules */ );
qry->groupDistinct = stmt->groupDistinct;
+ qry->groupAll = stmt->groupAll;
if (stmt->distinctClause == NIL)
{
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b1ae5f834c..84f8a4146a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -135,6 +135,7 @@ typedef struct SelectLimit
typedef struct GroupClause
{
bool distinct;
+ bool all;
List *list;
} GroupClause;
@@ -12580,6 +12581,7 @@ simple_select:
n->whereClause = $6;
n->groupClause = ($7)->list;
n->groupDistinct = ($7)->distinct;
+ n->groupAll = ($7)->all;
n->havingClause = $8;
n->windowClause = $9;
$$ = (Node *) n;
@@ -12597,6 +12599,7 @@ simple_select:
n->whereClause = $6;
n->groupClause = ($7)->list;
n->groupDistinct = ($7)->distinct;
+ n->groupAll = ($7)->all;
n->havingClause = $8;
n->windowClause = $9;
$$ = (Node *) n;
@@ -13074,14 +13077,25 @@ group_clause:
GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause));
n->distinct = $3 == SET_QUANTIFIER_DISTINCT;
+ n->all = false;
n->list = $4;
$$ = n;
}
+ | GROUP_P BY ALL
+ {
+ GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause));
+
+ n->all = true;
+ n->distinct = false;
+ n->list = NIL;
+ $$ = n;
+ }
| /*EMPTY*/
{
GroupClause *n = (GroupClause *) palloc(sizeof(GroupClause));
n->distinct = false;
+ n->all = false;
n->list = NIL;
$$ = n;
}
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 3ef9e8ee5e..8826829dbc 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -20,6 +20,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/optimizer.h"
+#include "parser/analyze.h"
#include "parser/parse_agg.h"
#include "parser/parse_clause.h"
#include "parser/parse_coerce.h"
@@ -1072,7 +1073,27 @@ parseCheckAggregates(ParseState *pstate, Query *qry)
Node *clause;
/* This should only be called if we found aggregates or grouping */
- Assert(pstate->p_hasAggs || qry->groupClause || qry->havingQual || qry->groupingSets);
+ Assert(pstate->p_hasAggs || qry->groupClause || qry->havingQual || qry->groupingSets || qry->groupAll);
+
+ Assert((!qry->groupAll) || (qry->groupClause == NULL));
+
+ if (qry->groupAll)
+ {
+ Index idx = 1;
+ Index sge_idx = 1;
+ foreach(l, qry->targetList)
+ {
+ TargetEntry *tle = lfirst(l);
+ if (IsA(tle->expr, Var))
+ {
+ Oid restype = exprType((Node *) tle->expr);
+ SortGroupClause *sgc = makeSortGroupClauseForSetOp(restype, false);
+ sgc->tleSortGroupRef = sge_idx++;
+ qry->groupClause = lappend(qry->groupClause, sgc);
+ tle->ressortgroupref = idx++;
+ }
+ }
+ }
/*
* If we have grouping sets, expand them and find the intersection of all
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index a20a1b069b..d0d4711c53 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5959,6 +5959,9 @@ get_basic_select_query(Query *query, deparse_context *context,
if (query->groupDistinct)
appendStringInfoString(buf, "DISTINCT ");
+ if (query->groupAll)
+ appendStringInfoString(buf, "ALL ");
+
save_exprkind = context->special_exprkind;
context->special_exprkind = EXPR_KIND_GROUP_BY;
diff --git a/src/backend/utils/misc/queryjumble.c b/src/backend/utils/misc/queryjumble.c
index 0ace74de78..feac9aa8b2 100644
--- a/src/backend/utils/misc/queryjumble.c
+++ b/src/backend/utils/misc/queryjumble.c
@@ -254,6 +254,7 @@ JumbleQueryInternal(JumbleState *jstate, Query *query)
JumbleExpr(jstate, (Node *) query->returningList);
JumbleExpr(jstate, (Node *) query->groupClause);
APP_JUMB(query->groupDistinct);
+ APP_JUMB(query->groupAll);
JumbleExpr(jstate, (Node *) query->groupingSets);
JumbleExpr(jstate, query->havingQual);
JumbleExpr(jstate, (Node *) query->windowClause);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6112cd85c8..5a0b1a43cf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -170,6 +170,7 @@ typedef struct Query
List *groupClause; /* a list of SortGroupClause's */
bool groupDistinct; /* is the group by clause distinct? */
+ bool groupAll;
List *groupingSets; /* a list of GroupingSet's if present */
@@ -1737,6 +1738,7 @@ typedef struct SelectStmt
Node *whereClause; /* WHERE qualification */
List *groupClause; /* GROUP BY clauses */
bool groupDistinct; /* Is this GROUP BY DISTINCT? */
+ bool groupAll;
Node *havingClause; /* HAVING conditional-expression */
List *windowClause; /* WINDOW window_name AS (...), ... */
--
2.37.0 (Apple Git-136)
view thread (42+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: GROUP BY ALL
In-Reply-To: <CAAhFRxjyTO5BHn9y1oOSEp0TtpTDTTTb7HJBNhTG+i3-hXC0XQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox