/ Check-in [0642d3e3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Allow GROUP BY on non-aggregate queries. Ticket #1064 (CVS 2276)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0642d3e3d6636a5f922f75c05252c9c1372d3936
User & Date: danielk1977 2005-01-26 03:58:36
Context
2005-01-26
10:39
Add an entry to faq.tcl regarding SQLITE_SCHEMA errors. (CVS 2277) check-in: 12defe8c user: danielk1977 tags: trunk
03:58
Allow GROUP BY on non-aggregate queries. Ticket #1064 (CVS 2276) check-in: 0642d3e3 user: danielk1977 tags: trunk
2005-01-25
04:27
Prevent collation sequences and user functions from being deleted or changed while SQL statements are executing. (CVS 2275) check-in: cabab62b user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442

2443
2444
2445
2446
2447
2448
2449
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.233 2005/01/21 08:13:15 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    if( pGroupBy==0 ){
      sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
      goto select_end;
    }
    if( sqlite3ExprResolveNames(pParse, pTabList, pEList, pNC, pHaving, 1, 1) ){
      goto select_end;
    }
    if( ExprHasProperty(pHaving, EP_Agg) ) isAgg = 1;
  }
  if( pGroupBy && !isAgg ){
    sqlite3ErrorMsg(pParse, "GROUP BY may only be used on aggregate queries");
    goto select_end;

  }
  if( processOrderGroupBy(pParse,pOrderBy,pTabList,pEList,pNC,isAgg,"ORDER")
   || processOrderGroupBy(pParse,pGroupBy,pTabList,pEList,pNC,isAgg,"GROUP")
  ){
    goto select_end;
  }








|







 







<

|
<
<
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2431
2432
2433
2434
2435
2436
2437

2438
2439


2440
2441
2442
2443
2444
2445
2446
2447
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.234 2005/01/26 03:58:36 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
    if( pGroupBy==0 ){
      sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING");
      goto select_end;
    }
    if( sqlite3ExprResolveNames(pParse, pTabList, pEList, pNC, pHaving, 1, 1) ){
      goto select_end;
    }

  }
  if( pGroupBy ){


    isAgg = 1;
  }
  if( processOrderGroupBy(pParse,pOrderBy,pTabList,pEList,pNC,isAgg,"ORDER")
   || processOrderGroupBy(pParse,pGroupBy,pTabList,pEList,pNC,isAgg,"GROUP")
  ){
    goto select_end;
  }

Changes to test/collate3.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
110
111
112
113
114
115
116





117
118
119
120
121
122
123
124
125
126
127
128
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate3.test,v 1.8 2005/01/22 03:39:39 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

#
# Tests are organised as follows:
#
................................................................................
  }
} {0 {}}
do_test collate3-2.7.1 {
  catchsql {
    SELECT count(*) FROM collate3t1 GROUP BY c1;
  }
} {1 {no such collation sequence: string_compare}} 





do_test collate3-2.7.2 {
  catchsql {
    SELECT * FROM collate3t1 GROUP BY c1;
  }
} {1 {GROUP BY may only be used on aggregate queries}}
do_test collate3-2.8 {
  catchsql {
    SELECT DISTINCT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 

ifcapable compound {







|







 







>
>
>
>
>




|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is page cache subsystem.
#
# $Id: collate3.test,v 1.9 2005/01/26 03:58:36 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

#
# Tests are organised as follows:
#
................................................................................
  }
} {0 {}}
do_test collate3-2.7.1 {
  catchsql {
    SELECT count(*) FROM collate3t1 GROUP BY c1;
  }
} {1 {no such collation sequence: string_compare}} 
# do_test collate3-2.7.2 {
#   catchsql {
#     SELECT * FROM collate3t1 GROUP BY c1;
#   }
# } {1 {GROUP BY may only be used on aggregate queries}}
do_test collate3-2.7.2 {
  catchsql {
    SELECT * FROM collate3t1 GROUP BY c1;
  }
} {1 {no such collation sequence: string_compare}} 
do_test collate3-2.8 {
  catchsql {
    SELECT DISTINCT c1 FROM collate3t1;
  }
} {1 {no such collation sequence: string_compare}} 

ifcapable compound {

Changes to test/select5.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
112
113
114
115
116
117
118




































119

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.8 2004/08/20 18:34:20 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
execsql {
................................................................................
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {0.0}





































finish_test








|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

>
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select5.test,v 1.9 2005/01/26 03:58:36 danielk1977 Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
execsql {
................................................................................
} {{}}
do_test select5-4.5 {
  execsql {
    SELECT sum(x) FROM t1 WHERE x>100
  }
} {0.0}

# Some tests for queries with a GROUP BY clause but no aggregate functions.
#
# Note: The query in test case 5-5.5 are not legal SQL. So if the 
# implementation changes in the future and it returns different results,
# this is not such a big deal.
#
do_test select5-5.1 {
  execsql {
    CREATE TABLE t2(a, b, c);
    INSERT INTO t2 VALUES(1, 2, 3);
    INSERT INTO t2 VALUES(1, 4, 5);
    INSERT INTO t2 VALUES(6, 4, 7);
    CREATE INDEX t2_idx ON t2(a);
  } 
} {}
do_test select5-5.2 {
  execsql {
    SELECT a FROM t2 GROUP BY a;
  } 
} {1 6}
do_test select5-5.3 {
  execsql {
    SELECT a FROM t2 WHERE a>2 GROUP BY a;
  } 
} {6}
do_test select5-5.4 {
  execsql {
    SELECT a, b FROM t2 GROUP BY a, b;
  } 
} {1 2 1 4 6 4}
do_test select5-5.5 {
  execsql {
    SELECT a, b FROM t2 GROUP BY a;
  } 
} {1 2 6 4}

finish_test

Changes to test/select7.test.

6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
50
51
52
53
54
55
56



57





58
59
60
61
62
63
64

#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.5 2005/01/21 03:12:16 danielk1977 Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable compound {

................................................................................
} {1 1}
} ;# ifcapable view

} ;# ifcapable compound

# Do not allow GROUP BY without an aggregate. Ticket #1039.
#



ifcapable subquery {





  do_test select7-3.1 {
    catchsql {
      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
    }
  } {1 {GROUP BY may only be used on aggregate queries}}
}
finish_test








|







 







>
>
>

>
>
>
>
>




|


>
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
..
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing compute SELECT statements and nested
# views.
#
# $Id: select7.test,v 1.6 2005/01/26 03:58:37 danielk1977 Exp $


set testdir [file dirname $argv0]
source $testdir/tester.tcl

ifcapable compound {

................................................................................
} {1 1}
} ;# ifcapable view

} ;# ifcapable compound

# Do not allow GROUP BY without an aggregate. Ticket #1039.
#
# Change: force any query with a GROUP BY clause to be processed as
# an aggregate query, whether it contains aggregates or not.
#
ifcapable subquery {
  # do_test select7-3.1 {
  #   catchsql {
  #     SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
  #   }
  # } {1 {GROUP BY may only be used on aggregate queries}}
  do_test select7-3.1 {
    catchsql {
      SELECT * FROM (SELECT * FROM sqlite_master) GROUP BY name
    }
  } [list 0 [execsql {SELECT * FROM sqlite_master ORDER BY name}]]
}
finish_test