/ Check-in [71cc292d]
Login

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

Overview
Comment:Fix datatype reporting and collating sequence selection so that it works correctly on views and with the UNION, EXCEPT, and INTERCEPT operators. (CVS 839)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:71cc292dce59cf8224b205d1cdbff59ad12f1043
User & Date: drh 2003-01-18 20:11:06
Context
2003-01-18
22:01
Update the speed comparison page. (CVS 840) check-in: 424cb2ed user: drh tags: trunk
20:11
Fix datatype reporting and collating sequence selection so that it works correctly on views and with the UNION, EXCEPT, and INTERCEPT operators. (CVS 839) check-in: 71cc292d user: drh tags: trunk
17:05
Do not report an error if the input to the sqlite shell ends in a comment. Ticket #211. (CVS 838) check-in: 32a8e6e9 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to VERSION.

1
2.7.6-beta1
|
1
2.7.6-beta2

Changes to publish.sh.

109
110
111
112
113
114
115


116
117
118
119
120
121
122
sqlite_set_result_string
sqlite_set_result_int
sqlite_set_result_double
sqlite_set_result_error
sqliteMalloc
sqliteFree
sqliteRealloc


END_OF_FILE
i386-mingw32msvc-dllwrap \
     --def sqlite.def -v --export-all \
     --driver-name i386-mingw32msvc-gcc \
     --dlltool-name i386-mingw32msvc-dlltool \
     --as i386-mingw32msvc-as \
     --target i386-mingw32 \







>
>







109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
sqlite_set_result_string
sqlite_set_result_int
sqlite_set_result_double
sqlite_set_result_error
sqliteMalloc
sqliteFree
sqliteRealloc
sqlite_set_authorizer
sqlite_trace
END_OF_FILE
i386-mingw32msvc-dllwrap \
     --def sqlite.def -v --export-all \
     --driver-name i386-mingw32msvc-gcc \
     --dlltool-name i386-mingw32msvc-dlltool \
     --as i386-mingw32msvc-as \
     --target i386-mingw32 \

Changes to src/build.c.

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548

549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
...
639
640
641
642
643
644
645
646
647




648
649







650
651








652
653
654





655
656
657
658
659







660
661
662
663
664
665
666
667
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.123 2003/01/14 02:54:08 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  if( z==0 ) return;
  for(i=j=0; z[i]; i++){
    int c = z[i];
    if( isspace(c) ) continue;
    z[j++] = c;
  }
  z[j] = 0;
  pCol->sortOrder = SQLITE_SO_NUM;
  if( pParse->db->file_format>=4 ){
    for(i=0; z[i]; i++){
      switch( z[i] ){
        case 'b':
        case 'B': {
          if( sqliteStrNICmp(&z[i],"blob",4)==0 ){
            pCol->sortOrder = SQLITE_SO_TEXT;
            return;
          }
          break;
        }
        case 'c':
        case 'C': {
          if( sqliteStrNICmp(&z[i],"char",4)==0 ||
                  sqliteStrNICmp(&z[i],"clob",4)==0 ){

            pCol->sortOrder = SQLITE_SO_TEXT;
            return;
          }
          break;
        }
        case 'x':
        case 'X': {
          if( i>=2 && sqliteStrNICmp(&z[i-2],"text",4)==0 ){
            pCol->sortOrder = SQLITE_SO_TEXT;
            return;
          }
          break;
        }
        default: {
          break;
        }
      }
    }
  }
}

/*
** The given token is the default value for the last column added to
** the table currently under construction.  If "minusFlag" is true, it
** means the value token was preceded by a minus sign.
................................................................................
    pTab->keyConf = onError;
  }else{
    sqliteCreateIndex(pParse, 0, 0, pList, onError, 0, 0);
  }
}

/*
** Return the appropriate collating type given the collation type token.
** Report an error if the type is undefined.




*/
int sqliteCollateType(Parse *pParse, Token *pType){







  if( pType==0 ) return SQLITE_SO_UNK;
  if( pType->n==4 && sqliteStrNICmp(pType->z, "text", 4)==0 ){








    return SQLITE_SO_TEXT;
  }
  if( pType->n==7 && sqliteStrNICmp(pType->z, "numeric", 7)==0 ){





    return SQLITE_SO_NUM;
  }
  sqliteSetNString(&pParse->zErrMsg, "unknown collating type: ", -1,
    pType->z, pType->n, 0);
  pParse->nErr++;







  return SQLITE_SO_UNK;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "COLLATE" clause has
** been seen on a column.  This routine sets the Column.sortOrder on
** the column currently under construction.







|







 







<

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







 







|
|
>
>
>
>

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







21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
...
526
527
528
529
530
531
532

533





534








535
536

















537
538
539
540
541
542
543
...
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631

632
633
634
635
636
637
638
639
640
641

642
643
644
645
646
647
648



649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
**     COPY
**     VACUUM
**     BEGIN TRANSACTION
**     COMMIT
**     ROLLBACK
**     PRAGMA
**
** $Id: build.c,v 1.124 2003/01/18 20:11:07 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** This routine is called when a new SQL statement is beginning to
** be parsed.  Check to see if the schema for the database needs
................................................................................
  if( z==0 ) return;
  for(i=j=0; z[i]; i++){
    int c = z[i];
    if( isspace(c) ) continue;
    z[j++] = c;
  }
  z[j] = 0;

  if( pParse->db->file_format>=4 ){





    pCol->sortOrder = sqliteCollateType(z, n);








  }else{
    pCol->sortOrder = SQLITE_SO_NUM;

















  }
}

