/ Check-in [0d2e258e]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional work on ticket #3015. The previous fix (check-in (4919)) did not appear to work in all cases and it disabled indexing in some places where it should not have. New test cases added to help insure that the current fix is better. (CVS 5026)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0d2e258e1a3276e55903ba2ded987f8d8a18cacd
User & Date: drh 2008-04-17 19:14:02
Context
2008-04-17
20:59
Continuing progress on the journal_mode pragma. It still does not work. (CVS 5027) check-in: 4a72a7bb user: drh tags: trunk
19:14
Additional work on ticket #3015. The previous fix (check-in (4919)) did not appear to work in all cases and it disabled indexing in some places where it should not have. New test cases added to help insure that the current fix is better. (CVS 5026) check-in: 0d2e258e user: drh tags: trunk
17:03
Fix a typo in the documentation for sqlite3_bind_text. Ticket #3056. (CVS 5025) check-in: 79571e2c user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
....
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.427 2008/04/15 12:14:22 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
** for helping to understand what is happening inside the code generator
** during the execution of complex SELECT statements.
**
** These routine are not called anywhere from within the normal
** code base.  Then are intended to be called from within the debugger
** or from temporary "printf" statements inserted for debugging.
*/
static void sqlite3PrintExpr(Expr *p){
  if( p->token.z && p->token.n>0 ){
    sqlite3DebugPrintf("(%.*s", p->token.n, p->token.z);
  }else{
    sqlite3DebugPrintf("(%d", p->op);
  }
  if( p->pLeft ){
    sqlite3DebugPrintf(" ");
................................................................................
  }
  if( p->pRight ){
    sqlite3DebugPrintf(" ");
    sqlite3PrintExpr(p->pRight);
  }
  sqlite3DebugPrintf(")");
}
static void sqlite3PrintExprList(ExprList *pList){
  int i;
  for(i=0; i<pList->nExpr; i++){
    sqlite3PrintExpr(pList->a[i].pExpr);
    if( i<pList->nExpr-1 ){
      sqlite3DebugPrintf(", ");
    }
  }
}
static void sqlite3PrintSelect(Select *p, int indent){
  sqlite3DebugPrintf("%*sSELECT(%p) ", indent, "", p);
  sqlite3PrintExprList(p->pEList);
  sqlite3DebugPrintf("\n");
  if( p->pSrc ){
    char *zPrefix;
    int i;
    zPrefix = "FROM";







|







 







|







 







|








|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
3649
3650
3651
3652
3653
3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
....
3665
3666
3667
3668
3669
3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains C code routines that are called by the parser
** to handle SELECT statements in SQLite.
**
** $Id: select.c,v 1.428 2008/04/17 19:14:02 drh Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
** for helping to understand what is happening inside the code generator
** during the execution of complex SELECT statements.
**
** These routine are not called anywhere from within the normal
** code base.  Then are intended to be called from within the debugger
** or from temporary "printf" statements inserted for debugging.
*/
void sqlite3PrintExpr(Expr *p){
  if( p->token.z && p->token.n>0 ){
    sqlite3DebugPrintf("(%.*s", p->token.n, p->token.z);
  }else{
    sqlite3DebugPrintf("(%d", p->op);
  }
  if( p->pLeft ){
    sqlite3DebugPrintf(" ");
................................................................................
  }
  if( p->pRight ){
    sqlite3DebugPrintf(" ");
    sqlite3PrintExpr(p->pRight);
  }
  sqlite3DebugPrintf(")");
}
void sqlite3PrintExprList(ExprList *pList){
  int i;
  for(i=0; i<pList->nExpr; i++){
    sqlite3PrintExpr(pList->a[i].pExpr);
    if( i<pList->nExpr-1 ){
      sqlite3DebugPrintf(", ");
    }
  }
}
void sqlite3PrintSelect(Select *p, int indent){
  sqlite3DebugPrintf("%*sSELECT(%p) ", indent, "", p);
  sqlite3PrintExprList(p->pEList);
  sqlite3DebugPrintf("\n");
  if( p->pSrc ){
    char *zPrefix;
    int i;
    zPrefix = "FROM";

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
714
715
716
717
718
719
720

721
722
723
724
725
726
727
...
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
...
972
973
974
975
976
977
978





979
980
981
982
983
984
985
** 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.298 2008/04/10 13:33:18 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
  int idxTerm               /* Index of the term to be analyzed */
){
  WhereTerm *pTerm;
  ExprMaskSet *pMaskSet;
  Expr *pExpr;
  Bitmask prereqLeft;
  Bitmask prereqAll;

  int nPattern;
  int isComplete;
  int noCase;
  int op;
  Parse *pParse = pWC->pParse;
  sqlite3 *db = pParse->db;

................................................................................
  }else{
    pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
  }
  prereqAll = exprTableUsage(pMaskSet, pExpr);
  if( ExprHasProperty(pExpr, EP_FromJoin) ){
    Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
    prereqAll |= x;
    pTerm->prereqRight |= x-1; /* ON clause terms may not be used with an index
                               ** on left table of a LEFT JOIN.  Ticket #3015 */
  }
  pTerm->prereqAll = prereqAll;
  pTerm->leftCursor = -1;
  pTerm->iParent = -1;
  pTerm->eOperator = 0;
  if( allowedOp(op) && (pTerm->prereqRight & prereqLeft)==0 ){
    Expr *pLeft = pExpr->pLeft;
................................................................................
      pTerm = &pWC->a[idxTerm];
      pTerm->nChild = 1;
      pTerm->flags |= TERM_COPIED;
      pNewTerm->prereqAll = pTerm->prereqAll;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */





}

/*
** Return TRUE if any of the expressions in pList->a[iFirst...] contain
** a reference to any table other than the iBase table.
*/
static int referencesOtherTables(







|







 







>







 







|
|







 







>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
...
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
...
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
** 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.299 2008/04/17 19:14:02 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
................................................................................
  int idxTerm               /* Index of the term to be analyzed */
){
  WhereTerm *pTerm;
  ExprMaskSet *pMaskSet;
  Expr *pExpr;
  Bitmask prereqLeft;
  Bitmask prereqAll;
  Bitmask extraRight = 0;
  int nPattern;
  int isComplete;
  int noCase;
  int op;
  Parse *pParse = pWC->pParse;
  sqlite3 *db = pParse->db;

................................................................................
  }else{
    pTerm->prereqRight = exprTableUsage(pMaskSet, pExpr->pRight);
  }
  prereqAll = exprTableUsage(pMaskSet, pExpr);
  if( ExprHasProperty(pExpr, EP_FromJoin) ){
    Bitmask x = getMask(pMaskSet, pExpr->iRightJoinTable);
    prereqAll |= x;
    extraRight = x-1;  /* ON clause terms may not be used with an index
                       ** on left table of a LEFT JOIN.  Ticket #3015 */
  }
  pTerm->prereqAll = prereqAll;
  pTerm->leftCursor = -1;
  pTerm->iParent = -1;
  pTerm->eOperator = 0;
  if( allowedOp(op) && (pTerm->prereqRight & prereqLeft)==0 ){
    Expr *pLeft = pExpr->pLeft;
................................................................................
      pTerm = &pWC->a[idxTerm];
      pTerm->nChild = 1;
      pTerm->flags |= TERM_COPIED;
      pNewTerm->prereqAll = pTerm->prereqAll;
    }
  }
#endif /* SQLITE_OMIT_VIRTUALTABLE */

  /* Prevent ON clause terms of a LEFT JOIN from being used to drive
  ** an index for tables to the left of the join.
  */
  pTerm->prereqRight |= extraRight;
}

/*
** Return TRUE if any of the expressions in pList->a[iFirst...] contain
** a reference to any table other than the iBase table.
*/
static int referencesOtherTables(

Changes to test/tester.tcl.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
364
365
366
367
368
369
370









371
372
373
374
375
376
377
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.115 2008/04/15 02:36:34 drh Exp $

#
# What for user input before continuing.  This gives an opportunity
# to connect profiling tools to the process.
#
for {set i 0} {$i<[llength $argv]} {incr i} {
  if {[regexp {^-+pause$} [lindex $argv $i] all value]} {
................................................................................
  puts "----  ------------  ------  ------  ------  ---------------  --  -"
  $db eval "explain $sql" {} {
    puts [format {%-4d  %-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
      $addr $opcode $p1 $p2 $p3 $p4 $p5 $comment
    ]
  }
}










# Another procedure to execute SQL.  This one includes the field
# names in the returned list.
#
proc execsql2 {sql} {
  set result {}
  db eval $sql data {







|







 







>
>
>
>
>
>
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements some common TCL routines used for regression
# testing the SQLite library
#
# $Id: tester.tcl,v 1.116 2008/04/17 19:14:02 drh Exp $

#
# What for user input before continuing.  This gives an opportunity
# to connect profiling tools to the process.
#
for {set i 0} {$i<[llength $argv]} {incr i} {
  if {[regexp {^-+pause$} [lindex $argv $i] all value]} {
................................................................................
  puts "----  ------------  ------  ------  ------  ---------------  --  -"
  $db eval "explain $sql" {} {
    puts [format {%-4d  %-12.12s  %-6d  %-6d  %-6d  % -17s %s  %s} \
      $addr $opcode $p1 $p2 $p3 $p4 $p5 $comment
    ]
  }
}

# Show the VDBE program for an SQL statement but omit the Trace
# opcode at the beginning.  This procedure can be used to prove
# that different SQL statements generate exactly the same VDBE code.
#
proc explain_no_trace {sql} {
  set tr [db eval "EXPLAIN $sql"]
  return [lrange $tr 7 end]
}

# Another procedure to execute SQL.  This one includes the field
# names in the returned list.
#
proc execsql2 {sql} {
  set result {}
  db eval $sql data {

Changes to test/where3.test.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
40
41
42
43
44
45
46








47
48
49
50
51
52
53
..
73
74
75
76
77
78
79
















80
81
82
83
84
85
86
...
116
117
118
119
120
121
122






























123
124
125
126
127
128
129
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the join reordering optimization
# in cases that include a LEFT JOIN.
#
# $Id: where3.test,v 1.3 2006/12/16 16:25:17 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# The following is from ticket #1652.
#
# A comma join then a left outer join:  A,B left join C.
................................................................................
    
    INSERT INTO t3 VALUES(999,'nine');
    CREATE INDEX t3i1 ON t3(x);
    
    SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q;
  }
} {222 two 2 222 {} {}}









# Ticket #1830
#
# This is similar to the above but with the LEFT JOIN on the
# other side.
#
do_test where3-1.2 {
................................................................................

    SELECT parent1.parent1key, child1.value, child2.value
    FROM parent1
    LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
    INNER JOIN child2 ON child2.child2key = parent1.child2key;
  }
} {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}}

















# This procedure executes the SQL.  Then it appends 
# the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
................................................................................
    CREATE TABLE tC(cpk integer primary key, cx);
    CREATE TABLE tD(dpk integer primary key, dx);
  }
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE cpk=bx AND bpk=ax
  }






























} {tA {} tB * tC * tD *}
do_test where3-2.2 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE cpk=bx AND apk=bx
  }
} {tB {} tA * tC * tD *}







