[tarantool-patches] Re: [PATCH v1 1/1] sql: support HAVING without GROUP BY clause

[Kirill Shcherbatov]

> 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
-    ]], {
+    ]],
         -- <count-2.9>
-        1, "a GROUP BY clause is required before HAVING"
+        {}
         -- </count-2.9>
-    })
+    )
 
 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
 ]], {
   -- <select3-3.1>
-  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"
   -- </select3-3.1>
 })
 
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(
         -- </select5-8.8>
     })
 
+--
+-- 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;
+    ]], {
+    -- <select6-1.1>
+    1, "SQL error: HAVING expression must appear in the GROUP BY clause or be used in an aggregate function"
+    -- <select6-1.1>
+})
+
+test:do_catchsql_test(
+    "select6-1.2",
+    [[
+        SELECT SUM(s1) FROM te40 HAVING s1 = 2;
+    ]], {
+    -- <select6-1.2>
+    1, "SQL error: HAVING expression must appear in the GROUP BY clause or be used in an aggregate function"
+    -- <select6-1.2>
+})
+
+test:do_catchsql_test(
+    "select6-1.3",
+    [[
+        SELECT s1 FROM te40 HAVING SUM(s1) = 2;
+    ]], {
+    -- <select6-1.3>
+    1, "SQL error: HAVING expression must appear in the GROUP BY clause or be used in an aggregate function"
+    -- <select6-1.3>
+})
+
+test:do_execsql_test(
+    "select6-1.4",
+    [[
+        SELECT SUM(s1) FROM te40 HAVING SUM(s1) > 0;
+    ]], {
+    -- <select6-1.4>
+    3
+    -- <select6-1.4>
+})
+
+test:do_execsql_test(
+    "select6-2.1",
+    [[
+        SELECT MIN(s1) FROM te40 HAVING SUM(s1) > 0;
+    ]], {
+    -- <select6-2.1>
+    1
+    -- <select6-2.1>
+})
+
+test:do_execsql_test(
+    "select6-3.1",
+    [[
+        SELECT SUM(s1) FROM te40 HAVING SUM(s1) < 0;
+    ]],
+    -- <select6-3.1>
+    {}
+    -- <select6-3.1>
+)
+
 test:finish_test()
 
-- 
2.7.4