/*
** The given token is the default value for the last column added to
** the table currently under construction.  If "minusFlag" is true, it
** means the value token was preceded by a minus sign.
................................................................................
    pTab->keyConf = onError;
  }else{
    sqliteCreateIndex(pParse, 0, 0, pList, onError, 0, 0);
  }
}

/*
** Return the appropriate collating type given a type name.
**
** The collation type is text (SQLITE_SO_TEXT) if the type
** name contains the character stream "text" or "blob" or
** "clob".  Any other type name is collated as numeric
** (SQLITE_SO_NUM).
*/
int sqliteCollateType(const char *zType, int nType){
  int i;
  int sortOrder = SQLITE_SO_NUM;
  for(i=0; i<nType-1; i++){
    switch( zType[i] ){
      case 'b':
      case 'B': {
        if( i<nType-3 && sqliteStrNICmp(&zType[i],"blob",4)==0 ){
          return SQLITE_SO_TEXT;

        }
        break;
      }
      case 'c':
      case 'C': {
        if( i<nType-3 && (sqliteStrNICmp(&zType[i],"char",4)==0 ||
                           sqliteStrNICmp(&zType[i],"clob",4)==0)
        ){
          return SQLITE_SO_TEXT;
        }

        break;
      }
      case 'x':
      case 'X': {
        if( i>=2 && sqliteStrNICmp(&zType[i-2],"text",4)==0 ){
          return SQLITE_SO_TEXT;
        }



        break;
      }
      default: {
        break;
      }
    }
  }
  return SQLITE_SO_NUM;
}

