From c52e355de1b79a5047358c501f3f6fa70b7a0c5d Mon Sep 17 00:00:00 2001
From: drh <drh@noemail.net>
Date: Fri, 15 Feb 2008 14:33:03 +0000
Subject: [PATCH] Do not apply the query flattening optimization when the outer
 query is an aggregate and the inner query contains an ORDER BY clause. 
 Ticket #2943. (CVS 4791)

FossilOrigin-Name: 6d33cbd99cb0db680767ceb31ec6345e90a805bc
---
 manifest          | 13 +++++-----
 manifest.uuid     |  2 +-
 src/select.c      | 17 +++++--------
 test/tkt2942.test | 62 +++++++++++++++++++++++++++++++++++++++++++++++
 4 files changed, 76 insertions(+), 18 deletions(-)
 create mode 100644 test/tkt2942.test

diff --git a/manifest b/manifest
index faf182132a..15cb4e01ea 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\sa\sbug\sin\sEXPLAIN\sgrowing\sout\sof\sthe\snew\sMem\simplementation.\s(CVS\s4790)
-D 2008-02-14T23:44:14
+C Do\snot\sapply\sthe\squery\sflattening\soptimization\swhen\sthe\souter\squery\sis\san\r\naggregate\sand\sthe\sinner\squery\scontains\san\sORDER\sBY\sclause.\s\sTicket\s#2943.\s(CVS\s4791)
+D 2008-02-15T14:33:04
 F Makefile.arm-wince-mingw32ce-gcc ac5f7b2cef0cd850d6f755ba6ee4ab961b1fadf7
 F Makefile.in bc2b5df3e3d0d4b801b824b7ef6dec43812b049b
 F Makefile.linux-gcc d53183f4aa6a9192d249731c90dbdffbd2c68654
@@ -133,7 +133,7 @@ F src/pragma.c e3f39f8576234887ecd0c1de43dc51af5855930c
 F src/prepare.c 1b0601ca3f97a9d253cc08697484e3045a1678e9
 F src/printf.c eb27822ba2eec669161409ca31279a24c26ac910
 F src/random.c 02ef38b469237482f1ea14a78b2087cfbaec48bd
-F src/select.c 1d780691071af7d58b83a6b38517c2687dd83c5b
+F src/select.c aef06a4b157cc7defe8f99255f9a13cf693c8f13
 F src/server.c 087b92a39d883e3fa113cae259d64e4c7438bc96
 F src/shell.c ca06cb687c40a8bff6307b5fad41a0e86a0f8558
 F src/sqlite.h.in 74e71510ce5967333a36329212eca0833f6300bd
@@ -497,6 +497,7 @@ F test/tkt2832.test cd56dc66bb31898b7eb2146baa5bde2eb80f96fe
 F test/tkt2854.test aebd5a9904d36d1ef7a074fc5e7c7da3ab00c32a
 F test/tkt2920.test a8737380e4ae6424e00c0273dc12775704efbebf
 F test/tkt2927.test 492c6a9a14b2fc7bdbb17ce8e497d82df627f64d
+F test/tkt2942.test c5c87d179799ca6d1fbe83c815510b87cd5ec7ce
 F test/trace.test 951cd0f5f571e7f36bf7bfe04be70f90fb16fb00
 F test/trans.test b73289992b46d38d9479ecc4fdc03d8edb2413dc
 F test/trigger1.test 7c13f39ca36f529bf856e05c7d004fc0531d48b4
@@ -618,7 +619,7 @@ F www/tclsqlite.tcl 8be95ee6dba05eabcd27a9d91331c803f2ce2130
 F www/vdbe.tcl 87a31ace769f20d3627a64fa1fade7fed47b90d0
 F www/version3.tcl 890248cf7b70e60c383b0e84d77d5132b3ead42b
 F www/whentouse.tcl fc46eae081251c3c181bd79c5faef8195d7991a5
-P 669ece8c82bfa69add852589dd1211751cb26fb2
-R cc04ce9e397a2238bd5d8ea817f1a9da
+P 4df62a55d6fc92c3d0a416c9c03d86d76478feb6
+R 171d3738564305862c44644c7b059e59
 U drh
