SQLite

Check-in [ce06c123d0]
Login

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

Overview
Comment:Create separate affinities for INTEGER and REAL. (CVS 2766)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ce06c123d0c5663dbaf263c2e0aaf5d9cdeb2ccd
User & Date: drh 2005-11-14 22:29:05.000
Context
2005-11-15
02:14
Fix a bug in UTF-16 handling introduced by the previous check-in. (CVS 2767) (check-in: 25fa16a2e1 user: drh tags: trunk)
2005-11-14
22:29
Create separate affinities for INTEGER and REAL. (CVS 2766) (check-in: ce06c123d0 user: drh tags: trunk)
11:51
Fix documentation typo. (CVS 2765) (check-in: c9b413ea22 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/analyze.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2005 July 8
**
** 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 contains code associated with the ANALYZE command.
**
** @(#) $Id: analyze.c,v 1.10 2005/11/01 15:48:24 drh Exp $
*/
#ifndef SQLITE_OMIT_ANALYZE
#include "sqliteInt.h"

/*
** This routine generates code that opens the sqlite_stat1 table on cursor
** iStatCur.













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2005 July 8
**
** 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 contains code associated with the ANALYZE command.
**
** @(#) $Id: analyze.c,v 1.11 2005/11/14 22:29:05 drh Exp $
*/
#ifndef SQLITE_OMIT_ANALYZE
#include "sqliteInt.h"

/*
** This routine generates code that opens the sqlite_stat1 table on cursor
** iStatCur.
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
      sqlite3VdbeAddOp(v, OP_ToInt, 0, 0);
      if( i==nCol-1 ){
        sqlite3VdbeAddOp(v, OP_Concat, nCol*2-1, 0);
      }else{
        sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
      }
    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "ttt", 0);
    sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0);
    sqlite3VdbeJumpHere(v, addr);
  }
}

/*
** Generate code that will cause the most recent index analysis to







|







191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
      sqlite3VdbeAddOp(v, OP_ToInt, 0, 0);
      if( i==nCol-1 ){
        sqlite3VdbeAddOp(v, OP_Concat, nCol*2-1, 0);
      }else{
        sqlite3VdbeAddOp(v, OP_Dup, 1, 0);
      }
    }
    sqlite3VdbeOp3(v, OP_MakeRecord, 3, 0, "aaa", 0);
    sqlite3VdbeAddOp(v, OP_Insert, iStatCur, 0);
    sqlite3VdbeJumpHere(v, addr);
  }
}

/*
** Generate code that will cause the most recent index analysis to
Changes to src/build.c.
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.354 2005/11/03 02:03:13 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
**     CREATE INDEX
**     DROP INDEX
**     creating ID lists
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**
** $Id: build.c,v 1.355 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Initialize the pParse structure as needed.
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896



897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920




921
922







923
924
925
926
927
928
929
930
931
** associated affinity type.
**
** This routine does a case-independent search of zType for the 
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of 
** the table take priority. For example, if zType is 'BLOBINT', 
** SQLITE_AFF_NUMERIC is returned.
**
** Substring     | Affinity
** --------------------------------
** 'INT'         | SQLITE_AFF_INTEGER
** 'CHAR'        | SQLITE_AFF_TEXT
** 'CLOB'        | SQLITE_AFF_TEXT
** 'TEXT'        | SQLITE_AFF_TEXT
** 'BLOB'        | SQLITE_AFF_NONE



**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
**
** The SQLITE_AFF_INTEGER type is only returned if useIntType is true.
** If useIntType is false, then SQLITE_AFF_INTEGER is reported back
** as SQLITE_AFF_NUMERIC
*/
char sqlite3AffinityType(const Token *pType, int useIntType){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const unsigned char *zIn = pType->z;
  const unsigned char *zEnd = &pType->z[pType->n];

  while( zIn!=zEnd ){
    h = (h<<8) + sqlite3UpperToLower[*zIn];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT; 
    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */




        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_NONE;







    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = useIntType ? SQLITE_AFF_INTEGER : SQLITE_AFF_NUMERIC; 
      break;
    }
  }

  return aff;
}








|








>
>
>



<
<
<
<

|















>
>
>
>

|
>
>
>
>
>
>
>

|







881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902




903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
** associated affinity type.
**
** This routine does a case-independent search of zType for the 
** substrings in the following table. If one of the substrings is
** found, the corresponding affinity is returned. If zType contains
** more than one of the substrings, entries toward the top of 
** the table take priority. For example, if zType is 'BLOBINT', 
** SQLITE_AFF_INTEGER is returned.
**
** Substring     | Affinity
** --------------------------------
** 'INT'         | SQLITE_AFF_INTEGER
** 'CHAR'        | SQLITE_AFF_TEXT
** 'CLOB'        | SQLITE_AFF_TEXT
** 'TEXT'        | SQLITE_AFF_TEXT
** 'BLOB'        | SQLITE_AFF_NONE
** 'REAL'        | SQLITE_AFF_REAL
** 'FLOA'        | SQLITE_AFF_REAL
** 'DOUB'        | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.




*/
char sqlite3AffinityType(const Token *pType){
  u32 h = 0;
  char aff = SQLITE_AFF_NUMERIC;
  const unsigned char *zIn = pType->z;
  const unsigned char *zEnd = &pType->z[pType->n];

  while( zIn!=zEnd ){
    h = (h<<8) + sqlite3UpperToLower[*zIn];
    zIn++;
    if( h==(('c'<<24)+('h'<<16)+('a'<<8)+'r') ){             /* CHAR */
      aff = SQLITE_AFF_TEXT; 
    }else if( h==(('c'<<24)+('l'<<16)+('o'<<8)+'b') ){       /* CLOB */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('t'<<24)+('e'<<16)+('x'<<8)+'t') ){       /* TEXT */
      aff = SQLITE_AFF_TEXT;
    }else if( h==(('b'<<24)+('l'<<16)+('o'<<8)+'b')          /* BLOB */
        && (aff==SQLITE_AFF_NUMERIC || aff==SQLITE_AFF_REAL) ){
      aff = SQLITE_AFF_NONE;
#ifndef SQLITE_OMIT_FLOATING_POINT
    }else if( h==(('r'<<24)+('e'<<16)+('a'<<8)+'l')          /* REAL */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('f'<<24)+('l'<<16)+('o'<<8)+'a')          /* FLOA */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
    }else if( h==(('d'<<24)+('o'<<16)+('u'<<8)+'b')          /* DOUB */
        && aff==SQLITE_AFF_NUMERIC ){
      aff = SQLITE_AFF_REAL;
#endif
    }else if( (h&0x00FFFFFF)==(('i'<<16)+('n'<<8)+'t') ){    /* INT */
      aff = SQLITE_AFF_INTEGER;
      break;
    }
  }

  return aff;
}

945
946
947
948
949
950
951
952
953
954
955
956
957
958
959

  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;
  if( i<0 ) return;
  pCol = &p->aCol[i];
  sqliteFree(pCol->zType);
  pCol->zType = sqlite3NameFromToken(pType);
  pCol->affinity = sqlite3AffinityType(pType, 0);
}

/*
** The expression is the default value for the most recently added column
** of the table currently under construction.
**
** Default value expressions must be constant.  Raise an exception if this







|







955
956
957
958
959
960
961
962
963
964
965
966
967
968
969

  if( (p = pParse->pNewTable)==0 ) return;
  i = p->nCol-1;
  if( i<0 ) return;
  pCol = &p->aCol[i];
  sqliteFree(pCol->zType);
  pCol->zType = sqlite3NameFromToken(pType);
  pCol->affinity = sqlite3AffinityType(pType);
}

/*
** The expression is the default value for the most recently added column
** of the table currently under construction.
**
** Default value expressions must be constant.  Raise an exception if this
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.236 2005/11/05 15:07:56 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**







|







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.237 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Return the 'affinity' of the expression pExpr if any.
**
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
    return sqlite3ExprAffinity(pExpr->pLeft);
  }
  if( op==TK_SELECT ){
    return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    return sqlite3AffinityType(&pExpr->token, 0);
  }
#endif
  return pExpr->affinity;
}

/*
** Return the default collation sequence for the expression pExpr. If







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
    return sqlite3ExprAffinity(pExpr->pLeft);
  }
  if( op==TK_SELECT ){
    return sqlite3ExprAffinity(pExpr->pSelect->pEList->a[0].pExpr);
  }
#ifndef SQLITE_OMIT_CAST
  if( op==TK_CAST ){
    return sqlite3AffinityType(&pExpr->token);
  }
#endif
  return pExpr->affinity;
}

/*
** Return the default collation sequence for the expression pExpr. If
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
*/
char sqlite3CompareAffinity(Expr *pExpr, char aff2){
  char aff1 = sqlite3ExprAffinity(pExpr);
  if( aff1 && aff2 ){
    /* Both sides of the comparison are columns. If one has numeric
    ** affinity, use that. Otherwise use no affinity.
    */
    if( aff1==SQLITE_AFF_NUMERIC || aff2==SQLITE_AFF_NUMERIC ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_NONE;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column.  Compare the
    ** results directly.







|







74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
*/
char sqlite3CompareAffinity(Expr *pExpr, char aff2){
  char aff1 = sqlite3ExprAffinity(pExpr);
  if( aff1 && aff2 ){
    /* Both sides of the comparison are columns. If one has numeric
    ** affinity, use that. Otherwise use no affinity.
    */
    if( sqlite3IsNumericAffinity(aff1) || sqlite3IsNumericAffinity(aff2) ){
      return SQLITE_AFF_NUMERIC;
    }else{
      return SQLITE_AFF_NONE;
    }
  }else if( !aff1 && !aff2 ){
    /* Neither side of the comparison is a column.  Compare the
    ** results directly.
122
123
124
125
126
127
128

129






130
131
132
133
134
135
136
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);

  return (aff==SQLITE_AFF_NONE) || (aff==idx_affinity);






}

/*
** Return the P1 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
** If jumpIfNull is true, then set the low byte of the returned
** P1 value to tell the opcode to jump if either expression







>
|
>
>
>
>
>
>







122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
** pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
** idx_affinity is the affinity of an indexed column. Return true
** if the index with affinity idx_affinity may be used to implement
** the comparison in pExpr.
*/
int sqlite3IndexAffinityOk(Expr *pExpr, char idx_affinity){
  char aff = comparisonAffinity(pExpr);
  switch( aff ){
    case SQLITE_AFF_NONE:
      return 1;
    case SQLITE_AFF_TEXT:
      return idx_affinity==SQLITE_AFF_TEXT;
    default:
      return sqlite3IsNumericAffinity(idx_affinity);
  }
}

/*
** Return the P1 value that should be used for a binary comparison
** opcode (OP_Eq, OP_Ge etc.) used to compare pExpr1 and pExpr2.
** If jumpIfNull is true, then set the low byte of the returned
** P1 value to tell the opcode to jump if either expression
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946

    /*
    ** Perhaps the name is a reference to the ROWID
    */
    if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){
      cnt = 1;
      pExpr->iColumn = -1;
      pExpr->affinity = SQLITE_AFF_NUMERIC;
    }

    /*
    ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z
    ** might refer to an result-set alias.  This happens, for example, when
    ** we are resolving names in the WHERE clause of the following command:
    **







|







939
940
941
942
943
944
945
946
947
948
949
950
951
952
953

    /*
    ** Perhaps the name is a reference to the ROWID
    */
    if( cnt==0 && cntTab==1 && sqlite3IsRowid(zCol) ){
      cnt = 1;
      pExpr->iColumn = -1;
      pExpr->affinity = SQLITE_AFF_INTEGER;
    }

    /*
    ** If the input is of the form Z (not Y.Z or X.Y.Z) then the name Z
    ** might refer to an result-set alias.  This happens, for example, when
    ** we are resolving names in the WHERE clause of the following command:
    **
1478
1479
1480
1481
1482
1483
1484


1485
1486





1487
1488
1489
1490
1491
1492
1493
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */
        assert( pParse->ckOffset>0 );
        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
      }else if( pExpr->iColumn>=0 ){


        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, pExpr->iColumn);
        sqlite3ColumnDefault(v, pExpr->pTab, pExpr->iColumn);





      }else{
        sqlite3VdbeAddOp(v, OP_Rowid, pExpr->iTable, 0);
      }
      break;
    }
    case TK_INTEGER: {
      codeInteger(v, pExpr->token.z, pExpr->token.n);







>
>
|
|
>
>
>
>
>







1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
    }
    case TK_COLUMN: {
      if( pExpr->iTable<0 ){
        /* This only happens when coding check constraints */
        assert( pParse->ckOffset>0 );
        sqlite3VdbeAddOp(v, OP_Dup, pParse->ckOffset-pExpr->iColumn-1, 1);
      }else if( pExpr->iColumn>=0 ){
        Table *pTab = pExpr->pTab;
        int iCol = pExpr->iColumn;
        sqlite3VdbeAddOp(v, OP_Column, pExpr->iTable, iCol);
        sqlite3ColumnDefault(v, pTab, iCol);
#ifndef SQLITE_OMIT_FLOATING_POINT
        if( pTab && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){
          sqlite3VdbeAddOp(v, OP_RealAffinity, 0, 0);
        }
#endif
      }else{
        sqlite3VdbeAddOp(v, OP_Rowid, pExpr->iTable, 0);
      }
      break;
    }
    case TK_INTEGER: {
      codeInteger(v, pExpr->token.z, pExpr->token.n);
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541

1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
      break;
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, op;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      aff = sqlite3AffinityType(&pExpr->token, 1);
      switch( aff ){
        case SQLITE_AFF_INTEGER:   op = OP_ToInt;      break;

        case SQLITE_AFF_NUMERIC:   op = OP_ToNumeric;  break;
        case SQLITE_AFF_TEXT:      op = OP_ToText;     break;
        case SQLITE_AFF_NONE:      op = OP_ToBlob;     break;
      }
      sqlite3VdbeAddOp(v, op, 0, 0);
      stackChng = 0;
      break;
    }
#endif /* SQLITE_OMIT_CAST */
    case TK_LT:
    case TK_LE:







|
|
|
>
|
|
|
<







1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559

1560
1561
1562
1563
1564
1565
1566
      break;
    }
