/ Check-in [4be8e676]
Login

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

Overview
Comment:Strengthen the tests for aggregate functions in GROUP BY clauses. Changes to test cases only. No changes to code. (CVS 4601)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 4be8e6765bf8bc48747b2542f2ed77066fb9dcb9
User & Date: drh 2007-12-08 18:01:31
Context
2007-12-08
21:10
Better resolution of ORDER BY terms in compound queries. Candidate solution for ticket #2822. Needs more testing and documentation before going final. (CVS 4602) check-in: 62a78d21 user: drh tags: trunk
18:01
Strengthen the tests for aggregate functions in GROUP BY clauses. Changes to test cases only. No changes to code. (CVS 4601) check-in: 4be8e676 user: drh tags: trunk
17:55
Fix a bug in the debugging printf logic. (CVS 4600) check-in: 1d6a9f5f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/misc4.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
94
95
96
97
98
99
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
126
127
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.22 2007/08/13 15:28:35 danielk1977 Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
  catchsql {
    INSERT INTO t3 VALUES(1);
  }
} {0 {}}

# Ticket #966
#
ifcapable compound {
do_test misc4-3.1 {
  execsql { 
    CREATE TABLE Table1(ID integer primary key, Value TEXT);
    INSERT INTO Table1 VALUES(1, 'x');
    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');








    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
    ORDER BY 1, 2;
  }
} {1 x 1 z}
do_test misc4-3.2 {
  catchsql { 
    SELECT ID, Value FROM Table1
       UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
    ORDER BY 1, 2;
  }
} {1 {aggregate functions are not allowed in the GROUP BY clause}}







} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {







|







 







<







>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
..
94
95
96
97
98
99
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc4.test,v 1.23 2007/12/08 18:01:31 drh Exp $

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

# Prepare a statement that will create a temporary table.  Then do
# a rollback.  Then try to execute the prepared statement.
#
................................................................................
  catchsql {
    INSERT INTO t3 VALUES(1);
  }
} {0 {}}

# Ticket #966
#

do_test misc4-3.1 {
  execsql { 
    CREATE TABLE Table1(ID integer primary key, Value TEXT);
    INSERT INTO Table1 VALUES(1, 'x');
    CREATE TABLE Table2(ID integer NOT NULL, Value TEXT);
    INSERT INTO Table2 VALUES(1, 'z');
    INSERT INTO Table2 VALUES (1, 'a');
  }
  catchsql { 
    SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2 ORDER BY 1, 2;
  }
} {1 {aggregate functions are not allowed in the GROUP BY clause}}
ifcapable compound {
  do_test misc4-3.2 {
    execsql {
      SELECT ID, Value FROM Table1
         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1
      ORDER BY 1, 2;
    }
  } {1 x 1 z}
  do_test misc4-3.3 {
    catchsql { 
      SELECT ID, Value FROM Table1
         UNION SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
      ORDER BY 1, 2;
    }
  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
  do_test misc4-3.4 {
    catchsql { 
      SELECT ID, max(Value) FROM Table2 GROUP BY 1, 2
         UNION SELECT ID, Value FROM Table1
      ORDER BY 1, 2;
    }
  } {1 {aggregate functions are not allowed in the GROUP BY clause}}
} ;# ifcapable compound

# Ticket #1047.  Make sure column types are preserved in subqueries.
#
ifcapable subquery {
  do_test misc4-4.1 {
    execsql {