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 0190528D73 for ; Wed, 29 Aug 2018 09:29:12 -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 xp3qXgcrlxeD for ; Wed, 29 Aug 2018 09:29:11 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 58F901FC99 for ; Wed, 29 Aug 2018 09:29:11 -0400 (EDT) Subject: [tarantool-patches] Re: [PATCH v1 1/1] sql: support HAVING without GROUP BY clause References: <16D5B1D2-2DBF-4BAE-9C5D-F23CCCE3723D@tarantool.org> From: Kirill Shcherbatov Message-ID: <5f602cc5-9617-6717-c13a-4d5595c23422@tarantool.org> Date: Wed, 29 Aug 2018 16:29:06 +0300 MIME-Version: 1.0 In-Reply-To: <16D5B1D2-2DBF-4BAE-9C5D-F23CCCE3723D@tarantool.org> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit 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, Nikita Pettik > Please, make it behave the same way as other DBs > and resend patch. ==================================== Allowed to use HAVING without GROUP BY when both select list and having clause have aggregate function. Closes 2364. @TarantoolBot document Title: HAVING without GROUP BY clause A query with a having clause should also have a group by clause. Another ability is to use aggregate function both in select list and having clause. 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(-) 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 &= ~NC_AllowAgg; } - /* 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 != NULL && pGroupBy == NULL) { + struct NameContext having_nc; + memset(&having_nc, 0, sizeof(having_nc)); + having_nc.pParse = pParse; + having_nc.ncFlags = NC_AllowAgg; + having_nc.pSrcList = p->pSrc; + if ((p->selFlags & SF_Aggregate) != 0 && + sqlite3ResolveExprNames(&having_nc, + p->pHaving) != 0) + return WRC_Abort; + if ((having_nc.ncFlags & NC_HasAgg) == 0) { + const char *err_msg = + tt_sprintf("HAVING expression must " + "appear in the GROUP BY " + "clause or be used in an " + "aggregate function"); + diag_set(ClientError, ER_SQL, err_msg); + pParse->nErr++; + pParse->rc = SQL_TARANTOOL_ERROR; + return WRC_Abort; + } } /* Add the output column list to the name-context before parsing the diff --git a/test/sql-tap/count.test.lua b/test/sql-tap/count.test.lua index 45808de..700d047 100755 --- a/test/sql-tap/count.test.lua +++ b/test/sql-tap/count.test.lua @@ -172,15 +172,15 @@ test:do_test( return uses_op_count("SELECT count(*) FROM t2 WHERE a IS NOT NULL") end, 0) -test:do_catchsql_test( +test:do_execsql_test( "count-2.9", [[ SELECT count(*) FROM t2 HAVING count(*)>1 - ]], { + ]], -- - 1, "a GROUP BY clause is required before HAVING" + {} -- - }) + ) test:do_test( "count-2.10", diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua index d49bb87..33a0359 100755 --- a/test/sql-tap/select3.test.lua +++ b/test/sql-tap/select3.test.lua @@ -200,7 +200,7 @@ test:do_catchsql_test("select3-3.1", [[ SELECT log, count(*) FROM t1 HAVING log>=4 ]], { -- - 1, "a GROUP BY clause is required before HAVING" + 1, "SQL error: HAVING expression must appear in the GROUP BY clause or be used in an aggregate function" -- }) 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 = require("sqltester") -test:plan(32) +test:plan(38) --!./tcltestrunner.lua -- 2001 September 15 @@ -412,5 +412,71 @@ test:do_execsql_test( -- }) +-- +-- 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 = 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 = 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) = 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", + [[ + SELECT MIN(s1) FROM te40 HAVING SUM(s1) > 0; + ]], { + -- + 1 + -- +}) + +test:do_execsql_test( + "select6-3.1", + [[ + SELECT SUM(s1) FROM te40 HAVING SUM(s1) < 0; + ]], + -- + {} + -- +) + test:finish_test() -- 2.7.4