Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | The right-hand side of an AS in a SELECT can be used within expressions of the WHERE, ORDER BY, GROUP BY, and/or HAVING clauses. (CVS 350) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3684beab0f8a71ebdf453871bbde7a9a |
User & Date: | drh 2002-01-22 03:13:42.000 |
Context
2002-01-22
| ||
12:39 | Fix a bug in the -separator command-line option. (CVS 351) (check-in: 593c986f69 user: drh tags: trunk) | |
03:13 | The right-hand side of an AS in a SELECT can be used within expressions of the WHERE, ORDER BY, GROUP BY, and/or HAVING clauses. (CVS 350) (check-in: 3684beab0f user: drh tags: trunk) | |
2002-01-16
| ||
21:05 | Version 2.2.3 (CVS 450) (check-in: a4fe893ce7 user: drh tags: trunk) | |
Changes
Changes to VERSION.
|
| | | 1 | 2.2.4 |
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.65 2002/01/22 03:13:42 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called after a single SQL statement has been ** parsed and we want to execute the VDBE code to implement |
︙ | ︙ | |||
123 124 125 126 127 128 129 | }else{ pNew->token.z = ""; pNew->token.n = 0; } return pNew; } | < < < < < < < < < < < < | 123 124 125 126 127 128 129 130 131 132 133 134 135 136 | }else{ pNew->token.z = ""; pNew->token.n = 0; } return pNew; } /* ** Locate the in-memory structure that describes ** a particular database table given the name ** of that table. Return NULL if not found. */ Table *sqliteFindTable(sqlite *db, char *zName){ Table *p = sqliteHashFind(&db->tblHash, zName, strlen(zName)+1); |
︙ | ︙ |
Changes to src/delete.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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 DELETE FROM statements. ** | | | 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 DELETE FROM statements. ** ** $Id: delete.c,v 1.23 2002/01/22 03:13:42 drh Exp $ */ #include "sqliteInt.h" /* ** Process a DELETE FROM statement. */ void sqliteDeleteFrom( |
︙ | ︙ | |||
66 67 68 69 70 71 72 | } pTab = pTabList->a[0].pTab; /* Resolve the column names in all the expressions. */ if( pWhere ){ sqliteExprResolveInSelect(pParse, pWhere); | | | 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | } pTab = pTabList->a[0].pTab; /* Resolve the column names in all the expressions. */ if( pWhere ){ sqliteExprResolveInSelect(pParse, pWhere); if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){ goto delete_from_cleanup; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto delete_from_cleanup; } } |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | > > > > > > > > > > > > > > > | 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 | ** 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.38 2002/01/22 03:13:42 drh Exp $ */ #include "sqliteInt.h" /* ** Recursively delete an expression tree. */ void sqliteExprDelete(Expr *p){ if( p==0 ) return; if( p->op!=TK_AS ){ if( p->pLeft ) sqliteExprDelete(p->pLeft); if( p->pRight ) sqliteExprDelete(p->pRight); } if( p->pList ) sqliteExprListDelete(p->pList); if( p->pSelect ) sqliteSelectDelete(p->pSelect); sqliteFree(p); } /* ** Walk an expression tree. Return 1 if the expression is constant ** and 0 if it involves variables. */ static int isConstant(Expr *p){ switch( p->op ){ case TK_ID: |
︙ | ︙ | |||
112 113 114 115 116 117 118 | ** This routine also looks for scalar SELECTs that are part of an expression. ** If it finds any, it generates code to write the value of that select ** into a memory cell. ** ** Unknown columns or tables provoke an error. The function returns ** the number of errors seen and leaves an error message on pParse->zErrMsg. */ | | > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > < | 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 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 | ** This routine also looks for scalar SELECTs that are part of an expression. ** If it finds any, it generates code to write the value of that select ** into a memory cell. ** ** Unknown columns or tables provoke an error. The function returns ** the number of errors seen and leaves an error message on pParse->zErrMsg. */ int sqliteExprResolveIds( Parse *pParse, /* The parser context */ IdList *pTabList, /* List of tables used to resolve column names */ ExprList *pEList, /* List of expressions used to resolve "AS" */ Expr *pExpr /* The expression to be analyzed. */ ){ if( pExpr==0 || pTabList==0 ) return 0; switch( pExpr->op ){ /* A lone identifier. Try and match it as follows: ** ** 1. To the name of a column of one of the tables in pTabList ** ** 2. To the right side of an AS keyword in the column list of ** a SELECT statement. (For example, match against 'x' in ** "SELECT a+b AS 'x' FROM t1".) ** ** 3. One of the special names "ROWID", "OID", or "_ROWID_". */ case TK_ID: { int cnt = 0; /* Number of matches */ int i; /* Loop counter */ char *z = sqliteStrNDup(pExpr->token.z, pExpr->token.n); if( z==0 ) return 1; for(i=0; i<pTabList->nId; i++){ int j; Table *pTab = pTabList->a[i].pTab; if( pTab==0 ) continue; for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, z)==0 ){ cnt++; pExpr->iTable = i + pParse->nTab; if( j==pTab->iPKey ){ /* Substitute the record number for the INTEGER PRIMARY KEY */ pExpr->iColumn = -1; }else{ pExpr->iColumn = j; } pExpr->op = TK_COLUMN; } } } if( cnt==0 && pEList!=0 ){ int j; for(j=0; j<pEList->nExpr; j++){ char *zAs = pEList->a[j].zName; if( zAs!=0 && sqliteStrICmp(zAs, z)==0 ){ cnt++; assert( pExpr->pLeft==0 && pExpr->pRight==0 ); pExpr->op = TK_AS; pExpr->iColumn = j; pExpr->pLeft = pEList->a[j].pExpr; } } } if( cnt==0 && sqliteIsRowid(z) ){ pExpr->iColumn = -1; pExpr->iTable = pParse->nTab; cnt = 1 + (pTabList->nId>1); pExpr->op = TK_COLUMN; } sqliteFree(z); if( cnt==0 ){ sqliteSetNString(&pParse->zErrMsg, "no such column: ", -1, pExpr->token.z, pExpr->token.n, 0); pParse->nErr++; return 1; }else if( cnt>1 ){ sqliteSetNString(&pParse->zErrMsg, "ambiguous column name: ", -1, pExpr->token.z, pExpr->token.n, 0); pParse->nErr++; return 1; } break; } /* A table name and column name: ID.ID */ case TK_DOT: { int cnt = 0; /* Number of matches */ int cntTab = 0; /* Number of matching tables */ |
︙ | ︙ | |||
236 237 238 239 240 241 242 | pExpr->op = TK_COLUMN; break; } case TK_IN: { Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return 1; | | | 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 | pExpr->op = TK_COLUMN; break; } case TK_IN: { Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return 1; if( sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pLeft) ){ return 1; } if( pExpr->pSelect ){ /* Case 1: expr IN (SELECT ...) ** ** Generate code to write the results of the select into a temporary ** table. The cursor number of the temporary table has already |
︙ | ︙ | |||
305 306 307 308 309 310 311 | } break; } /* For all else, just recursively walk the tree */ default: { if( pExpr->pLeft | | | | | 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 | } break; } /* For all else, just recursively walk the tree */ default: { if( pExpr->pLeft && sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pLeft) ){ return 1; } if( pExpr->pRight && sqliteExprResolveIds(pParse, pTabList, pEList, pExpr->pRight) ){ return 1; } if( pExpr->pList ){ int i; ExprList *pList = pExpr->pList; for(i=0; i<pList->nExpr; i++){ if( sqliteExprResolveIds(pParse,pTabList,pEList,pList->a[i].pExpr) ){ return 1; } } } } } return 0; |
︙ | ︙ | |||
699 700 701 702 703 704 705 706 707 708 709 710 711 712 | case TK_BETWEEN: { int lbl = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_Integer, 0, 0); sqliteExprIfFalse(pParse, pExpr, lbl); sqliteVdbeAddOp(v, OP_AddImm, 1, 0); sqliteVdbeResolveLabel(v, lbl); break; } } return; } /* ** Generate code for a boolean expression such that a jump is made | > > > > | 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 | case TK_BETWEEN: { int lbl = sqliteVdbeMakeLabel(v); sqliteVdbeAddOp(v, OP_Integer, 0, 0); sqliteExprIfFalse(pParse, pExpr, lbl); sqliteVdbeAddOp(v, OP_AddImm, 1, 0); sqliteVdbeResolveLabel(v, lbl); break; } case TK_AS: { sqliteExprCode(pParse, pExpr->pLeft); break; } } return; } /* ** Generate code for a boolean expression such that a jump is made |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 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.54 2002/01/22 03:13:42 drh Exp $ */ #include "sqliteInt.h" /* ** Allocate a new Select structure and return a pointer to that ** structure. */ |
︙ | ︙ | |||
694 695 696 697 698 699 700 | int eDest, /* One of: SRT_Callback Mem Set Union Except */ int iParm /* Save result in this memory location, if >=0 */ ){ int i; WhereInfo *pWInfo; Vdbe *v; int isAgg = 0; /* True for select lists like "count(*)" */ | | | 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 | int eDest, /* One of: SRT_Callback Mem Set Union Except */ int iParm /* Save result in this memory location, if >=0 */ ){ int i; WhereInfo *pWInfo; Vdbe *v; int isAgg = 0; /* True for select lists like "count(*)" */ ExprList *pEList; /* List of columns to extract. */ IdList *pTabList; /* List of tables to select from */ Expr *pWhere; /* The WHERE clause. May be NULL */ ExprList *pOrderBy; /* The ORDER BY clause. May be NULL */ ExprList *pGroupBy; /* The GROUP BY clause. May be NULL */ Expr *pHaving; /* The HAVING clause. May be NULL */ int isDistinct; /* True if the DISTINCT keyword is present */ int distinct; /* Table to use for the distinct set */ |
︙ | ︙ | |||
794 795 796 797 798 799 800 | /* At this point, we should have allocated all the cursors that we ** need to handle subquerys and temporary tables. From here on we ** are committed to keeping the same value for pParse->nTab. ** ** Resolve the column names and do a semantics check on all the expressions. */ for(i=0; i<pEList->nExpr; i++){ | | | | | | | 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 | /* At this point, we should have allocated all the cursors that we ** need to handle subquerys and temporary tables. From here on we ** are committed to keeping the same value for pParse->nTab. ** ** Resolve the column names and do a semantics check on all the expressions. */ for(i=0; i<pEList->nExpr; i++){ if( sqliteExprResolveIds(pParse, pTabList, 0, pEList->a[i].pExpr) ){ return 1; } if( sqliteExprCheck(pParse, pEList->a[i].pExpr, 1, &isAgg) ){ return 1; } } if( pWhere ){ if( sqliteExprResolveIds(pParse, pTabList, pEList, pWhere) ){ return 1; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ return 1; } } if( pOrderBy ){ for(i=0; i<pOrderBy->nExpr; i++){ Expr *pE = pOrderBy->a[i].pExpr; if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){ return 1; } if( sqliteExprCheck(pParse, pE, isAgg, 0) ){ return 1; } } } if( pGroupBy ){ for(i=0; i<pGroupBy->nExpr; i++){ Expr *pE = pGroupBy->a[i].pExpr; if( sqliteExprResolveIds(pParse, pTabList, pEList, pE) ){ return 1; } if( sqliteExprCheck(pParse, pE, isAgg, 0) ){ return 1; } } } if( pHaving ){ if( pGroupBy==0 ){ sqliteSetString(&pParse->zErrMsg, "a GROUP BY clause is required " "before HAVING", 0); pParse->nErr++; return 1; } if( sqliteExprResolveIds(pParse, pTabList, pEList, pHaving) ){ return 1; } if( sqliteExprCheck(pParse, pHaving, isAgg, 0) ){ return 1; } } |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 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. ** | | | 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.78 2002/01/22 03:13:42 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
513 514 515 516 517 518 519 | void sqliteVacuum(Parse*, Token*); int sqliteGlobCompare(const unsigned char*,const unsigned char*); int sqliteLikeCompare(const unsigned char*,const unsigned char*); char *sqliteTableNameFromToken(Token*); int sqliteExprCheck(Parse*, Expr*, int, int*); int sqliteExprCompare(Expr*, Expr*); int sqliteFuncId(Token*); | | | 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 | void sqliteVacuum(Parse*, Token*); int sqliteGlobCompare(const unsigned char*,const unsigned char*); int sqliteLikeCompare(const unsigned char*,const unsigned char*); char *sqliteTableNameFromToken(Token*); int sqliteExprCheck(Parse*, Expr*, int, int*); int sqliteExprCompare(Expr*, Expr*); int sqliteFuncId(Token*); int sqliteExprResolveIds(Parse*, IdList*, ExprList*, Expr*); void sqliteExprResolveInSelect(Parse*, Expr*); int sqliteExprAnalyzeAggregates(Parse*, Expr*); void sqliteParseInfoReset(Parse*); Vdbe *sqliteGetVdbe(Parse*); int sqliteRandomByte(void); int sqliteRandomInteger(void); void sqliteBeginTransaction(Parse*); void sqliteCommitTransaction(Parse*); void sqliteRollbackTransaction(Parse*); char *sqlite_mprintf(const char *, ...); |
Changes to src/update.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** 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. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle UPDATE statements. ** ** $Id: update.c,v 1.27 2002/01/22 03:13:42 drh Exp $ */ #include "sqliteInt.h" /* ** Process an UPDATE statement. */ void sqliteUpdate( |
︙ | ︙ | |||
80 81 82 83 84 85 86 | if( pWhere ){ sqliteExprResolveInSelect(pParse, pWhere); } for(i=0; i<pChanges->nExpr; i++){ sqliteExprResolveInSelect(pParse, pChanges->a[i].pExpr); } if( pWhere ){ | | | | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 | if( pWhere ){ sqliteExprResolveInSelect(pParse, pWhere); } for(i=0; i<pChanges->nExpr; i++){ sqliteExprResolveInSelect(pParse, pChanges->a[i].pExpr); } if( pWhere ){ if( sqliteExprResolveIds(pParse, pTabList, 0, pWhere) ){ goto update_cleanup; } if( sqliteExprCheck(pParse, pWhere, 0, 0) ){ goto update_cleanup; } } chngRecno = 0; for(i=0; i<pChanges->nExpr; i++){ if( sqliteExprResolveIds(pParse, pTabList, 0, pChanges->a[i].pExpr) ){ goto update_cleanup; } if( sqliteExprCheck(pParse, pChanges->a[i].pExpr, 0, 0) ){ goto update_cleanup; } for(j=0; j<pTab->nCol; j++){ if( sqliteStrICmp(pTab->aCol[j].zName, pChanges->a[i].zName)==0 ){ |
︙ | ︙ |
Changes to test/select1.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the SELECT statement. # # $Id: select1.test,v 1.17 2002/01/22 03:13:43 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to select on a non-existant table. # do_test select1-1.1 { |
︙ | ︙ | |||
462 463 464 465 466 467 468 469 470 | } {f1 f2} do_test select1-9.5 { set r(*) {} db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} set r(*) } {f1 f2} unset r finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 | } {f1 f2} do_test select1-9.5 { set r(*) {} db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {} set r(*) } {f1 f2} unset r # Check for ORDER BY clauses that refer to an AS name in the column list # do_test select1-10.1 { execsql { SELECT f1 AS x FROM test1 ORDER BY x } } {11 33} do_test select1-10.2 { execsql { SELECT f1 AS x FROM test1 ORDER BY -x } } {33 11} do_test select1-10.3 { execsql { SELECT f1-23 AS x FROM test1 ORDER BY abs(x) } } {10 -12} do_test select1-10.4 { execsql { SELECT f1-23 AS x FROM test1 ORDER BY -abs(x) } } {-12 10} do_test select1-10.5 { execsql { SELECT f1-22 AS x, f2-22 as y FROM test1 } } {-11 0 11 22} do_test select1-10.6 { execsql { SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50 } } {11 22} finish_test |
Changes to test/select3.test.
︙ | ︙ | |||
8 9 10 11 12 13 14 | # 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. # | | | 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.4 2002/01/22 03:13:43 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Build some test data # do_test select3-1.0 { |
︙ | ︙ | |||
65 66 67 68 69 70 71 72 73 74 75 76 77 78 | } {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} # Cannot have a HAVING without a GROUP BY # do_test select3-3.1 { set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg] lappend v $msg } {1 {a GROUP BY clause is required before HAVING}} | > > > > > > > > > > > > > > > | 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 | } {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 { SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY y } } {1 1 3 1 5 2 7 4 9 8 11 15} do_test select3-2.8 { execsql { SELECT log*2+1 AS x, count(*) AS y FROM t1 GROUP BY x ORDER BY 10-(x+y) } } {11 15 9 8 7 4 5 2 3 1 1 1} # Cannot have a HAVING without a GROUP BY # do_test select3-3.1 { set v [catch {execsql {SELECT log, count(*) FROM t1 HAVING log>=4}} msg] lappend v $msg } {1 {a GROUP BY clause is required before HAVING}} |
︙ | ︙ | |||
93 94 95 96 97 98 99 100 101 102 103 104 105 106 | do_test select3-4.3 { execsql { SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY max(n) } } {3 4 4 8 5 15} do_test select3-5.1 { execsql { SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2), avg(n) | > > > > > > > > | 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | do_test select3-4.3 { execsql { SELECT log, count(*) FROM t1 GROUP BY log HAVING count(*)>=4 ORDER BY max(n) } } {3 4 4 8 5 15} do_test select3-4.4 { execsql { SELECT log AS x, count(*) AS y FROM t1 GROUP BY x HAVING y>=4 ORDER BY max(n) } } {3 4 4 8 5 15} do_test select3-5.1 { execsql { SELECT log, count(*), avg(n), max(n+log*2) FROM t1 GROUP BY log ORDER BY max(n+log*2), avg(n) |
︙ | ︙ |
Changes to www/changes.tcl.
︙ | ︙ | |||
12 13 14 15 16 17 18 19 20 21 22 23 24 25 | } proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Jan 16 (2.2.3)} { <li>Fix warning messages in VC++ 7.0. (Patches from nicolas352001)</li> <li>Make the library thread-safe. (The code is there and appears to work but has not been stressed.)</li> <li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li> } | > > > > > > | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | } proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Jan ?? (2.2.4)} { <li>The label to the right of an AS in the column list of a SELECT can now be used as part of an expression in the WHERE, ORDER BY, GROUP BY, and/or HAVING clauses.</li> } chng {2002 Jan 16 (2.2.3)} { <li>Fix warning messages in VC++ 7.0. (Patches from nicolas352001)</li> <li>Make the library thread-safe. (The code is there and appears to work but has not been stressed.)</li> <li>Added the new <b>sqlite_last_insert_rowid()</b> API function.</li> } |
︙ | ︙ |
Changes to www/faq.tcl.
1 2 3 | # # Run this script to generated a faq.html output file # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this script to generated a faq.html output file # set rcsid {$Id: faq.tcl,v 1.7 2002/01/22 03:13:43 drh Exp $} puts {<html> <head> <title>SQLite Frequently Asked Questions</title> </head> <body bgcolor="white"> <h1 align="center">Frequently Asked Questions</h1> |
︙ | ︙ | |||
157 158 159 160 161 162 163 | } faq { My linux box is not able to read an SQLite database that was created on my SparcStation. } { <p>The x86 processor on your linux box is little-endian (meaning that | | | 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | } faq { My linux box is not able to read an SQLite database that was created on my SparcStation. } { <p>The x86 processor on your linux box is little-endian (meaning that the least significant byte of integers comes first) but the Sparc is big-endian (the most significant bytes comes first). SQLite databases created on a little-endian architecture cannot be used on a big-endian machine and vice versa.</p> <p>If you need to move the database from one machine to another, you'll have to do an ASCII dump of the database on the source machine and then reconstruct the database at the destination machine. The following is |
︙ | ︙ |