|







 







>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
..
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
...
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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the join reordering optimization
# in cases that include a LEFT JOIN.
#
# $Id: where3.test,v 1.4 2008/04/17 19:14:02 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# The following is from ticket #1652.
#
# A comma join then a left outer join:  A,B left join C.
................................................................................
    
    INSERT INTO t3 VALUES(999,'nine');
    CREATE INDEX t3i1 ON t3(x);
    
    SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q;
  }
} {222 two 2 222 {} {}}

ifcapable explain {
  do_test where3-1.1.1 {
     explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x
                        WHERE p=2 AND a=q}
  } [explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON x=q
                        WHERE p=2 AND a=q}]
}

# Ticket #1830
#
# This is similar to the above but with the LEFT JOIN on the
# other side.
#
do_test where3-1.2 {
................................................................................

    SELECT parent1.parent1key, child1.value, child2.value
    FROM parent1
    LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
    INNER JOIN child2 ON child2.child2key = parent1.child2key;
  }
} {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}}

ifcapable explain {
  do_test where3-1.2.1 {
     explain_no_trace {
       SELECT parent1.parent1key, child1.value, child2.value
       FROM parent1
       LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
       INNER JOIN child2 ON child2.child2key = parent1.child2key;
     }
  } [explain_no_trace {
       SELECT parent1.parent1key, child1.value, child2.value
       FROM parent1
       LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key 
       INNER JOIN child2 ON child2.child2key = parent1.child2key;
     }]
}

