Another fix to the indexed expressions in aggregate queries with GROUP BY

enhancement of ticket [99378177930f87bd] and implemented by
check-in [b9190d3da70c4171] to address a problem described by
[forum:/forumpost/f34e32d120|forum post f34e32d120],

FossilOrigin-Name: 5acc3ef83e16a9f2803981a6e61eca62fe177cac4eed1befe2ade3a5977a1eab

5 files changed, 41 insertions, 15 deletions

diff --git a/manifest b/manifest
index c6fee5bf8..789885d3b 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sthe\s--fullsync\soption\sto\sspeedtest1.
-D 2023-04-18T11:35:09.601
+C Another\sfix\sto\sthe\sindexed\sexpressions\sin\saggregate\squeries\swith\sGROUP\sBY\nenhancement\sof\sticket\s[99378177930f87bd]\sand\simplemented\sby\ncheck-in\s[b9190d3da70c4171]\sto\saddress\sa\sproblem\sdescribed\sby\n[forum:/forumpost/f34e32d120|forum\spost\sf34e32d120],
+D 2023-04-18T14:13:54.801
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -631,7 +631,7 @@ F src/printf.c 7eac1a9896a80697e03e08963e210830532ae2ff610e16c193e95af007ca5623
 F src/random.c 606b00941a1d7dd09c381d3279a058d771f406c5213c9932bbd93d5587be4b9c
 F src/resolve.c 3e53e02ce87c9582bd7e7d22f13f4094a271678d9dc72820fa257a2abb5e4032
 F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
-F src/select.c ad6f48e0953fb0d9dd434a7fcd8bc6bde05beb82500a59fea7a7b02dc0ecfb3b
+F src/select.c 4054886848b2aaf3d92d28bda9fabff3b5c5c1ffe8511b0dbafcea008d58a66b
 F src/shell.c.in 2a11419d77d8e4f7b4e3aea6922a9560630889423e3728205d77b4be2511cc89
 F src/sqlite.h.in 4fff9c6cc5d4cbba9532a668112efb6dc469c425e1a2196664d7c07d508363ef
 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
@@ -1201,7 +1201,7 @@ F test/index7.test b238344318e0b4e42126717f6554f0e7dfd0b39cecad4b736039b43e1e3b6
 F test/index8.test caa097735c91dbc23d8a402f5e63a2a03c83840ba3928733ed7f9a03f8a912a3
 F test/index9.test 2ac891806a4136ef3e91280477e23114e67575207dc331e6797fa0ed9379f997
 F test/indexedby.test f21eca4f7a6ffe14c8500a7ad6cd53166666c99e5ccd311842a28bc94a195fe0
-F test/indexexpr1.test b2a15637dcbae7fd8d7e2fc51f74ac4feaf5510130ee2089a5ec5bd1ef7270e1
+F test/indexexpr1.test 62558b1cfd7ccbe7bc015849cc6d1a13ef124e80cbd5b3a98dc66c3c9cce0cf4
 F test/indexexpr2.test 1c382e81ef996d8ae8b834a74f2a9013dddf59214c32201d7c8a656d739f999a
 F test/indexfault.test 98d78a8ff1f5335628b62f886a1cb7c7dac1ef6d48fa39c51ec871c87dce9811
 F test/init.test 15c823093fdabbf7b531fe22cf037134d09587a7
@@ -1625,7 +1625,7 @@ F test/tkt-868145d012.test a5f941107ece6a64410ca4755c6329b7eb57a356
 F test/tkt-8c63ff0ec.test 258b7fc8d7e4e1cb5362c7d65c143528b9c4cbed
 F test/tkt-91e2e8ba6f.test 08c4f94ae07696b05c9b822da0b4e5337a2f54c5
 F test/tkt-94c04eaadb.test f738c57c7f68ab8be1c054415af7774617cb6223
-F test/tkt-99378177930f87bd.test 28530bf9903dcd7743185ce78b1c02b1f9ba09fe4fa77a70ecbd0af83fe3353c
+F test/tkt-99378177930f87bd.test 9d6cff39b50d062c813ae1cb0ebbd1b7acf81ecc23ae5d5215e5bb05667dc137
 F test/tkt-9a8b09f8e6.test b2ef151d0984b2ebf237760dbeaa50724e5a0667
 F test/tkt-9d68c883.test 16f7cb96781ba579bc2e19bb14b4ad609d9774b6
 F test/tkt-9f2eb3abac.test cb6123ac695a08b4454c3792fbe85108f67fabf8
