SQLite

Check-in [da61aa1d23]
Login

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

Overview
Comment:Refinements to NULL processing: NULLs are indistinct for DISTINCT and UNION. Multiplying a NULL by zero yields zero. In a CASE expression, a NULL comparison is considered false, not NULL. With these changes, NULLs in SQLite now work the same as in PostgreSQL and in Oracle. (CVS 600)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: da61aa1d238539dff9c43fd9f464d311e28d669f
User & Date: drh 2002-05-31 15:51:25.000
Context
2002-06-01
21:41
Multiplying NULL by zero gives NULL, not zero. I misread the test data and coded it wrong. This check-in fixes the problem. (CVS 601) (check-in: df9cc852ad user: drh tags: trunk)
2002-05-31
15:51
Refinements to NULL processing: NULLs are indistinct for DISTINCT and UNION. Multiplying a NULL by zero yields zero. In a CASE expression, a NULL comparison is considered false, not NULL. With these changes, NULLs in SQLite now work the same as in PostgreSQL and in Oracle. (CVS 600) (check-in: da61aa1d23 user: drh tags: trunk)
2002-05-30
12:27
Once it is opened, leave the checkpoint journal file open for the duration of a transaction, rather than closing it and reopening it for each statement. (Ticket #53) (CVS 599) (check-in: 7a24336d50 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.65 2002/05/30 02:35:12 drh Exp $
*/
#include "sqliteInt.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.66 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"


/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958

959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974



975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
    }
    case TK_AS: {
      sqliteExprCode(pParse, pExpr->pLeft);
      break;
    }
    case TK_CASE: {
      int expr_end_label;
      int null_result_label;
      int jumpInst;
      int nullBypassInst;
      int addr;
      int nExpr;
      int i;

      assert(pExpr->pList);
      assert((pExpr->pList->nExpr % 2) == 0);
      assert(pExpr->pList->nExpr > 0);
      nExpr = pExpr->pList->nExpr;
      expr_end_label = sqliteVdbeMakeLabel(v);
      null_result_label = sqliteVdbeMakeLabel(v);
      if( pExpr->pLeft ){
        sqliteExprCode(pParse, pExpr->pLeft);
        nullBypassInst = sqliteVdbeAddOp(v, OP_IsNull, -1, 0);
      }
      for(i=0; i<nExpr; i=i+2){
        sqliteExprCode(pParse, pExpr->pList->a[i].pExpr);
        sqliteVdbeAddOp(v, OP_IsNull, -1, null_result_label);
        if( pExpr->pLeft ){
          sqliteVdbeAddOp(v, OP_Dup, 1, 1);
          jumpInst = sqliteVdbeAddOp(v, OP_Ne, 0, 0);

        }else{
          jumpInst = sqliteVdbeAddOp(v, OP_IfNot, 0, 0);
        }
        sqliteExprCode(pParse, pExpr->pList->a[i+1].pExpr);
        sqliteVdbeAddOp(v, OP_Goto, 0, expr_end_label);
        if( i>=nExpr-2 ){
          sqliteVdbeResolveLabel(v, null_result_label);
          sqliteVdbeAddOp(v, OP_Pop, 1, 0);
          if( pExpr->pRight!=0 ){
            sqliteVdbeAddOp(v, OP_String, 0, 0);
            sqliteVdbeAddOp(v, OP_Goto, 0, expr_end_label);
          }
        }
        addr = sqliteVdbeCurrentAddr(v);
        sqliteVdbeChangeP2(v, jumpInst, addr);
      }



      if( pExpr->pRight ){
        sqliteExprCode(pParse, pExpr->pRight);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0);
      }
      sqliteVdbeResolveLabel(v, expr_end_label);
      if( pExpr->pLeft ){
        sqliteVdbeAddOp(v, OP_Pull, 1, 0);
        sqliteVdbeAddOp(v, OP_Pop, 1, 0);
        sqliteVdbeChangeP2(v, nullBypassInst, sqliteVdbeCurrentAddr(v));
      }
    }
    break;
  }
}