# This procedure executes the SQL.  Then it appends 
# the ::sqlite_query_plan variable.
#
proc queryplan {sql} {
  set ::sqlite_sort_count 0
  set data [execsql $sql]
................................................................................
    CREATE TABLE tC(cpk integer primary key, cx);
    CREATE TABLE tD(dpk integer primary key, dx);
  }
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE cpk=bx AND bpk=ax
  }
} {tA {} tB * tC * tD *}
do_test where3-2.1.1 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
     WHERE cpk=bx AND bpk=ax
  }
} {tA {} tB * tC * tD *}
do_test where3-2.1.2 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
     WHERE bx=cpk AND bpk=ax
  }
} {tA {} tB * tC * tD *}
do_test where3-2.1.3 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
     WHERE bx=cpk AND ax=bpk
  }
} {tA {} tB * tC * tD *}
do_test where3-2.1.4 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE bx=cpk AND ax=bpk
  }
} {tA {} tB * tC * tD *}
do_test where3-2.1.5 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE cpk=bx AND ax=bpk
  }
} {tA {} tB * tC * tD *}
do_test where3-2.2 {
  queryplan {
    SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
     WHERE cpk=bx AND apk=bx
  }
} {tB {} tA * tC * tD *}

Changes to test/where6.test.

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
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing that terms in the ON clause of
# a LEFT OUTER JOIN are not used with indices.  See ticket #3015.
#
# $Id: where6.test,v 1.1 2008/03/26 14:56:35 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test where6-1.0 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
    INSERT INTO t1 VALUES(1,3,1);
    INSERT INTO t1 VALUES(2,4,2);
    CREATE TABLE t2(x INTEGER PRIMARY KEY);
    INSERT INTO t2 VALUES(3);

    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
  }
} {1 3 1 3 2 4 2 {}}

