#ifndef SQLITE_OMIT_CAST
    case TK_CAST: {
      /* Expressions of the form:   CAST(pLeft AS token) */
      int aff, op;
      sqlite3ExprCode(pParse, pExpr->pLeft);
      aff = sqlite3AffinityType(&pExpr->token);
      op = aff - SQLITE_AFF_TEXT + OP_ToText;
      assert( op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
      assert( op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
      assert( op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
      assert( op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
      assert( op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );

      sqlite3VdbeAddOp(v, op, 0, 0);
      stackChng = 0;
      break;
    }
#endif /* SQLITE_OMIT_CAST */
    case TK_LT:
    case TK_LE:
Changes to src/insert.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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.147 2005/11/03 02:15:03 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:







|







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 INSERT statements in SQLite.
**
** $Id: insert.c,v 1.148 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"

/*
** Set P3 of the most recently inserted opcode to a column affinity
** string for index pIdx. A column affinity string has one character
** for each column in the table, according to the affinity of the column:
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
    counterMem = pParse->nMem++;
    sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqlite3VdbeAddOp(v, OP_OpenRead, iCur, pDb->pSeqTab->tnum);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, 2);
    sqlite3VdbeAddOp(v, OP_Rewind, iCur, base+13);
    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
    sqlite3VdbeAddOp(v, OP_Ne, 28417, base+12);
    sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, counterRowid, 1);
    sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
    sqlite3VdbeAddOp(v, OP_MemStore, counterMem, 1);
    sqlite3VdbeAddOp(v, OP_Goto, 0, base+13);
    sqlite3VdbeAddOp(v, OP_Next, iCur, base+4);
    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);







|







301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
    counterMem = pParse->nMem++;
    sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0);
    sqlite3VdbeAddOp(v, OP_OpenRead, iCur, pDb->pSeqTab->tnum);
    sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, 2);
    sqlite3VdbeAddOp(v, OP_Rewind, iCur, base+13);
    sqlite3VdbeAddOp(v, OP_Column, iCur, 0);
    sqlite3VdbeOp3(v, OP_String8, 0, 0, pTab->zName, 0);
    sqlite3VdbeAddOp(v, OP_Ne, 0x100, base+12);
    sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
    sqlite3VdbeAddOp(v, OP_MemStore, counterRowid, 1);
    sqlite3VdbeAddOp(v, OP_Column, iCur, 1);
    sqlite3VdbeAddOp(v, OP_MemStore, counterMem, 1);
    sqlite3VdbeAddOp(v, OP_Goto, 0, base+13);
    sqlite3VdbeAddOp(v, OP_Next, iCur, base+4);
    sqlite3VdbeAddOp(v, OP_Close, iCur, 0);
Changes to src/parse.y.
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** 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.183 2005/11/06 04:06:59 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.







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
**
*************************************************************************
** 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.184 2005/11/14 22:29:05 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.
90
91
92
93
94
95
96



97
98
99
100
101
102
103

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
%nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          COLUMN AGG_FUNCTION AGG_COLUMN CONST_FUNC.




// Input is a single SQL command
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
cmdx ::= cmd.           { sqlite3FinishCoding(pParse); }
ecmd ::= SEMI.







>
>
>







90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

// These are extra tokens used by the lexer but never seen by the
// parser.  We put them in a rule so that the parser generator will
// add them to the parse.h output file.
//
%nonassoc END_OF_FILE ILLEGAL SPACE UNCLOSED_STRING COMMENT FUNCTION
          COLUMN AGG_FUNCTION AGG_COLUMN CONST_FUNC.

// Extra tokens used by the code generator by never seen by the parser.
%nonassoc TO_TEXT TO_BLOB TO_NUMERIC TO_INT TO_REAL.

// Input is a single SQL command
input ::= cmdlist.
cmdlist ::= cmdlist ecmd.
cmdlist ::= ecmd.
cmdx ::= cmd.           { sqlite3FinishCoding(pParse); }
ecmd ::= SEMI.
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.278 2005/11/03 00:41:17 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.279 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "n", P3_STATIC);
      sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */







|







683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
    }
#ifndef SQLITE_OMIT_SUBQUERY
    case SRT_Set: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_NotNull, -1, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeAddOp(v, OP_Pop, 1, 0);
      sqlite3VdbeAddOp(v, OP_Goto, 0, sqlite3VdbeCurrentAddr(v)+3);
      sqlite3VdbeOp3(v, OP_MakeRecord, 1, 0, "c", P3_STATIC);
      sqlite3VdbeAddOp(v, OP_IdxInsert, (iParm&0x0000FFFF), 0);
      break;
    }
    case SRT_Mem: {
      assert( nColumn==1 );
      sqlite3VdbeAddOp(v, OP_MemStore, iParm, 1);
      /* The LIMIT clause will terminate the loop for us */
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.427 2005/11/03 02:15:03 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following













|







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.428 2005/11/14 22:29:05 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Many people are failing to set -DNDEBUG=1 when compiling SQLite.
** Setting NDEBUG makes the code smaller and run faster.  So the following
581
582
583
584
585
586
587











588

589
590
591
592
593

594
595
596
597
598
599
600
** A sort order can be either ASC or DESC.
*/
#define SQLITE_SO_ASC       0  /* Sort in ascending order */
#define SQLITE_SO_DESC      1  /* Sort in ascending order */

/*
** Column affinity types.











*/

#define SQLITE_AFF_NUMERIC  'n'
#define SQLITE_AFF_INTEGER  'i'  /* Used for CAST operators only */
#define SQLITE_AFF_TEXT     't'
#define SQLITE_AFF_NONE     'o'



/*
** Each SQL table is represented in memory by an instance of the
** following structure.
**
** Table.zName is the name of the table.  The case of the original
** CREATE TABLE statement is stored, but case is not significant for







>
>
>
>
>
>
>
>
>
>
>

>
|
|
|
|

>







581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
** A sort order can be either ASC or DESC.
*/
#define SQLITE_SO_ASC       0  /* Sort in ascending order */
#define SQLITE_SO_DESC      1  /* Sort in ascending order */

/*
** Column affinity types.
**
** These used to have mnemonic name like 'i' for SQLITE_AFF_INTEGER and
** 't' for SQLITE_AFF_TEXT.  But we can save a little space and improve
** the speed a little by number the values consecutively.  
**
** But rather than start with 0 or 1, we begin with 'a'.  That way,
** when multiple affinity types are concatenated into a string and
** used as the P3 operand, they will be more readable.
**
** Note also that the numeric types are grouped together so that testing
** for a numeric type is a single comparison.
*/
#define SQLITE_AFF_TEXT     'a'
#define SQLITE_AFF_NONE     'b'
#define SQLITE_AFF_NUMERIC  'c'
#define SQLITE_AFF_INTEGER  'd'
#define SQLITE_AFF_REAL     'e'

#define sqlite3IsNumericAffinity(X)  ((X)>=SQLITE_AFF_NUMERIC)

/*
** Each SQL table is represented in memory by an instance of the
** following structure.
**
** Table.zName is the name of the table.  The case of the original
** CREATE TABLE statement is stored, but case is not significant for
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
void sqlite3CodeSubselect(Parse *, Expr *);
int sqlite3SelectResolve(Parse *, Select *, NameContext *);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
const char *sqlite3TestErrorName(int);
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*, int);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
void sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DefaultRowEst(Index*);
void sqlite3RegisterLikeFunctions(sqlite3*, int);
int sqlite3IsLikeFunction(sqlite3*,Expr*,int*,char*);

#ifdef SQLITE_SSE
#include "sseInt.h"
#endif

#endif







|













1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
void sqlite3CodeSubselect(Parse *, Expr *);
int sqlite3SelectResolve(Parse *, Select *, NameContext *);
void sqlite3ColumnDefault(Vdbe *, Table *, int);
void sqlite3AlterFinishAddColumn(Parse *, Token *);
void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
const char *sqlite3TestErrorName(int);
CollSeq *sqlite3GetCollSeq(sqlite3*, CollSeq *, const char *, int);
char sqlite3AffinityType(const Token*);
void sqlite3Analyze(Parse*, Token*, Token*);
int sqlite3InvokeBusyHandler(BusyHandler*);
int sqlite3FindDb(sqlite3*, Token*);
void sqlite3AnalysisLoad(sqlite3*,int iDB);
void sqlite3DefaultRowEst(Index*);
void sqlite3RegisterLikeFunctions(sqlite3*, int);
int sqlite3IsLikeFunction(sqlite3*,Expr*,int*,char*);

#ifdef SQLITE_SSE
#include "sseInt.h"
#endif

#endif
Changes to src/trigger.c.
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
      { OP_String8,    0, 0,  "trigger"  },
      { OP_String8,    0, 0,  0          },  /* 2: trigger name */
      { OP_String8,    0, 0,  0          },  /* 3: table name */
      { OP_Integer,    0, 0,  0          },
      { OP_String8,    0, 0,  "CREATE TRIGGER "},
      { OP_String8,    0, 0,  0          },  /* 6: SQL */
      { OP_Concat,     0, 0,  0          }, 
      { OP_MakeRecord, 5, 0,  "tttit"    },
      { OP_Insert,     0, 0,  0          },
    };
    int addr;
    Vdbe *v;

    /* Make an entry in the sqlite_master table */
    v = sqlite3GetVdbe(pParse);







|







219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
      { OP_String8,    0, 0,  "trigger"  },
      { OP_String8,    0, 0,  0          },  /* 2: trigger name */
      { OP_String8,    0, 0,  0          },  /* 3: table name */
      { OP_Integer,    0, 0,  0          },
      { OP_String8,    0, 0,  "CREATE TRIGGER "},
      { OP_String8,    0, 0,  0          },  /* 6: SQL */
      { OP_Concat,     0, 0,  0          }, 
      { OP_MakeRecord, 5, 0,  "aaada"    },
      { OP_Insert,     0, 0,  0          },
    };
    int addr;
    Vdbe *v;

    /* Make an entry in the sqlite_master table */
    v = sqlite3GetVdbe(pParse);
Changes to src/update.c.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
**    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.112 2005/09/20 17:42:23 drh Exp $
*/
#include "sqliteInt.h"

/*
** The most recently coded instruction was an OP_Column to retrieve column
** 'i' of table pTab. This routine sets the P3 parameter of the 
** OP_Column to the default value, if any.
**
** The default value of a column is specified by a DEFAULT clause in the 
** column definition. This was either supplied by the user when the table
** was created, or added later to the table definition by an ALTER TABLE
** command. If the latter, then the row-records in the table btree on disk
** may not contain a value for the column and the default value, taken







|




|
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
**    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.113 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"

/*
** The most recently coded instruction was an OP_Column to retrieve the
** i-th column of table pTab. This routine sets the P3 parameter of the 
** OP_Column to the default value, if any.
**
** The default value of a column is specified by a DEFAULT clause in the 
** column definition. This was either supplied by the user when the table
** was created, or added later to the table definition by an ALTER TABLE
** command. If the latter, then the row-records in the table btree on disk
** may not contain a value for the column and the default value, taken
Changes to src/vdbe.c.
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.495 2005/11/01 15:48:24 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*







|







39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.496 2005/11/14 22:29:05 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
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
** string that the stack entry itself controls.  In other words, it
** converts an MEM_Ephem string into an MEM_Dyn string.
*/
#define Deephemeralize(P) \
   if( ((P)->flags&MEM_Ephem)!=0 \
       && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}

/*
** Convert the given stack entity into a integer if it isn't one
** already.
**
** Any prior string or real representation is invalidated.  
** NULLs are converted into 0.
*/
#define Integerify(P) sqlite3VdbeMemIntegerify(P)

/*
** Convert P so that it has type MEM_Real.
**
** Any prior string or integer representation is invalidated.
** NULLs are converted into 0.0.
*/
#define Realify(P) sqlite3VdbeMemRealify(P)

/*
** Argument pMem points at a memory cell that will be passed to a
** user-defined function or returned to the user as the result of a query.
** The second argument, 'db_enc' is the text encoding used by the vdbe for
** stack variables.  This routine sets the pMem->enc and pMem->type
** variables used by the sqlite3_value_*() routines.
*/







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







113
114
115
116
117
118
119

















120
121
122
123
124
125
126
** string that the stack entry itself controls.  In other words, it
** converts an MEM_Ephem string into an MEM_Dyn string.
*/
#define Deephemeralize(P) \
   if( ((P)->flags&MEM_Ephem)!=0 \
       && sqlite3VdbeMemMakeWriteable(P) ){ goto no_mem;}


















/*
** Argument pMem points at a memory cell that will be passed to a
** user-defined function or returned to the user as the result of a query.
** The second argument, 'db_enc' is the text encoding used by the vdbe for
** stack variables.  This routine sets the pMem->enc and pMem->type
** variables used by the sqlite3_value_*() routines.
*/
184
185
186
187
188
189
190
191
192
193


194






195


196

197
198
199
200
201
202
203
204
205
206
207
208
209
210


211
212
213
214
215
216
217
218

219
220


221
222
223
224
225
226
227
228
229
    sqlite3VdbeFreeCursor(p->apCsr[iCur]);
  }
  p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) );
  return pCx;
}