@@ -2058,8 +2058,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 49595083ba58e13d404b6a11c28f53768a257c59f3f36be3591438127df7e875
-R cc728f1caef69a29ba8fe0bb9cbdc7a1
+P 960fe54c8227a376c97bb2a3ba604d97e79e27e51a9798be9d318cf56c04fa99
+R a2cf5162922bbbf19ca3c71a88c9436f
 U drh
-Z a2caae4ae9157cd4de2a01e274c37a8f
+Z 9e57bb75bc9de4a8455f559e00ff6f17
 # Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index 4bc144f09..fd881b095 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-960fe54c8227a376c97bb2a3ba604d97e79e27e51a9798be9d318cf56c04fa99
\ No newline at end of file
+5acc3ef83e16a9f2803981a6e61eca62fe177cac4eed1befe2ade3a5977a1eab
\ No newline at end of file
diff --git a/src/select.c b/src/select.c
index 80a51d61d..da454efc3 100644
--- a/src/select.c
+++ b/src/select.c
@@ -6448,12 +6448,13 @@ static void optimizeAggregateUseOfIndexedExpr(
   assert( pSelect->pGroupBy!=0 );
   pAggInfo->nColumn = pAggInfo->nAccumulator;
   if( ALWAYS(pAggInfo->nSortingColumn>0) ){
-    if( pAggInfo->nColumn==0 ){
-      pAggInfo->nSortingColumn = pSelect->pGroupBy->nExpr;
-    }else{
-      pAggInfo->nSortingColumn =
-        pAggInfo->aCol[pAggInfo->nColumn-1].iSorterColumn+1;
+    int mx = pSelect->pGroupBy->nExpr - 1;
+    int j, k;
+    for(j=0; j<pAggInfo->nColumn; j++){
+      k = pAggInfo->aCol[j].iSorterColumn;
+      if( k>mx ) mx = k;
     }
+    pAggInfo->nSortingColumn = mx+1;
   }
   analyzeAggFuncArgs(pAggInfo, pNC);
 #if TREETRACE_ENABLED
diff --git a/test/indexexpr1.test b/test/indexexpr1.test
index 7d94806cd..51ef73bbf 100644
--- a/test/indexexpr1.test
+++ b/test/indexexpr1.test
@@ -591,6 +591,29 @@ do_execsql_test indexexpr1-2140 {
   SELECT b FROM t1;
 } 400
 
-
+# 2023-04-18 Forum post https://sqlite.org/forum/forumpost/f34e32d120 from
+# Alexis King.
+#
+# This problem originates at check-in b9190d3da70c4171 (2022-11-25).
+# A similar problem arose on 2023-03-04 at
+# https://sqlite.org/forum/forumpost/a68313d054 and was fixed at
+# check-in e06973876993926f.  See the test case tkt-99378-400.
+# 
+reset_db
+do_execsql_test indexexpr1-2200 {
+  CREATE TABLE t1(id INTEGER PRIMARY KEY, tag INT);
+  INSERT INTO t1 VALUES (0, 7), (1, 8);
+  CREATE TABLE t2(type INT, t1_id  INT, value  INT);
+  INSERT INTO t2 VALUES (0, 0, 100), (0, 1, 101);
+  CREATE INDEX t1x ON t1(-tag);
+  SELECT u.tag, v.max_value
+    FROM (SELECT tag FROM t1 GROUP BY -tag) u
+    JOIN (SELECT t1.tag AS "tag", t2.type AS "type",
+                 MAX(t2.value) AS "max_value"
+            FROM t1
+                 JOIN t2 ON t2.t1_id = t1.id
+           GROUP BY t2.type, t1.tag
+         ) v ON v.type = 0 AND v.tag = u.tag;
+} {7 100 8 101}
 
 finish_test
diff --git a/test/tkt-99378177930f87bd.test b/test/tkt-99378177930f87bd.test
index ff73529bb..ba9fdc702 100644
--- a/test/tkt-99378177930f87bd.test
+++ b/test/tkt-99378177930f87bd.test
@@ -178,6 +178,8 @@ do_execsql_test tkt-99378-310 {
 
 # 2023-03-04 https://sqlite.org/forum/forumpost/a68313d054
 #
+# See also indexexpr1-2200 added on 2023-03-18.
+#
 do_execsql_test tkt-99378-400 {
   DROP TABLE t1;
   CREATE TABLE t0(w);