do_test where6-1.1 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
  }
} {1 3 1 3}
do_test where6-1.2 {













  execsql {
    CREATE INDEX i1 ON t1(c);

    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-1.3 {
  execsql {


























    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
  }















} {1 3 1 3}

finish_test







|






|










>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|




|
>
>
>
>
>
>
>
>
>
>
>
>
>






|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



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
118
119
120
121
122
123
124
125
126
127
128
129
130
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing that terms in the ON clause of
# a LEFT OUTER JOIN are not used with indices.  See ticket #3015.
#
# $Id: where6.test,v 1.2 2008/04/17 19:14:02 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Build some test data
#
do_test where6-1.1 {
  execsql {
    CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
    INSERT INTO t1 VALUES(1,3,1);
    INSERT INTO t1 VALUES(2,4,2);
    CREATE TABLE t2(x INTEGER PRIMARY KEY);
    INSERT INTO t2 VALUES(3);

    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-1.2 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-1.3 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-1.4 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c;
  }
} {1 3 1 3 2 4 2 {}}

ifcapable explain {
  do_test where6-1.5 {
     explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c}
  } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}]
  do_test where6-1.6 {
     explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c}
  } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}]
}

do_test where6-1.11 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
  }
} {1 3 1 3}
do_test where6-1.12 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1;
  }
} {1 3 1 3}
do_test where6-1.13 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
  }
} {1 3 1 3}



do_test where6-2.1 {
  execsql {
    CREATE INDEX i1 ON t1(c);

    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-2.2 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b AND c=1;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-2.3 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c;
  }
} {1 3 1 3 2 4 2 {}}
do_test where6-2.4 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND 1=c;
  }
} {1 3 1 3 2 4 2 {}}

ifcapable explain {
  do_test where6-2.5 {
     explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b AND 1=c}
  } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1}]
  do_test where6-2.6 {
     explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c}
  } [explain_no_trace {SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1}]
}


do_test where6-2.11 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
  }
} {1 3 1 3}
do_test where6-2.12 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE c=1;
  }
} {1 3 1 3}
do_test where6-2.13 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON x=b WHERE 1=c;
  }
} {1 3 1 3}
do_test where6-2.14 {
  execsql {
    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE 1=c;
  }
} {1 3 1 3}

finish_test