/*
** Generate code for a boolean expression such that a jump is made







<

<









<


<



<


|
>

|



<
<
<
<
<
<
<
<



>
>
>






<
<
<
<
<







929
930
931
932
933
934
935

936

937
938
939
940
941
942
943
944
945

946
947

948
949
950

951
952
953
954
955
956
957
958
959








960
961
962
963
964
965
966
967
968
969
970
971





972
973
974
975
976
977
978
    }
    case TK_AS: {
      sqliteExprCode(pParse, pExpr->pLeft);
      break;
    }
    case TK_CASE: {
      int expr_end_label;

      int jumpInst;

      int addr;
      int nExpr;
      int i;

      assert(pExpr->pList);
      assert((pExpr->pList->nExpr % 2) == 0);
      assert(pExpr->pList->nExpr > 0);
      nExpr = pExpr->pList->nExpr;
      expr_end_label = sqliteVdbeMakeLabel(v);

      if( pExpr->pLeft ){
        sqliteExprCode(pParse, pExpr->pLeft);

      }
      for(i=0; i<nExpr; i=i+2){
        sqliteExprCode(pParse, pExpr->pList->a[i].pExpr);

        if( pExpr->pLeft ){
          sqliteVdbeAddOp(v, OP_Dup, 1, 1);
          jumpInst = sqliteVdbeAddOp(v, OP_Ne, 1, 0);
          sqliteVdbeAddOp(v, OP_Pop, 1, 0);
        }else{
          jumpInst = sqliteVdbeAddOp(v, OP_IfNot, 1, 0);
        }
        sqliteExprCode(pParse, pExpr->pList->a[i+1].pExpr);
        sqliteVdbeAddOp(v, OP_Goto, 0, expr_end_label);








        addr = sqliteVdbeCurrentAddr(v);
        sqliteVdbeChangeP2(v, jumpInst, addr);
      }
      if( pExpr->pLeft ){
        sqliteVdbeAddOp(v, OP_Pop, 1, 0);
      }
      if( pExpr->pRight ){
        sqliteExprCode(pParse, pExpr->pRight);
      }else{
        sqliteVdbeAddOp(v, OP_String, 0, 0);
      }
      sqliteVdbeResolveLabel(v, expr_end_label);





    }
    break;
  }
}

/*
** Generate code for a boolean expression such that a jump is made
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.18 2002/05/29 23:22:23 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.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.19 2002/05/31 15:51:25 drh Exp $
*/
#include <ctype.h>
#include <math.h>
#include <stdlib.h>
#include <assert.h>
#include "sqliteInt.h"

