From 2e30d95fb63e73cbae3cf07b0ac57d8d7e0321ba Mon Sep 17 00:00:00 2001
From: drh <>
Date: Wed, 23 Nov 2022 18:51:04 +0000
Subject: [PATCH] Aggregates with GROUP BY now make use of expressions on
 indexes.  This code works and gets the correct answer for the test case in
 the ticket.  Lots more testing and documentation is needed, however.

FossilOrigin-Name: 8dcf9f2031c16f296d187fe876d4204c71fc96fec120984ff11b6d8b03d58a5f
---
 manifest      | 14 +++++++-------
 manifest.uuid |  2 +-
 src/expr.c    |  6 ++++--
 src/select.c  | 48 +++++++++++++++++++++++++++++++++++++++++++++++-
 4 files changed, 59 insertions(+), 11 deletions(-)

diff --git a/manifest b/manifest
index 294477b047..5a5586e90e 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C This\sattempt\sat\smodifying\sAggInfo\sto\smake\suse\sof\sindexed\sexpressions\sdoes\snot\nwork.\s\sIt\sgets\san\sincorrect\sanswer\sfor\sthe\stest\scase\sshown\sin\sthe\sticket.
-D 2022-11-23T17:56:00.136
+C Aggregates\swith\sGROUP\sBY\snow\smake\suse\sof\sexpressions\son\sindexes.\s\sThis\scode\nworks\sand\sgets\sthe\scorrect\sanswer\sfor\sthe\stest\scase\sin\sthe\sticket.\s\sLots\smore\ntesting\sand\sdocumentation\sis\sneeded,\showever.
+D 2022-11-23T18:51:04.363
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -591,7 +591,7 @@ F src/date.c 94ce83b4cd848a387680a5f920c9018c16655db778c4d36525af0a0f34679ac5
 F src/dbpage.c f1a87f4ebcf22284e0aaf0697862f4ccfc120dcd6db3d8dfa3b049b2580c01d8
 F src/dbstat.c 861e08690fcb0f2ee1165eff0060ea8d4f3e2ea10f80dab7d32ad70443a6ff2d
 F src/delete.c 86573edae75e3d3e9a8b590d87db8e47222103029df4f3e11fa56044459b514e
-F src/expr.c 527fc56468f75c380a34d38426cc1deece693a3cc6139fb45544923ff461d569
+F src/expr.c 141af8139174010ab37591df6234a647ecc38b41f72ac6e2c128ebcf167e0bc0
 F src/fault.c 460f3e55994363812d9d60844b2a6de88826e007
 F src/fkey.c 722f20779f5342a787922deded3628d8c74b5249cab04098cf17ee2f2aaff002
 F src/func.c 7e86074afc4dc702691a29b7801f6dcc191db092b52e8bbe69dcd2f7be52194d
@@ -641,7 +641,7 @@ F src/printf.c e99ee9741e79ae3873458146f59644276657340385ade4e76a5f5d1c25793764
 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
 F src/resolve.c efea4e5fbecfd6d0a9071b0be0d952620991673391b6ffaaf4c277b0bb674633
 F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
-F src/select.c 8ac7f73d40d5615cdb73660a85e05ee26943a618ec5c7262160994f263fa7178
+F src/select.c f253214cbd2458f744b95fc6bf8dee103cecf4b051f620fd5f62b43dbab92ec1
 F src/shell.c.in 7d1705f139e6762e8c0fe254a8ebf3ab77aec6d8366f033cdd5f5ebadefbbb20
 F src/sqlite.h.in 100fc660c2f19961b8ed8437b9d53d687de2f8eb2b96437ec6da216adcb643ca
 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
@@ -2059,8 +2059,8 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 23145fe999ff74d787a3999baedd4ffe755c5f1f1275082ed0338ba637ecc56e
-R f3df5c5148120a1bf76a131c870fdc09
+P 84c06023f4a1606664fdb9811312603b31f7c94a43d0e443ba7dde7fdba029e3
+R 2344df85f68d9987b55d50d38bac47c1
 U drh
-Z 97820e8b433aee285f0d7a5d438ada65
+Z 4c924f435822db601e489fbe4dcd4a9e
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index c7c9044f2a..56c84edce5 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-84c06023f4a1606664fdb9811312603b31f7c94a43d0e443ba7dde7fdba029e3
\ No newline at end of file
+8dcf9f2031c16f296d187fe876d4204c71fc96fec120984ff11b6d8b03d58a5f
\ No newline at end of file
diff --git a/src/expr.c b/src/expr.c
index db81983e4a..c34a28dc47 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -53,7 +53,7 @@ char sqlite3ExprAffinity(const Expr *pExpr){
   }
   op = pExpr->op;
   if( op==TK_REGISTER ) op = pExpr->op2;