/*
** This routine is called by the parser while in the middle of
** parsing a CREATE TABLE statement.  A "COLLATE" clause has
** been seen on a column.  This routine sets the Column.sortOrder on
** the column currently under construction.

Changes to src/expr.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
**    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.85 2003/01/14 02:49:28 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
** by subsequent calls to sqlite*ListAppend() routines.
**
** Any tables that the SrcList might point to are not duplicated.
*/
Expr *sqliteExprDup(Expr *p){
  Expr *pNew;
  if( p==0 ) return 0;
  pNew = sqliteMalloc( sizeof(*p) );
  if( pNew==0 ) return 0;
  memcpy(pNew, p, sizeof(*pNew));
  if( p->token.z!=0 ){
    pNew->token.z = sqliteStrDup(p->token.z);
    pNew->token.dyn = 1;
  }else{
    pNew->token.z = 0;







|







 







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
**    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.86 2003/01/18 20:11:07 drh Exp $
*/
#include "sqliteInt.h"
#include <ctype.h>

/*
** Construct a new expression node and return a pointer to it.  Memory
** for this node is obtained from sqliteMalloc().  The calling function
................................................................................
** by subsequent calls to sqlite*ListAppend() routines.
**
** Any tables that the SrcList might point to are not duplicated.
*/
Expr *sqliteExprDup(Expr *p){
  Expr *pNew;
  if( p==0 ) return 0;
  pNew = sqliteMallocRaw( sizeof(*p) );
  if( pNew==0 ) return 0;
  memcpy(pNew, p, sizeof(*pNew));
  if( p->token.z!=0 ){
    pNew->token.z = sqliteStrDup(p->token.z);
    pNew->token.dyn = 1;
  }else{
    pNew->token.z = 0;

Changes to src/parse.y.

10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
**
*************************************************************************
** 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.87 2003/01/13 23:27:33 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
ccons ::= PRIMARY KEY sortorder onconf(R).  {sqliteAddPrimaryKey(pParse,0,R);}
ccons ::= UNIQUE onconf(R).            {sqliteCreateIndex(pParse,0,0,0,R,0,0);}
ccons ::= CHECK LP expr RP onconf.
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqliteCreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqliteDeferForeignKey(pParse,D);}
ccons ::= COLLATE id(C).  {
   sqliteAddCollateType(pParse, sqliteCollateType(pParse, &C));
}

// The next group of rules parses the arguments to a REFERENCES clause
// that determine if the referential integrity checking is deferred or
// or immediate and which determine what action to take if a ref-integ
// check fails.
//
................................................................................
%type sortorder {int}
%type collate {int}

sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_ASC;}
collate(C) ::= .                {C = SQLITE_SO_UNK;}
collate(C) ::= COLLATE id(X).   {C = sqliteCollateType(pParse, &X);}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqliteExprListDelete($$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}

%type having_opt {Expr*}







|







 







|







 







|







10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
...
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
...
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
**
*************************************************************************
** 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.88 2003/01/18 20:11:07 drh Exp $
*/
%token_prefix TK_
%token_type {Token}
%default_type {Token}
%extra_argument {Parse *pParse}
%syntax_error {
  sqliteSetString(&pParse->zErrMsg,"syntax error",0);
................................................................................
ccons ::= PRIMARY KEY sortorder onconf(R).  {sqliteAddPrimaryKey(pParse,0,R);}
ccons ::= UNIQUE onconf(R).            {sqliteCreateIndex(pParse,0,0,0,R,0,0);}
ccons ::= CHECK LP expr RP onconf.
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
                                {sqliteCreateForeignKey(pParse,0,&T,TA,R);}
ccons ::= defer_subclause(D).   {sqliteDeferForeignKey(pParse,D);}
ccons ::= COLLATE id(C).  {
   sqliteAddCollateType(pParse, sqliteCollateType(C.z, C.n));
}

// The next group of rules parses the arguments to a REFERENCES clause
// that determine if the referential integrity checking is deferred or
// or immediate and which determine what action to take if a ref-integ
// check fails.
//
................................................................................
%type sortorder {int}
%type collate {int}

sortorder(A) ::= ASC.           {A = SQLITE_SO_ASC;}
sortorder(A) ::= DESC.          {A = SQLITE_SO_DESC;}
sortorder(A) ::= .              {A = SQLITE_SO_ASC;}
collate(C) ::= .                {C = SQLITE_SO_UNK;}
collate(C) ::= COLLATE id(X).   {C = sqliteCollateType(X.z, X.n);}

%type groupby_opt {ExprList*}
%destructor groupby_opt {sqliteExprListDelete($$);}
groupby_opt(A) ::= .                      {A = 0;}
groupby_opt(A) ::= GROUP BY exprlist(X).  {A = X;}

%type having_opt {Expr*}

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
671
672
673
674
675
676
677
678
679







































680
681
682
683
684
685
686
687
...
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
....
1046
1047
1048
1049
1050
1051
1052







1053
1054
1055
1056
1057
1058
1059
....
1085
1086
1087
1088
1089
1090
1091

1092
1093
1094
1095
1096
1097
1098
....
1133
1134
1135
1136
1137
1138
1139
1140




































1141
1142
1143
1144
1145
1146
1147
....
1244
1245
1246
1247
1248
1249
1250

1251
1252
1253
1254
1255

1256
1257
1258
1259
1260
1261
1262
....
1299
1300
1301
1302
1303
1304
1305

1306
1307
1308
1309
1310
1311

1312
1313
1314
1315
1316
1317
1318
....
1327
1328
1329
1330
1331
1332
1333






1334
1335
1336
1337
1338
1339
1340
....
1379
1380
1381
1382
1383
1384
1385

1386
1387
1388
1389
1390
1391
1392
....
1682
1683
1684
1685
1686
1687
1688

1689
1690
1691
1692
1693
1694
1695
....
1987
1988
1989
1990
1991
1992
1993







1994
1995
1996
1997
1998
1999
2000
**    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.121 2003/01/13 23:27:33 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  sqliteVdbeAddOp(v, OP_Goto, 0, addr);
  sqliteVdbeResolveLabel(v, end);
  sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
}

/*
** Generate code that will tell the VDBE how many columns there
** are in the result and the name for each column.  This information







































** is used to provide "argc" and "azCol[]" values in the callback.
*/
static void generateColumnNames(
  Parse *pParse,      /* Parser context */
  int base,           /* VDBE cursor corresponding to first entry in pTabList */
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
................................................................................
  pParse->colNamesSet = 1;
  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;
    p = pEList->a[i].pExpr;
    if( p==0 ) continue;
    if( pParse->db->flags & SQLITE_ReportTypes ){
      if( zType==0 ){
        if( sqliteExprType(p)==SQLITE_SO_TEXT ){
          zType = "TEXT";
        }else{
          zType = "NUMERIC";
        }
      }
      sqliteVdbeAddOp(v, OP_ColumnName, i + pEList->nExpr, 0);
      sqliteVdbeChangeP3(v, -1, zType, P3_STATIC);
    }
    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
................................................................................
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first.  A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.







*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this value in iTable */
  int mustComplete        /* If TRUE all ORDER BYs must match */
................................................................................
        sprintf(zBuf,"ORDER BY position %d should be between 1 and %d",
           iCol, pEList->nExpr);
        sqliteSetString(&pParse->zErrMsg, zBuf, 0);
        pParse->nErr++;
        nErr++;
        break;
      }

      iCol--;
    }
    for(j=0; iCol<0 && j<pEList->nExpr; j++){
      if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
        char *zName, *zLabel;
        zName = pEList->a[j].zName;
        assert( pE->token.z );
................................................................................
Vdbe *sqliteGetVdbe(Parse *pParse){
  Vdbe *v = pParse->pVdbe;
  if( v==0 ){
    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db);
  }
  return v;
}
    





































/*
** This routine is called to process a query that is really the union
** or intersection of two or more separate queries.
**
** "p" points to the right-most of the two queries.  The results should
** be stored in eDest with parameter iParm.
................................................................................
      ** it is that we currently need.
      */      
      if( eDest!=priorOp || unionTab!=iParm ){
        int iCont, iBreak, iStart;
        assert( p->pEList );
        if( eDest==SRT_Callback ){
          generateColumnNames(pParse, p->base, 0, p->pEList);

        }
        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeMakeLabel(v);
        sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
        iStart = sqliteVdbeCurrentAddr(v);

        rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
        if( rc ) return 1;
        sqliteVdbeResolveLabel(v, iCont);
        sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
        sqliteVdbeResolveLabel(v, iBreak);
................................................................................

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      if( eDest==SRT_Callback ){
        generateColumnNames(pParse, p->base, 0, p->pEList);

      }
      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
      iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);

      rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
      if( rc ) return 1;
      sqliteVdbeResolveLabel(v, iCont);
      sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
      sqliteVdbeResolveLabel(v, iBreak);
................................................................................
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    sqliteSetString(&pParse->zErrMsg, "SELECTs to the left and right of ",
      selectOpName(p->op), " do not have the same number of result columns", 0);
    pParse->nErr++;
    return 1;
  }






  return 0;
}