358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
/*
** Routines to implement min() and max() aggregate functions.
*/
static void minStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;
  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)<0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);
  }
}
static void maxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;
  if( sqlite_aggregate_count(context)==1 || sqliteCompare(argv[0],p->z)>0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;







|


















|







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
/*
** Routines to implement min() and max() aggregate functions.
*/
static void minStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;
  if( p->z==0 || sqliteCompare(argv[0],p->z)<0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
    }else{
      p->z = sqliteMalloc( len+1 );
      if( p->z==0 ) return;
    }
    strcpy(p->z, argv[0]);
  }
}
static void maxStep(sqlite_func *context, int argc, const char **argv){
  MinMaxCtx *p;
  p = sqlite_aggregate_context(context, sizeof(*p));
  if( p==0 || argc<1 || argv[0]==0 ) return;
  if( p->z==0 || sqliteCompare(argv[0],p->z)>0 ){
    int len;
    if( p->z && p->z!=p->zBuf ){
      sqliteFree(p->z);
    }
    len = strlen(argv[0]);
    if( len < sizeof(p->zBuf) ){
      p->z = p->zBuf;
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.88 2002/05/27 12:24:48 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.89 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"

/*
** Allocate a new Select structure and return a pointer to that
** structure.
*/
322
323
324
325
326
327
328




329

330
331
332
333
334
335
336
  }

  /* If the DISTINCT keyword was present on the SELECT statement
  ** and this row has been seen before, then do not make this row
  ** part of the result.
  */
  if( distinct>=0 && pEList && pEList->nExpr>0 ){




    sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr, sqliteVdbeCurrentAddr(v)+7);

    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
    sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
    sqliteVdbeAddOp(v, OP_String, 0, 0);
    sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
  }







>
>
>
>
|
>







322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
  }

  /* If the DISTINCT keyword was present on the SELECT statement
  ** and this row has been seen before, then do not make this row
  ** part of the result.
  */
  if( distinct>=0 && pEList && pEList->nExpr>0 ){
    /* For the purposes of the DISTINCT keyword to a SELECT, NULLs
    ** are indistinct.  This was confirmed by experiment in Oracle
    ** and PostgreSQL.  It seems contradictory, but it appears to be
    ** true.
    ** sqliteVdbeAddOp(v, OP_IsNull, -pEList->nExpr,sqliteVdbeCurrentAddr(v)+7);
    */
    sqliteVdbeAddOp(v, OP_MakeKey, pEList->nExpr, 1);
    sqliteVdbeAddOp(v, OP_Distinct, distinct, sqliteVdbeCurrentAddr(v)+3);
    sqliteVdbeAddOp(v, OP_Pop, pEList->nExpr+1, 0);
    sqliteVdbeAddOp(v, OP_Goto, 0, iContinue);
    sqliteVdbeAddOp(v, OP_String, 0, 0);
    sqliteVdbeAddOp(v, OP_PutStrKey, distinct, 0);
  }
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
    sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
  }else 

  /* In this mode, write each query result to the key of the temporary
  ** table iParm.
  */
  if( eDest==SRT_Union ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 1);
    sqliteVdbeAddOp(v, OP_String, 0, 0);
    sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table || eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
  }else 

  /* Construct a record from the query result, but instead of
  ** saving that record, use it as a key to delete elements from
  ** the temporary table iParm.
  */
  if( eDest==SRT_Except ){
    int addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 1);
    sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
    sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
  }else 

  /* 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.







|


















|







359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
    sqliteVdbeAddOp(v, OP_SortPut, 0, 0);
  }else 

  /* In this mode, write each query result to the key of the temporary
  ** table iParm.
  */
  if( eDest==SRT_Union ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_String, 0, 0);
    sqliteVdbeAddOp(v, OP_PutStrKey, iParm, 0);
  }else 

  /* Store the result as data using a unique key.
  */
  if( eDest==SRT_Table || eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_NewRecno, iParm, 0);
    sqliteVdbeAddOp(v, OP_Pull, 1, 0);
    sqliteVdbeAddOp(v, OP_PutIntKey, iParm, 0);
  }else 

  /* Construct a record from the query result, but instead of
  ** saving that record, use it as a key to delete elements from
  ** the temporary table iParm.
  */
  if( eDest==SRT_Except ){
    int addr = sqliteVdbeAddOp(v, OP_MakeRecord, nColumn, 0);
    sqliteVdbeAddOp(v, OP_NotFound, iParm, addr+3);
    sqliteVdbeAddOp(v, OP_Delete, iParm, 0);
  }else 

  /* 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.
Changes to src/util.c.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.44 2002/05/26 21:34:58 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** Utility functions used throughout sqlite.
**
** This file contains functions for allocating memory, comparing
** strings, and stuff like that.
**
** $Id: util.c,v 1.45 2002/05/31 15:51:25 drh Exp $
*/
#include "sqliteInt.h"
#include <stdarg.h>
#include <ctype.h>

