Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch tkt-6709574 Excluding Merge-Ins
This is equivalent to a diff from 0ba67b64de to fc3630cdef
2013-05-08
| ||
16:57 | Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Fix for ticket [6709574d2a8d8]. (check-in: ce853a7506 user: drh tags: trunk) | |
14:20 | Fix the wholenumber virtual table so that it returns higher costs for unconstrained usage. Cherrypick from [ceff8955020cd13]. (check-in: 7227b61512 user: drh tags: trunk) | |
2013-05-07
| ||
17:49 | Make sure the ORDER BY collating sequences are compatible with the comparison collations before using the merge algorithm for compound SELECT statements. Candidate fix for ticket [6709574d2a8d8]. (Closed-Leaf check-in: fc3630cdef user: drh tags: tkt-6709574) | |
12:16 | Modify the fts3tokenize table implementation so that it does not use the SQL function fts3_tokenizer. The user may have installed an authorizer callback that prohibits this. (check-in: 0ba67b64de user: dan tags: trunk) | |
2013-05-06
| ||
21:01 | In the command-line shell, output the elements of the schema in the order that they appear in the sqlite_master table. This is necessary in some cases to make the schema parsable. (check-in: e5b3cd747b user: drh tags: trunk) | |
Changes to src/select.c.
︙ | ︙ | |||
3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 | pParse->checkSchema = 1; return SQLITE_ERROR; } pFrom->pIndex = pIdx; } return SQLITE_OK; } /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 3266 3267 3268 3269 3270 3271 3272 3273 3274 3275 3276 3277 3278 3279 3280 3281 3282 3283 3284 3285 3286 3287 3288 3289 3290 3291 3292 3293 3294 3295 3296 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 3312 3313 3314 3315 3316 3317 3318 3319 3320 3321 3322 3323 3324 3325 3326 3327 3328 3329 3330 3331 3332 | pParse->checkSchema = 1; return SQLITE_ERROR; } pFrom->pIndex = pIdx; } return SQLITE_OK; } /* ** Detect compound SELECT statements that use an ORDER BY clause with ** an alternative collating sequence. ** ** SELECT ... FROM t1 EXCEPT SELECT ... FROM t2 ORDER BY .. COLLATE ... ** ** These are rewritten as a subquery: ** ** SELECT * FROM (SELECT ... FROM t1 EXCEPT SELECT ... FROM t2) ** ORDER BY ... COLLATE ... ** ** This transformation is necessary because the multiSelectOrderBy() routine ** above that generates the code for a compound SELECT with an ORDER BY clause ** uses a merge algorithm that requires the same collating sequence on the ** result columns as on the ORDER BY clause. See ticket ** http://www.sqlite.org/src/info/6709574d2a ** ** This transformation is only needed for EXCEPT, INTERSECT, and UNION. ** The UNION ALL operator works fine with multiSelectOrderBy() even when ** there are COLLATE terms in the ORDER BY. */ static int convertCompoundSelectToSubquery(Walker *pWalker, Select *p){ int i; Select *pNew; Select *pX; sqlite3 *db; struct ExprList_item *a; SrcList *pNewSrc; Parse *pParse; Token dummy; if( p->pPrior==0 ) return WRC_Continue; if( p->pOrderBy==0 ) return WRC_Continue; for(pX=p; pX && (pX->op==TK_ALL || pX->op==TK_SELECT); pX=pX->pPrior){} if( pX==0 ) return WRC_Continue; a = p->pOrderBy->a; for(i=p->pOrderBy->nExpr-1; i>=0; i--){ if( a[i].pExpr->flags & EP_Collate ) break; } if( i<0 ) return WRC_Continue; /* If we reach this point, that means the transformation is required. */ pParse = pWalker->pParse; db = pParse->db; pNew = sqlite3DbMallocZero(db, sizeof(*pNew) ); if( pNew==0 ) return WRC_Abort; memset(&dummy, 0, sizeof(dummy)); pNewSrc = sqlite3SrcListAppendFromTerm(pParse,0,0,0,&dummy,pNew,0,0); if( pNewSrc==0 ) return WRC_Abort; *pNew = *p; p->pSrc = pNewSrc; p->pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ALL, 0)); p->op = TK_SELECT; p->pWhere = 0; pNew->pGroupBy = 0; pNew->pHaving = 0; pNew->pOrderBy = 0; p->pPrior = 0; pNew->pLimit = 0; pNew->pOffset = 0; return WRC_Continue; } /* ** This routine is a Walker callback for "expanding" a SELECT statement. ** "Expanding" means to do the following: ** ** (1) Make sure VDBE cursor numbers have been assigned to every ** element of the FROM clause. |
︙ | ︙ | |||
3573 3574 3575 3576 3577 3578 3579 | ** If anything goes wrong, an error message is written into pParse. ** The calling function can detect the problem by looking at pParse->nErr ** and/or pParse->db->mallocFailed. */ static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){ Walker w; memset(&w, 0, sizeof(w)); | | > > | 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 3646 3647 3648 3649 3650 3651 3652 3653 3654 | ** If anything goes wrong, an error message is written into pParse. ** The calling function can detect the problem by looking at pParse->nErr ** and/or pParse->db->mallocFailed. */ static void sqlite3SelectExpand(Parse *pParse, Select *pSelect){ Walker w; memset(&w, 0, sizeof(w)); w.xSelectCallback = convertCompoundSelectToSubquery; w.xExprCallback = exprWalkNoop; w.pParse = pParse; sqlite3WalkSelect(&w, pSelect); w.xSelectCallback = selectExpander; sqlite3WalkSelect(&w, pSelect); } #ifndef SQLITE_OMIT_SUBQUERY /* ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo() |
︙ | ︙ |
Changes to test/selectA.test.
︙ | ︙ | |||
277 278 279 280 281 282 283 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-2.35 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 | | | | 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-2.35 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY y COLLATE NOCASE,x,z } } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} do_test selectA-2.36 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY y COLLATE NOCASE DESC,x,z } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-2.37 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c,b,a } |
︙ | ︙ | |||
307 308 309 310 311 312 313 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-2.40 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 | | | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-2.40 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY z COLLATE BINARY DESC,x,y } } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} do_test selectA-2.41 { execsql { SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY a,b,c } |
︙ | ︙ | |||
598 599 600 601 602 603 604 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-2.86 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 | | | 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-2.86 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY y COLLATE NOCASE,x,z } } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} do_test selectA-2.87 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY y COLLATE NOCASE DESC,x,z } |
︙ | ︙ | |||
628 629 630 631 632 633 634 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-2.91 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 | | | 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-2.91 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY z COLLATE BINARY DESC,x,y } } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} do_test selectA-2.92 { execsql { SELECT x,y,z FROM t2 INTERSECT SELECT a,b,c FROM t3 EXCEPT SELECT c,b,a FROM t1 |
︙ | ︙ | |||
889 890 891 892 893 894 895 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-3.35 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 | | | | 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-3.35 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY y COLLATE NOCASE,x,z } } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} do_test selectA-3.36 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY y COLLATE NOCASE DESC,x,z } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-3.37 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c,b,a } |
︙ | ︙ | |||
919 920 921 922 923 924 925 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-3.40 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 | | | 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-3.40 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t1 ORDER BY z COLLATE BINARY DESC,x,y } } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} do_test selectA-3.41 { execsql { SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d' ORDER BY a,b,c } |
︙ | ︙ | |||
1210 1211 1212 1213 1214 1215 1216 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-3.86 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 | | | 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY b,a,c } } {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M} do_test selectA-3.86 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY y COLLATE NOCASE,x,z } } {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z} do_test selectA-3.87 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY y COLLATE NOCASE DESC,x,z } |
︙ | ︙ | |||
1240 1241 1242 1243 1244 1245 1246 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-3.91 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 | | | 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 | SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY c DESC,a,b } } {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a} do_test selectA-3.91 { execsql { SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3 ORDER BY z COLLATE BINARY DESC,x,y } } {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B} do_test selectA-3.92 { execsql { SELECT x,y,z FROM t2 INTERSECT SELECT a,b,c FROM t3 EXCEPT SELECT c,b,a FROM t1 |
︙ | ︙ |
Added test/selectE.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | # 2013-05-07 # # 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 compound SELECT statements # that have ORDER BY clauses with collating sequences that differ # from the collating sequence used for comparison in the compound. # # Ticket 6709574d2a8d8b9be3a9cb1afbf4ff2de48ea4e7: # drh added on 2013-05-06 15:21:16: # # In the code shown below (which is intended to be run from the # sqlite3.exe command-line tool) the three SELECT statements should all # generate the same answer. But the third one does not. It is as if the # COLLATE clause on the ORDER BY somehow got pulled into the EXCEPT # operator. Note that the ".print" commands are instructions to the # sqlite3.exe shell program to output delimiter lines so that you can more # easily tell where the output of one query ends and the next query # begins. # # CREATE TABLE t1(a); # INSERT INTO t1 VALUES('abc'),('def'); # CREATE TABLE t2(a); # INSERT INTO t2 VALUES('DEF'); # # SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a; # .print ----- # SELECT a FROM (SELECT a FROM t1 EXCEPT SELECT a FROM t2) # ORDER BY a COLLATE nocase; # .print ----- # SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; # # Bisecting shows that this problem was introduced in SQLite version 3.6.0 # by check-in [8bbfa97837a74ef] on 2008-06-15. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test selectE-1.0 { db eval { CREATE TABLE t1(a); INSERT INTO t1 VALUES('abc'),('def'),('ghi'); CREATE TABLE t2(a); INSERT INTO t2 VALUES('DEF'),('abc'); CREATE TABLE t3(a); INSERT INTO t3 VALUES('def'),('jkl'); SELECT a FROM t1 EXCEPT SELECT a FROM t2 ORDER BY a COLLATE nocase; } } {def ghi} do_test selectE-1.1 { db eval { SELECT a FROM t2 EXCEPT SELECT a FROM t3 ORDER BY a COLLATE nocase; } } {abc DEF} do_test selectE-1.2 { db eval { SELECT a FROM t2 EXCEPT SELECT a FROM t3 ORDER BY a COLLATE binary; } } {DEF abc} do_test selectE-1.3 { db eval { SELECT a FROM t2 EXCEPT SELECT a FROM t3 ORDER BY a; } } {DEF abc} do_test selectE-2.1 { db eval { DELETE FROM t2; DELETE FROM t3; INSERT INTO t2 VALUES('ABC'),('def'),('GHI'),('jkl'); INSERT INTO t3 SELECT lower(a) FROM t2; SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3 ORDER BY 1 } } {} do_test selectE-2.2 { db eval { SELECT a COLLATE nocase FROM t2 EXCEPT SELECT a FROM t3 ORDER BY 1 COLLATE binary } } {} finish_test |