/ Check-in [9581e7a4]
Login

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

Overview
Comment:Disallow empty GROUP BY clauses. Ticket #2431. (CVS 4099)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9581e7a4a4d74b08ce5380b49862957c804e46bb
User & Date: drh 2007-06-20 12:18:31
Context
2007-06-20
13:10
Fix a bug in the command-line shell that prevents the reading of the ".sqliterc" file. Ticket #2433. (CVS 4100) check-in: 6eaf29f5 user: drh tags: trunk
12:18
Disallow empty GROUP BY clauses. Ticket #2431. (CVS 4099) check-in: 9581e7a4 user: drh tags: trunk
11:56
Additional changes for ticket #2426. (CVS 4098) check-in: 79debf95 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
845
846
847
848
849
850
851
852
853
854
855

856
857
858
859

860
861
862
863
864
865
866
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.230 2007/06/15 17:03:14 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
................................................................................
sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_ASC;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqlite3ExprListDelete($$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}

%type having_opt {Expr*}
%destructor having_opt {sqlite3ExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}
................................................................................
%type case_operand {Expr*}
%destructor case_operand {sqlite3ExprDelete($$);}
case_operand(A) ::= expr(X).            {A = X;} 
case_operand(A) ::= .                   {A = 0;} 

%type exprlist {ExprList*}
%destructor exprlist {sqlite3ExprListDelete($$);}
%type expritem {Expr*}
%destructor expritem {sqlite3ExprDelete($$);}

exprlist(A) ::= exprlist(X) COMMA expritem(Y). 

                                        {A = sqlite3ExprListAppend(X,Y,0);}
exprlist(A) ::= expritem(X).            {A = sqlite3ExprListAppend(0,X,0);}
expritem(A) ::= expr(X).                {A = X;}
expritem(A) ::= .                       {A = 0;}


///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= CREATE(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP idxlist(Z) RP(E). {
  sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(0,&Y,0), Z, U,
                      &S, &E, SQLITE_SO_ASC, NE);







|







 







|







 







|
|

|
>
|
|
<
<
>







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
...
845
846
847
848
849
850
851
852
853
854
855
856
857
858


859
860
861
862
863
864
865
866
**
*************************************************************************
** This file contains SQLite's grammar for SQL.  Process this file
** using the lemon parser generator to generate C code that runs
** the parser.  Lemon will also generate a header file containing
** numeric codes for all of the tokens.
**
** @(#) $Id: parse.y,v 1.231 2007/06/20 12:18:31 drh Exp $
*/

// All token codes are small integers with #defines that begin with "TK_"
%token_prefix TK_

// The type of the data attached to each token is Token.  This is also the
// default type for non-terminals.
................................................................................
sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_ASC;}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqlite3ExprListDelete($$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY nexprlist(X). {A = X;}

%type having_opt {Expr*}
%destructor having_opt {sqlite3ExprDelete($$);}
having_opt(A) ::= .                {A = 0;}
having_opt(A) ::= HAVING expr(X).  {A = X;}

%type limit_opt {struct LimitVal}
................................................................................
%type case_operand {Expr*}
%destructor case_operand {sqlite3ExprDelete($$);}
case_operand(A) ::= expr(X).            {A = X;} 
case_operand(A) ::= .                   {A = 0;} 

%type exprlist {ExprList*}
%destructor exprlist {sqlite3ExprListDelete($$);}
%type nexprlist {ExprList*}
%destructor nexprlist {sqlite3ExprListDelete($$);}

exprlist(A) ::= nexprlist(X).                {A = X;}
exprlist(A) ::= .                            {A = 0;}
nexprlist(A) ::= nexprlist(X) COMMA expr(Y). {A = sqlite3ExprListAppend(X,Y,0);}
nexprlist(A) ::= expr(Y).                    {A = sqlite3ExprListAppend(0,Y,0);}




///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= CREATE(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X) dbnm(D)
        ON nm(Y) LP idxlist(Z) RP(E). {
  sqlite3CreateIndex(pParse, &X, &D, sqlite3SrcListAppend(0,&Y,0), Z, U,
                      &S, &E, SQLITE_SO_ASC, NE);

Changes to test/select3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
100
101
102
103
104
105
106


107
108
109
110
111


112
113
114
115
116


117
118
119
120
121
122
123
#    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: select3.test,v 1.20 2007/02/24 11:52:55 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {1 {GROUP BY column number 3 out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}


#do_test select3-2.13 {
#  catchsql {
#    SELECT log, count(*) FROM t1 GROUP BY 2 ORDER BY log;
#  }
#} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}


#do_test select3-2.14 {
#  catchsql {
#    SELECT log, count(*) FROM t1 GROUP BY count(*) ORDER BY log;
#  }
#} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}



# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}







|







 







>
>
|
|
|
<
<
>
>
|
|
|
<
<
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
100
101
102
103
104
105
106
107
108
109
110
111


112
113
114
115
116


117
118
119
120
121
122
123
124
125
#    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: select3.test,v 1.21 2007/06/20 12:18:31 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
................................................................................
  }
} {1 {GROUP BY column number 3 out of range - should be between 1 and 2}}
do_test select3-2.12 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY 1 ORDER BY log;
  }
} {0 {0 1 1 1 2 2 3 4 4 8 5 15}}

# Cannot have an empty GROUP BY
do_test select3-2.13 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY ORDER BY log;


  }
} {1 {near "ORDER": syntax error}}
do_test select3-2.14 {
  catchsql {
    SELECT log, count(*) FROM t1 GROUP BY;


  }
} {1 {near ";": syntax error}}

# Cannot have a HAVING without a GROUP BY
#
do_test select3-3.1 {
  set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg]
  lappend v $msg
} {1 {a GROUP BY clause is required before HAVING}}