-  if( op==TK_COLUMN || op==TK_AGG_COLUMN ){
+  if( op==TK_COLUMN || (op==TK_AGG_COLUMN && pExpr->y.pTab!=0) ){
     assert( ExprUseYTab(pExpr) );
     assert( pExpr->y.pTab!=0 );
     return sqlite3TableColumnAffinity(pExpr->y.pTab, pExpr->iColumn);
@@ -173,7 +173,9 @@ CollSeq *sqlite3ExprCollSeq(Parse *pParse, const Expr *pExpr){
   while( p ){
     int op = p->op;
     if( op==TK_REGISTER ) op = p->op2;
-    if( op==TK_AGG_COLUMN || op==TK_COLUMN || op==TK_TRIGGER ){
+    if( (op==TK_AGG_COLUMN && p->y.pTab!=0) 
+     || op==TK_COLUMN || op==TK_TRIGGER
+    ){
       int j;
       assert( ExprUseYTab(p) );
       assert( p->y.pTab!=0 );
diff --git a/src/select.c b/src/select.c
index 49bef86bab..dcb878dde9 100644
--- a/src/select.c
+++ b/src/select.c
@@ -6222,7 +6222,7 @@ static void printAggInfo(AggInfo *pAggInfo){
     sqlite3TreeViewExpr(0, pAggInfo->aCol[ii].pCExpr, 0);
   }
   for(ii=0; ii<pAggInfo->nFunc; ii++){
-    sqlite3DebugPrintf("agg-func[%d]: iMem=\n",
+    sqlite3DebugPrintf("agg-func[%d]: iMem=%d\n",
         ii, AggInfoFuncReg(pAggInfo,ii));
     sqlite3TreeViewExpr(0, pAggInfo->aFunc[ii].pFExpr, 0);
   }
@@ -6309,6 +6309,40 @@ static void optimizeAggregateUseOfIndexedExpr(
 #endif
 }
 
+/*
+** Walker callback for aggregateConvertIndexedExprRefToColumn().
+*/
+static int aggregateIdxEprRefToColCallback(Walker *pWalker, Expr *pExpr){
+  AggInfo *pAggInfo;
+  struct AggInfo_col *pCol;
+  if( pExpr->pAggInfo==0 ) return WRC_Continue;
+  if( pExpr->op==TK_AGG_COLUMN ) return WRC_Continue;
+  if( pExpr->op==TK_AGG_FUNCTION ) return WRC_Continue;
+  pAggInfo = pExpr->pAggInfo;
+  assert( pExpr->iAgg>=0 && pExpr->iAgg<pAggInfo->nColumn );
+  pCol = &pAggInfo->aCol[pExpr->iAgg];
+  pExpr->op = TK_AGG_COLUMN;
+  pExpr->iTable = pCol->iTable;
+  pExpr->iColumn = pCol->iColumn;
+  return WRC_Prune;
+}
+
+/*
+** Convert every pAggInfo->aFunc[].pExpr such that any node within
+** those expressions that has pAppInfo set is changed into a TK_AGG_COLUMN
+** opcode.
+*/
+static void aggregateConvertIndexedExprRefToColumn(AggInfo *pAggInfo){
+  int i;
+  Walker w;
+  memset(&w, 0, sizeof(w));
+  w.xExprCallback = aggregateIdxEprRefToColCallback;
+  for(i=0; i<pAggInfo->nFunc; i++){
+    sqlite3WalkExpr(&w, pAggInfo->aFunc[i].pFExpr);
+  }
+}
+
+
 /*
 ** Allocate a block of registers so that there is one register for each
 ** pAggInfo->aCol[] and pAggInfo->aFunc[] entry in pAggInfo.  The first
@@ -7669,6 +7703,18 @@ int sqlite3Select(
         pAggInfo->useSortingIdx = 1;
       }
 
+      if( pParse->pIdxEpr ){
+        aggregateConvertIndexedExprRefToColumn(pAggInfo);
+#if TREETRACE_ENABLED
+        if( sqlite3TreeTrace & 0x20 ){
+          TREETRACE(0x20, pParse, p,
+             ("AggInfo function expressions converted to reference index\n"));
+          sqlite3TreeViewSelect(0, p, 0);
+          printAggInfo(pAggInfo);
+        }
+#endif
+      }
+
       /* If the index or temporary table used by the GROUP BY sort
       ** will naturally deliver rows in the order required by the ORDER BY
       ** clause, cancel the ephemeral table open coded earlier.
-- 
2.47.2