SQLite

Check-in [91c0db66c8]
Login

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

Overview
Comment:Add a few more tests and fix a few bugs that the tests uncovered. (CVS 652)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 91c0db66c86facb21b5b522afadd83d91a488256
User & Date: drh 2002-06-29 02:20:08.000
Context
2002-07-01
00:31
Added additional tests. No new errors found. (CVS 653) (check-in: 8924a7f5ba user: drh tags: trunk)
2002-06-29
02:20
Add a few more tests and fix a few bugs that the tests uncovered. (CVS 652) (check-in: 91c0db66c8 user: drh tags: trunk)
2002-06-28
12:18
Additional test cases added. The following bug fixed: A segfault was occurring if a VIEW consisted of a join with a USING clause. (CVS 651) (check-in: 96515b813e user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.75 2002/06/28 12:18:47 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains routines used for analyzing expressions and
** for generating VDBE code that evaluates expressions in SQLite.
**
** $Id: expr.c,v 1.76 2002/06/29 02:20:08 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
  }
  return 0;
}

/*
** Return TRUE if the given string is a row-id column name.
*/
static int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==0 ) return 1;
  if( sqliteStrICmp(z, "OID")==0 ) return 1;
  return 0;
}

/*







|







366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
  }
  return 0;
}

/*
** Return TRUE if the given string is a row-id column name.
*/
int sqliteIsRowid(const char *z){
  if( sqliteStrICmp(z, "_ROWID_")==0 ) return 1;
  if( sqliteStrICmp(z, "ROWID")==0 ) return 1;
  if( sqliteStrICmp(z, "OID")==0 ) return 1;
  return 0;
}

/*
Changes to src/select.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.101 2002/06/28 12:18:47 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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.102 2002/06/29 02:20:08 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
432
433
434
435
436
437
438

439
440
441
442
443
444

445
446

447
448
449
450
451
452
453
    }

    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {

      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_IsNull, -1, sqliteVdbeCurrentAddr(v)+3);
      sqliteVdbeAddOp(v, OP_String, 0, 0);
      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{

        sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      }

      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */







>

|
<



>


>







432
433
434
435
436
437
438
439
440
441

442
443
444
445
446
447
448
449
450
451
452
453
454
455
    }

    /* If we are creating a set for an "expr IN (SELECT ...)" construct,
    ** then there should be a single item on the stack.  Write this
    ** item into the set table with bogus data.
    */
    case SRT_Set: {
      int lbl = sqliteVdbeMakeLabel(v);
      assert( nColumn==1 );
      sqliteVdbeAddOp(v, OP_IsNull, -1, lbl);

      if( pOrderBy ){
        pushOntoSorter(pParse, v, pOrderBy);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0);
        sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
      }
      sqliteVdbeResolveLabel(v, lbl);
      break;
    }

    /* If this is a scalar select that is part of an expression, then
    ** store the results in the appropriate memory cell and break out
    ** of the scan loop.
    */
Changes to src/sqliteInt.h.
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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.132 2002/06/28 12:18:47 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>













|







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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.133 2002/06/29 02:20:09 drh Exp $
*/
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
#include <stdio.h>
904
905
906
907
908
909
910

911
912
913
914
915
916
917
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*, int);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
int sqliteExprIsInteger(Expr*, int*);

void sqliteGenerateRowDelete(Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);







>







904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
int sqliteRandomInteger(void);
void sqliteBeginTransaction(Parse*, int);
void sqliteCommitTransaction(Parse*);
void sqliteRollbackTransaction(Parse*);
char *sqlite_mprintf(const char *, ...);
int sqliteExprIsConstant(Expr*);
int sqliteExprIsInteger(Expr*, int*);
int sqliteIsRowid(const char*);
void sqliteGenerateRowDelete(Vdbe*, Table*, int, int);
void sqliteGenerateRowIndexDelete(Vdbe*, Table*, int, char*);
void sqliteGenerateConstraintChecks(Parse*,Table*,int,char*,int,int,int,int);
void sqliteCompleteInsertion(Parse*, Table*, int, char*, int, int);
void sqliteBeginWriteOperation(Parse*, int);
void sqliteEndWriteOperation(Parse*);
void sqliteExprMoveStrings(Expr*, int);
Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 UPDATE statements.
**
** $Id: update.c,v 1.45 2002/06/19 14:27:06 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
**    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 UPDATE statements.
**
** $Id: update.c,v 1.46 2002/06/29 02:20:09 drh Exp $
*/
#include "sqliteInt.h"