/*
** Apply any conversion required by the supplied column affinity to
** memory cell pRec. affinity may be one of:
**


** SQLITE_AFF_NUMERIC






** SQLITE_AFF_TEXT


** SQLITE_AFF_NONE

*/
static void applyAffinity(Mem *pRec, char affinity, u8 enc){
  if( affinity==SQLITE_AFF_NONE ){
    /* do nothing */
  }else if( affinity==SQLITE_AFF_TEXT ){
    /* Only attempt the conversion to TEXT if there is an integer or real
    ** representation (blob and NULL do not get converted) but no string
    ** representation.
    */
    if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){
      sqlite3VdbeMemStringify(pRec, enc);
    }
    pRec->flags &= ~(MEM_Real|MEM_Int);
  }else{


    if( 0==(pRec->flags&(MEM_Real|MEM_Int)) ){
      /* pRec does not have a valid integer or real representation. 
      ** Attempt a conversion if pRec has a string representation and
      ** it looks like a number.
      */
      int realnum;
      sqlite3VdbeMemNulTerminate(pRec);
      if( pRec->flags&MEM_Str && sqlite3IsNumber(pRec->z, &realnum, enc) ){

        if( realnum ){
          Realify(pRec);


        }else{
          Integerify(pRec);
        }
      }
    }else if( pRec->flags & MEM_Real ){
      sqlite3VdbeIntegerAffinity(pRec);
    }
  }
}







<
|

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


|
<
<








|
>
>







|
>
|
|
>
>

|







167
168
169
170
171
172
173

174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192


193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
    sqlite3VdbeFreeCursor(p->apCsr[iCur]);
  }
  p->apCsr[iCur] = pCx = sqliteMalloc( sizeof(Cursor) );
  return pCx;
}

/*

** Processing is determine by the affinity parameter:
**
** SQLITE_AFF_INTEGER:
** SQLITE_AFF_REAL:
** SQLITE_AFF_NUMERIC:
**    Try to convert pRec to an integer representation or a 
**    floating-point representation if an integer representation
**    is not possible.  Note that the integer representation is
**    always preferred, even if the affinity is REAL, because
**    an integer representation is more space efficient on disk.
**
** SQLITE_AFF_TEXT:
**    Convert pRec to a text representation.
**
** SQLITE_AFF_NONE:
**    No-op.  pRec is unchanged.
*/
static void applyAffinity(Mem *pRec, char affinity, u8 enc){
  if( affinity==SQLITE_AFF_TEXT ){


    /* Only attempt the conversion to TEXT if there is an integer or real
    ** representation (blob and NULL do not get converted) but no string
    ** representation.
    */
    if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){
      sqlite3VdbeMemStringify(pRec, enc);
    }
    pRec->flags &= ~(MEM_Real|MEM_Int);
  }else if( affinity!=SQLITE_AFF_NONE ){
    assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL
             || affinity==SQLITE_AFF_NUMERIC );
    if( 0==(pRec->flags&(MEM_Real|MEM_Int)) ){
      /* pRec does not have a valid integer or real representation. 
      ** Attempt a conversion if pRec has a string representation and
      ** it looks like a number.
      */
      int realnum;
      sqlite3VdbeMemNulTerminate(pRec);
      if( (pRec->flags&MEM_Str) && sqlite3IsNumber(pRec->z, &realnum, enc) ){
        i64 value;
        if( !realnum && sqlite3atoi64(pRec->z, &value) ){
          sqlite3VdbeMemRelease(pRec);
          pRec->i = value;
          pRec->flags = MEM_Int;
        }else{
          sqlite3VdbeMemNumerify(pRec);
        }
      }
    }else if( pRec->flags & MEM_Real ){
      sqlite3VdbeIntegerAffinity(pRec);
    }
  }
}
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
  pTos->flags = MEM_Str|MEM_Static|MEM_Term;
  pTos->z = pOp->p3;
  pTos->n = strlen(pTos->z);
  pTos->enc = SQLITE_UTF8;
  pTos->r = sqlite3VdbeRealValue(pTos);
  pTos->flags |= MEM_Real;
  sqlite3VdbeChangeEncoding(pTos, db->enc);
  sqlite3VdbeIntegerAffinity(pTos);
  break;
}

/* Opcode: String8 * * P3
**
** P3 points to a nul terminated UTF-8 string. This opcode is transformed
** into an OP_String before it is executed for the first time.







<







627
628
629
630
631
632
633

634
635
636
637
638
639
640
  pTos->flags = MEM_Str|MEM_Static|MEM_Term;
  pTos->z = pOp->p3;
  pTos->n = strlen(pTos->z);
  pTos->enc = SQLITE_UTF8;
  pTos->r = sqlite3VdbeRealValue(pTos);
  pTos->flags |= MEM_Real;
  sqlite3VdbeChangeEncoding(pTos, db->enc);

  break;
}

/* Opcode: String8 * * P3
**
** P3 points to a nul terminated UTF-8 string. This opcode is transformed
** into an OP_String before it is executed for the first time.
1001
1002
1003
1004
1005
1006
1007

1008
1009

1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
*/
case OP_Add:                   /* same as TK_PLUS, no-push */
case OP_Subtract:              /* same as TK_MINUS, no-push */
case OP_Multiply:              /* same as TK_STAR, no-push */
case OP_Divide:                /* same as TK_SLASH, no-push */
case OP_Remainder: {           /* same as TK_REM, no-push */
  Mem *pNos = &pTos[-1];

  assert( pNos>=p->aStack );
  if( ((pTos->flags | pNos->flags) & MEM_Null)!=0 ){

    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->flags = MEM_Null;
  }else if( (pTos->flags & pNos->flags & MEM_Int)==MEM_Int ){
    i64 a, b;
    a = pTos->i;
    b = pNos->i;
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
      case OP_Multiply:    b *= a;       break;
      case OP_Divide: {
        if( a==0 ) goto divide_by_zero;
        if( b%a!=0 ) goto floating_point_divide;
        b /= a;
        break;
      }
      default: {
        if( a==0 ) goto divide_by_zero;
        b %= a;
        break;
      }
    }
    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->i = b;
    pTos->flags = MEM_Int;
  }else{
    double a, b;
    floating_point_divide:
    a = sqlite3VdbeRealValue(pTos);
    b = sqlite3VdbeRealValue(pNos);
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
      case OP_Multiply:    b *= a;       break;
      case OP_Divide: {







>

|
>














<
















<







996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020

1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036

1037
1038
1039
1040
1041
1042
1043
*/
case OP_Add:                   /* same as TK_PLUS, no-push */
case OP_Subtract:              /* same as TK_MINUS, no-push */
case OP_Multiply:              /* same as TK_STAR, no-push */
case OP_Divide:                /* same as TK_SLASH, no-push */
case OP_Remainder: {           /* same as TK_REM, no-push */
  Mem *pNos = &pTos[-1];
  int flags;
  assert( pNos>=p->aStack );
  flags = pTos->flags | pNos->flags;
  if( (flags & MEM_Null)!=0 ){
    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->flags = MEM_Null;
  }else if( (pTos->flags & pNos->flags & MEM_Int)==MEM_Int ){
    i64 a, b;
    a = pTos->i;
    b = pNos->i;
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
      case OP_Multiply:    b *= a;       break;
      case OP_Divide: {
        if( a==0 ) goto divide_by_zero;

        b /= a;
        break;
      }
      default: {
        if( a==0 ) goto divide_by_zero;
        b %= a;
        break;
      }
    }
    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->i = b;
    pTos->flags = MEM_Int;
  }else{
    double a, b;

    a = sqlite3VdbeRealValue(pTos);
    b = sqlite3VdbeRealValue(pNos);
    switch( pOp->opcode ){
      case OP_Add:         b += a;       break;
      case OP_Subtract:    b -= a;       break;
      case OP_Multiply:    b *= a;       break;
      case OP_Divide: {
1059
1060
1061
1062
1063
1064
1065

1066

1067
1068
1069
1070
1071
1072
1073
      }
    }
    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->r = b;
    pTos->flags = MEM_Real;

    sqlite3VdbeIntegerAffinity(pTos);

  }
  break;

divide_by_zero:
  Release(pTos);
  pTos--;
  Release(pTos);







>
|
>







1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
      }
    }
    Release(pTos);
    pTos--;
    Release(pTos);
    pTos->r = b;
    pTos->flags = MEM_Real;
    if( (flags & MEM_Real)==0 ){
      sqlite3VdbeIntegerAffinity(pTos);
    }
  }
  break;

divide_by_zero:
  Release(pTos);
  pTos--;
  Release(pTos);
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
** Add the value P1 to whatever is on top of the stack.  The result
** is always an integer.
**
** To force the top of the stack to be an integer, just add 0.
*/
case OP_AddImm: {            /* no-push */
  assert( pTos>=p->aStack );
  Integerify(pTos);
  pTos->i += pOp->p1;
  break;
}

/* Opcode: ForceInt P1 P2 *
**
** Convert the top of the stack into an integer.  If the current top of







|







1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
** Add the value P1 to whatever is on top of the stack.  The result
** is always an integer.
**
** To force the top of the stack to be an integer, just add 0.
*/
case OP_AddImm: {            /* no-push */
  assert( pTos>=p->aStack );
  sqlite3VdbeMemIntegerify(pTos);
  pTos->i += pOp->p1;
  break;
}

/* Opcode: ForceInt P1 P2 *
**
** Convert the top of the stack into an integer.  If the current top of
1267
1268
1269
1270
1271
1272
1273

1274
1275
1276
1277
1278
1279
1280
1281
    pTos--;
    pc = pOp->p2 - 1;
    break;
  }
  if( pTos->flags & MEM_Int ){
    v = pTos->i + (pOp->p1!=0);
  }else{

    Realify(pTos);
    v = (int)pTos->r;
    if( pTos->r>(double)v ) v++;
    if( pOp->p1 && pTos->r==(double)v ) v++;
  }
  Release(pTos);
  pTos->i = v;
  pTos->flags = MEM_Int;







>
|







1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
    pTos--;
    pc = pOp->p2 - 1;
    break;
  }
  if( pTos->flags & MEM_Int ){
    v = pTos->i + (pOp->p1!=0);
  }else{
    /* FIX ME:  should this not be assert( pTos->flags & MEM_Real ) ??? */
    sqlite3VdbeMemRealify(pTos);
    v = (int)pTos->r;
    if( pTos->r>(double)v ) v++;
    if( pOp->p1 && pTos->r==(double)v ) v++;
  }
  Release(pTos);
  pTos->i = v;
  pTos->flags = MEM_Int;
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336

1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359

1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399






















































1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418

1419
1420
1421
1422
1423
1424
1425
1426
  }else{
    Release(pTos);
    pTos->flags = MEM_Int;
  }
  break;
}

