From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D420624EBD for ; Mon, 3 Sep 2018 10:15:11 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id jp1cvmOoMbta for ; Mon, 3 Sep 2018 10:15:11 -0400 (EDT) Received: from smtp46.i.mail.ru (smtp46.i.mail.ru [94.100.177.106]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 847D62A54E for ; Mon, 3 Sep 2018 09:23:13 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: support HAVING without GROUP BY clause From: "n.pettik" In-Reply-To: <5f602cc5-9617-6717-c13a-4d5595c23422@tarantool.org> Date: Mon, 3 Sep 2018 16:23:10 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <99A1FBB4-3831-4BB6-A1D6-505D09F792AD@tarantool.org> References: <16D5B1D2-2DBF-4BAE-9C5D-F23CCCE3723D@tarantool.org> <5f602cc5-9617-6717-c13a-4d5595c23422@tarantool.org> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org Cc: Kirill Shcherbatov > On 29 Aug 2018, at 16:29, Kirill Shcherbatov = wrote: >=20 >> Please, make it behave the same way as other DBs >> and resend patch. Still your patch doesn=E2=80=99t handle some cases. Example: Postgres: drop table if exists t1 \\ create table t1(id int primary key, a int, b int) \\ insert into t1 values(1, -2,3), (4,5,6), (7,8,9) \\ SELECT SUM(a),a FROM t1 HAVING sum(a) > 0 \\ 42803: column "t1.a" must appear in the GROUP BY clause or be used in an = aggregate function db2: db2 =3D> SELECT SUM(a), a from t1 having sum(a) > 0 SQL0119N An expression starting with "A" specified in a SELECT clause, = HAVING=20 clause, or ORDER BY clause is not specified in the GROUP BY clause or it = is in=20 a SELECT clause, HAVING clause, or ORDER BY clause with a column = function and=20 no GROUP BY clause is specified. SQLSTATE=3D42803 Oracle: ORA-00937: not a single-group group function Tarantool: tarantool> SELECT SUM(a),a FROM t1 HAVING sum(a) > 0 --- - - [11, 8] ... >=20 > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D >=20 > Allowed to use HAVING without GROUP BY when both select list > and having clause have aggregate function. >=20 > Closes 2364. >=20 > @TarantoolBot document > Title: HAVING without GROUP BY clause > A query with a having clause should also have a group by clause. Nit: outline also here HAVING and GROUP BY with capital letters pls.=20 > Another ability is to use aggregate function both in select list > and having clause. I would re-phrase it like: However, it is still possible to drop GROUP BY clause in case both = SELECT and HAVING arguments are aggregate functions > Example: > SELECT SUM(s1) FROM te40 HAVING SUM(s1) > 0; > --- > src/box/sql/resolve.c | 30 +++++++++++++++---- > test/sql-tap/count.test.lua | 8 ++--- > test/sql-tap/select3.test.lua | 2 +- > test/sql-tap/select5.test.lua | 68 = ++++++++++++++++++++++++++++++++++++++++++- > 4 files changed, 97 insertions(+), 11 deletions(-) >=20 > diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c > index 280ecd9..9d28089 100644 > --- a/src/box/sql/resolve.c > +++ b/src/box/sql/resolve.c > @@ -1304,12 +1304,32 @@ resolveSelectStep(Walker * pWalker, Select * = p) > sNC.ncFlags &=3D ~NC_AllowAgg; > } >=20 > - /* If a HAVING clause is present, then there must be a = GROUP BY clause. > + /* > + * If a HAVING clause is present, then there must > + * be a GROUP BY clause or aggregate function > + * should be specified. > */ > - if (p->pHaving && !pGroupBy) { > - sqlite3ErrorMsg(pParse, > - "a GROUP BY clause is required = before HAVING"); > - return WRC_Abort; > + if (p->pHaving !=3D NULL && pGroupBy =3D=3D NULL) { > + struct NameContext having_nc; > + memset(&having_nc, 0, sizeof(having_nc)); > + having_nc.pParse =3D pParse; > + having_nc.ncFlags =3D NC_AllowAgg; > + having_nc.pSrcList =3D p->pSrc; > + if ((p->selFlags & SF_Aggregate) !=3D 0 && > + sqlite3ResolveExprNames(&having_nc, > + p->pHaving) !=3D = 0) > + return WRC_Abort; > + if ((having_nc.ncFlags & NC_HasAgg) =3D=3D 0) { > + const char *err_msg =3D > + tt_sprintf("HAVING expression = must =E2=80=9C Nit: HAVING is rather clause, than expression. Overall, this msg looks a little bit weird. In Postgres it is like: column "t1.a" must appear in the GROUP BY clause or be used in an = aggregate function So, not HAVING clause must appear, but HAVING argument. > + "appear in the GROUP = BY " > + "clause or be used in = an " > + "aggregate = function"); > + diag_set(ClientError, ER_SQL, err_msg); > + pParse->nErr++; > + pParse->rc =3D SQL_TARANTOOL_ERROR; > + return WRC_Abort; > + } > } >=20 > /* Add the output column list to the name-context before = parsing the >=20 >=20 > diff --git a/test/sql-tap/select5.test.lua = b/test/sql-tap/select5.test.lua > index 7ca25ae..db3f447 100755 > --- a/test/sql-tap/select5.test.lua > +++ b/test/sql-tap/select5.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test =3D require("sqltester") > -test:plan(32) > +test:plan(38) >=20 > --!./tcltestrunner.lua > -- 2001 September 15 > @@ -412,5 +412,71 @@ test:do_execsql_test( > -- > }) >=20 > +-- > +-- gh-2364: Support HAVING without GROUP BY clause. > +-- > +test:do_catchsql_test( > + "select6-1.1", > + [[ > + CREATE TABLE te40 (s1 INT, s2 INT, PRIMARY KEY (s1,s2)); > + INSERT INTO te40 VALUES (1,1); > + INSERT INTO te40 VALUES (2,2); > + SELECT s1 FROM te40 HAVING s1 =3D 1; > + ]], { > + -- > + 1, "SQL error: HAVING expression must appear in the GROUP BY = clause or be used in an aggregate function" > + -- > +}) > + > +test:do_catchsql_test( > + "select6-1.2", > + [[ > + SELECT SUM(s1) FROM te40 HAVING s1 =3D 2; > + ]], { > + -- > + 1, "SQL error: HAVING expression must appear in the GROUP BY = clause or be used in an aggregate function" > + -- > +}) > + > +test:do_catchsql_test( > + "select6-1.3", > + [[ > + SELECT s1 FROM te40 HAVING SUM(s1) =3D 2; > + ]], { > + -- > + 1, "SQL error: HAVING expression must appear in the GROUP BY = clause or be used in an aggregate function" > + -- > +}) > + > +test:do_execsql_test( > + "select6-1.4", > + [[ > + SELECT SUM(s1) FROM te40 HAVING SUM(s1) > 0; > + ]], { > + -- > + 3 > + -- > +}) > + > +test:do_execsql_test( > + "select6-2.1=E2=80=9D, Why 2.1 and not 1.5? The same for tests below.