/ Check-in [7fdb1e2a]
Login

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

Overview
Comment:Fix a problem preventing the planner from identifying scans that visit at most one row in cases where that property is guaranteed by a unique, not-null, non-IPK column that is the leftmost in its table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:7fdb1e2ac2040dc47800a224d33a5c95d55200c480d46fedec1e97fb4f089ef7
User & Date: dan 2017-11-20 15:46:10
Context
2017-11-21
20:53
Update the omit-table-from-left-join optimization so that it can omit tables from the middle of the join as well as the end. check-in: 0cd82ee9 user: dan tags: trunk
19:22
Update the omit-table-from-left-join optimization so that it can omit tables from the middle of the join as well as the end. Closed-Leaf check-in: 618ca9fe user: dan tags: left-join-optimization
2017-11-20
15:46
Fix a problem preventing the planner from identifying scans that visit at most one row in cases where that property is guaranteed by a unique, not-null, non-IPK column that is the leftmost in its table. check-in: 7fdb1e2a user: dan tags: trunk
15:45
Fix a typo in a test script on this branch. Closed-Leaf check-in: bff5dcfd user: dan tags: left-join-omit-fix
2017-11-18
18:07
Enhance the log messages produced in some cases if database corruption is encountered by an SQLITE_DEBUG build. check-in: ee840a76 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
                          ** changes "x IN (?)" into "x=?". */
      }
    }else if( eOp & (WO_EQ|WO_IS) ){
      int iCol = pProbe->aiColumn[saved_nEq];
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      assert( saved_nEq==pNew->u.btree.nEq );
      if( iCol==XN_ROWID 
       || (iCol>0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1)
      ){
        if( iCol>=0 && pProbe->uniqNotNull==0 ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }
      }







|







2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
                          ** changes "x IN (?)" into "x=?". */
      }
    }else if( eOp & (WO_EQ|WO_IS) ){
      int iCol = pProbe->aiColumn[saved_nEq];
      pNew->wsFlags |= WHERE_COLUMN_EQ;
      assert( saved_nEq==pNew->u.btree.nEq );
      if( iCol==XN_ROWID 
       || (iCol>=0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1)
      ){
        if( iCol>=0 && pProbe->uniqNotNull==0 ){
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }else{
          pNew->wsFlags |= WHERE_ONEROW;
        }
      }

Changes to test/join2.test.

87
88
89
90
91
92
93
94































95

do_catchsql_test 2.1 {
  SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=c);
} {1 {ON clause references tables to its right}}
do_catchsql_test 2.2 {
  SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
} {0 {one one one}}
































finish_test








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

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

do_catchsql_test 2.1 {
  SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=c);
} {1 {ON clause references tables to its right}}
do_catchsql_test 2.2 {
  SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
} {0 {one one one}}

#-------------------------------------------------------------------------
# Test that a problem causing where.c to overlook opportunities to
# omit unnecessary tables from a LEFT JOIN when UNIQUE, NOT NULL column 
# that makes this possible happens to be the leftmost in its table.
#
reset_db
do_execsql_test 3.0 {
  CREATE TABLE t1(k1 INTEGER PRIMARY KEY, k2, k3);
  CREATE TABLE t2(k2 INTEGER PRIMARY KEY, v2);

  -- Prior to this problem being fixed, table t3_2 would be omitted from
  -- the join queries below, but if t3_1 were used in its place it would
  -- not.
  CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;
  CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;
}

do_eqp_test 3.1 {
  SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3);
} {
  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_eqp_test 3.2 {
  SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3);
} {
  0 0 0 {SCAN TABLE t1} 
  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}

finish_test