/* Opcode: ToInt * * *
**
** Force the value on the top of the stack to be an integer.  If
** The value is currently a real number, drop its fractional part.
** If the value is text or blob, try to convert it to an integer using the
** equivalent of atoi() and store 0 if no such conversion is possible.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToInt: {                  /* no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  assert( MEM_Str==(MEM_Blob>>3) );
  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
  applyAffinity(pTos, SQLITE_AFF_NUMERIC, db->enc);
  sqlite3VdbeMemIntegerify(pTos);
  break;
}

#ifndef SQLITE_OMIT_CAST
/* Opcode: ToNumeric * * *
**
** Force the value on the top of the stack to be numeric (either an

** integer or a floating-point number.
** If the value is text or blob, try to convert it to an using the
** equivalent of atoi() or atof() and store 0 if no such conversion 
** is possible.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToNumeric: {                  /* no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  assert( MEM_Str==(MEM_Blob>>3) );
  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
  applyAffinity(pTos, SQLITE_AFF_NUMERIC, db->enc);
  if( (pTos->flags & (MEM_Int|MEM_Real))==0 ){
    sqlite3VdbeMemRealify(pTos);
  }else{
    sqlite3VdbeMemRelease(pTos);
  }
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos->flags &= (MEM_Int|MEM_Real);
  break;
}


/* Opcode: ToText * * *
**
** Force the value on the top of the stack to be text.
** If the value is numeric, convert it to an using the
** equivalent of printf().  Blob values are unchanged and
** are afterwards simply interpreted as text.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToText: {                  /* no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  assert( MEM_Str==(MEM_Blob>>3) );
  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
  applyAffinity(pTos, SQLITE_AFF_TEXT, db->enc);
  assert( pTos->flags & MEM_Str );
  pTos->flags &= ~(MEM_Int|MEM_Real|MEM_Blob);
  break;
}

/* Opcode: ToBlob * * *
**
** Force the value on the top of the stack to be a BLOB.
** If the value is numeric, convert it to a string first.
** Strings are simply reinterpreted as blobs with no change
** to the underlying data.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToBlob: {                  /* no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  if( (pTos->flags & MEM_Blob)==0 ){
    applyAffinity(pTos, SQLITE_AFF_TEXT, db->enc);
    assert( pTos->flags & MEM_Str );
    pTos->flags |= MEM_Blob;
  }
  pTos->flags &= ~(MEM_Int|MEM_Real|MEM_Str);
  break;
}






















































#endif /* SQLITE_OMIT_CAST */

/* Opcode: Eq P1 P2 P3
**
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
**
** If the 0x100 bit of P1 is true and either operand is NULL then take the
** jump.  If the 0x100 bit of P1 is clear then fall thru if either operand
** is NULL.
**
** If the 0x200 bit of P1 is set and either operand is NULL then
** both operands are converted to integers prior to comparison.
** NULL operands are converted to zero and non-NULL operands are
** converted to 1.  Thus, for example, with 0x200 set,  NULL==NULL is true
** whereas it would normally be NULL.  Similarly,  NULL==123 is false when
** 0x200 is set but is NULL when the 0x200 bit of P1 is clear.
**
** The least significant byte of P1 (mask 0xff) must be an affinity character -

** 'n', 't', or 'o' - or 0x00. An attempt is made to coerce both values
** according to the affinity before the comparison is made. If the byte is
** 0x00, then numeric affinity is used.
**
** Once any conversions have taken place, and neither value is NULL, 
** the values are compared. If both values are blobs, or both are text,
** then memcmp() is used to determine the results of the comparison. If
** both values are numeric, then a numeric comparison is used. If the







|

|
<
<
<

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

|

|
<
<
<
<

<
<

<
<



>









|



















|










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



















>
|







1305
1306
1307
1308
1309
1310
1311
1312
1313
1314



1315











1316




1317
1318


1319


1320
1321
1322
1323




1324


1325


1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
  }else{
    Release(pTos);
    pTos->flags = MEM_Int;
  }
  break;
}

/* Opcode: RealAffinity * * *
**
** If the top of the stack is an integer, convert it to a real value.



**











** This opcode is used when extracting information from a column that




** has REAL affinity.  Such column values may still be stored as
** integers, for space efficiency, but after extraction we want them


** to have only a real value.


*/
case OP_RealAffinity: {                  /* no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Int ){




    sqlite3VdbeMemRealify(pTos);


  }


  break;
}

#ifndef SQLITE_OMIT_CAST
/* Opcode: ToText * * *
**
** Force the value on the top of the stack to be text.
** If the value is numeric, convert it to an using the
** equivalent of printf().  Blob values are unchanged and
** are afterwards simply interpreted as text.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToText: {                  /* same as TK_TO_TEXT, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  assert( MEM_Str==(MEM_Blob>>3) );
  pTos->flags |= (pTos->flags&MEM_Blob)>>3;
  applyAffinity(pTos, SQLITE_AFF_TEXT, db->enc);
  assert( pTos->flags & MEM_Str );
  pTos->flags &= ~(MEM_Int|MEM_Real|MEM_Blob);
  break;
}

/* Opcode: ToBlob * * *
**
** Force the value on the top of the stack to be a BLOB.
** If the value is numeric, convert it to a string first.
** Strings are simply reinterpreted as blobs with no change
** to the underlying data.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToBlob: {                  /* same as TK_TO_BLOB, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;
  if( (pTos->flags & MEM_Blob)==0 ){
    applyAffinity(pTos, SQLITE_AFF_TEXT, db->enc);
    assert( pTos->flags & MEM_Str );
    pTos->flags |= MEM_Blob;
  }
  pTos->flags &= ~(MEM_Int|MEM_Real|MEM_Str);
  break;
}

/* Opcode: ToNumeric * * *
**
** Force the value on the top of the stack to be numeric (either an
** integer or a floating-point number.)
** If the value is text or blob, try to convert it to an using the
** equivalent of atoi() or atof() and store 0 if no such conversion 
** is possible.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToNumeric: {                  /* same as TK_TO_NUMERIC, no-push */
  assert( pTos>=p->aStack );
  if( (pTos->flags & MEM_Null)==0 ){
    sqlite3VdbeMemNumerify(pTos);
  }
  break;
}
#endif /* SQLITE_OMIT_CAST */

/* Opcode: ToInt * * *
**
** Force the value on the top of the stack to be an integer.  If
** The value is currently a real number, drop its fractional part.
** If the value is text or blob, try to convert it to an integer using the
** equivalent of atoi() and store 0 if no such conversion is possible.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToInt: {                  /* same as TK_TO_INT, no-push */
  assert( pTos>=p->aStack );
  if( (pTos->flags & MEM_Null)==0 ){
    sqlite3VdbeMemIntegerify(pTos);
  }
  break;
}

#ifndef SQLITE_OMIT_CAST
/* Opcode: ToReal * * *
**
** Force the value on the top of the stack to be a floating point number.
** If The value is currently an integer, convert it.
** If the value is text or blob, try to convert it to an integer using the
** equivalent of atoi() and store 0 if no such conversion is possible.
**
** A NULL value is not changed by this routine.  It remains NULL.
*/
case OP_ToReal: {                  /* same as TK_TO_REAL, no-push */
  assert( pTos>=p->aStack );
  if( (pTos->flags & MEM_Null)==0 ){
    sqlite3VdbeMemRealify(pTos);
  }
  break;
}
#endif /* SQLITE_OMIT_CAST */