/*
** If malloc() ever fails, this global variable gets set to 1.
724
725
726
727
728
729
730







731
732
733
734
735
736
737
*/
int sqliteSortCompare(const char *a, const char *b){
  int len;
  int res = 0;
  int isNumA, isNumB;

  while( res==0 && *a && *b ){







    isNumA = sqliteIsNumber(&a[1]);
    isNumB = sqliteIsNumber(&b[1]);
    if( isNumA ){
      double rA, rB;
      if( !isNumB ){
        res = -1;
        break;







>
>
>
>
>
>
>







724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
*/
int sqliteSortCompare(const char *a, const char *b){
  int len;
  int res = 0;
  int isNumA, isNumB;

  while( res==0 && *a && *b ){
    if( a[1]==0 ){
      res = -1;
      break;
    }else if( b[1]==0 ){
      res = +1;
      break;
    }
    isNumA = sqliteIsNumber(&a[1]);
    isNumB = sqliteIsNumber(&b[1]);
    if( isNumA ){
      double rA, rB;
      if( !isNumB ){
        res = -1;
        break;
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.150 2002/05/27 01:04:51 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.151 2002/05/31 15:51:26 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
1765
1766
1767
1768
1769
1770
1771



















1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
case OP_Multiply:
case OP_Divide:
case OP_Remainder: {
  int tos = p->tos;
  int nos = tos - 1;
  VERIFY( if( nos<0 ) goto not_enough_stack; )
  if( ((aStack[tos].flags | aStack[nos].flags) & STK_Null)!=0 ){



















    POPSTACK;
    Release(p, nos);
    aStack[nos].flags = STK_Null;
  }else if( (aStack[tos].flags & aStack[nos].flags & STK_Int)==STK_Int ){
    int a, b;
    a = aStack[tos].i;
    b = aStack[nos].i;
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;







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


|







1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
case OP_Multiply:
case OP_Divide:
case OP_Remainder: {
  int tos = p->tos;
  int nos = tos - 1;
  VERIFY( if( nos<0 ) goto not_enough_stack; )
  if( ((aStack[tos].flags | aStack[nos].flags) & STK_Null)!=0 ){
    int resultType = STK_Null;
    if( pOp->opcode==OP_Multiply ){
      /* Special case: multiplying NULL by zero gives a zero result, not a
      ** NULL result as it would normally. */
      if( (aStack[tos].flags & (STK_Int|STK_Real))!=0
              || ((aStack[tos].flags & STK_Str)!=0 && isNumber(zStack[tos])) ){
        Integerify(p,tos);
        if( aStack[tos].i==0 ){
          resultType = STK_Int;
          aStack[nos].i = 0;
        }
      }else if( (aStack[nos].flags & (STK_Int|STK_Real))!=0
              || ((aStack[nos].flags & STK_Str)!=0 && isNumber(zStack[nos])) ){
        Integerify(p,nos);
        if( aStack[nos].i==0 ){
          resultType = STK_Int;
        }
      }
    }
    POPSTACK;
    Release(p, nos);
    aStack[nos].flags = resultType;
  }else if( (aStack[tos].flags & aStack[nos].flags & STK_Int)==STK_Int ){
    int a, b;
    a = aStack[tos].i;
    b = aStack[nos].i;
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
2342
2343
2344
2345
2346
2347
2348




2349
2350
2351
2352
2353
2354
2355
** that go into building the record is NULL, then add some extra
** bytes to the record to make it distinct for other entries created
** during the same run of the VDBE.  The extra bytes added are a
** counter that is reset with each run of the VDBE, so records
** created this way will not necessarily be distinct across runs.
** But they should be distinct for transient tables (created using
** OP_OpenTemp) which is what they are intended for.




*/
case OP_MakeRecord: {
  char *zNewRecord;
  int nByte;
  int nField;
  int i, j;
  int idxWidth;







>
>
>
>







2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
** that go into building the record is NULL, then add some extra
** bytes to the record to make it distinct for other entries created
** during the same run of the VDBE.  The extra bytes added are a
** counter that is reset with each run of the VDBE, so records
** created this way will not necessarily be distinct across runs.
** But they should be distinct for transient tables (created using
** OP_OpenTemp) which is what they are intended for.
**
** (Later:) The P2==1 option was intended to make NULLs distinct
** for the UNION operator.  But I have since discovered that NULLs
** are indistinct for UNION.  So this option is never used.
*/
case OP_MakeRecord: {
  char *zNewRecord;
  int nByte;
  int nField;
  int i, j;
  int idxWidth;
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.23 2002/05/30 12:27:03 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.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)}
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
test_expr expr-6.66 {t1='ac', t2=NULL} {t1 NOT GLOB t2} {{}}

test_expr expr-case.1 {i1=1, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
test_expr expr-case.2 {i1=2, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq
test_expr expr-case.3 {i1=NULL, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} {{}}
test_expr expr-case.4 {i1=2, i2=NULL} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} {{}}
test_expr expr-case.5 {i1=2} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two
test_expr expr-case.6 {i1=1} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} one
test_expr expr-case.7 {i1=2} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} {{}}
test_expr expr-case.8 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} {{}}
test_expr expr-case.9 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error
test_expr expr-case.10 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}}
test_expr expr-case.11 {i1=null} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 3 END} {{}}
test_expr expr-case.12 {i1=1} \
	{CASE i1 WHEN 1 THEN null WHEN 2 THEN 'two' ELSE 3 END} {{}}
test_expr expr-case.13 {i1=7} \
	{ CASE WHEN i1 < 5 THEN 'low' 
	       WHEN i1 < 10 THEN 'medium' 
               WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium








|

|





|

|





|







333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
test_expr expr-6.66 {t1='ac', t2=NULL} {t1 NOT GLOB t2} {{}}

test_expr expr-case.1 {i1=1, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
test_expr expr-case.2 {i1=2, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} eq
test_expr expr-case.3 {i1=NULL, i2=2} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
test_expr expr-case.4 {i1=2, i2=NULL} \
	{CASE WHEN i1 = i2 THEN 'eq' ELSE 'ne' END} ne
test_expr expr-case.5 {i1=2} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} two
test_expr expr-case.6 {i1=1} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} one
test_expr expr-case.7 {i1=2} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error
test_expr expr-case.8 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN NULL THEN 'two' ELSE 'error' END} error
test_expr expr-case.9 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'error' END} error
test_expr expr-case.10 {i1=3} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END} {{}}
test_expr expr-case.11 {i1=null} \
	{CASE i1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 3 END} 3
test_expr expr-case.12 {i1=1} \
	{CASE i1 WHEN 1 THEN null WHEN 2 THEN 'two' ELSE 3 END} {{}}
test_expr expr-case.13 {i1=7} \
	{ CASE WHEN i1 < 5 THEN 'low' 
	       WHEN i1 < 10 THEN 'medium' 
               WHEN i1 < 15 THEN 'high' ELSE 'error' END} medium

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.3 2002/05/29 23:22:23 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.4 2002/05/31 15:51:26 drh Exp $

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

do_test minmax-1.0 {
  execsql {
    BEGIN;
124
125
126
127
128
129
130





131









132

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM
      (SELECT * FROM t1 UNION SELECT NULL as 'x', NULL as 'y')
  }
} {1 20}















finish_test







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

124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146

do_test minmax-4.1 {
  execsql {
    SELECT coalesce(min(x),-1), coalesce(max(x),-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}
do_test minmax-4.3 {
  execsql {
    SELECT y, count(x), count(*) FROM
      (SELECT null, y+1 FROM t1 UNION SELECT * FROM t1)
    GROUP BY y ORDER BY y;
  }
} {1 1 1 2 2 3 3 4 5 4 8 9 5 5 6 6 0 1}

finish_test
Added test/null.test.














































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
# 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.
#
# This file implements tests for proper treatment of the special
# value NULL.
#

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

# Create a table and some data to work with.
#
do_test null-1.0 {
  execsql {
    begin;
    create table t1(a,b,c);
    insert into t1 values(1,0,0);
    insert into t1 values(2,0,1);
    insert into t1 values(3,1,0);
    insert into t1 values(4,1,1);
    insert into t1 values(5,null,0);
    insert into t1 values(6,null,1);
    insert into t1 values(7,null,null);
    commit;
    select * from t1;
  }
} {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}}

# Check for how arithmetic expressions handle NULL
#
do_test null-1.1 {
  execsql {
    select ifnull(a+b,99) from t1;
  }
} {1 2 4 5 99 99 99}
do_test null-1.2 {
  execsql {
    select ifnull(b*c,99) from t1;
  }
} {0 0 0 1 0 99 99}
do_test null-1.2.1 {
  execsql {
    select ifnull(c*b,99) from t1;
  }
} {0 0 0 1 0 99 99}

# Check to see how the CASE expression handles NULL values.  The
# first WHEN for which the test expression is TRUE is selected.
# FALSE and UNKNOWN test expressions are skipped.
#
do_test null-2.1 {
  execsql {
    select ifnull(case when b<>0 then 1 else 0 end, 99) from t1;
  }
} {0 0 1 1 0 0 0}
do_test null-2.2 {
  execsql {
    select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1;
  }
} {1 1 0 0 0 0 0}
do_test null-2.3 {
  execsql {
    select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1;
  }
} {0 0 0 1 0 0 0}
do_test null-2.4 {
  execsql {
    select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1;
  }
} {1 1 1 0 1 0 0}
do_test null-2.5 {
  execsql {
    select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1;
  }
} {0 1 1 1 0 1 0}
do_test null-2.6 {
  execsql {
    select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1;
  }
} {1 0 0 0 0 0 0}
do_test null-2.7 {
  execsql {
    select ifnull(case b when c then 1 else 0 end, 99) from t1;
  }
} {1 0 0 1 0 0 0}
do_test null-2.8 {
  execsql {
    select ifnull(case c when b then 1 else 0 end, 99) from t1;
  }
} {1 0 0 1 0 0 0}

# 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 {
    select a from t1 where b<10
  }
} {1 2 3 4}
do_test null-4.2 {
  execsql {
    select a from t1 where not b>10
  }
} {1 2 3 4}
do_test null-4.3 {
  execsql {
    select a from t1 where b<10 or c=1;
  }
} {1 2 3 4 6}
do_test null-4.4 {
  execsql {
    select a from t1 where b<10 and c=1;
  }
} {2 4}
do_test null-4.5 {
  execsql {
    select a from t1 where not (b<10 and c=1);
  }
} {1 3 5}

# The DISTINCT keyword on a SELECT statement should treat NULL values
# as distinct
#
do_test null-5.1 {
  execsql {
    select distinct b from t1 order by b;
  }
} {{} 0 1}

# A UNION to two queries should treat NULL values
# as distinct
#
do_test null-6.1 {
  execsql {
    select b from t1 union select c from t1 order by c;
  }
} {{} 0 1}

# The UNIQUE constraint only applies to non-null values
#
do_test null-7.1 {
  execsql {
    create table t2(a, b unique on conflict ignore);
    insert into t2 values(1,1);
    insert into t2 values(2,null);
    insert into t2 values(3,null);
    insert into t2 values(4,1);
    select a from t2;
  }
} {1 2 3}
do_test null-7.2 {
  execsql {
    create table t3(a, b, c, unique(b,c) on conflict ignore);
    insert into t3 values(1,1,1);
    insert into t3 values(2,null,1);
    insert into t3 values(3,null,1);
    insert into t3 values(4,1,1);
    select a from t3;
  }
} {1 2 3}



finish_test
Changes to test/select4.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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.8 2002/05/27 01:04:51 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]







|







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 UNION, INTERSECT and EXCEPT operators
# in SELECT statements.
#
# $Id: select4.test,v 1.9 2002/05/31 15:51:26 drh Exp $

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

# Build some test data
#
set fd [open data1.txt w]
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
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
    SELECT log, count(*) FROM t1 GROUP BY log
    UNION
    SELECT log, n FROM t1 WHERE n=7
    ORDER BY count(*), log;
  }
} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}