/*
** Recursively scan through an expression tree.  For every reference
** to a column in table number iFrom, change that reference to the
** same column in table number iTo.
................................................................................
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable && pExpr->iColumn>=0 ){
    Expr *pNew;
    assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
    assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
    pNew = pEList->a[pExpr->iColumn].pExpr;
    assert( pNew!=0 );
    pExpr->op = pNew->op;

    assert( pExpr->pLeft==0 );
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    assert( pExpr->pRight==0 );
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    assert( pExpr->pList==0 );
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
................................................................................
  /* Identify column names if we will be using the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, p->pSrc, p->pEList);

  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.
  */
................................................................................
  ** If flattening is a possiblity, do so and return immediately.  
  */
  if( pParent && pParentAgg &&
      flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }








  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
  }








|







 







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







 







<
<
<
<
<
<
<
<
<
<
<







 







>
>
>
>
>
>
>







 







>







 







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







 







>





>







 







>






>







 







>
>
>
>
>
>







 







>







 







>







 







>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
...
730
731
732
733
734
735
736











737
738
739
740
741
742
743
....
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
....
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
....
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
....
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
....
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
....
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
....
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
....
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
....
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
**    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.122 2003/01/18 20:11:07 drh Exp $
*/
#include "sqliteInt.h"


/*
** Allocate a new Select structure and return a pointer to that
** structure.
................................................................................
  }
  sqliteVdbeAddOp(v, OP_Goto, 0, addr);
  sqliteVdbeResolveLabel(v, end);
  sqliteVdbeAddOp(v, OP_SortReset, 0, 0);
}

/*
** Generate code that will tell the VDBE the datatypes of
** columns in the result set.
*/
static void generateColumnTypes(
  Parse *pParse,      /* Parser context */
  int base,           /* VDBE cursor corresponding to first entry in pTabList */
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
  Vdbe *v = pParse->pVdbe;
  int i;
  if( (pParse->db->flags & SQLITE_ReportTypes)==0 ) return;
  for(i=0; i<pEList->nExpr; i++){
    Expr *p = pEList->a[i].pExpr;
    char *zType = 0;
    if( p==0 ) continue;
    if( p->op==TK_COLUMN && pTabList ){
      Table *pTab = pTabList->a[p->iTable - base].pTab;
      int iCol = p->iColumn;
      if( iCol<0 ) iCol = pTab->iPKey;
      assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
      if( iCol<0 ){
        zType = "INTEGER";
      }else{
        zType = pTab->aCol[iCol].zType;
      }
    }else{
      if( sqliteExprType(p)==SQLITE_SO_TEXT ){
        zType = "TEXT";
      }else{
        zType = "NUMERIC";
      }
    }
    sqliteVdbeAddOp(v, OP_ColumnName, i + pEList->nExpr, 0);
    sqliteVdbeChangeP3(v, -1, zType, P3_STATIC);
  }
}

