Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved handling of USING and NATURAL JOIN in 3-way and higher joins. Ticket [3338b3fa19ac4ab] |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
551ce407bd77149865423511bd52eba2 |
User & Date: | drh 2011-10-18 18:10:40.151 |
Context
2011-10-18
| ||
19:14 | Fix an uninitialized variable in OR-clause processing. (check-in: 54aecd9298 user: drh tags: trunk) | |
18:10 | Improved handling of USING and NATURAL JOIN in 3-way and higher joins. Ticket [3338b3fa19ac4ab] (check-in: 551ce407bd user: drh tags: trunk) | |
12:44 | Fix a floating-point exception that can occur when an FTS4 query contains a large number of tokens connected by AND or NEAR operators. (check-in: 3126754c72 user: dan tags: trunk) | |
Changes
Changes to src/resolve.c.
︙ | ︙ | |||
93 94 95 96 97 98 99 100 101 102 103 104 105 106 | ** allowing it to be repopulated by the memcpy() on the following line. */ ExprSetProperty(pExpr, EP_Static); sqlite3ExprDelete(db, pExpr); memcpy(pExpr, pDup, sizeof(*pExpr)); sqlite3DbFree(db, pDup); } /* ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up ** that name in the set of source tables in pSrcList and make the pExpr ** expression node refer back to that source column. The following changes ** are made to pExpr: ** | > > > > > > > > > > > > > > > > > > | 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 | ** allowing it to be repopulated by the memcpy() on the following line. */ ExprSetProperty(pExpr, EP_Static); sqlite3ExprDelete(db, pExpr); memcpy(pExpr, pDup, sizeof(*pExpr)); sqlite3DbFree(db, pDup); } /* ** Return TRUE if the name zCol occurs anywhere in the USING clause. ** ** Return FALSE if the USING clause is NULL or if it does not contain ** zCol. */ static int nameInUsingClause(IdList *pUsing, const char *zCol){ if( pUsing ){ int k; for(k=0; k<pUsing->nId; k++){ if( sqlite3StrICmp(pUsing->a[k].zName, zCol)==0 ) return 1; } } return 0; } /* ** Given the name of a column of the form X.Y.Z or Y.Z or just Z, look up ** that name in the set of source tables in pSrcList and make the pExpr ** expression node refer back to that source column. The following changes ** are made to pExpr: ** |
︙ | ︙ | |||
185 186 187 188 189 190 191 | pExpr->iTable = pItem->iCursor; pExpr->pTab = pTab; pSchema = pTab->pSchema; pMatch = pItem; } for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ | > > > > > > | > < < < < < < < < < < < < < < < < < < < < | 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | pExpr->iTable = pItem->iCursor; pExpr->pTab = pTab; pSchema = pTab->pSchema; pMatch = pItem; } for(j=0, pCol=pTab->aCol; j<pTab->nCol; j++, pCol++){ if( sqlite3StrICmp(pCol->zName, zCol)==0 ){ /* If there has been exactly one prior match and this match ** is for the right-hand table of a NATURAL JOIN or is in a ** USING clause, then skip this match. */ if( cnt==1 ){ if( pItem->jointype & JT_NATURAL ) continue; if( nameInUsingClause(pItem->pUsing, zCol) ) continue; } cnt++; pExpr->iTable = pItem->iCursor; pExpr->pTab = pTab; pMatch = pItem; pSchema = pTab->pSchema; /* Substitute the rowid (column -1) for the INTEGER PRIMARY KEY */ pExpr->iColumn = j==pTab->iPKey ? -1 : (i16)j; break; } } } } #ifndef SQLITE_OMIT_TRIGGER |
︙ | ︙ |
Changes to test/where3.test.
︙ | ︙ | |||
337 338 339 340 341 342 343 344 345 | AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 337 338 339 340 341 342 343 344 345 346 347 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 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 | AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } # Name resolution with NATURAL JOIN and USING # do_test where3-6.setup { db eval { CREATE TABLE t6w(a, w); INSERT INTO t6w VALUES(1, 'w-one'); INSERT INTO t6w VALUES(2, 'w-two'); INSERT INTO t6w VALUES(9, 'w-nine'); CREATE TABLE t6x(a, x); INSERT INTO t6x VALUES(1, 'x-one'); INSERT INTO t6x VALUES(3, 'x-three'); INSERT INTO t6x VALUES(9, 'x-nine'); CREATE TABLE t6y(a, y); INSERT INTO t6y VALUES(1, 'y-one'); INSERT INTO t6y VALUES(4, 'y-four'); INSERT INTO t6y VALUES(9, 'y-nine'); CREATE TABLE t6z(a, z); INSERT INTO t6z VALUES(1, 'z-one'); INSERT INTO t6z VALUES(5, 'z-five'); INSERT INTO t6z VALUES(9, 'z-nine'); } } {} set cnt 0 foreach predicate { {} {ORDER BY a} {ORDER BY t6w.a} {WHERE a>0} {WHERE t6y.a>0} {WHERE a>0 ORDER BY a} } { incr cnt do_test where3-6.$cnt.1 { set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y" append sql " NATURAL JOIN t6z " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.2 { set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)" append sql " JOIN t6z USING(a) " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.3 { set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)" append sql " JOIN t6z USING(a) " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.4 { set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y" append sql " JOIN t6z USING(a) " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.5 { set sql "SELECT * FROM t6w JOIN t6x USING(a) JOIN t6y USING(a)" append sql " NATURAL JOIN t6z " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.6 { set sql "SELECT * FROM t6w JOIN t6x USING(a) NATURAL JOIN t6y" append sql " NATURAL JOIN t6z " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.7 { set sql "SELECT * FROM t6w NATURAL JOIN t6x JOIN t6y USING(a)" append sql " NATURAL JOIN t6z " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} do_test where3-6.$cnt.8 { set sql "SELECT * FROM t6w NATURAL JOIN t6x NATURAL JOIN t6y" append sql " JOIN t6z USING(a) " append sql $::predicate db eval $sql } {1 w-one x-one y-one z-one 9 w-nine x-nine y-nine z-nine} } finish_test |