Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure left joins still work even when the OR clause optimization fires. Ticket #1537. (CVS 2788) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
cbbeb9de0019a0b81318158711590078 |
User & Date: | drh 2005-11-26 14:08:08.000 |
Context
2005-11-26
| ||
14:24 | Disable the OR-clause optimization if it does not result in an index being used that would not have been used otherwise. In other words, do not convert OR clauses into an IN statement if it does not help the optimizer. (CVS 2789) (check-in: 7e7cfce0f8 user: drh tags: trunk) | |
14:08 | Make sure left joins still work even when the OR clause optimization fires. Ticket #1537. (CVS 2788) (check-in: cbbeb9de00 user: drh tags: trunk) | |
03:51 | Remove some vestiges of the old OS_TEST driver. (CVS 2787) (check-in: 008f676f20 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This module contains C code that generates VDBE code used to process ** the WHERE clause of SQL statements. This module is reponsible for ** generating the code that loops through a table looking for applicable ** rows. Indices are selected and used to speed the search when doing ** so is applicable. Because this module is responsible for selecting ** indices, you might also think of this module as the "query optimizer". ** ** $Id: where.c,v 1.184 2005/11/26 14:08:08 drh Exp $ */ #include "sqliteInt.h" /* ** The number of bits in a Bitmask. "BMS" means "BitMask Size". */ #define BMS (sizeof(Bitmask)*8) |
︙ | ︙ | |||
527 528 529 530 531 532 533 534 535 536 537 538 539 540 | return 0; } *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0; *pnPattern = cnt; return 1; } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ /* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm ** structure. ** | > > > > > > > > > > | 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 | return 0; } *pisComplete = z[cnt]==wc[0] && z[cnt+1]==0; *pnPattern = cnt; return 1; } #endif /* SQLITE_OMIT_LIKE_OPTIMIZATION */ /* ** If the pBase expression originated in the ON or USING clause of ** a join, then transfer the appropriate markings over to derived. */ static void transferJoinMarkings(Expr *pDerived, Expr *pBase){ pDerived->flags |= pBase->flags & EP_FromJoin; pDerived->iRightJoinTable = pBase->iRightJoinTable; } /* ** The input to this routine is an WhereTerm structure with only the ** "pExpr" field filled in. The job of this routine is to analyze the ** subexpression and populate all the other fields of the WhereTerm ** structure. ** |
︙ | ︙ | |||
686 687 688 689 690 691 692 693 694 695 696 697 698 699 | pDup = sqlite3Expr(TK_COLUMN, 0, 0, 0); if( pDup ){ pDup->iTable = iCursor; pDup->iColumn = iColumn; } pNew = sqlite3Expr(TK_IN, pDup, 0, 0); if( pNew ){ pNew->pList = pList; }else{ sqlite3ExprListDelete(pList); } pTerm->pExpr = pNew; pTerm->flags |= TERM_DYNAMIC; exprAnalyze(pSrc, pMaskSet, pWC, idxTerm); | > | 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 | pDup = sqlite3Expr(TK_COLUMN, 0, 0, 0); if( pDup ){ pDup->iTable = iCursor; pDup->iColumn = iColumn; } pNew = sqlite3Expr(TK_IN, pDup, 0, 0); if( pNew ){ transferJoinMarkings(pNew, pExpr); pNew->pList = pList; }else{ sqlite3ExprListDelete(pList); } pTerm->pExpr = pNew; pTerm->flags |= TERM_DYNAMIC; exprAnalyze(pSrc, pMaskSet, pWC, idxTerm); |
︙ | ︙ |
Added test/tkt1537.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 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | # 2005 November 26 # # 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. # # This file implements tests to verify that ticket #1537 is # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl do_test tkt1537-1.1 { execsql { CREATE TABLE t1(id, a1, a2); INSERT INTO t1 VALUES(1, NULL, NULL); INSERT INTO t1 VALUES(2, 1, 3); CREATE TABLE t2(id, b); INSERT INTO t2 VALUES(3, 1); INSERT INTO t2 VALUES(4, NULL); SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=+b; } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-1.2 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b; } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-1.3 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b; } } {3 1 2 1 3 4 {} {} {} {}} do_test tkt1537-1.4 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2); } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-1.5 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1); } } {3 1 2 1 3 4 {} {} {} {}} do_test tkt1537-1.6 { execsql { CREATE INDEX t1a1 ON t1(a1); CREATE INDEX t1a2 ON t1(a2); CREATE INDEX t2b ON t2(b); SELECT * FROM t1 LEFT JOIN t2 ON a1=b OR a2=b; } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-1.7 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON a1=b OR a2=b; } } {3 1 2 1 3 4 {} {} {} {}} do_test tkt1537-1.8 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON b IN (a1,a2); } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-1.9 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON b IN (a2,a1); } } {3 1 2 1 3 4 {} {} {} {}} execsql { DROP INDEX t1a1; DROP INDEX t1a2; DROP INDEX t2b; } do_test tkt1537-2.1 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2; } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-2.2 { execsql { CREATE INDEX t2b ON t2(b); SELECT * FROM t1 LEFT JOIN t2 ON b BETWEEN a1 AND a2; } } {1 {} {} {} {} 2 1 3 3 1} do_test tkt1537-2.3 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2; } } {3 1 2 1 3 4 {} {} {} {}} do_test tkt1537-2.4 { execsql { CREATE INDEX t1a1 ON t1(a1); CREATE INDEX t1a2 ON t1(a2); SELECT * FROM t2 LEFT JOIN t1 ON b BETWEEN a1 AND a2; } } {3 1 2 1 3 4 {} {} {} {}} do_test tkt1537-3.1 { execsql { SELECT * FROM t1 LEFT JOIN t2 ON b GLOB 'abc*' WHERE t1.id=1; } } {1 {} {} {} {}} do_test tkt1537-3.2 { execsql { SELECT * FROM t2 LEFT JOIN t1 ON a1 GLOB 'abc*' WHERE t2.id=3; } } {3 1 {} {} {}} finish_test |