# NULLs are distinct.  Make sure the UNION operator recognizes this
#
do_test select4-6.3 {
  execsql {
    SELECT NULL UNION SELECT NULL UNION
    SELECT 1 UNION SELECT 2 AS 'x'
    ORDER BY x;
  }
} {{} {} 1 2}
do_test select4-6.3 {
  execsql {
    SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT 1 UNION ALL SELECT 2 AS 'x'
    ORDER BY x;
  }
} {{} {} 1 2}

# Make sure the DISTINCT keyword treats NULLs as DISTINCT
#
do_test select4-6.4 {
  execsql {
    SELECT * FROM (
       SELECT NULL, 1 UNION ALL SELECT NULL, 1
    );
  }
} {{} 1 {} 1}
do_test select4-6.5 {
  execsql {
    SELECT DISTINCT * FROM (
       SELECT NULL, 1 UNION ALL SELECT NULL, 1
    );
  }
} {{} 1 {} 1}
do_test select4-6.6 {
  execsql {
    SELECT DISTINCT * FROM (
       SELECT 1,2  UNION ALL SELECT 1,2
    );
  }
} {1 2}

# Test distinctness of NULL in other ways.
#
do_test select4-6.7 {
  execsql {
    SELECT NULL EXCEPT SELECT NULL
  }
} {{}}