-Z 2c8ee1e11248569a5dd84e07edd7c948
+Z c1c355e8e6620694fb23b02247622eef
diff --git a/manifest.uuid b/manifest.uuid
index 29d33b880c..8bc5686de6 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-4df62a55d6fc92c3d0a416c9c03d86d76478feb6
\ No newline at end of file
+6d33cbd99cb0db680767ceb31ec6345e90a805bc
\ No newline at end of file
diff --git a/src/select.c b/src/select.c
index a6ecc6fc8f..856f1f057a 100644
--- a/src/select.c
+++ b/src/select.c
@@ -12,7 +12,7 @@
 ** This file contains C code routines that are called by the parser
 ** to handle SELECT statements in SQLite.
 **
-** $Id: select.c,v 1.413 2008/02/13 18:25:27 danielk1977 Exp $
+** $Id: select.c,v 1.414 2008/02/15 14:33:04 drh Exp $
 */
 #include "sqliteInt.h"
 
@@ -2417,6 +2417,10 @@ static void substSelect(
 **        subquery does not have both an ORDER BY and a LIMIT clause.
 **        (See ticket #2339)
 **
+**  (16)  The outer query is not an aggregate or the subquery does
+**        not contain ORDER BY.  (Ticket #2942)  This used to not matter
+**        until we introduced the group_concat() function.  
+**
 ** In this routine, the "p" parameter is a pointer to the outer query.
 ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
 ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
@@ -2474,6 +2478,7 @@ static int flattenSubquery(
   if( (p->disallowOrderBy || p->pOrderBy) && pSub->pOrderBy ){
      return 0;                                           /* Restriction (11) */
   }
+  if( isAgg && pSub->pOrderBy ) return 0;                /* Restriction (16) */
 
   /* Restriction 3:  If the subquery is a join, make sure the subquery is 
   ** not used as the right operand of an outer join.  Examples of why this
@@ -3143,16 +3148,6 @@ int sqlite3Select(
   }
 #endif
 
-  /* Check for the special case of a min() or max() function by itself
-  ** in the result set.
-  */
-#if 0
-  if( simpleMinMaxQuery(pParse, p, pDest) ){
-    rc = 0;
-    goto select_end;
-  }
-#endif
-
   /* Check to see if this is a subquery that can be "flattened" into its parent.
   ** If flattening is a possiblity, do so and return immediately.  
   */
diff --git a/test/tkt2942.test b/test/tkt2942.test
new file mode 100644
index 0000000000..a7b861c75c
--- /dev/null
+++ b/test/tkt2942.test
@@ -0,0 +1,62 @@
+# 2008 February 15
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Ticket #2942.  
+#
+# Queries of the form:
+#
+#     SELECT group_concat(x) FROM (SELECT * FROM table ORDER BY 1);
+#
+# The ORDER BY would be dropped by the query flattener.  This used
+# to not matter because aggregate functions sum(), min(), max(), avg(),
+# and so forth give the same result regardless of the order of inputs.
+# But with the addition of the group_concat() function, suddenly the
+# order does matter.
+#
+# $Id: tkt2942.test,v 1.1 2008/02/15 14:33:04 drh Exp $
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable !subquery {
+  finish_test
+  return
+}
+
+do_test tkt2942.1 {
+  execsql {
+    create table t1(num int);
+    insert into t1 values (2);
+    insert into t1 values (1);
+    insert into t1 values (3);
+    insert into t1 values (4);
+    SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY num DESC);
+  }
+} {4,3,2,1}
+do_test tkt2942.2 {
+  execsql {
+    SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY num);
+  }
+} {1,2,3,4}
+do_test tkt2942.3 {
+  execsql {
+    SELECT group_concat(num) FROM (SELECT num FROM t1);
+  }
+} {2,1,3,4}
+do_test tkt2942.4 {
+  execsql {
+    SELECT group_concat(num) FROM (SELECT num FROM t1 ORDER BY rowid DESC);
+  }
+} {4,3,1,2}
+
+
+finish_test
-- 
2.47.3