/*
** Process an UPDATE statement.
*/
void sqliteUpdate(
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
  sqliteVdbeAddOp(v, OP_NotExists, base, addr);

  /* If the record number will change, push the record number as it
  ** will be after the update. (The old record number is currently
  ** on top of the stack.)
  */
  if( chngRecno ){
    if( pTab->iPKey<0 || (j = aXRef[pTab->iPKey])<0 ){
      sqliteVdbeAddOp(v, OP_Dup, 0, 0);
    }else{
      sqliteExprCode(pParse, pChanges->a[j].pExpr);
      sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);
    }
  }

  /* Compute new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      sqliteVdbeAddOp(v, OP_String, 0, 0);







<
<
<
|
|
<







289
290
291
292
293
294
295



296
297

298
299
300
301
302
303
304
  sqliteVdbeAddOp(v, OP_NotExists, base, addr);

  /* If the record number will change, push the record number as it
  ** will be after the update. (The old record number is currently
  ** on top of the stack.)
  */
  if( chngRecno ){



    sqliteExprCode(pParse, pRecnoExpr);
    sqliteVdbeAddOp(v, OP_MustBeInt, 0, 0);

  }

  /* Compute new data for this record.  
  */
  for(i=0; i<pTab->nCol; i++){
    if( i==pTab->iPKey ){
      sqliteVdbeAddOp(v, OP_String, 0, 0);
Changes to src/vdbe.c.
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.162 2002/06/28 01:02:38 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test







|







26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
** type to the other occurs as necessary.
** 
** Most of the code in this file is taken up by the sqliteVdbeExec()
** function which does the work of interpreting a VDBE program.
** But other routines are also provided to help in building up
** a program instruction by instruction.
**
** $Id: vdbe.c,v 1.163 2002/06/29 02:20:09 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** The following global variable is incremented every time a cursor
** moves, either by the OP_MoveTo or the OP_Next opcode.  The test
1645
1646
1647
1648
1649
1650
1651
1652

1653
1654
1655
1656
1657
1658
1659
}

/* Opcode: Concat P1 P2 P3
**
** Look at the first P1 elements of the stack.  Append them all 
** together with the lowest element first.  Use P3 as a separator.  
** Put the result on the top of the stack.  The original P1 elements
** are popped from the stack if P2==0 and retained if P2==1.

**
** If P3 is NULL, then use no separator.  When P1==1, this routine
** makes a copy of the top stack element into memory obtained
** from sqliteMalloc().
*/
case OP_Concat: {
  char *zNew;







|
>







1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
}

/* Opcode: Concat P1 P2 P3
**
** Look at the first P1 elements of the stack.  Append them all 
** together with the lowest element first.  Use P3 as a separator.  
** Put the result on the top of the stack.  The original P1 elements
** are popped from the stack if P2==0 and retained if P2==1.  If
** any element of the stack is NULL, then the result is NULL.
**
** If P3 is NULL, then use no separator.  When P1==1, this routine
** makes a copy of the top stack element into memory obtained
** from sqliteMalloc().
*/
case OP_Concat: {
  char *zNew;
1667
1668
1669
1670
1671
1672
1673
1674

1675
1676
1677
1678








1679
1680
1681
1682
1683
1684
1685
  zSep = pOp->p3;
  if( zSep==0 ) zSep = "";
  nSep = strlen(zSep);
  VERIFY( if( p->tos+1<nField ) goto not_enough_stack; )
  nByte = 1 - nSep;
  for(i=p->tos-nField+1; i<=p->tos; i++){
    if( aStack[i].flags & STK_Null ){
      nByte += nSep;

    }else{
      if( Stringify(p, i) ) goto no_mem;
      nByte += aStack[i].n - 1 + nSep;
    }








  }
  zNew = sqliteMalloc( nByte );
  if( zNew==0 ) goto no_mem;
  j = 0;
  for(i=p->tos-nField+1; i<=p->tos; i++){
    if( (aStack[i].flags & STK_Null)==0 ){
      memcpy(&zNew[j], zStack[i], aStack[i].n-1);







|
>




>
>
>
>
>
>
>
>







1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
  zSep = pOp->p3;
  if( zSep==0 ) zSep = "";
  nSep = strlen(zSep);
  VERIFY( if( p->tos+1<nField ) goto not_enough_stack; )
  nByte = 1 - nSep;
  for(i=p->tos-nField+1; i<=p->tos; i++){
    if( aStack[i].flags & STK_Null ){
      nByte = -1;
      break;
    }else{
      if( Stringify(p, i) ) goto no_mem;
      nByte += aStack[i].n - 1 + nSep;
    }
  }
  if( nByte<0 ){
    if( pOp->p2==0 ) PopStack(p, nField);
    VERIFY( NeedStack(p, p->tos+1); )
    p->tos++;
    aStack[p->tos].flags = STK_Null;
    zStack[p->tos] = 0;
    break;
  }
  zNew = sqliteMalloc( nByte );
  if( zNew==0 ) goto no_mem;
  j = 0;
  for(i=p->tos-nField+1; i<=p->tos; i++){
    if( (aStack[i].flags & STK_Null)==0 ){
      memcpy(&zNew[j], zStack[i], aStack[i].n-1);
4190
4191
4192
4193
4194
4195
4196
4197
4198
4199
4200

4201



4202
4203
4204
4205
4206
4207
4208
  VERIFY( if( tos<1 ) goto not_enough_stack; )
  if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
  pSorter = sqliteMalloc( sizeof(Sorter) );
  if( pSorter==0 ) goto no_mem;
  pSorter->pNext = p->pSort;
  p->pSort = pSorter;
  assert( aStack[tos].flags & STK_Dyn );
  assert( aStack[nos].flags & STK_Dyn );
  pSorter->nKey = aStack[tos].n;
  pSorter->zKey = zStack[tos];
  pSorter->nData = aStack[nos].n;

  pSorter->pData = zStack[nos];



  aStack[tos].flags = 0;
  aStack[nos].flags = 0;
  zStack[tos] = 0;
  zStack[nos] = 0;
  p->tos -= 2;
  break;
}







<



>
|
>
>
>







4200
4201
4202
4203
4204
4205
4206

4207
4208
4209
4210
4211
4212
4213
4214
4215
4216
4217
4218
4219
4220
4221
  VERIFY( if( tos<1 ) goto not_enough_stack; )
  if( Stringify(p, tos) || Stringify(p, nos) ) goto no_mem;
  pSorter = sqliteMalloc( sizeof(Sorter) );
  if( pSorter==0 ) goto no_mem;
  pSorter->pNext = p->pSort;
  p->pSort = pSorter;
  assert( aStack[tos].flags & STK_Dyn );

  pSorter->nKey = aStack[tos].n;
  pSorter->zKey = zStack[tos];
  pSorter->nData = aStack[nos].n;
  if( aStack[nos].flags & STK_Dyn ){
    pSorter->pData = zStack[nos];
  }else{
    pSorter->pData = sqliteStrDup(zStack[nos]);
  }
  aStack[tos].flags = 0;
  aStack[nos].flags = 0;
  zStack[tos] = 0;
  zStack[nos] = 0;
  p->tos -= 2;
  break;
}
Changes to test/expr.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 expressions.
#
# $Id: expr.test,v 1.24 2002/05/31 15:51:26 drh Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}













|







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 expressions.
#
# $Id: expr.test,v 1.25 2002/06/29 02:20:09 drh Exp $

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

# Create a table to work with.
#
execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)}
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
test_expr expr-3.25 {t1=NULL, t2='hi'} {t1 isnull} 1
test_expr expr-3.25b {t1=NULL, t2='hi'} {t1 is null} 1
test_expr expr-3.26 {t1=NULL, t2='hi'} {t2 isnull} 0
test_expr expr-3.27 {t1=NULL, t2='hi'} {t1 notnull} 0
test_expr expr-3.28 {t1=NULL, t2='hi'} {t2 notnull} 1
test_expr expr-3.28b {t1=NULL, t2='hi'} {t2 is not null} 1
test_expr expr-3.29 {t1='xyz', t2='abc'} {t1||t2} {xyzabc}
test_expr expr-3.30 {t1=NULL, t2='abc'} {t1||t2} {abc}
test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {xyz}
test_expr expr-3.32 {t1='xyz', t2='abc'} {t1||' hi '||t2} {{xyz hi abc}}

test_expr expr-4.1 {t1='abc', t2='Abc'} {t1<t2} 0
test_expr expr-4.2 {t1='abc', t2='Abc'} {t1>t2} 1
test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 0
test_expr expr-4.4 {t1='abc', t2='Bbc'} {t1>t2} 1
test_expr expr-4.5 {t1='0', t2='0.0'} {t1==t2} 1







|
|







177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
test_expr expr-3.25 {t1=NULL, t2='hi'} {t1 isnull} 1
test_expr expr-3.25b {t1=NULL, t2='hi'} {t1 is null} 1
test_expr expr-3.26 {t1=NULL, t2='hi'} {t2 isnull} 0
test_expr expr-3.27 {t1=NULL, t2='hi'} {t1 notnull} 0
test_expr expr-3.28 {t1=NULL, t2='hi'} {t2 notnull} 1
test_expr expr-3.28b {t1=NULL, t2='hi'} {t2 is not null} 1
test_expr expr-3.29 {t1='xyz', t2='abc'} {t1||t2} {xyzabc}
test_expr expr-3.30 {t1=NULL, t2='abc'} {t1||t2} {{}}
test_expr expr-3.31 {t1='xyz', t2=NULL} {t1||t2} {{}}
test_expr expr-3.32 {t1='xyz', t2='abc'} {t1||' hi '||t2} {{xyz hi abc}}

test_expr expr-4.1 {t1='abc', t2='Abc'} {t1<t2} 0
test_expr expr-4.2 {t1='abc', t2='Abc'} {t1>t2} 1
test_expr expr-4.3 {t1='abc', t2='Bbc'} {t1<t2} 0
test_expr expr-4.4 {t1='abc', t2='Bbc'} {t1>t2} 1
test_expr expr-4.5 {t1='0', t2='0.0'} {t1==t2} 1
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.12 2002/05/29 23:22:23 drh 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.13 2002/06/29 02:20:09 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231










232
233
234
235
236
237
238
239
240
241
242
243
244
245



246





247










248
do_test func-5.4 {
  catchsql {SELECT upper(a,5) FROM t2}
} {1 {wrong number of arguments to function upper()}}
do_test func-5.5 {
  catchsql {SELECT upper(*) FROM t2}
} {1 {wrong number of arguments to function upper()}}

# Test the coalesce() function
#
do_test func-6.1 {
  execsql {SELECT coalesce(a,'xyz') FROM t2}
} {1 xyz 345 xyz 67890}
do_test func-6.2 {
  execsql {SELECT coalesce(upper(a),'nil') FROM t2}
} {1 nil 345 nil 67890}











# Test the last_insert_rowid() function
#
do_test func-7.1 {
  execsql {SELECT last_insert_rowid()}
} [db last_insert_rowid]

# 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}




















finish_test







|







>
>
>
>
>
>
>
>
>
>














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

217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
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
do_test func-5.4 {
  catchsql {SELECT upper(a,5) FROM t2}
} {1 {wrong number of arguments to function upper()}}
do_test func-5.5 {
  catchsql {SELECT upper(*) FROM t2}
} {1 {wrong number of arguments to function upper()}}

# Test the coalesce() and nullif() functions
#
do_test func-6.1 {
  execsql {SELECT coalesce(a,'xyz') FROM t2}
} {1 xyz 345 xyz 67890}
do_test func-6.2 {
  execsql {SELECT coalesce(upper(a),'nil') FROM t2}
} {1 nil 345 nil 67890}
do_test func-6.3 {
  execsql {SELECT coalesce(nullif(1,1),'nil')}
} {nil}
do_test func-6.4 {
  execsql {SELECT coalesce(nullif(1,2),'nil')}
} {1}
do_test func-6.5 {
  execsql {SELECT coalesce(nullif(1,NULL),'nil')}
} {1}


# Test the last_insert_rowid() function
#
do_test func-7.1 {
  execsql {SELECT last_insert_rowid()}
} [db last_insert_rowid]

# 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;
  }
} {1}

finish_test
Changes to test/subselect.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.5 2002/05/26 20:54:35 drh Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    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 SELECT statements that are part of
# expressions.
#
# $Id: subselect.test,v 1.6 2002/06/29 02:20:09 drh Exp $

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

# Basic sanity checking.  Try a simple subselect.
#
do_test subselect-1.1 {
77
78
79
80
81
82
83
84


















85
# Try something useful.  Delete every entry from t2 where the
# x value is less than half of the maximum.
#
do_test subselect-1.6 {
  execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
  execsql {SELECT x FROM t2 ORDER BY x}
} {2 3 4}



















finish_test








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

77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
# Try something useful.  Delete every entry from t2 where the
# x value is less than half of the maximum.
#
do_test subselect-1.6 {
  execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
  execsql {SELECT x FROM t2 ORDER BY x}
} {2 3 4}

# Make sure sorting works for SELECTs there used as a scalar expression.
#
do_test subselect-2.1 {
  execsql {
    SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC)
  }
} {1 5}
do_test subselect-2.2 {
  execsql {
    SELECT 1 IN (SELECT a FROM t1 ORDER BY a);
  }
} {1}
do_test subselect-2.3 {
  execsql {
    SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);
  }
} {0}

finish_test