/*
** Generate code that will tell the VDBE the names of columns
** in the result set.  This information is used to provide the
** azCol[] vaolues in the callback.
*/
static void generateColumnNames(
  Parse *pParse,      /* Parser context */
  int base,           /* VDBE cursor corresponding to first entry in pTabList */
  SrcList *pTabList,  /* List of tables */
  ExprList *pEList    /* Expressions defining the result set */
){
................................................................................
  pParse->colNamesSet = 1;
  for(i=0; i<pEList->nExpr; i++){
    Expr *p;
    char *zType = 0;
    int showFullNames;
    p = pEList->a[i].pExpr;
    if( p==0 ) continue;











    if( pEList->a[i].zName ){
      char *zName = pEList->a[i].zName;
      sqliteVdbeAddOp(v, OP_ColumnName, i, 0);
      sqliteVdbeChangeP3(v, -1, zName, strlen(zName));
      continue;
    }
    showFullNames = (pParse->db->flags & SQLITE_FullColNames)!=0;
................................................................................
** value of the top-level node is filled with iTable parameter.
**
** If there are prior SELECT clauses, they are processed first.  A match
** in an earlier SELECT takes precedence over a later SELECT.
**
** Any entry that does not match is flagged as an error.  The number
** of errors is returned.
**
** This routine does NOT correctly initialize the Expr.dataType  field
** of the ORDER BY expressions.  The multiSelectSortOrder() routine
** must be called to do that after the individual select statements
** have all been analyzed.  This routine is unable to compute Expr.dataType
** because it must be called before the individual select statements
** have been analyzed.
*/
static int matchOrderbyToColumn(
  Parse *pParse,          /* A place to leave error messages */
  Select *pSelect,        /* Match to result columns of this SELECT */
  ExprList *pOrderBy,     /* The ORDER BY values to match against columns */
  int iTable,             /* Insert this value in iTable */
  int mustComplete        /* If TRUE all ORDER BYs must match */
................................................................................
        sprintf(zBuf,"ORDER BY position %d should be between 1 and %d",
           iCol, pEList->nExpr);
        sqliteSetString(&pParse->zErrMsg, zBuf, 0);
        pParse->nErr++;
        nErr++;
        break;
      }
      if( !mustComplete ) continue;
      iCol--;
    }
    for(j=0; iCol<0 && j<pEList->nExpr; j++){
      if( pEList->a[j].zName && (pE->op==TK_ID || pE->op==TK_STRING) ){
        char *zName, *zLabel;
        zName = pEList->a[j].zName;
        assert( pE->token.z );
................................................................................
Vdbe *sqliteGetVdbe(Parse *pParse){
  Vdbe *v = pParse->pVdbe;
  if( v==0 ){
    v = pParse->pVdbe = sqliteVdbeCreate(pParse->db);
  }
  return v;
}

/*
** This routine sets the Expr.dataType field on all elements of
** the pOrderBy expression list.  The pOrderBy list will have been
** set up by matchOrderbyToColumn().  Hence each expression has
** a TK_COLUMN as its root node.  The Expr.iColumn refers to a 
** column in the result set.   The datatype is set to SQLITE_SO_TEXT
** if the corresponding column in p and every SELECT to the left of
** p has a datatype of SQLITE_SO_TEXT.  If the cooressponding column
** in p or any of the left SELECTs is SQLITE_SO_NUM, then the datatype
** of the order-by expression is set to SQLITE_SO_NUM.
**
** Examples:
**
**    SELECT a,b
*/ 
static void multiSelectSortOrder(Select *p, ExprList *pOrderBy){
  int i;
  ExprList *pEList;
  if( pOrderBy==0 ) return;
  if( p==0 ){
    for(i=0; i<pOrderBy->nExpr; i++){
      pOrderBy->a[i].pExpr->dataType = SQLITE_SO_TEXT;
    }
    return;
  }
  multiSelectSortOrder(p->pPrior, pOrderBy);
  pEList = p->pEList;
  for(i=0; i<pOrderBy->nExpr; i++){
    Expr *pE = pOrderBy->a[i].pExpr;
    if( pE->dataType==SQLITE_SO_NUM ) continue;
    assert( pE->iColumn>=0 );
    if( pEList->nExpr>pE->iColumn ){
      pE->dataType = sqliteExprType(pEList->a[pE->iColumn].pExpr);
    }
  }
}

/*
** This routine is called to process a query that is really the union
** or intersection of two or more separate queries.
**
** "p" points to the right-most of the two queries.  The results should
** be stored in eDest with parameter iParm.
................................................................................
      ** it is that we currently need.
      */      
      if( eDest!=priorOp || unionTab!=iParm ){
        int iCont, iBreak, iStart;
        assert( p->pEList );
        if( eDest==SRT_Callback ){
          generateColumnNames(pParse, p->base, 0, p->pEList);
          generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
        }
        iBreak = sqliteVdbeMakeLabel(v);
        iCont = sqliteVdbeMakeLabel(v);
        sqliteVdbeAddOp(v, OP_Rewind, unionTab, iBreak);
        iStart = sqliteVdbeCurrentAddr(v);
        multiSelectSortOrder(p, p->pOrderBy);
        rc = selectInnerLoop(pParse, p, p->pEList, unionTab, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
        if( rc ) return 1;
        sqliteVdbeResolveLabel(v, iCont);
        sqliteVdbeAddOp(v, OP_Next, unionTab, iStart);
        sqliteVdbeResolveLabel(v, iBreak);
................................................................................

      /* Generate code to take the intersection of the two temporary
      ** tables.
      */
      assert( p->pEList );
      if( eDest==SRT_Callback ){
        generateColumnNames(pParse, p->base, 0, p->pEList);
        generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
      }
      iBreak = sqliteVdbeMakeLabel(v);
      iCont = sqliteVdbeMakeLabel(v);
      sqliteVdbeAddOp(v, OP_Rewind, tab1, iBreak);
      iStart = sqliteVdbeAddOp(v, OP_FullKey, tab1, 0);
      sqliteVdbeAddOp(v, OP_NotFound, tab2, iCont);
      multiSelectSortOrder(p, p->pOrderBy);
      rc = selectInnerLoop(pParse, p, p->pEList, tab1, p->pEList->nExpr,
                             p->pOrderBy, -1, eDest, iParm, 
                             iCont, iBreak);
      if( rc ) return 1;
      sqliteVdbeResolveLabel(v, iCont);
      sqliteVdbeAddOp(v, OP_Next, tab1, iStart);
      sqliteVdbeResolveLabel(v, iBreak);
................................................................................
  assert( p->pEList && pPrior->pEList );
  if( p->pEList->nExpr!=pPrior->pEList->nExpr ){
    sqliteSetString(&pParse->zErrMsg, "SELECTs to the left and right of ",
      selectOpName(p->op), " do not have the same number of result columns", 0);
    pParse->nErr++;
    return 1;
  }

  /* Issue a null callback if that is what the user wants.
  */
  if( (pParse->db->flags & SQLITE_NullCallback)!=0 && eDest==SRT_Callback ){
    sqliteVdbeAddOp(v, OP_NullCallback, p->pEList->nExpr, 0);
  }
  return 0;
}

/*
** Recursively scan through an expression tree.  For every reference
** to a column in table number iFrom, change that reference to the
** same column in table number iTo.
................................................................................
  if( pExpr->op==TK_COLUMN && pExpr->iTable==iTable && pExpr->iColumn>=0 ){
    Expr *pNew;
    assert( pEList!=0 && pExpr->iColumn<pEList->nExpr );
    assert( pExpr->pLeft==0 && pExpr->pRight==0 && pExpr->pList==0 );
    pNew = pEList->a[pExpr->iColumn].pExpr;
    assert( pNew!=0 );
    pExpr->op = pNew->op;
    pExpr->dataType = pNew->dataType;
    assert( pExpr->pLeft==0 );
    pExpr->pLeft = sqliteExprDup(pNew->pLeft);
    assert( pExpr->pRight==0 );
    pExpr->pRight = sqliteExprDup(pNew->pRight);
    assert( pExpr->pList==0 );
    pExpr->pList = sqliteExprListDup(pNew->pList);
    pExpr->iTable = pNew->iTable;
................................................................................
  /* Identify column names if we will be using the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  v = sqliteGetVdbe(pParse);
  if( v==0 ) return 0;
  if( eDest==SRT_Callback ){
    generateColumnNames(pParse, p->base, p->pSrc, p->pEList);
    generateColumnTypes(pParse, p->base, p->pSrc, p->pEList);
  }

  /* Generating code to find the min or the max.  Basically all we have
  ** to do is find the first or the last entry in the chosen index.  If
  ** the min() or max() is on the INTEGER PRIMARY KEY, then find the first
  ** or last entry in the main table.
  */
................................................................................
  ** If flattening is a possiblity, do so and return immediately.  
  */
  if( pParent && pParentAgg &&
      flattenSubquery(pParse, pParent, parentTab, *pParentAgg, isAgg) ){
    if( isAgg ) *pParentAgg = 1;
    return rc;
  }

  /* Identify column types if we will be using in the callback.  This
  ** step is skipped if the output is going to a table or a memory cell.
  */
  if( eDest==SRT_Callback ){
    generateColumnTypes(pParse, p->base, pTabList, pEList);
  }

  /* If the output is destined for a temporary table, open that table.
  */
  if( eDest==SRT_TempTable ){
    sqliteVdbeAddOp(v, OP_OpenTemp, iParm, 0);
  }

Changes to src/sqliteInt.h.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
**    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.155 2003/01/16 16:28:54 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
void sqliteOpenMasterTable(Vdbe *v, int);
void sqliteStartTable(Parse*,Token*,Token*,int,int);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddNotNull(Parse*, int);
void sqliteAddPrimaryKey(Parse*, IdList*, int);
void sqliteAddColumnType(Parse*,Token*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
int sqliteCollateType(Parse*, Token*);
void sqliteAddCollateType(Parse*, int);
void sqliteEndTable(Parse*,Token*,Select*);
void sqliteCreateView(Parse*,Token*,Token*,Select*,int);
int sqliteViewGetColumnNames(Parse*,Table*);
void sqliteViewResetAll(sqlite*);
void sqliteDropTable(Parse*, Token*, int);
void sqliteDeleteTable(sqlite*, Table*);







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
**    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.156 2003/01/18 20:11:07 drh Exp $
*/
#include "config.h"
#include "sqlite.h"
#include "hash.h"
#include "vdbe.h"
#include "parse.h"
#include "btree.h"
................................................................................
void sqliteOpenMasterTable(Vdbe *v, int);
void sqliteStartTable(Parse*,Token*,Token*,int,int);
void sqliteAddColumn(Parse*,Token*);
void sqliteAddNotNull(Parse*, int);
void sqliteAddPrimaryKey(Parse*, IdList*, int);
void sqliteAddColumnType(Parse*,Token*,Token*);
void sqliteAddDefaultValue(Parse*,Token*,int);
int sqliteCollateType(const char*, int);
void sqliteAddCollateType(Parse*, int);
void sqliteEndTable(Parse*,Token*,Select*);
void sqliteCreateView(Parse*,Token*,Token*,Select*,int);
int sqliteViewGetColumnNames(Parse*,Table*);
void sqliteViewResetAll(sqlite*);
void sqliteDropTable(Parse*, Token*, int);
void sqliteDeleteTable(sqlite*, Table*);

Changes to test/misc1.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
372
373
374
375
376
377
378





379
380
381
382
383
384
385
386
387
388
389
390
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.17 2002/12/07 21:45:14 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#
................................................................................
  }
} {1 4 4}
do_test misc1-12.9 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
  }
} {1 2 2 3 4 2}





do_test misc1-12.10 {
  catchsql {
    SELECT * FROM t6 ORDER BY a COLLATE unknown;
  }
} {1 {unknown collating type: unknown}}
do_test misc1-12.11 {
  execsql {
    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,0,1);
    INSERT INTO t8 VALUES(0.0,0,2);
    INSERT INTO t8 VALUES(0,0.0,3);
    INSERT INTO t8 VALUES(0.0,0.0,4);







|







 







>
>
>
>
>




|







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc1.test,v 1.18 2003/01/18 20:11:07 drh Exp $

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

# Test the creation and use of tables that have a large number
# of columns.
#
................................................................................
  }
} {1 4 4}
do_test misc1-12.9 {
  execsql {
    SELECT min(z), max(z), count(z) FROM t7 GROUP BY y ORDER BY 1;
  }
} {1 2 2 3 4 2}

