From 36379e973bb17016d910707315975f64a19c6e0f Mon Sep 17 00:00:00 2001
From: drh <drh@noemail.net>
Date: Mon, 23 Jul 2007 22:51:15 +0000
Subject: [PATCH] Detect nested aggregate functions even if the inner aggregate
 function is aliased using an AS clause.  Ticket #2526. (CVS 4179)

FossilOrigin-Name: de000280c6d0b13440d2450eb6ba42073ad46c56
---
 manifest          | 14 +++++++-------
 manifest.uuid     |  2 +-
 src/expr.c        | 12 +++++++++---
 test/select1.test | 27 ++++++++++++++++++++++++++-
 4 files changed, 43 insertions(+), 12 deletions(-)

diff --git a/manifest b/manifest
index f44ed88103..c591138893 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C The\sREPLACE\sconflict\sresolution\sfalls\sback\sto\sFAIL\swhen\son\sa\nCHECK\sconstraint\sviolation.\s\sTicket\s#2525.\s(CVS\s4178)
-D 2007-07-23T19:39:47
+C Detect\snested\saggregate\sfunctions\seven\sif\sthe\sinner\saggregate\sfunction\sis\naliased\susing\san\sAS\sclause.\s\sTicket\s#2526.\s(CVS\s4179)
+D 2007-07-23T22:51:15
 F Makefile.in 0c0e53720f658c7a551046442dd7afba0b72bfbe
 F Makefile.linux-gcc 65241babba6faf1152bf86574477baab19190499
 F README 9c4e2d6706bdcc3efdd773ce752a8cdab4f90028
@@ -77,7 +77,7 @@ F src/complete.c 7d1a44be8f37de125fcafd3d3a018690b3799675
 F src/date.c 6049db7d5a8fdf2c677ff7d58fa31d4f6593c988
 F src/delete.c 5c0d89b3ef7d48fe1f5124bfe8341f982747fe29
 F src/experimental.c 1b2d1a6cd62ecc39610e97670332ca073c50792b
-F src/expr.c de9f55b1baed00199466028ad96967208d487798
+F src/expr.c d39d87cf15da59ab87028278d92e3e3064d54605
 F src/func.c dcba54fc18d2b2fd02f8b7c3dc13e27d100a4d8e
 F src/hash.c 67b23e14f0257b69a3e8aa663e4eeadc1a2b6fd5
 F src/hash.h 1b3f7e2609141fd571f62199fc38687d262e9564
@@ -347,7 +347,7 @@ F test/rowid.test d125991eea1ffdea800d48471afd8fc4acc10b01
 F test/safety.test 4a06934e45d03b8b50ebcd8d174eb0367d2fd851
 F test/schema.test b479341d04cc40a11f47929b0198c07ddd6b2565
 F test/schema2.test b438d2c7fd627227f405887c2328b4aed5dad012
-F test/select1.test 1a35bf8201c8a42a44d65acc3e6c9796a9c43dfb
+F test/select1.test 7603a4d406ea00516233e26539d2fac0cc85e732
 F test/select2.test f3c2678c3a9f3cf08ec4988a3845bda64be6d9e3
 F test/select3.test 47439f28862489626b483b0c718cfb0562e6f6d5
 F test/select4.test 305ba0a6e97efc5544def5e5cb49b54e1bf87fd9
@@ -520,7 +520,7 @@ F www/tclsqlite.tcl 8be95ee6dba05eabcd27a9d91331c803f2ce2130
 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0
 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b
 F www/whentouse.tcl fc46eae081251c3c181bd79c5faef8195d7991a5
-P bc02c7f1621ff46707f147c75f7e98462b01907f
-R 2049a5db1d0bbd632d2230f714f15f27
+P b213614abff90c65cc7228c87f4973150e8a9679
+R 0fa1afe917039c6d6220f9448ebeacb8
 U drh
-Z 3942dc4034b0e9a4c4fc0fcd7bf61431
+Z 2aeee86ab0edd2e20bd18cb1e94315fd
diff --git a/manifest.uuid b/manifest.uuid
index c5d93d42dd..473fee7a98 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-b213614abff90c65cc7228c87f4973150e8a9679
\ No newline at end of file
+de000280c6d0b13440d2450eb6ba42073ad46c56
\ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index 9a44c122d3..415b0b951e 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -12,7 +12,7 @@
 ** This file contains routines used for analyzing expressions and
 ** for generating VDBE code that evaluates expressions in SQLite.
 **
-** $Id: expr.c,v 1.300 2007/06/25 16:29:34 danielk1977 Exp $
+** $Id: expr.c,v 1.301 2007/07/23 22:51:15 drh Exp $
 */
 #include "sqliteInt.h"
 #include <ctype.h>
@@ -1140,11 +1140,17 @@ static int lookupName(
       for(j=0; j<pEList->nExpr; j++){
         char *zAs = pEList->a[j].zName;
         if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){
-          Expr *pDup;
+          Expr *pDup, *pOrig;
           assert( pExpr->pLeft==0 && pExpr->pRight==0 );
           assert( pExpr->pList==0 );
           assert( pExpr->pSelect==0 );
-          pDup = sqlite3ExprDup(pEList->a[j].pExpr);
+          pOrig = pEList->a[j].pExpr;
+          if( !pNC->allowAgg && ExprHasProperty(pOrig, EP_Agg) ){
+            sqlite3ErrorMsg(pParse, "misuse of aliased aggregate %s", zAs);
+            sqliteFree(zCol);
+            return 2;
+          }
+          pDup = sqlite3ExprDup(pOrig);
           if( pExpr->flags & EP_ExpCollate ){
             pDup->pColl = pExpr->pColl;
             pDup->flags |= EP_ExpCollate;
diff --git a/test/select1.test b/test/select1.test
index c5ecd860cb..0393145e79 100644
--- a/test/select1.test
+++ b/test/select1.test
@@ -11,7 +11,7 @@
 # This file implements regression tests for SQLite library.  The
 # focus of this file is testing the SELECT statement.
 #
-# $Id: select1.test,v 1.53 2007/04/13 16:06:34 drh Exp $
+# $Id: select1.test,v 1.54 2007/07/23 22:51:15 drh Exp $
 
 set testdir [file dirname $argv0]
 source $testdir/tester.tcl
@@ -225,6 +225,31 @@ do_test select1-2.20 {
   lappend v $msg
 } {1 {misuse of aggregate function min()}}
 
+# Ticket #2526
+#
+do_test select1-2.21 {
+  catchsql {
+     SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10
+  }
+} {1 {misuse of aliased aggregate m}}
+do_test select1-2.22 {
+  catchsql {
+     SELECT coalesce(min(f1)+5,11) AS m FROM test1
+      GROUP BY f1
+     HAVING max(m+5)<10
+  }
+} {1 {misuse of aliased aggregate m}}
+do_test select1-2.23 {
+  execsql {
+    CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
+    INSERT INTO tkt2526 VALUES('x','y',NULL);
+    INSERT INTO tkt2526 VALUES('x','z',NULL);
+  }
+  catchsql {
+    SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
+  }
+} {1 {misuse of aliased aggregate cn}}
+
 # WHERE clause expressions
 #
 do_test select1-3.1 {
-- 
2.47.3