/* Opcode: Eq P1 P2 P3
**
** Pop the top two elements from the stack.  If they are equal, then
** jump to instruction P2.  Otherwise, continue to the next instruction.
**
** If the 0x100 bit of P1 is true and either operand is NULL then take the
** jump.  If the 0x100 bit of P1 is clear then fall thru if either operand
** is NULL.
**
** If the 0x200 bit of P1 is set and either operand is NULL then
** both operands are converted to integers prior to comparison.
** NULL operands are converted to zero and non-NULL operands are
** converted to 1.  Thus, for example, with 0x200 set,  NULL==NULL is true
** whereas it would normally be NULL.  Similarly,  NULL==123 is false when
** 0x200 is set but is NULL when the 0x200 bit of P1 is clear.
**
** The least significant byte of P1 (mask 0xff) must be an affinity character -
** SQLITE_AFF_TEXT, SQLITE_AFF_INTEGER, and so forth. An attempt is made 
** to coerce both values
** according to the affinity before the comparison is made. If the byte is
** 0x00, then numeric affinity is used.
**
** Once any conversions have taken place, and neither value is NULL, 
** the values are compared. If both values are blobs, or both are text,
** then memcmp() is used to determine the results of the comparison. If
** both values are numeric, then a numeric comparison is used. If the
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
  Mem *pNos = &pTos[-1];
  int v1, v2;    /* 0==TRUE, 1==FALSE, 2==UNKNOWN or NULL */

  assert( pNos>=p->aStack );
  if( pTos->flags & MEM_Null ){
    v1 = 2;
  }else{
    Integerify(pTos);
    v1 = pTos->i==0;
  }
  if( pNos->flags & MEM_Null ){
    v2 = 2;
  }else{
    Integerify(pNos);
    v2 = pNos->i==0;
  }
  if( pOp->opcode==OP_And ){
    static const unsigned char and_logic[] = { 0, 1, 2, 1, 1, 1, 2, 1, 2 };
    v1 = and_logic[v1*3+v2];
  }else{
    static const unsigned char or_logic[] = { 0, 0, 0, 0, 1, 2, 0, 2, 2 };







|





|







1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
  Mem *pNos = &pTos[-1];
  int v1, v2;    /* 0==TRUE, 1==FALSE, 2==UNKNOWN or NULL */

  assert( pNos>=p->aStack );
  if( pTos->flags & MEM_Null ){
    v1 = 2;
  }else{
    sqlite3VdbeMemIntegerify(pTos);
    v1 = pTos->i==0;
  }
  if( pNos->flags & MEM_Null ){
    v2 = 2;
  }else{
    sqlite3VdbeMemIntegerify(pNos);
    v2 = pNos->i==0;
  }
  if( pOp->opcode==OP_And ){
    static const unsigned char and_logic[] = { 0, 1, 2, 1, 1, 1, 2, 1, 2 };
    v1 = and_logic[v1*3+v2];
  }else{
    static const unsigned char or_logic[] = { 0, 0, 0, 0, 1, 2, 0, 2, 2 };
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
  if( pTos->flags & MEM_Real ){
    neg_abs_real_case:
    Release(pTos);
    if( pOp->opcode==OP_Negative || pTos->r<0.0 ){
      pTos->r = -pTos->r;
    }
    pTos->flags = MEM_Real;
    sqlite3VdbeIntegerAffinity(pTos);
  }else if( pTos->flags & MEM_Int ){
    Release(pTos);
    if( pOp->opcode==OP_Negative || pTos->i<0 ){
      pTos->i = -pTos->i;
    }
    pTos->flags = MEM_Int;
  }else if( pTos->flags & MEM_Null ){
    /* Do nothing */
  }else{
    Realify(pTos);
    goto neg_abs_real_case;
  }
  break;
}

/* Opcode: Not * * *
**
** Interpret the top of the stack as a boolean value.  Replace it
** with its complement.  If the top of the stack is NULL its value
** is unchanged.
*/
case OP_Not: {                /* same as TK_NOT, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
  Integerify(pTos);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos->i = !pTos->i;
  pTos->flags = MEM_Int;
  break;
}

/* Opcode: BitNot * * *
**
** Interpret the top of the stack as an value.  Replace it
** with its ones-complement.  If the top of the stack is NULL its
** value is unchanged.
*/
case OP_BitNot: {             /* same as TK_BITNOT, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
  Integerify(pTos);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos->i = ~pTos->i;
  pTos->flags = MEM_Int;
  break;
}

/* Opcode: Noop * * *







<









|














|















|







1635
1636
1637
1638
1639
1640
1641

1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
  if( pTos->flags & MEM_Real ){
    neg_abs_real_case:
    Release(pTos);
    if( pOp->opcode==OP_Negative || pTos->r<0.0 ){
      pTos->r = -pTos->r;
    }
    pTos->flags = MEM_Real;

  }else if( pTos->flags & MEM_Int ){
    Release(pTos);
    if( pOp->opcode==OP_Negative || pTos->i<0 ){
      pTos->i = -pTos->i;
    }
    pTos->flags = MEM_Int;
  }else if( pTos->flags & MEM_Null ){
    /* Do nothing */
  }else{
    sqlite3VdbeMemNumerify(pTos);
    goto neg_abs_real_case;
  }
  break;
}

/* Opcode: Not * * *
**
** Interpret the top of the stack as a boolean value.  Replace it
** with its complement.  If the top of the stack is NULL its value
** is unchanged.
*/
case OP_Not: {                /* same as TK_NOT, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
  sqlite3VdbeMemIntegerify(pTos);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos->i = !pTos->i;
  pTos->flags = MEM_Int;
  break;
}

/* Opcode: BitNot * * *
**
** Interpret the top of the stack as an value.  Replace it
** with its ones-complement.  If the top of the stack is NULL its
** value is unchanged.
*/
case OP_BitNot: {             /* same as TK_BITNOT, no-push */
  assert( pTos>=p->aStack );
  if( pTos->flags & MEM_Null ) break;  /* Do nothing to NULLs */
  sqlite3VdbeMemIntegerify(pTos);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos->i = ~pTos->i;
  pTos->flags = MEM_Int;
  break;
}

/* Opcode: Noop * * *
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
** uniqueness test on indices.
**
** P3 may be a string that is P1 characters long.  The nth character of the
** string indicates the column affinity that should be used for the nth
** field of the index key (i.e. the first character of P3 corresponds to the
** lowest element on the stack).
**
** The mapping from character to affinity is as follows:
**    'n' = NUMERIC.
**    't' = TEXT.
**    'o' = NONE.
**
** If P3 is NULL then all index fields have the affinity NONE.
**
** See also OP_MakeIdxRec
*/
/* Opcode: MakeRecordI P1 P2 P3
**







|
<
|
<







2094
2095
2096
2097
2098
2099
2100
2101

2102

2103
2104
2105
2106
2107
2108
2109
** uniqueness test on indices.
**
** P3 may be a string that is P1 characters long.  The nth character of the
** string indicates the column affinity that should be used for the nth
** field of the index key (i.e. the first character of P3 corresponds to the
** lowest element on the stack).
**
** The mapping from character to affinity is given by the SQLITE_AFF_

** macros defined in sqliteInt.h.

**
** If P3 is NULL then all index fields have the affinity NONE.
**
** See also OP_MakeIdxRec
*/
/* Opcode: MakeRecordI P1 P2 P3
**
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
  /* If we have to append a varint rowid to this record, set 'rowid'
  ** to the value of the rowid and increase nByte by the amount of space
  ** required to store it and the 0x00 seperator byte.
  */
  if( addRowid ){
    pRowid = &pTos[0-nField];
    assert( pRowid>=p->aStack );
    Integerify(pRowid);
    serial_type = sqlite3VdbeSerialType(pRowid);
    nData += sqlite3VdbeSerialTypeLen(serial_type);
    nHdr += sqlite3VarintLen(serial_type);
  }

  /* Add the initial header varint and total the size */
  nHdr += nVarint = sqlite3VarintLen(nHdr);







|







2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
  /* If we have to append a varint rowid to this record, set 'rowid'
  ** to the value of the rowid and increase nByte by the amount of space
  ** required to store it and the 0x00 seperator byte.
  */
  if( addRowid ){
    pRowid = &pTos[0-nField];
    assert( pRowid>=p->aStack );
    sqlite3VdbeMemIntegerify(pRowid);
    serial_type = sqlite3VdbeSerialType(pRowid);
    nData += sqlite3VdbeSerialTypeLen(serial_type);
    nHdr += sqlite3VarintLen(serial_type);
  }

  /* Add the initial header varint and total the size */
  nHdr += nVarint = sqlite3VarintLen(nHdr);
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
case OP_SetCookie: {       /* no-push */
  Db *pDb;
  assert( pOp->p2<SQLITE_N_BTREE_META );
  assert( pOp->p1>=0 && pOp->p1<db->nDb );
  pDb = &db->aDb[pOp->p1];
  assert( pDb->pBt!=0 );
  assert( pTos>=p->aStack );
  Integerify(pTos);
  /* See note about index shifting on OP_ReadCookie */
  rc = sqlite3BtreeUpdateMeta(pDb->pBt, 1+pOp->p2, (int)pTos->i);
  if( pOp->p2==0 ){
    /* When the schema cookie changes, record the new cookie internally */
    pDb->schema_cookie = pTos->i;
    db->flags |= SQLITE_InternChanges;
  }







|







2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
case OP_SetCookie: {       /* no-push */
  Db *pDb;
  assert( pOp->p2<SQLITE_N_BTREE_META );
  assert( pOp->p1>=0 && pOp->p1<db->nDb );
  pDb = &db->aDb[pOp->p1];
  assert( pDb->pBt!=0 );
  assert( pTos>=p->aStack );
  sqlite3VdbeMemIntegerify(pTos);
  /* See note about index shifting on OP_ReadCookie */
  rc = sqlite3BtreeUpdateMeta(pDb->pBt, 1+pOp->p2, (int)pTos->i);
  if( pOp->p2==0 ){
    /* When the schema cookie changes, record the new cookie internally */
    pDb->schema_cookie = pTos->i;
    db->flags |= SQLITE_InternChanges;
  }
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
  int p2 = pOp->p2;
  int wrFlag;
  Btree *pX;
  int iDb;
  Cursor *pCur;
  
  assert( pTos>=p->aStack );
  Integerify(pTos);
  iDb = pTos->i;
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  assert( iDb>=0 && iDb<db->nDb );
  pX = db->aDb[iDb].pBt;
  assert( pX!=0 );
  wrFlag = pOp->opcode==OP_OpenWrite;
  if( p2<=0 ){
    assert( pTos>=p->aStack );
    Integerify(pTos);
    p2 = pTos->i;
    assert( (pTos->flags & MEM_Dyn)==0 );
    pTos--;
    assert( p2>=2 );
  }
  assert( i>=0 );
  pCur = allocateCursor(p, i);







|









|







2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
  int p2 = pOp->p2;
  int wrFlag;
  Btree *pX;
  int iDb;
  Cursor *pCur;
  
  assert( pTos>=p->aStack );
  sqlite3VdbeMemIntegerify(pTos);
  iDb = pTos->i;
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  assert( iDb>=0 && iDb<db->nDb );
  pX = db->aDb[iDb].pBt;
  assert( pX!=0 );
  wrFlag = pOp->opcode==OP_OpenWrite;
  if( p2<=0 ){
    assert( pTos>=p->aStack );
    sqlite3VdbeMemIntegerify(pTos);
    p2 = pTos->i;
    assert( (pTos->flags & MEM_Dyn)==0 );
    pTos--;
    assert( p2>=2 );
  }
  assert( i>=0 );
  pCur = allocateCursor(p, i);
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
  if( pC->pCursor!=0 ){
    int res, oc;
    oc = pOp->opcode;
    pC->nullRow = 0;
    *pC->pIncrKey = oc==OP_MoveGt || oc==OP_MoveLe;
    if( pC->isTable ){
      i64 iKey;
      Integerify(pTos);
      iKey = intToKey(pTos->i);
      if( pOp->p2==0 && pOp->opcode==OP_MoveGe ){
        pC->movetoTarget = iKey;
        pC->deferredMoveto = 1;
        assert( (pTos->flags & MEM_Dyn)==0 );
        pTos--;
        break;







|







2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
  if( pC->pCursor!=0 ){
    int res, oc;
    oc = pOp->opcode;
    pC->nullRow = 0;
    *pC->pIncrKey = oc==OP_MoveGt || oc==OP_MoveLe;
    if( pC->isTable ){
      i64 iKey;
      sqlite3VdbeMemIntegerify(pTos);
      iKey = intToKey(pTos->i);
      if( pOp->p2==0 && pOp->opcode==OP_MoveGe ){
        pC->movetoTarget = iKey;
        pC->deferredMoveto = 1;
        assert( (pTos->flags & MEM_Dyn)==0 );
        pTos--;
        break;
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
  Cursor *pCx;
  BtCursor *pCrsr;
  i64 R;

  /* Pop the value R off the top of the stack
  */
  assert( pNos>=p->aStack );
  Integerify(pTos);
  R = pTos->i;
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  assert( i>=0 && i<=p->nCursor );
  pCx = p->apCsr[i];
  assert( pCx!=0 );
  pCrsr = pCx->pCursor;







|







2905
2906
2907
2908
2909
2910
2911
2912
2913
2914
2915
2916
2917
2918
2919
  Cursor *pCx;
  BtCursor *pCrsr;
  i64 R;

  /* Pop the value R off the top of the stack
  */
  assert( pNos>=p->aStack );
  sqlite3VdbeMemIntegerify(pTos);
  R = pTos->i;
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  assert( i>=0 && i<=p->nCursor );
  pCx = p->apCsr[i];
  assert( pCx!=0 );
  pCrsr = pCx->pCursor;
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
      }

#ifndef SQLITE_OMIT_AUTOINCREMENT
      if( pOp->p2 ){
        Mem *pMem;
        assert( pOp->p2>0 && pOp->p2<p->nMem );  /* P2 is a valid memory cell */
        pMem = &p->aMem[pOp->p2];
        Integerify(pMem);
        assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P2) holds an integer */
        if( pMem->i==MAX_ROWID || pC->useRandomRowid ){
          rc = SQLITE_FULL;
          goto abort_due_to_error;
        }
        if( v<pMem->i+1 ){
          v = pMem->i + 1;







|







3134
3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
      }

#ifndef SQLITE_OMIT_AUTOINCREMENT
      if( pOp->p2 ){
        Mem *pMem;
        assert( pOp->p2>0 && pOp->p2<p->nMem );  /* P2 is a valid memory cell */
        pMem = &p->aMem[pOp->p2];
        sqlite3VdbeMemIntegerify(pMem);
        assert( (pMem->flags & MEM_Int)!=0 );  /* mem(P2) holds an integer */
        if( pMem->i==MAX_ROWID || pC->useRandomRowid ){
          rc = SQLITE_FULL;
          goto abort_due_to_error;
        }
        if( v<pMem->i+1 ){
          v = pMem->i + 1;
3993
3994
3995
3996
3997
3998
3999
4000
4001
4002
4003
4004
4005
4006
4007
/* Opcode: FifoWrite * * *
**
** Write the integer on the top of the stack
** into the Fifo.
*/
case OP_FifoWrite: {        /* no-push */
  assert( pTos>=p->aStack );
  Integerify(pTos);
  sqlite3VdbeFifoPush(&p->sFifo, pTos->i);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  break;
}

/* Opcode: FifoRead * P2 *







|







4015
4016
4017
4018
4019
4020
4021
4022
4023
4024
4025
4026
4027
4028
4029
/* Opcode: FifoWrite * * *
**
** Write the integer on the top of the stack
** into the Fifo.
*/
case OP_FifoWrite: {        /* no-push */
  assert( pTos>=p->aStack );
  sqlite3VdbeMemIntegerify(pTos);
  sqlite3VdbeFifoPush(&p->sFifo, pTos->i);
  assert( (pTos->flags & MEM_Dyn)==0 );
  pTos--;
  break;
}

/* Opcode: FifoRead * P2 *
4117
4118
4119
4120
4121
4122
4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
*/
case OP_MemMax: {        /* no-push */
  int i = pOp->p1;
  Mem *pMem;
  assert( pTos>=p->aStack );
  assert( i>=0 && i<p->nMem );
  pMem = &p->aMem[i];
  Integerify(pMem);
  Integerify(pTos);
  if( pMem->i<pTos->i){
    pMem->i = pTos->i;
  }
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */








|
|







4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
*/
case OP_MemMax: {        /* no-push */
  int i = pOp->p1;
  Mem *pMem;
  assert( pTos>=p->aStack );
  assert( i>=0 && i<p->nMem );
  pMem = &p->aMem[i];
  sqlite3VdbeMemIntegerify(pMem);
  sqlite3VdbeMemIntegerify(pTos);
  if( pMem->i<pTos->i){
    pMem->i = pTos->i;
  }
  break;
}
#endif /* SQLITE_OMIT_AUTOINCREMENT */

Changes to src/vdbeInt.h.
358
359
360
361
362
363
364

365
366
367
368
369
370
371
int sqlite3VdbeMemDynamicify(Mem*);
int sqlite3VdbeMemStringify(Mem*, int);
i64 sqlite3VdbeIntValue(Mem*);
int sqlite3VdbeMemIntegerify(Mem*);
double sqlite3VdbeRealValue(Mem*);
void sqlite3VdbeIntegerAffinity(Mem*);
int sqlite3VdbeMemRealify(Mem*);

int sqlite3VdbeMemFromBtree(BtCursor*,int,int,int,Mem*);
void sqlite3VdbeMemRelease(Mem *p);
void sqlite3VdbeMemFinalize(Mem*, FuncDef*);
#ifndef NDEBUG
void sqlite3VdbeMemSanity(Mem*, u8);
int sqlite3VdbeOpcodeNoPush(u8);
#endif







>







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
int sqlite3VdbeMemDynamicify(Mem*);
int sqlite3VdbeMemStringify(Mem*, int);
i64 sqlite3VdbeIntValue(Mem*);
int sqlite3VdbeMemIntegerify(Mem*);
double sqlite3VdbeRealValue(Mem*);
void sqlite3VdbeIntegerAffinity(Mem*);
int sqlite3VdbeMemRealify(Mem*);
int sqlite3VdbeMemNumerify(Mem*);
int sqlite3VdbeMemFromBtree(BtCursor*,int,int,int,Mem*);
void sqlite3VdbeMemRelease(Mem *p);
void sqlite3VdbeMemFinalize(Mem*, FuncDef*);
#ifndef NDEBUG
void sqlite3VdbeMemSanity(Mem*, u8);
int sqlite3VdbeOpcodeNoPush(u8);
#endif
Changes to src/vdbemem.c.
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
    sqlite3atoi64(pMem->z, &value);
    return value;
  }else{
    return 0;
  }
}

/*
** Convert pMem to type integer.  Invalidate any prior representations.
*/
int sqlite3VdbeMemIntegerify(Mem *pMem){
  pMem->i = sqlite3VdbeIntValue(pMem);
  sqlite3VdbeMemRelease(pMem);
  pMem->flags = MEM_Int;
  return SQLITE_OK;
}

/*
** Return the best representation of pMem that we can get into a
** double.  If pMem is already a double or an integer, return its
** value.  If it is a string or blob, try to convert it to a double.
** If it is a NULL, return 0.0.
*/
double sqlite3VdbeRealValue(Mem *pMem){







<
<
<
<
<
<
<
<
<
<







260
261
262
263
264
265
266










267
268
269
270
271
272
273
    sqlite3atoi64(pMem->z, &value);
    return value;
  }else{
    return 0;
  }
}











/*
** Return the best representation of pMem that we can get into a
** double.  If pMem is already a double or an integer, return its
** value.  If it is a string or blob, try to convert it to a double.
** If it is a NULL, return 0.0.
*/
double sqlite3VdbeRealValue(Mem *pMem){
307
308
309
310
311
312
313








314

315
316
317
318
319
320
321
322









323
324
325
326
327
328
329
  assert( pMem->flags & MEM_Real );
  pMem->i = pMem->r;
  if( ((double)pMem->i)==pMem->r ){
    pMem->flags |= MEM_Int;
  }
}











/*
** Convert pMem so that it is of type MEM_Real and also MEM_Int if
** possible.  Invalidate any prior representations.
*/
int sqlite3VdbeMemRealify(Mem *pMem){
  pMem->r = sqlite3VdbeRealValue(pMem);
  sqlite3VdbeMemRelease(pMem);
  pMem->flags = MEM_Real;









  sqlite3VdbeIntegerAffinity(pMem);
  return SQLITE_OK;
}

/*
** Delete any previous value and set the value stored in *pMem to NULL.
*/







>
>
>
>
>
>
>
>
|
>

|
|





>
>
>
>
>
>
>
>
>







297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
  assert( pMem->flags & MEM_Real );
  pMem->i = pMem->r;
  if( ((double)pMem->i)==pMem->r ){
    pMem->flags |= MEM_Int;
  }
}

/*
** Convert pMem to type integer.  Invalidate any prior representations.
*/
int sqlite3VdbeMemIntegerify(Mem *pMem){
  pMem->i = sqlite3VdbeIntValue(pMem);
  sqlite3VdbeMemRelease(pMem);
  pMem->flags = MEM_Int;
  return SQLITE_OK;
}

/*
** Convert pMem so that it is of type MEM_Real.
** Invalidate any prior representations.
*/
int sqlite3VdbeMemRealify(Mem *pMem){
  pMem->r = sqlite3VdbeRealValue(pMem);
  sqlite3VdbeMemRelease(pMem);
  pMem->flags = MEM_Real;
  return SQLITE_OK;
}

/*
** Convert pMem so that it has types MEM_Real or MEM_Int or both.
** Invalidate any prior representations.
*/
int sqlite3VdbeMemNumerify(Mem *pMem){
  sqlite3VdbeMemRealify(pMem);
  sqlite3VdbeIntegerAffinity(pMem);
  return SQLITE_OK;
}

/*
** Delete any previous value and set the value stored in *pMem to NULL.
*/
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.180 2005/10/13 02:09:50 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.181 2005/11/14 22:29:06 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
      start = sqlite3VdbeCurrentAddr(v);
      pLevel->op = bRev ? OP_Prev : OP_Next;
      pLevel->p1 = iCur;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
        sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, testOp, 'n', brk);
      }
    }else if( pLevel->flags & WHERE_COLUMN_RANGE ){
      /* Case 3: The WHERE clause term that refers to the right-most
      **         column of the index is an inequality.  For example, if
      **         the index is on (x,y,z) and the WHERE clause is of the
      **         form "x=5 AND y<10" then this case is used.  Only the
      **         right-most column can be an inequality - the rest must







|







1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
      start = sqlite3VdbeCurrentAddr(v);
      pLevel->op = bRev ? OP_Prev : OP_Next;
      pLevel->p1 = iCur;
      pLevel->p2 = start;
      if( testOp!=OP_Noop ){
        sqlite3VdbeAddOp(v, OP_Rowid, iCur, 0);
        sqlite3VdbeAddOp(v, OP_MemLoad, pLevel->iMem, 0);
        sqlite3VdbeAddOp(v, testOp, SQLITE_AFF_NUMERIC, brk);
      }
    }else if( pLevel->flags & WHERE_COLUMN_RANGE ){
      /* Case 3: The WHERE clause term that refers to the right-most
      **         column of the index is an inequality.  For example, if
      **         the index is on (x,y,z) and the WHERE clause is of the
      **         form "x=5 AND y<10" then this case is used.  Only the
      **         right-most column can be an inequality - the rest must
Changes to test/autoinc.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2004 November 12
#
# 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 script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.7 2005/08/11 02:10:19 drh Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2004 November 12
#
# 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 script is testing the AUTOINCREMENT features.
#
# $Id: autoinc.test,v 1.8 2005/11/14 22:29:06 drh Exp $
#

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

# If the library is not compiled with autoincrement support then
# skip all tests in this file.
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
    CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));
    INSERT INTO t7(y) VALUES(123);
    INSERT INTO t7(y) VALUES(234);
    DELETE FROM t7;
    INSERT INTO t7(y) VALUES(345);
    SELECT * FROM t7;
  }
} {3 345}

# Test that if the AUTOINCREMENT is applied to a non integer primary key
# the error message is sensible.
do_test autoinc-7.2 {
  catchsql {
    CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);
  }







|







490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
    CREATE TABLE t7(x INTEGER, y REAL, PRIMARY KEY(x AUTOINCREMENT));
    INSERT INTO t7(y) VALUES(123);
    INSERT INTO t7(y) VALUES(234);
    DELETE FROM t7;
    INSERT INTO t7(y) VALUES(345);
    SELECT * FROM t7;
  }
} {3 345.0}

# Test that if the AUTOINCREMENT is applied to a non integer primary key
# the error message is sensible.
do_test autoinc-7.2 {
  catchsql {
    CREATE TABLE t8(x TEXT PRIMARY KEY AUTOINCREMENT);
  }
Changes to test/check.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 November 2
#
# 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 CHECK constraints
#
# $Id: check.test,v 1.5 2005/11/03 12:33:29 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2005 November 2
#
# 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 CHECK constraints
#
# $Id: check.test,v 1.6 2005/11/14 22:29:06 drh Exp $

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

# Only run these tests if the build includes support for CHECK constraints
ifcapable !check {
  finish_test
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
  }
} {}
do_test check-1.2 {
  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.3 {
  catchsql {
    INSERT INTO t1 VALUES(6,7);
  }
} {1 {constraint failed}}
do_test check-1.4 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.5 {
  catchsql {
    INSERT INTO t1 VALUES(4,3);
  }
} {1 {constraint failed}}
do_test check-1.6 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4}
do_test check-1.7 {
  catchsql {
    INSERT INTO t1 VALUES(NULL,6);
  }
} {0 {}}
do_test check-1.8 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4 {} 6}
do_test check-1.9 {
  catchsql {
    INSERT INTO t1 VALUES(2,NULL);
  }
} {0 {}}
do_test check-1.10 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4 {} 6 2 {}}
do_test check-1.11 {
  execsql {
    DELETE FROM t1 WHERE x IS NULL OR x!=3;
    UPDATE t1 SET x=2 WHERE x==3;
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.13 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.14 {
  catchsql {
    UPDATE t1 SET x=5 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.15 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4}
do_test check-1.16 {
  catchsql {
    UPDATE t1 SET x=4, y=11 WHERE x==2
  }
} {0 {}}
do_test check-1.17 {
  execsql {
    SELECT * FROM t1;
  }
} {4 11}

do_test check-2.1 {
  execsql {
    CREATE TABLE t2(
      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),
      z TEXT CHECK( typeof(coalesce(z,''))=="text" )







|









|









|









|









|






|









|









|









|







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
  }
} {}
do_test check-1.2 {
  execsql {
    INSERT INTO t1 VALUES(3,4);
    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.3 {
  catchsql {
    INSERT INTO t1 VALUES(6,7);
  }
} {1 {constraint failed}}
do_test check-1.4 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.5 {
  catchsql {
    INSERT INTO t1 VALUES(4,3);
  }
} {1 {constraint failed}}
do_test check-1.6 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0}
do_test check-1.7 {
  catchsql {
    INSERT INTO t1 VALUES(NULL,6);
  }
} {0 {}}
do_test check-1.8 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0 {} 6.0}
do_test check-1.9 {
  catchsql {
    INSERT INTO t1 VALUES(2,NULL);
  }
} {0 {}}
do_test check-1.10 {
  execsql {
    SELECT * FROM t1;
  }  
} {3 4.0 {} 6.0 2 {}}
do_test check-1.11 {
  execsql {
    DELETE FROM t1 WHERE x IS NULL OR x!=3;
    UPDATE t1 SET x=2 WHERE x==3;
    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.12 {
  catchsql {
    UPDATE t1 SET x=7 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.13 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.14 {
  catchsql {
    UPDATE t1 SET x=5 WHERE x==2
  }
} {1 {constraint failed}}
do_test check-1.15 {
  execsql {
    SELECT * FROM t1;
  }
} {2 4.0}
do_test check-1.16 {
  catchsql {
    UPDATE t1 SET x=4, y=11 WHERE x==2
  }
} {0 {}}
do_test check-1.17 {
  execsql {
    SELECT * FROM t1;
  }
} {4 11.0}

do_test check-2.1 {
  execsql {
    CREATE TABLE t2(
      x INTEGER CHECK( typeof(coalesce(x,0))=="integer" ),
      y REAL CHECK( typeof(coalesce(y,0.1))=="real" ),
      z TEXT CHECK( typeof(coalesce(z,''))=="text" )
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
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
# 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.47 2005/11/01 15:48:25 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)}
execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
proc test_expr {name settings expr result} {
  do_test $name [format {
    execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
  } $settings $expr] $result
}

test_expr expr-1.1 {i1=10, i2=20} {i1+i2} 30
test_expr expr-1.2 {i1=10, i2=20} {i1-i2} -10
test_expr expr-1.3 {i1=10, i2=20} {i1*i2} 200
test_expr expr-1.4 {i1=10, i2=20} {i1/i2} 0.5
test_expr expr-1.5 {i1=10, i2=20} {i2/i1} 2
test_expr expr-1.6 {i1=10, i2=20} {i2<i1} 0
test_expr expr-1.7 {i1=10, i2=20} {i2<=i1} 0
test_expr expr-1.8 {i1=10, i2=20} {i2>i1} 1
test_expr expr-1.9 {i1=10, i2=20} {i2>=i1} 1
test_expr expr-1.10 {i1=10, i2=20} {i2!=i1} 1
test_expr expr-1.11 {i1=10, i2=20} {i2=i1} 0
test_expr expr-1.12 {i1=10, i2=20} {i2<>i1} 1
test_expr expr-1.13 {i1=10, i2=20} {i2==i1} 0
test_expr expr-1.14 {i1=20, i2=20} {i2<i1} 0
test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7}
test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9}
test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}













|

















|

















|
|







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
# 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.48 2005/11/14 22:29:06 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)}
execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')}
proc test_expr {name settings expr result} {
  do_test $name [format {
    execsql {BEGIN; UPDATE test1 SET %s; SELECT %s FROM test1; ROLLBACK;}
  } $settings $expr] $result
}

test_expr expr-1.1 {i1=10, i2=20} {i1+i2} 30
test_expr expr-1.2 {i1=10, i2=20} {i1-i2} -10
test_expr expr-1.3 {i1=10, i2=20} {i1*i2} 200
test_expr expr-1.4 {i1=10, i2=20} {i1/i2} 0
test_expr expr-1.5 {i1=10, i2=20} {i2/i1} 2
test_expr expr-1.6 {i1=10, i2=20} {i2<i1} 0
test_expr expr-1.7 {i1=10, i2=20} {i2<=i1} 0
test_expr expr-1.8 {i1=10, i2=20} {i2>i1} 1
test_expr expr-1.9 {i1=10, i2=20} {i2>=i1} 1
test_expr expr-1.10 {i1=10, i2=20} {i2!=i1} 1
test_expr expr-1.11 {i1=10, i2=20} {i2=i1} 0
test_expr expr-1.12 {i1=10, i2=20} {i2<>i1} 1
test_expr expr-1.13 {i1=10, i2=20} {i2==i1} 0
test_expr expr-1.14 {i1=20, i2=20} {i2<i1} 0
test_expr expr-1.15 {i1=20, i2=20} {i2<=i1} 1
test_expr expr-1.16 {i1=20, i2=20} {i2>i1} 0
test_expr expr-1.17 {i1=20, i2=20} {i2>=i1} 1
test_expr expr-1.18 {i1=20, i2=20} {i2!=i1} 0
test_expr expr-1.19 {i1=20, i2=20} {i2=i1} 1
test_expr expr-1.20 {i1=20, i2=20} {i2<>i1} 0
test_expr expr-1.21 {i1=20, i2=20} {i2==i1} 1
test_expr expr-1.22 {i1=1, i2=2, r1=3.0} {i1+i2*r1} {7.0}
test_expr expr-1.23 {i1=1, i2=2, r1=3.0} {(i1+i2)*r1} {9.0}
test_expr expr-1.24 {i1=1, i2=2} {min(i1,i2,i1+i2,i1-i2)} {-1}
test_expr expr-1.25 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.26 {i1=1, i2=2} {max(i1,i2,i1+i2,i1-i2)} {3}
test_expr expr-1.27 {i1=1, i2=2} {i1==1 AND i2=2} {1}
test_expr expr-1.28 {i1=1, i2=2} {i1=2 AND i2=1} {0}
test_expr expr-1.29 {i1=1, i2=2} {i1=1 AND i2=1} {0}
test_expr expr-1.30 {i1=1, i2=2} {i1=2 AND i2=2} {0}
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
test_expr expr-2.17 {r1=2.34, r2=2.34} {r2>=r1} 1
test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0
test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3
test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0
test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1
test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0
test_expr expr-3.6 {t1='abc', t2='abc'} {t1<=t2} 1







|
|







156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
test_expr expr-2.17 {r1=2.34, r2=2.34} {r2>=r1} 1
test_expr expr-2.18 {r1=2.34, r2=2.34} {r2!=r1} 0
test_expr expr-2.19 {r1=2.34, r2=2.34} {r2=r1} 1
test_expr expr-2.20 {r1=2.34, r2=2.34} {r2<>r1} 0
test_expr expr-2.21 {r1=2.34, r2=2.34} {r2==r1} 1
test_expr expr-2.22 {r1=1.23, r2=2.34} {min(r1,r2,r1+r2,r1-r2)} {-1.11}
test_expr expr-2.23 {r1=1.23, r2=2.34} {max(r1,r2,r1+r2,r1-r2)} {3.57}
test_expr expr-2.24 {r1=25.0, r2=11.0} {r1%r2} 3.0
test_expr expr-2.25 {r1=1.23, r2=NULL} {coalesce(r1+r2,99.0)} 99.0

test_expr expr-3.1 {t1='abc', t2='xyz'} {t1<t2} 1
test_expr expr-3.2 {t1='xyz', t2='abc'} {t1<t2} 0
test_expr expr-3.3 {t1='abc', t2='abc'} {t1<t2} 0
test_expr expr-3.4 {t1='abc', t2='xyz'} {t1<=t2} 1
test_expr expr-3.5 {t1='xyz', t2='abc'} {t1<=t2} 0
test_expr expr-3.6 {t1='abc', t2='abc'} {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.41 2005/11/01 15:48:25 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.42 2005/11/14 22:29:06 drh Exp $

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

# Create a table to work with.
#
do_test func-0.0 {
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
  }
} {9902}
do_test func-18.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902}

# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x) FROM t5;







|







507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
  }
} {9902}
do_test func-18.2 {
  execsql {
    INSERT INTO t5 VALUES(0.0);
    SELECT sum(x) FROM t5;
  }
} {9902.0}

# The sum of nothing is NULL.  But the sum of all NULLs is NULL.
#
do_test func-18.3 {
  execsql {
    DELETE FROM t5;
    SELECT sum(x) FROM t5;
Changes to test/index.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 CREATE INDEX statement.
#
# $Id: index.test,v 1.38 2005/11/01 15:48:25 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-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 CREATE INDEX statement.
#
# $Id: index.test,v 1.39 2005/11/14 22:29:06 drh Exp $

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

# Create a basic index and verify it is added to sqlite_master
#
do_test index-1.1 {
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
      b float PRIMARY KEY,
      c varchar(10),
      UNIQUE(a,c)
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
  }]
  llength $::idxlist
} {3}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2 3 a b c}
integrity_check index-13.5

# Check the sort order of data in an index.
#
do_test index-14.1 {
  execsql {
    CREATE TABLE t6(a,b,c);







|


















|







415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
      b float PRIMARY KEY,
      c varchar(10),
      UNIQUE(a,c)
   );
   INSERT INTO t5 VALUES(1,2,3);
   SELECT * FROM t5;
  }
} {1 2.0 3}
do_test index-13.2 {
  set ::idxlist [execsql {
    SELECT name FROM sqlite_master WHERE type="index" AND tbl_name="t5";
  }]
  llength $::idxlist
} {3}
for {set i 0} {$i<[llength $::idxlist]} {incr i} {
  do_test index-13.3.$i {
    catchsql "
      DROP INDEX '[lindex $::idxlist $i]';
    "
  } {1 {index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped}}
}
do_test index-13.4 {
  execsql {
    INSERT INTO t5 VALUES('a','b','c');
    SELECT * FROM t5;
  }
} {1 2.0 3 a b c}
integrity_check index-13.5

# Check the sort order of data in an index.
#
do_test index-14.1 {
  execsql {
    CREATE TABLE t6(a,b,c);
Changes to test/main.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 exercising the code in main.c.
#
# $Id: main.test,v 1.23 2005/11/01 15:48:25 drh Exp $

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

# Only do the next group of tests if the sqlite3_complete API is available
#
ifcapable {complete} {













|







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 exercising the code in main.c.
#
# $Id: main.test,v 1.24 2005/11/14 22:29:06 drh Exp $

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

# Only do the next group of tests if the sqlite3_complete API is available
#
ifcapable {complete} {
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
    insert into T1 values(-5.1e-2);
    insert into T1 values(0.5e2);
    insert into T1 values(0.5E+02);
    insert into T1 values(5E+02);
    insert into T1 values(5.0E+03);
    select x*10 from T1 order by x*5;
  }
} {-0.51 -0.5 0.05 0.5 5 500 500 500 5000 50000}
do_test main-3.4 {
  set v [catch {execsql {create bogus}} msg]
  lappend v $msg
} {1 {near "bogus": syntax error}}
do_test main-3.5 {
  set v [catch {execsql {create}} msg]
  lappend v $msg







|







298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
    insert into T1 values(-5.1e-2);
    insert into T1 values(0.5e2);
    insert into T1 values(0.5E+02);
    insert into T1 values(5E+02);
    insert into T1 values(5.0E+03);
    select x*10 from T1 order by x*5;
  }
} {-0.51 -0.5 0.05 0.5 5.0 500.0 500.0 500.0 5000.0 50000.0}
do_test main-3.4 {
  set v [catch {execsql {create bogus}} msg]
  lappend v $msg
} {1 {near "bogus": syntax error}}
do_test main-3.5 {
  set v [catch {execsql {create}} msg]
  lappend v $msg
Changes to test/misc5.test.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.6 2005/11/01 15:48:25 drh Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#







|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc5.test,v 1.7 2005/11/14 22:29:06 drh Exp $

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

# Build records using the MakeRecord opcode such that the size of the 
# header is at the transition point in the size of a varint.
#
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
# Ticket #1371.  Allow floating point numbers of the form .N  or N.
#
do_test misc5-5.1 {
  execsql {SELECT .1 }
} 0.1
do_test misc5-5.2 {
  execsql {SELECT 2. }
} 2
do_test misc5-5.3 {
  execsql {SELECT 3.e0 }
} 3
do_test misc5-5.4 {
  execsql {SELECT .4e+1}
} 4


finish_test







|


|


|



490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
# Ticket #1371.  Allow floating point numbers of the form .N  or N.
#
do_test misc5-5.1 {
  execsql {SELECT .1 }
} 0.1
do_test misc5-5.2 {
  execsql {SELECT 2. }
} 2.0
do_test misc5-5.3 {
  execsql {SELECT 3.e0 }
} 3.0
do_test misc5-5.4 {
  execsql {SELECT .4e+1}
} 4.0


finish_test
Changes to test/select3.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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.17 2005/11/01 15:48:25 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {







|







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 aggregate functions and the
# GROUP BY and HAVING clauses of SELECT statements.
#
# $Id: select3.test,v 1.18 2005/11/14 22:29:06 drh Exp $

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

# Build some test data
#
do_test select3-1.0 {
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
  execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-2.3.1 {
  execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}
do_test select3-2.3.2 {
  execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
} {0 2 1 3 2 4.5 3 7.5 4 13.5 5 25}
do_test select3-2.4 {
  execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 0 1 0 2 0.5 3 1.5 4 3.5 5 7}
do_test select3-2.5 {
  execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {1 0 3 0 5 0.5 7 1.5 9 3.5 11 7}
do_test select3-2.6 {
  execsql {
    SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
  execsql {







|


|


|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
  execsql {SELECT log, min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1 1 2 2 3 3 5 4 9 5 17}
do_test select3-2.3.1 {
  execsql {SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log}
} {0 1.0 1 2.0 2 3.5 3 6.5 4 12.5 5 24.0}
do_test select3-2.3.2 {
  execsql {SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log}
} {0 2.0 1 3.0 2 4.5 3 7.5 4 13.5 5 25.0}
do_test select3-2.4 {
  execsql {SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {0 0.0 1 0.0 2 0.5 3 1.5 4 3.5 5 7.0}
do_test select3-2.5 {
  execsql {SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log}
} {1 0.0 3 0.0 5 0.5 7 1.5 9 3.5 11 7.0}
do_test select3-2.6 {
  execsql {
    SELECT log*2+1 as x, count(*) FROM t1 GROUP BY x ORDER BY x
  }
} {1 1 3 1 5 2 7 4 9 8 11 15}
do_test select3-2.7 {
  execsql {
Changes to test/select6.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 contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.20 2005/11/01 15:48:25 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_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 contain
# subqueries in their FROM clause.
#
# $Id: select6.test,v 1.21 2005/11/14 22:29:06 drh Exp $

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

# Omit this whole file if the library is build without subquery support.
ifcapable !subquery {
  finish_test
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4.0 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4.0 3.0 7}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {







|







182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1 WHERE y=4)
  }
} {11.5 4.0 15.5}
do_test select6-3.5 {
  execsql {
    SELECT x,y,x+y FROM (SELECT avg(a) as 'x', avg(b) as 'y' FROM t2 WHERE a=4)
  }
} {4.0 3.0 7.0}
do_test select6-3.6 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
    WHERE a>10
  }
} {10.5 3.7 14.2}
do_test select6-3.7 {
213
214
215
216
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 select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1.0 1 2 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1.0 1 2 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {







|






|













|







213
214
215
216
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 select6-3.10 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    ORDER BY a
  }
} {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
do_test select6-3.11 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b)
    WHERE b<4 ORDER BY a
  }
} {1.0 1 2.0 2.5 2 4.5 5.5 3 8.5}
do_test select6-3.12 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    WHERE b<4 ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5}
do_test select6-3.13 {
  execsql {
    SELECT a,b,a+b FROM 
       (SELECT avg(x) as 'a', y as 'b' FROM t1 GROUP BY b HAVING a>1)
    ORDER BY a
  }
} {2.5 2 4.5 5.5 3 8.5 11.5 4 15.5 18.0 5 23.0}
do_test select6-3.14 {
  execsql {
    SELECT [count(*)],y FROM (SELECT count(*), y FROM t1 GROUP BY y)
    ORDER BY [count(*)]
  }
} {1 1 2 2 4 3 5 5 8 4}
do_test select6-3.15 {
Changes to test/sort.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 CREATE TABLE statement.
#
# $Id: sort.test,v 1.24 2005/11/01 15:48:25 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.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 the CREATE TABLE statement.
#
# $Id: sort.test,v 1.25 2005/11/14 22:29:06 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test sort-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test sort-1.5 {
  execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11 -1.6 -0.0013442 0.123 2.15 3.141592653 123 4221}
do_test sort-1.6 {
  execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221 123 3.141592653 2.15 0.123 -0.0013442 -1.6 -11}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {







|


|







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
  execsql {SELECT n FROM t1 ORDER BY v}
} {8 5 4 1 7 6 3 2}
do_test sort-1.4 {
  execsql {SELECT n FROM t1 ORDER BY v DESC}
} {2 3 6 7 1 4 5 8}
do_test sort-1.5 {
  execsql {SELECT flt FROM t1 ORDER BY flt}
} {-11.0 -1.6 -0.0013442 0.123 2.15 3.141592653 123.0 4221.0}
do_test sort-1.6 {
  execsql {SELECT flt FROM t1 ORDER BY flt DESC}
} {4221.0 123.0 3.141592653 2.15 0.123 -0.0013442 -1.6 -11.0}
do_test sort-1.7 {
  execsql {SELECT roman FROM t1 ORDER BY roman}
} {I II III IV V VI VII VIII}
do_test sort-1.8 {
  execsql {SELECT n FROM t1 ORDER BY log, flt}
} {1 2 3 5 4 6 7 8}
do_test sort-1.8.1 {
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
#
do_test sort-2.1.1 {
  execsql {
    UPDATE t1 SET v='x' || -flt;
    UPDATE t1 SET v='x-2b' where v=='x-0.123';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
do_test sort-2.1.2 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999);
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4221 x0.0013442 x1.6 x11}
do_test sort-2.1.3 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
  }
} {x-4221 x-123 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11}
do_test sort-2.1.4 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
  }
} {x11 x1.6 x0.0013442 x-4221 x-3.141592653 x-2b x-2.15 x-123}
do_test sort-2.1.5 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
  }
} {x11 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123 x-4221}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test sort-3.1 {
  execsql {







|




|




|




|




|







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
#
do_test sort-2.1.1 {
  execsql {
    UPDATE t1 SET v='x' || -flt;
    UPDATE t1 SET v='x-2b' where v=='x-0.123';
    SELECT v FROM t1 ORDER BY v;
  }
} {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0}
do_test sort-2.1.2 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999);
  }
} {x-123.0 x-2.15 x-2b x-3.141592653 x-4221.0 x0.0013442 x1.6 x11.0}
do_test sort-2.1.3 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0;
  }
} {x-4221.0 x-123.0 x-3.141592653 x-2.15 x-2b x0.0013442 x1.6 x11.0}
do_test sort-2.1.4 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999) DESC;
  }
} {x11.0 x1.6 x0.0013442 x-4221.0 x-3.141592653 x-2b x-2.15 x-123.0}
do_test sort-2.1.5 {
  execsql {
    SELECT v FROM t1 ORDER BY substr(v,2,999)+0.0 DESC;
  }
} {x11.0 x1.6 x0.0013442 x-2b x-2.15 x-3.141592653 x-123.0 x-4221.0}

# This is a bug fix for 2.2.4.
# Strings are normally mapped to upper-case for a caseless comparison.
# But this can cause problems for characters in between 'Z' and 'a'.
#
do_test sort-3.1 {
  execsql {
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
    SELECT n+0 FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test sort-4.6 {
  execsql {
    SELECT v FROM t1 ORDER BY 1;
  }
} {x-123 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221 x0.0013442 x01234567890123456789 x1.6 x11 x2.7 x5.0e10}
do_test sort-4.7 {
  execsql {
    SELECT v FROM t1 ORDER BY 1 DESC;
  }
} {x5.0e10 x2.7 x11 x1.6 x01234567890123456789 x0.0013442 x-4221 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123}
do_test sort-4.8 {
  execsql {
    SELECT substr(v,2,99) FROM t1 ORDER BY 1;
  }
} {-123 -2.15 -2b -3.141592653 -4.0e9 -4221 0.0013442 01234567890123456789 1.6 11 2.7 5.0e10}
#do_test sort-4.9 {
#  execsql {
#    SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1;
#  }
#} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18}

do_test sort-5.1 {







|




|




|







192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
    SELECT n+0 FROM t1 ORDER BY 1 DESC;
  }
} {12 11 10 9 8 7 6 5 4 3 2 1}
do_test sort-4.6 {
  execsql {
    SELECT v FROM t1 ORDER BY 1;
  }
} {x-123.0 x-2.15 x-2b x-3.141592653 x-4.0e9 x-4221.0 x0.0013442 x01234567890123456789 x1.6 x11.0 x2.7 x5.0e10}
do_test sort-4.7 {
  execsql {
    SELECT v FROM t1 ORDER BY 1 DESC;
  }
} {x5.0e10 x2.7 x11.0 x1.6 x01234567890123456789 x0.0013442 x-4221.0 x-4.0e9 x-3.141592653 x-2b x-2.15 x-123.0}
do_test sort-4.8 {
  execsql {
    SELECT substr(v,2,99) FROM t1 ORDER BY 1;
  }
} {-123.0 -2.15 -2b -3.141592653 -4.0e9 -4221.0 0.0013442 01234567890123456789 1.6 11.0 2.7 5.0e10}
#do_test sort-4.9 {
#  execsql {
#    SELECT substr(v,2,99)+0.0 FROM t1 ORDER BY 1;
#  }
#} {-4000000000 -4221 -123 -3.141592653 -2.15 -2 0.0013442 1.6 2.7 11 50000000000 1.23456789012346e+18}

do_test sort-5.1 {
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
do_test sort-8.1 {
  execsql {
    CREATE TABLE t5(a real, b text);
    INSERT INTO t5 VALUES(100,'A1');
    INSERT INTO t5 VALUES(100.0,'A2');
    SELECT * FROM t5 ORDER BY a, b;
  }
} {100 A1 100 A2}


ifcapable {bloblit} {
# BLOBs should sort after TEXT
#
do_test sort-9.1 {
  execsql {







|







358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
do_test sort-8.1 {
  execsql {
    CREATE TABLE t5(a real, b text);
    INSERT INTO t5 VALUES(100,'A1');
    INSERT INTO t5 VALUES(100.0,'A2');
    SELECT * FROM t5 ORDER BY a, b;
  }
} {100.0 A1 100.0 A2}


ifcapable {bloblit} {
# BLOBs should sort after TEXT
#
do_test sort-9.1 {
  execsql {
Changes to test/tkt1444.test.
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
    CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real);
    CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
    INSERT INTO DemoTable VALUES(9,8,7);
    INSERT INTO DemoTable VALUES(1,2,3);
    CREATE VIEW DemoView AS SELECT * FROM DemoTable ORDER BY TextKey;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3 1 2 3 9 8 7 9 8 7}
do_test tkt1444-1.2 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7 1 2 3 1 2 3 9 8 7}
do_test tkt1444-1.3 {
  execsql {
    DROP VIEW DemoView;
    CREATE VIEW DemoView AS SELECT * FROM DemoTable;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3 1 2 3 9 8 7 9 8 7}
do_test tkt1444-1.4 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7 1 2 3 9 8 7 1 2 3}

finish_test







|




|






|




|


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
    CREATE TABLE DemoTable (x INTEGER, TextKey TEXT, DKey Real);
    CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
    INSERT INTO DemoTable VALUES(9,8,7);
    INSERT INTO DemoTable VALUES(1,2,3);
    CREATE VIEW DemoView AS SELECT * FROM DemoTable ORDER BY TextKey;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3.0 1 2 3.0 9 8 7.0 9 8 7.0}
do_test tkt1444-1.2 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7.0 1 2 3.0 1 2 3 9 8 7}
do_test tkt1444-1.3 {
  execsql {
    DROP VIEW DemoView;
    CREATE VIEW DemoView AS SELECT * FROM DemoTable;
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
  }
} {1 2 3.0 1 2 3 9 8 7.0 9 8 7}
do_test tkt1444-1.4 {
  execsql {
    SELECT * FROM DemoTable UNION ALL SELECT * FROM DemoView;
  }
} {9 8 7.0 1 2 3.0 9 8 7 1 2 3}

finish_test
Changes to test/types.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. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.15 2005/11/01 15:48:25 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage







|







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. Specfically
# it tests that the different storage classes (integer, real, text etc.)
# all work correctly.
#
# $Id: types.test,v 1.16 2005/11/14 22:29:06 drh Exp $

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

# Tests in this file are organized roughly as follows:
#
# types-1.*.*: Test that values are stored using the expected storage
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    integer integer text integer }
  { 5.1    real    real    text real    }
  { 5      integer integer text integer }
  { '5.0'  integer integer text text    }
  { '5.1'  real    real    text text    }
  { '-5.0' integer integer text text    }
  { '-5.0' integer integer text text    }
  { '5'    integer integer text text    }







|







52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# Each element of the following list represents one test case.
#
# The first value of each sub-list is an SQL literal. The following
# four value are the storage classes that would be used if the
# literal were inserted into a column with affinity INTEGER, NUMERIC, TEXT
# or NONE, respectively.
set values {
  { 5.0    integer integer text real    }
  { 5.1    real    real    text real    }
  { 5      integer integer text integer }
  { '5.0'  integer integer text text    }
  { '5.1'  real    real    text text    }
  { '-5.0' integer integer text text    }
  { '-5.0' integer integer text text    }
  { '5'    integer integer text text    }
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
    INSERT INTO t2 VALUES(-12345.678);
  }
} {}
do_test types-2.2.2 {
  execsql {
    SELECT a FROM t2;
  }
} {0 12345.678 -12345.678}

# Check that all the record sizes are as we expected.
do_test types-2.2.3 {
  set root [db eval {select rootpage from sqlite_master where name = 't2'}]
  record_sizes $root
} {3 10 10}








|







219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
    INSERT INTO t2 VALUES(-12345.678);
  }
} {}
do_test types-2.2.2 {
  execsql {
    SELECT a FROM t2;
  }
} {0.0 12345.678 -12345.678}

# Check that all the record sizes are as we expected.
do_test types-2.2.3 {
  set root [db eval {select rootpage from sqlite_master where name = 't2'}]
  record_sizes $root
} {3 10 10}

Changes to test/types3.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 the interaction of SQLite manifest types
# with Tcl dual-representations.
#
# $Id: types3.test,v 1.2 2005/11/01 15:48:25 drh Exp $
#

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

# A variable with only a string representation comes in as TEXT
do_test types3-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 the interaction of SQLite manifest types
# with Tcl dual-representations.
#
# $Id: types3.test,v 1.3 2005/11/14 22:29:06 drh Exp $
#

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

# A variable with only a string representation comes in as TEXT
do_test types3-1.1 {
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
do_test types3-2.3 {
  set V [db one {SELECT 1234567890123456}]
  tcl_variable_type V
} wideInt
do_test types3-2.4.1 {
  set V [db one {SELECT 1234567890123456.1}]
  tcl_variable_type V
} wideInt
do_test types3-2.4.2 {
  set V [db one {SELECT 1234567890123.456}]
  tcl_variable_type V
} double
do_test types3-2.5 {
  set V [db one {SELECT '1234567890123456.0'}]
  tcl_variable_type V







|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
do_test types3-2.3 {
  set V [db one {SELECT 1234567890123456}]
  tcl_variable_type V
} wideInt
do_test types3-2.4.1 {
  set V [db one {SELECT 1234567890123456.1}]
  tcl_variable_type V
} double
do_test types3-2.4.2 {
  set V [db one {SELECT 1234567890123.456}]
  tcl_variable_type V
} double
do_test types3-2.5 {
  set V [db one {SELECT '1234567890123456.0'}]
  tcl_variable_type V
Changes to test/where.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 use of indices in WHERE clases.
#
# $Id: where.test,v 1.37 2005/10/06 16:53:17 drh Exp $

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

# Build some test data
#
do_test where-1.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 the use of indices in WHERE clases.
#
# $Id: where.test,v 1.38 2005/11/14 22:29:06 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
  count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
} {97 99}


# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {
btree_breakpoint
  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 6}
do_test where-2.2 {
  count {







<







226
227
228
229
230
231
232

233
234
235
236
237
238
239
  count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)}
} {97 99}


# Do the same kind of thing except use a join as the data source.
#
do_test where-2.1 {

  count {
    SELECT w, p FROM t2, t1
    WHERE x=q AND y=s AND r=8977
  }
} {34 67 6}
do_test where-2.2 {
  count {