# This used to be an error.  But we changed the code so that arbitrary
# identifiers can be used as a collating sequence.  Collation is by text
# if the identifier contains "text", "blob", or "clob" and is numeric
# otherwise.
do_test misc1-12.10 {
  catchsql {
    SELECT * FROM t6 ORDER BY a COLLATE unknown;
  }
} {0 {0 0.0 y 0}}
do_test misc1-12.11 {
  execsql {
    CREATE TABLE t8(x TEXT COLLATE numeric, y INTEGER COLLATE text, z);
    INSERT INTO t8 VALUES(0,0,1);
    INSERT INTO t8 VALUES(0.0,0,2);
    INSERT INTO t8 VALUES(0,0.0,3);
    INSERT INTO t8 VALUES(0.0,0.0,4);

Changes to test/pragma.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156



157





158



















159
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.2 2003/01/11 14:19:52 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {}
do_test pragma-1.12 {
  execsql {
    PRAGMA empty_result_callbacks=on;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {NUMERIC NUMERIC NUMERIC NUMERIC NUMERIC}
do_test pragma-1.13 {
  execsql {
    CREATE TABLE t1(
       a INTEGER,
       b TEXT,
       c WHATEVER,
       d CLOB,
       e BLOB,
       f VARCHAR(123),
       g nVaRcHaR(432)
    );
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {NUMERIC TEXT NUMERIC TEXT TEXT TEXT TEXT}
do_test pragma-1.14 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-1.15 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}





























finish_test







|







 







|













|










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

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
...
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the PRAGMA command.
#
# $Id: pragma.test,v 1.3 2003/01/18 20:11:07 drh Exp $

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

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
................................................................................
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {}
do_test pragma-1.12 {
  execsql {
    PRAGMA empty_result_callbacks=on;
  }
  sqlite_datatypes $::DB {SELECT * FROM sqlite_master}
} {text text text integer text}
do_test pragma-1.13 {
  execsql {
    CREATE TABLE t1(
       a INTEGER,
       b TEXT,
       c WHATEVER,
       d CLOB,
       e BLOB,
       f VARCHAR(123),
       g nVaRcHaR(432)
    );
  }
  sqlite_datatypes $::DB {SELECT * FROM t1}
} {INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-1.14 {
  sqlite_datatypes $::DB {
     SELECT 1, 'hello', NULL
  }
} {NUMERIC TEXT TEXT}
do_test pragma-1.15 {
  sqlite_datatypes $::DB {
     SELECT 1+2 AS X, 'hello' || 5 AS Y, NULL AS Z
  }
} {NUMERIC TEXT TEXT}
do_test pragma-1.16 {
  execsql {
    CREATE VIEW v1 AS SELECT a+b, b||c, * FROM t1;
  }
  sqlite_datatypes $::DB {SELECT * FROM v1}
} {NUMERIC TEXT INTEGER TEXT WHATEVER CLOB BLOB VARCHAR(123) nVaRcHaR(432)}
do_test pragma-1.17 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 UNION SELECT a,c FROM t1
  }
} {INTEGER WHATEVER}
do_test pragma-1.18 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 EXCEPT SELECT c,e FROM t1
  }
} {WHATEVER BLOB}
do_test pragma-1.19 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM t1
  }
} {WHATEVER BLOB}
do_test pragma-1.20 {
  sqlite_datatypes $::DB {
    SELECT d,e FROM t1 INTERSECT SELECT c,e FROM v1
  }
} {WHATEVER BLOB}
    


