Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Merge recent trunk changes into the STAT4 branch. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | sqlite_stat4 |
Files: | files | file ages | folders |
SHA1: |
c69b512af276a438399747af22659415 |
User & Date: | drh 2013-08-16 12:26:33.317 |
Context
2013-08-16
| ||
13:34 | Fix a potential segfault following an OOM while running ANALYZE. (check-in: 0118797823 user: drh tags: sqlite_stat4) | |
12:26 | Merge recent trunk changes into the STAT4 branch. (check-in: c69b512af2 user: drh tags: sqlite_stat4) | |
2013-08-15
| ||
22:40 | Make sure that GROUP BY terms select input column names in preference to output column names, in compliance with the SQL standard. Ticket [1c69be2dafc28]. (check-in: f2d175f975 user: drh tags: trunk) | |
19:56 | Fix a crash that can occur if the sqlite_stat3 or sqlite_stat4 table is corrupt. (check-in: d51df8a8fc user: dan tags: sqlite_stat4) | |
Changes
Changes to src/main.c.
︙ | ︙ | |||
113 114 115 116 117 118 119 120 121 122 123 124 125 126 | ** ** * Recursive calls to this routine from thread X return immediately ** without blocking. */ int sqlite3_initialize(void){ MUTEX_LOGIC( sqlite3_mutex *pMaster; ) /* The main static mutex */ int rc; /* Result code */ #ifdef SQLITE_OMIT_WSD rc = sqlite3_wsd_init(4096, 24); if( rc!=SQLITE_OK ){ return rc; } #endif | > > > | 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | ** ** * Recursive calls to this routine from thread X return immediately ** without blocking. */ int sqlite3_initialize(void){ MUTEX_LOGIC( sqlite3_mutex *pMaster; ) /* The main static mutex */ int rc; /* Result code */ #ifdef SQLITE_EXTRA_INIT int bRunExtraInit = 0; /* Extra initialization needed */ #endif #ifdef SQLITE_OMIT_WSD rc = sqlite3_wsd_init(4096, 24); if( rc!=SQLITE_OK ){ return rc; } #endif |
︙ | ︙ | |||
210 211 212 213 214 215 216 217 218 219 220 221 222 223 | sqlite3GlobalConfig.isPCacheInit = 1; rc = sqlite3OsInit(); } if( rc==SQLITE_OK ){ sqlite3PCacheBufferSetup( sqlite3GlobalConfig.pPage, sqlite3GlobalConfig.szPage, sqlite3GlobalConfig.nPage); sqlite3GlobalConfig.isInit = 1; } sqlite3GlobalConfig.inProgress = 0; } sqlite3_mutex_leave(sqlite3GlobalConfig.pInitMutex); /* Go back under the static mutex and clean up the recursive ** mutex to prevent a resource leak. | > > > | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 | sqlite3GlobalConfig.isPCacheInit = 1; rc = sqlite3OsInit(); } if( rc==SQLITE_OK ){ sqlite3PCacheBufferSetup( sqlite3GlobalConfig.pPage, sqlite3GlobalConfig.szPage, sqlite3GlobalConfig.nPage); sqlite3GlobalConfig.isInit = 1; #ifdef SQLITE_EXTRA_INIT bRunExtraInit = 1; #endif } sqlite3GlobalConfig.inProgress = 0; } sqlite3_mutex_leave(sqlite3GlobalConfig.pInitMutex); /* Go back under the static mutex and clean up the recursive ** mutex to prevent a resource leak. |
︙ | ︙ | |||
250 251 252 253 254 255 256 | #endif #endif /* Do extra initialization steps requested by the SQLITE_EXTRA_INIT ** compile-time option. */ #ifdef SQLITE_EXTRA_INIT | | | 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 | #endif #endif /* Do extra initialization steps requested by the SQLITE_EXTRA_INIT ** compile-time option. */ #ifdef SQLITE_EXTRA_INIT if( bRunExtraInit ){ int SQLITE_EXTRA_INIT(const char*); rc = SQLITE_EXTRA_INIT(0); } #endif return rc; } |
︙ | ︙ | |||
438 439 440 441 442 443 444 | ** back to NULL pointers too. This will cause the malloc to go ** back to its default implementation when sqlite3_initialize() is ** run. */ memset(&sqlite3GlobalConfig.m, 0, sizeof(sqlite3GlobalConfig.m)); }else{ /* The heap pointer is not NULL, then install one of the | | | | | 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 | ** back to NULL pointers too. This will cause the malloc to go ** back to its default implementation when sqlite3_initialize() is ** run. */ memset(&sqlite3GlobalConfig.m, 0, sizeof(sqlite3GlobalConfig.m)); }else{ /* The heap pointer is not NULL, then install one of the ** mem5.c/mem3.c methods. The enclosing #if guarantees at ** least one of these methods is currently enabled. */ #ifdef SQLITE_ENABLE_MEMSYS3 sqlite3GlobalConfig.m = *sqlite3MemGetMemsys3(); #endif #ifdef SQLITE_ENABLE_MEMSYS5 sqlite3GlobalConfig.m = *sqlite3MemGetMemsys5(); #endif } break; } #endif case SQLITE_CONFIG_LOOKASIDE: { sqlite3GlobalConfig.szLookaside = va_arg(ap, int); sqlite3GlobalConfig.nLookaside = va_arg(ap, int); break; } /* Record a pointer to the logger function and its first argument. ** The default is NULL. Logging is disabled if the function pointer is ** NULL. */ case SQLITE_CONFIG_LOG: { /* MSVC is picky about pulling func ptrs from va lists. ** http://support.microsoft.com/kb/47961 ** sqlite3GlobalConfig.xLog = va_arg(ap, void(*)(void*,int,const char*)); |
︙ | ︙ | |||
2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 | ** method that there may be extra parameters following the file-name. */ flags |= SQLITE_OPEN_URI; for(iIn=0; iIn<nUri; iIn++) nByte += (zUri[iIn]=='&'); zFile = sqlite3_malloc(nByte); if( !zFile ) return SQLITE_NOMEM; /* Discard the scheme and authority segments of the URI. */ if( zUri[5]=='/' && zUri[6]=='/' ){ iIn = 7; while( zUri[iIn] && zUri[iIn]!='/' ) iIn++; | > > < < < > | 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 | ** method that there may be extra parameters following the file-name. */ flags |= SQLITE_OPEN_URI; for(iIn=0; iIn<nUri; iIn++) nByte += (zUri[iIn]=='&'); zFile = sqlite3_malloc(nByte); if( !zFile ) return SQLITE_NOMEM; iIn = 5; #ifndef SQLITE_ALLOW_URI_AUTHORITY /* Discard the scheme and authority segments of the URI. */ if( zUri[5]=='/' && zUri[6]=='/' ){ iIn = 7; while( zUri[iIn] && zUri[iIn]!='/' ) iIn++; if( iIn!=7 && (iIn!=16 || memcmp("localhost", &zUri[7], 9)) ){ *pzErrMsg = sqlite3_mprintf("invalid uri authority: %.*s", iIn-7, &zUri[7]); rc = SQLITE_ERROR; goto parse_uri_out; } } #endif /* Copy the filename and any query parameters into the zFile buffer. ** Decode %HH escape codes along the way. ** ** Within this loop, variable eState may be set to 0, 1 or 2, depending ** on the parsing context. As follows: ** |
︙ | ︙ |
Changes to src/mem5.c.
︙ | ︙ | |||
126 127 128 129 130 131 132 | ** of each block. One byte per block. */ u8 *aCtrl; } mem5; /* | | | | 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 | ** of each block. One byte per block. */ u8 *aCtrl; } mem5; /* ** Access the static variable through a macro for SQLITE_OMIT_WSD. */ #define mem5 GLOBAL(struct Mem5Global, mem5) /* ** Assuming mem5.zPool is divided up into an array of Mem5Link ** structures, return a pointer to the idx-th such link. */ #define MEM5LINK(idx) ((Mem5Link *)(&mem5.zPool[(idx)*mem5.szAtom])) /* ** Unlink the chunk at mem5.aPool[i] from list it is currently ** on. It should be found on mem5.aiFreelist[iLogsize]. */ |
︙ | ︙ | |||
228 229 230 231 232 233 234 | return iFirst; } /* ** Return a block of memory of at least nBytes in size. ** Return NULL if unable. Return NULL if nBytes==0. ** | | | 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 | return iFirst; } /* ** Return a block of memory of at least nBytes in size. ** Return NULL if unable. Return NULL if nBytes==0. ** ** The caller guarantees that nByte is positive. ** ** The caller has obtained a mutex prior to invoking this ** routine so there is never any chance that two or more ** threads can be in this routine at the same time. */ static void *memsys5MallocUnsafe(int nByte){ int i; /* Index of a mem5.aPool[] slot */ |
︙ | ︙ | |||
350 351 352 353 354 355 356 | } size *= 2; } memsys5Link(iBlock, iLogsize); } /* | | | 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 | } size *= 2; } memsys5Link(iBlock, iLogsize); } /* ** Allocate nBytes of memory. */ static void *memsys5Malloc(int nBytes){ sqlite3_int64 *p = 0; if( nBytes>0 ){ memsys5Enter(); p = memsys5MallocUnsafe(nBytes); memsys5Leave(); |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
51 52 53 54 55 56 57 | ** TK_AS operator. The TK_AS operator causes the expression to be ** evaluated just once and then reused for each alias. ** ** The reason for suppressing the TK_AS term when the expression is a simple ** column reference is so that the column reference will be recognized as ** usable by indices within the WHERE clause processing logic. ** | | | | > | 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 | ** TK_AS operator. The TK_AS operator causes the expression to be ** evaluated just once and then reused for each alias. ** ** The reason for suppressing the TK_AS term when the expression is a simple ** column reference is so that the column reference will be recognized as ** usable by indices within the WHERE clause processing logic. ** ** The TK_AS operator is inhibited if zType[0]=='G'. This means ** that in a GROUP BY clause, the expression is evaluated twice. Hence: ** ** SELECT random()%5 AS x, count(*) FROM tab GROUP BY x ** ** Is equivalent to: ** ** SELECT random()%5 AS x, count(*) FROM tab GROUP BY random()%5 ** ** The result of random()%5 in the GROUP BY clause is probably different ** from the result in the result-set. On the other hand Standard SQL does ** not allow the GROUP BY clause to contain references to result-set columns. ** So this should never come up in well-formed queries. ** ** If the reference is followed by a COLLATE operator, then make sure ** the COLLATE operator is preserved. For example: ** ** SELECT a+b, c+d FROM t1 ORDER BY 1 COLLATE nocase; ** ** Should be transformed into: |
︙ | ︙ | |||
392 393 394 395 396 397 398 399 400 401 | ** ** SELECT a+b AS x FROM table WHERE x<10; ** ** In cases like this, replace pExpr with a copy of the expression that ** forms the result set entry ("a+b" in the example) and return immediately. ** Note that the expression in the result set should have already been ** resolved by the time the WHERE clause is resolved. */ if( (pEList = pNC->pEList)!=0 && zTab==0 | > > > > > > | | 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 | ** ** SELECT a+b AS x FROM table WHERE x<10; ** ** In cases like this, replace pExpr with a copy of the expression that ** forms the result set entry ("a+b" in the example) and return immediately. ** Note that the expression in the result set should have already been ** resolved by the time the WHERE clause is resolved. ** ** The ability to use an output result-set column in the WHERE, GROUP BY, ** or HAVING clauses, or as part of a larger expression in the ORDRE BY ** clause is not standard SQL. This is a (goofy) SQLite extension, that ** is supported for backwards compatibility only. TO DO: Issue a warning ** on sqlite3_log() whenever the capability is used. */ if( (pEList = pNC->pEList)!=0 && zTab==0 && cnt==0 ){ for(j=0; j<pEList->nExpr; j++){ char *zAs = pEList->a[j].zName; if( zAs!=0 && sqlite3StrICmp(zAs, zCol)==0 ){ Expr *pOrig; assert( pExpr->pLeft==0 && pExpr->pRight==0 ); assert( pExpr->x.pList==0 ); |
︙ | ︙ | |||
957 958 959 960 961 962 963 | } return 0; } /* ** Check every term in the ORDER BY or GROUP BY clause pOrderBy of ** the SELECT statement pSelect. If any term is reference to a | | | 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 | } return 0; } /* ** Check every term in the ORDER BY or GROUP BY clause pOrderBy of ** the SELECT statement pSelect. If any term is reference to a ** result set expression (as determined by the ExprList.a.iOrderByCol field) ** then convert that term into a copy of the corresponding result set ** column. ** ** If any errors are detected, add an error message to pParse and ** return non-zero. Return zero if no errors are seen. */ int sqlite3ResolveOrderGroupBy( |
︙ | ︙ | |||
1005 1006 1007 1008 1009 1010 1011 | ** The Name context of the SELECT statement is pNC. zType is either ** "ORDER" or "GROUP" depending on which type of clause pOrderBy is. ** ** This routine resolves each term of the clause into an expression. ** If the order-by term is an integer I between 1 and N (where N is the ** number of columns in the result set of the SELECT) then the expression ** in the resolution is a copy of the I-th result-set expression. If | | | 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 | ** The Name context of the SELECT statement is pNC. zType is either ** "ORDER" or "GROUP" depending on which type of clause pOrderBy is. ** ** This routine resolves each term of the clause into an expression. ** If the order-by term is an integer I between 1 and N (where N is the ** number of columns in the result set of the SELECT) then the expression ** in the resolution is a copy of the I-th result-set expression. If ** the order-by term is an identifier that corresponds to the AS-name of ** a result-set expression, then the term resolves to a copy of the ** result-set expression. Otherwise, the expression is resolved in ** the usual way - using sqlite3ResolveExprNames(). ** ** This routine returns the number of errors. If errors occur, then ** an appropriate error message might be left in pParse. (OOM errors ** excepted.) |
︙ | ︙ | |||
1031 1032 1033 1034 1035 1036 1037 | int nResult; /* Number of terms in the result set */ if( pOrderBy==0 ) return 0; nResult = pSelect->pEList->nExpr; pParse = pNC->pParse; for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ Expr *pE = pItem->pExpr; | > > | | | | | | | | | > | | 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 | int nResult; /* Number of terms in the result set */ if( pOrderBy==0 ) return 0; nResult = pSelect->pEList->nExpr; pParse = pNC->pParse; for(i=0, pItem=pOrderBy->a; i<pOrderBy->nExpr; i++, pItem++){ Expr *pE = pItem->pExpr; Expr *pE2 = sqlite3ExprSkipCollate(pE); if( zType[0]!='G' ){ iCol = resolveAsName(pParse, pSelect->pEList, pE2); if( iCol>0 ){ /* If an AS-name match is found, mark this ORDER BY column as being ** a copy of the iCol-th result-set column. The subsequent call to ** sqlite3ResolveOrderGroupBy() will convert the expression to a ** copy of the iCol-th result-set expression. */ pItem->iOrderByCol = (u16)iCol; continue; } } if( sqlite3ExprIsInteger(pE2, &iCol) ){ /* The ORDER BY term is an integer constant. Again, set the column ** number so that sqlite3ResolveOrderGroupBy() will convert the ** order-by term to a copy of the result-set expression */ if( iCol<1 || iCol>0xffff ){ resolveOutOfRangeError(pParse, zType, i+1, nResult); return 1; } |
︙ | ︙ | |||
1183 1184 1185 1186 1187 1188 1189 | /* If a HAVING clause is present, then there must be a GROUP BY clause. */ if( p->pHaving && !pGroupBy ){ sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING"); return WRC_Abort; } | | < < | 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 | /* If a HAVING clause is present, then there must be a GROUP BY clause. */ if( p->pHaving && !pGroupBy ){ sqlite3ErrorMsg(pParse, "a GROUP BY clause is required before HAVING"); return WRC_Abort; } /* Add the output column list to the name-context before parsing the ** other expressions in the SELECT statement. This is so that ** expressions in the WHERE clause (etc.) can refer to expressions by ** aliases in the result set. ** ** Minor point: If this is the case, then the expression will be ** re-evaluated for each reference to it. */ sNC.pEList = p->pEList; if( sqlite3ResolveExprNames(&sNC, p->pHaving) ) return WRC_Abort; if( sqlite3ResolveExprNames(&sNC, p->pWhere) ) return WRC_Abort; /* The ORDER BY and GROUP BY clauses may not refer to terms in ** outer queries */ sNC.pNext = 0; sNC.ncFlags |= NC_AllowAgg; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1998 1999 2000 2001 2002 2003 2004 | ** NameContext in the parent query. Thus the process of scanning the ** NameContext list corresponds to searching through successively outer ** subqueries looking for a match. */ struct NameContext { Parse *pParse; /* The parser */ SrcList *pSrcList; /* One or more tables used to resolve names */ | | < < | | 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 | ** NameContext in the parent query. Thus the process of scanning the ** NameContext list corresponds to searching through successively outer ** subqueries looking for a match. */ struct NameContext { Parse *pParse; /* The parser */ SrcList *pSrcList; /* One or more tables used to resolve names */ ExprList *pEList; /* Optional list of result-set columns */ AggInfo *pAggInfo; /* Information about aggregates at this level */ NameContext *pNext; /* Next outer name context. NULL for outermost */ int nRef; /* Number of names resolved by this context */ int nErr; /* Number of errors encountered while resolving names */ u8 ncFlags; /* Zero or more NC_* flags defined below */ }; /* ** Allowed values for the NameContext, ncFlags field. */ #define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */ #define NC_HasAgg 0x02 /* One or more aggregate functions seen */ #define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */ #define NC_PartIdx 0x10 /* True if resolving a partial index WHERE */ /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** ** nLimit is set to -1 if there is no LIMIT clause. nOffset is set to 0. ** If there is a LIMIT clause, the parser sets nLimit to the value of the |
︙ | ︙ |
Changes to src/tclsqlite.c.
︙ | ︙ | |||
37 38 39 40 41 42 43 44 45 46 47 48 49 50 | # include <stdlib.h> # include <string.h> # include <assert.h> typedef unsigned char u8; #endif #include <ctype.h> /* * Windows needs to know which symbols to export. Unix does not. * BUILD_sqlite should be undefined for Unix. */ #ifdef BUILD_sqlite #undef TCL_STORAGE_CLASS #define TCL_STORAGE_CLASS DLLEXPORT | > > > > > > > > > > > > | 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 | # include <stdlib.h> # include <string.h> # include <assert.h> typedef unsigned char u8; #endif #include <ctype.h> /* Used to get the current process ID */ #if !defined(_WIN32) # include <unistd.h> # define GETPID getpid #elif !defined(_WIN32_WCE) # ifndef SQLITE_AMALGAMATION # define WIN32_LEAN_AND_MEAN # include <windows.h> # endif # define GETPID (int)GetCurrentProcessId #endif /* * Windows needs to know which symbols to export. Unix does not. * BUILD_sqlite should be undefined for Unix. */ #ifdef BUILD_sqlite #undef TCL_STORAGE_CLASS #define TCL_STORAGE_CLASS DLLEXPORT |
︙ | ︙ | |||
3742 3743 3744 3745 3746 3747 3748 | } #endif } #define TCLSH_MAIN main /* Needed to fake out mktclapp */ int TCLSH_MAIN(int argc, char **argv){ Tcl_Interp *interp; | | > > > > > > > > > | 3754 3755 3756 3757 3758 3759 3760 3761 3762 3763 3764 3765 3766 3767 3768 3769 3770 3771 3772 3773 3774 3775 3776 3777 | } #endif } #define TCLSH_MAIN main /* Needed to fake out mktclapp */ int TCLSH_MAIN(int argc, char **argv){ Tcl_Interp *interp; #if !defined(_WIN32_WCE) if( getenv("BREAK") ){ fprintf(stderr, "attach debugger to process %d and press any key to continue.\n", GETPID()); fgetc(stdin); } #endif /* Call sqlite3_shutdown() once before doing anything else. This is to ** test that sqlite3_shutdown() can be safely called by a process before ** sqlite3_initialize() is. */ sqlite3_shutdown(); Tcl_FindExecutable(argv[0]); interp = Tcl_CreateInterp(); |
︙ | ︙ |
Changes to test/resolver01.test.
︙ | ︙ | |||
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | # #*********************************************************************** # # This file tests features of the name resolver (the component that # figures out what identifiers in the SQL statement refer to) that # were fixed by ticket [2500cdb9be] # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test resolver01-1.1 { catchsql { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22); CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44); SELECT 1 AS y FROM t1, t2 ORDER BY y; } } {0 1} do_test resolver01-1.2 { catchsql { SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase; } } {0 2} | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 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 | # #*********************************************************************** # # This file tests features of the name resolver (the component that # figures out what identifiers in the SQL statement refer to) that # were fixed by ticket [2500cdb9be] # # See also tickets [1c69be2daf] and [f617ea3125] from 2013-08-14. # set testdir [file dirname $argv0] source $testdir/tester.tcl # "ORDER BY y" binds to the output result-set column named "y" # if available. If no output column is named "y", then try to # bind against an input column named "y". # # This is classical SQL92 behavior. # do_test resolver01-1.1 { catchsql { CREATE TABLE t1(x, y); INSERT INTO t1 VALUES(11,22); CREATE TABLE t2(y, z); INSERT INTO t2 VALUES(33,44); SELECT 1 AS y FROM t1, t2 ORDER BY y; } } {0 1} do_test resolver01-1.2 { catchsql { SELECT 1 AS yy FROM t1, t2 ORDER BY y; } } {1 {ambiguous column name: y}} do_test resolver01-1.3 { catchsql { CREATE TABLE t3(x,y); INSERT INTO t3 VALUES(11,44),(33,22); SELECT x AS y FROM t3 ORDER BY y; } } {0 {11 33}} do_test resolver01-1.4 { catchsql { SELECT x AS yy FROM t3 ORDER BY y; } } {0 {33 11}} # SQLite allows the WHERE clause to reference output columns if there is # no other way to resolve the name. # do_test resolver01-1.5 { catchsql { SELECT x AS yy FROM t3 ORDER BY yy; } } {0 {11 33}} do_test resolver01-1.6 { catchsql { SELECT x AS yy FROM t3 ORDER BY 1; } } {0 {11 33}} # The "ORDER BY y COLLATE nocase" form works the same as "ORDER BY y". # The "y" binds more tightly to output columns than to input columns. # # This is for compatibility with SQL92 and with historical SQLite behavior. # Note that PostgreSQL considers "y COLLATE nocase" to be an expression # and thus PostgreSQL treats this case as if it where the 3.x case below. # do_test resolver01-2.1 { catchsql { SELECT 2 AS y FROM t1, t2 ORDER BY y COLLATE nocase; } } {0 2} do_test resolver01-2.2 { catchsql { SELECT 2 AS yy FROM t1, t2 ORDER BY y COLLATE nocase; } } {1 {ambiguous column name: y}} do_test resolver01-2.3 { catchsql { SELECT x AS y FROM t3 ORDER BY y COLLATE nocase; } } {0 {11 33}} do_test resolver01-2.4 { catchsql { SELECT x AS yy FROM t3 ORDER BY y COLLATE nocase; } } {0 {33 11}} do_test resolver01-2.5 { catchsql { SELECT x AS yy FROM t3 ORDER BY yy COLLATE nocase; } } {0 {11 33}} do_test resolver01-2.6 { catchsql { SELECT x AS yy FROM t3 ORDER BY 1 COLLATE nocase; } } {0 {11 33}} # But if the form is "ORDER BY expr" then bind more tightly to the # the input column names and only use the output column names if no # input column name matches. # # This is SQL99 behavior, as implemented by PostgreSQL and MS-SQL. # Note that Oracle works differently. # do_test resolver01-3.1 { catchsql { SELECT 3 AS y FROM t1, t2 ORDER BY +y; } } {1 {ambiguous column name: y}} do_test resolver01-3.2 { catchsql { SELECT 2 AS yy FROM t1, t2 ORDER BY +y; } } {1 {ambiguous column name: y}} do_test resolver01-3.3 { catchsql { SELECT x AS y FROM t3 ORDER BY +y; } } {0 {33 11}} do_test resolver01-3.4 { catchsql { SELECT x AS yy FROM t3 ORDER BY +y; } } {0 {33 11}} do_test resolver01-3.5 { catchsql { SELECT x AS yy FROM t3 ORDER BY +yy } } {0 {11 33}} # This is the test case given in ticket [f617ea3125e9] (with table name # changed from "t1" to "t4". The behavior of (1) and (3) match with # PostgreSQL, but we intentionally break with PostgreSQL to provide # SQL92 behavior for case (2). # do_execsql_test resolver01-4.1 { CREATE TABLE t4(m CHAR(2)); INSERT INTO t4 VALUES('az'); INSERT INTO t4 VALUES('by'); INSERT INTO t4 VALUES('cx'); SELECT '1', substr(m,2) AS m FROM t4 ORDER BY m; SELECT '2', substr(m,2) AS m FROM t4 ORDER BY m COLLATE binary; SELECT '3', substr(m,2) AS m FROM t4 ORDER BY lower(m); } {1 x 1 y 1 z 2 x 2 y 2 z 3 z 3 y 3 x} ########################################################################## # Test cases for ticket [1c69be2dafc28]: Make sure the GROUP BY binds # more tightly to the input tables in all cases. # # This first case case has been wrong in SQLite for time out of mind. # For SQLite version 3.7.17 the answer was two rows, which is wrong. # do_execsql_test resolver01-5.1 { CREATE TABLE t5(m CHAR(2)); INSERT INTO t5 VALUES('ax'); INSERT INTO t5 VALUES('bx'); INSERT INTO t5 VALUES('cy'); SELECT count(*), substr(m,2,1) AS m FROM t5 GROUP BY m ORDER BY 1, 2; } {1 x 1 x 1 y} # This case is unambiguous and has always been correct. # do_execsql_test resolver01-5.2 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY m ORDER BY 1, 2; } {1 x 1 x 1 y} # This case is not allowed in standard SQL, but SQLite allows and does # the sensible thing. # do_execsql_test resolver01-5.3 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY mx ORDER BY 1, 2; } {1 y 2 x} do_execsql_test resolver01-5.4 { SELECT count(*), substr(m,2,1) AS mx FROM t5 GROUP BY substr(m,2,1) ORDER BY 1, 2; } {1 y 2 x} # These test case weere provided in the 2013-08-14 email from Rob Golsteijn # that originally reported the problem of ticket [1c69be2dafc28]. # do_execsql_test resolver01-6.1 { CREATE TABLE t61(name); SELECT min(name) FROM t61 GROUP BY lower(name); } {} do_execsql_test resolver01-6.2 { SELECT min(name) AS name FROM t61 GROUP BY lower(name); } {} do_execsql_test resolver01-6.3 { CREATE TABLE t63(name); INSERT INTO t63 VALUES (NULL); INSERT INTO t63 VALUES ('abc'); SELECT count(), NULLIF(name,'abc') AS name FROM t63 GROUP BY lower(name); } {1 {} 1 {}} finish_test |
Changes to test/tkt2822.test.
︙ | ︙ | |||
204 205 206 207 208 209 210 | execsql { SELECT a AS "b" FROM t3 ORDER BY [B]; } } {1 9} # In "ORDER BY +b" the term is now an expression rather than # a label. It therefore matches by rule (3) instead of rule (2). | < < < | | 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 | execsql { SELECT a AS "b" FROM t3 ORDER BY [B]; } } {1 9} # In "ORDER BY +b" the term is now an expression rather than # a label. It therefore matches by rule (3) instead of rule (2). # do_test tkt2822-5.5 { execsql { SELECT a AS b FROM t3 ORDER BY +b; } } {9 1} # Tests for rule 2 in compound queries # do_test tkt2822-6.1 { execsql { CREATE TABLE t6a(p,q); INSERT INTO t6a VALUES(1,8); |
︙ | ︙ |