/ Check-in [cbbeb9de]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:cbbeb9de0019a0b81318158711590078fcb7e98a
User & Date: drh 2005-11-26 14:08:08
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: 7e7cfce0 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: cbbeb9de user: drh tags: trunk
03:51
Remove some vestiges of the old OS_TEST driver. (CVS 2787) check-in: 008f676f user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
527
528
529
530
531
532
533










534
535
536
537
538
539
540
...
686
687
688
689
690
691
692

693
694
695
696
697
698
699
** 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.183 2005/11/21 12:48:24 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
    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.
**
................................................................................
      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);







|







 







>
>
>
>
>
>
>
>
>
>







 







>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
...
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
** 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)
................................................................................
    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.
**
................................................................................
      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