SQLite

Check-in [0a26b91580]
Login

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

Overview
Comment:Apply (1679) to version 3. Ticket #777. (CVS 1680)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0a26b9158095f0995fce2f0ccdfb383ab26c76a5
User & Date: danielk1977 2004-06-24 00:20:05.000
Context
2004-06-24
01:30
Minor fix for 'make doc'. (CVS 1681) (check-in: 31fe5cbd87 user: danielk1977 tags: trunk)
00:20
Apply (1679) to version 3. Ticket #777. (CVS 1680) (check-in: 0a26b91580 user: danielk1977 tags: trunk)
2004-06-23
13:46
Improve test coverage of utf.c. (CVS 1678) (check-in: 53c553c760 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/func.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.75 2004/06/21 06:50:28 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This file contains the C functions that implement various SQL
** functions of SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterBuildinFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: func.c,v 1.76 2004/06/24 00:20:05 danielk1977 Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"
#include "vdbeInt.h"
967
968
969
970
971
972
973

974
975
976
977
978
979
980
981
982
** Routines to implement min() and max() aggregate functions.
*/
static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  int max = 0;
  int cmp = 0;
  Mem *pArg  = (Mem *)argv[0];
  Mem *pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));


  if( !pBest || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return;
  if( pBest->flags ){
    CollSeq *pColl = sqlite3GetFuncCollSeq(context);
    /* This step function is used for both the min() and max() aggregates,
    ** the only difference between the two being that the sense of the
    ** comparison is inverted. For the max() aggregate, the
    ** sqlite3_user_data() function returns (void *)-1. For min() it
    ** returns (void *)db, where db is the sqlite3* database pointer.







>

<







967
968
969
970
971
972
973
974
975

976
977
978
979
980
981
982
** Routines to implement min() and max() aggregate functions.
*/
static void minmaxStep(sqlite3_context *context, int argc, sqlite3_value **argv){
  int max = 0;
  int cmp = 0;
  Mem *pArg  = (Mem *)argv[0];
  Mem *pBest = (Mem *)sqlite3_aggregate_context(context, sizeof(*pBest));
  if( !pBest ) return;


  if( pBest->flags ){
    CollSeq *pColl = sqlite3GetFuncCollSeq(context);
    /* This step function is used for both the min() and max() aggregates,
    ** the only difference between the two being that the sense of the
    ** comparison is inverted. For the max() aggregate, the
    ** sqlite3_user_data() function returns (void *)-1. For min() it
    ** returns (void *)db, where db is the sqlite3* database pointer.
Changes to test/func.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.25 2004/06/23 01:05:27 danielk1977 Exp $

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

# Create a table to work with.
#
do_test func-0.0 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing built-in functions.
#
# $Id: func.test,v 1.26 2004/06/24 00:20:05 danielk1977 Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264





265
266
267
268
269
270
271
272

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 1 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }





} {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?
#
do_test func-9.1 {
  execsql {
    SELECT random() is not null;
  }







|










>
>
>
>
>
|







247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277

# Tests for aggregate functions and how they handle NULLs.
#
do_test func-8.1 {
  execsql {
    SELECT sum(a), count(a), round(avg(a),2), min(a), max(a), count(*) FROM t2;
  }
} {68236 3 22745.33 {} 67890 5}
do_test func-8.2 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t2;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
do_test func-8.3 {
  execsql {
    CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
    SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {{}}
do_test func-8.4 {
  execsql {
    SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
  }
} {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}

# How do you test the random() function in a meaningful, deterministic way?
#
do_test func-9.1 {
  execsql {
    SELECT random() is not null;
  }
Changes to test/minmax.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.9 2004/03/13 14:00:37 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing SELECT statements that contain
# aggregate min() and max() functions and which are handled as
# as a special case.
#
# $Id: minmax.test,v 1.10 2004/06/24 00:20:05 danielk1977 Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}







|







123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
} {0}

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x+0),-1), coalesce(max(x+0),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {-1 20}
do_test minmax-4.2 {
  execsql {
    SELECT y, sum(x) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 2 5 3 22 4 92 5 90 6 0}
271
272
273
274
275
276
277
278






























279
do_test minmax-9.2 {
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}































finish_test








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

271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
do_test minmax-9.2 {
  execsql {
    SELECT max(rowid) FROM (
      SELECT max(rowid) FROM t4 EXCEPT SELECT max(rowid) FROM t5
    )
  }
} {{}}

# If there is a NULL in an aggregate max(), ignore it.  If a NULL
# occurs in an aggregate min(), then the result will be NULL because
# NULL compares less than all other values.
#
do_test minmax-10.1 {
  execsql {
    CREATE TABLE t6(x);
    INSERT INTO t6 VALUES(1);
    INSERT INTO t6 VALUES(2);
    INSERT INTO t6 VALUES(NULL);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.2 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}
do_test minmax-10.3 {
  execsql {
    CREATE INDEX i6 ON t6(x);
    SELECT coalesce(min(x),-1) FROM t6;
  }
} {-1}
do_test minmax-10.4 {
  execsql {
    SELECT max(x) FROM t6;
  }
} {2}

finish_test
Changes to test/null.test.
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# Check to see that NULL values are ignored in aggregate functions.
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 0 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {







|







96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
# Check to see that NULL values are ignored in aggregate functions.
#
do_test null-3.1 {
  execsql {
    select count(*), count(b), count(c), sum(b), sum(c), 
           avg(b), avg(c), min(b), max(b) from t1;
  }
} {7 4 6 2 3 0.5 0.5 {} 1}

# Check to see how WHERE clauses handle NULL values.  A NULL value
# is the same as UNKNOWN.  The WHERE clause should only select those
# rows that are TRUE.  FALSE and UNKNOWN rows are rejected.
#
do_test null-4.1 {
  execsql {
Changes to test/select1.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.32 2004/05/28 11:37:29 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2001 September 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.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the SELECT statement.
#
# $Id: select1.test,v 1.33 2004/06/24 00:20:05 danielk1977 Exp $

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

# Try to select on a non-existant table.
#
do_test select1-1.1 {
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {11}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {







|







155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
} {0 11}
do_test select1-2.8 {
  set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
  lappend v [lsort $msg]
} {0 {11 33}}
do_test select1-2.8.1 {
  execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
} {xyzzy}
do_test select1-2.8.2 {
  execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
} {11}
do_test select1-2.8.3 {
  execsql {SELECT min(b), min(b) FROM t4}
} [list $long $long]
do_test select1-2.9 {