# Make sure column names are correct when a compound select appears as
# an expression in the WHERE clause.
#
do_test select4-7.1 {
  execsql {







>
|







|
|







|














|














|







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
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
    SELECT log, count(*) FROM t1 GROUP BY log
    UNION
    SELECT log, n FROM t1 WHERE n=7
    ORDER BY count(*), log;
  }
} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

# NULLs are indistinct for the UNION operator.
# Make sure the UNION operator recognizes this
#
do_test select4-6.3 {
  execsql {
    SELECT NULL UNION SELECT NULL UNION
    SELECT 1 UNION SELECT 2 AS 'x'
    ORDER BY x;
  }
} {{} 1 2}
do_test select4-6.3.1 {
  execsql {
    SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT 1 UNION ALL SELECT 2 AS 'x'
    ORDER BY x;
  }
} {{} {} 1 2}

# Make sure the DISTINCT keyword treats NULLs as indistinct.
#
do_test select4-6.4 {
  execsql {
    SELECT * FROM (
       SELECT NULL, 1 UNION ALL SELECT NULL, 1
    );
  }
} {{} 1 {} 1}
do_test select4-6.5 {
  execsql {
    SELECT DISTINCT * FROM (
       SELECT NULL, 1 UNION ALL SELECT NULL, 1
    );
  }
} {{} 1}
do_test select4-6.6 {
  execsql {
    SELECT DISTINCT * FROM (
       SELECT 1,2  UNION ALL SELECT 1,2
    );
  }
} {1 2}

# Test distinctness of NULL in other ways.
#
do_test select4-6.7 {
  execsql {
    SELECT NULL EXCEPT SELECT NULL
  }
} {}


# Make sure column names are correct when a compound select appears as
# an expression in the WHERE clause.
#
do_test select4-7.1 {
  execsql {