finish_test

Changes to test/sort.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
262
263
264
265
266
267
268
269
















































































270
#    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.7 2002/08/26 19:55:11 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
  }
} {4 1 2 3 5 6}
do_test sort-6.4 {
  execsql {
    select a from t3 order by b desc, a desc;
  }
} {4 1 6 5 3 2}

















































































finish_test







|







 








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

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
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
338
339
340
341
342
343
344
345
346
347
348
349
350
#    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.8 2003/01/18 20:11:07 drh Exp $

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

# Create a bunch of data to sort against
#
do_test sort-1.0 {
................................................................................
  }
} {4 1 2 3 5 6}
do_test sort-6.4 {
  execsql {
    select a from t3 order by b desc, a desc;
  }
} {4 1 6 5 3 2}

do_test sort-7.1 {
  execsql {
    CREATE TABLE t4(
      a INTEGER,
      b VARCHAR(30)
    );
    INSERT INTO t4 VALUES(1,1);
    INSERT INTO t4 VALUES(2,2);
    INSERT INTO t4 VALUES(11,11);
    INSERT INTO t4 VALUES(12,12);
    SELECT a FROM t4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.2 {
  execsql {
    SELECT b FROM t4 ORDER BY 1
  }
} {1 11 12 2}
do_test sort-7.3 {
  execsql {
    CREATE VIEW v4 AS SELECT * FROM t4;
    SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.4 {
  execsql {
    SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}
do_test sort-7.5 {
  execsql {
    SELECT a FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.6 {
  execsql {
    SELECT b FROM t4 UNION SELECT a FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.7 {
  execsql {
    SELECT a FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 2 11 12}
do_test sort-7.8 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1;
  }
} {1 11 12 2}
do_test sort-7.9 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE numeric;
  }
} {1 2 11 12}
do_test sort-7.10 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE integer;
  }
} {1 2 11 12}
do_test sort-7.11 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE text;
  }
} {1 11 12 2}
do_test sort-7.12 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE blob;
  }
} {1 11 12 2}
do_test sort-7.13 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE clob;
  }
} {1 11 12 2}
do_test sort-7.14 {
  execsql {
    SELECT b FROM t4 UNION SELECT b FROM v4 ORDER BY 1 COLLATE varchar;
  }
} {1 11 12 2}

finish_test

Changes to www/index.tcl.

1
2
3
4
5
6
7
8
9
10
11
...
176
177
178
179
180
181
182
183


184
185
186
187
188
189
190
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.70 2002/11/06 14:08:12 drh Exp $}

puts {<html>
<head><title>SQLite: An Embeddable SQL Database Engine</title></head>
<body bgcolor=white>
<h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
puts {<h2>Professional Support and Custom Modifications</h2>}

puts {
<p>
If you would like professional support for SQLite
or if you want custom modifications to SQLite preformed by the
original author, these services are available for a modest fee.
For additional information contact:</p>



<blockquote>
D. Richard Hipp <br />
Hwaci - Applied Software Research <br />
704.948.4565 <br />
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>
</blockquote>



|







 







|
>
>







1
2
3
4
5
6
7
8
9
10
11
...
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.71 2003/01/18 20:11:07 drh Exp $}

puts {<html>
<head><title>SQLite: An Embeddable SQL Database Engine</title></head>
<body bgcolor=white>
<h1 align=center>SQLite<br>An Embeddable SQL Database Engine</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
................................................................................
puts {<h2>Professional Support and Custom Modifications</h2>}

puts {
<p>
If you would like professional support for SQLite
or if you want custom modifications to SQLite preformed by the
original author, these services are available for a modest fee.
For additional information visit
<a href="http://www.hwaci.com/sw/sqlite/support.html">
http://www.hwaci.com/sw/sqlite/support.html</a> or contact:</p>

<blockquote>
D. Richard Hipp <br />
Hwaci - Applied Software Research <br />
704.948.4565 <br />
<a href="mailto:drh@hwaci.com">drh@hwaci.com</a>
</blockquote>