/ Check-in [88411a40]
Login

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

Overview
Comment:Update the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | omit-left-join-fix
Files: files | file ages | folders
SHA3-256: 88411a402b29f19d69e0d06d9099eabb98c238ddb7c07ce110236316f008753e
User & Date: dan 2018-01-29 17:08:52
Context
2018-01-31
14:07
Fix a failing assert() in the new code on this branch. Closed-Leaf check-in: 74d857d1 user: dan tags: omit-left-join-fix
2018-01-29
17:08
Update the omit-left-join optimization so that it works in some cases when the RHS is subject to a UNIQUE but not NOT NULL constraint. check-in: 88411a40 user: dan tags: omit-left-join-fix
16:22
Ensure the "unique-not-null" flag is set for automatic indexes on columns declared with "col UNIQUE NOT NULL" (where the NOT NULL comes after the UNIQUE). check-in: 8767f7b8 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2477
2478
2479
2480
2481
2482
2483

2484


2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
....
2821
2822
2823
2824
2825
2826
2827

2828
2829
2830
2831
2832
2833
2834
    }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;
        }
      }
    }else if( eOp & WO_ISNULL ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
    }else if( eOp & (WO_GT|WO_GE) ){
      testcase( eOp & WO_GT );
      testcase( eOp & WO_GE );
................................................................................
    sPk.nKeyCol = 1;
    sPk.nColumn = 1;
    sPk.aiColumn = &aiColumnPk;
    sPk.aiRowLogEst = aiRowEstPk;
    sPk.onError = OE_Replace;
    sPk.pTable = pTab;
    sPk.szIdxRow = pTab->szTabRow;

    aiRowEstPk[0] = pTab->nRowLogEst;
    aiRowEstPk[1] = 0;
    pFirst = pSrc->pTab->pIndex;
    if( pSrc->fg.notIndexed==0 ){
      /* The real indices of the table are only considered if the
      ** NOT INDEXED qualifier is omitted from the FROM clause */
      sPk.pNext = pFirst;







>
|
>
>
|

|







 







>







2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
....
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
    }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)
      ){
        assert( iCol!=XN_ROWID || pProbe->uniqNotNull );
        if( pProbe->uniqNotNull 
         || (pProbe->nKeyCol==1 && pProbe->onError && eOp==WO_EQ) 
        ){
          pNew->wsFlags |= WHERE_ONEROW;
        }else{
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }
      }
    }else if( eOp & WO_ISNULL ){
      pNew->wsFlags |= WHERE_COLUMN_NULL;
    }else if( eOp & (WO_GT|WO_GE) ){
      testcase( eOp & WO_GT );
      testcase( eOp & WO_GE );
................................................................................
    sPk.nKeyCol = 1;
    sPk.nColumn = 1;
    sPk.aiColumn = &aiColumnPk;
    sPk.aiRowLogEst = aiRowEstPk;
    sPk.onError = OE_Replace;
    sPk.pTable = pTab;
    sPk.szIdxRow = pTab->szTabRow;
    sPk.uniqNotNull = 1;
    aiRowEstPk[0] = pTab->nRowLogEst;
    aiRowEstPk[1] = 0;
    pFirst = pSrc->pTab->pIndex;
    if( pSrc->fg.notIndexed==0 ){
      /* The real indices of the table are only considered if the
      ** NOT INDEXED qualifier is omitted from the FROM clause */
      sPk.pNext = pFirst;

Changes to test/join2.test.

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
...
187
188
189
190
191
192
193
194

















195
  SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}

do_execsql_test 4.1.4 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}

do_eqp_test 4.2.1 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)}
  0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test 4.2.2 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}














































# 2017-11-23 (Thanksgiving day)
# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
#
do_execsql_test 4.3.0 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
................................................................................
    INSERT INTO t1(x) SELECT x FROM c;
  INSERT INTO t2(x) SELECT x+9 FROM t1;
  SELECT a.x, c.x
    FROM t1 AS a
    LEFT JOIN t1 AS b ON (a.x=b.x)
    LEFT JOIN t2 AS c ON (a.x=c.x);
} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}


















finish_test







|






|





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







 








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

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
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
...
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
  SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}

do_execsql_test 4.1.4 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}

do_eqp_test 4.1.5 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)}
  0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test 4.1.6 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
}

do_execsql_test 4.2.0 {
  DROP TABLE c1;
  DROP TABLE c2;
  DROP TABLE c3;
  CREATE TABLE c1(k UNIQUE, v1);
  CREATE TABLE c2(k UNIQUE, v2);
  CREATE TABLE c3(k UNIQUE, v3);

  INSERT INTO c1 VALUES(1, 2);
  INSERT INTO c2 VALUES(2, 3);
  INSERT INTO c3 VALUES(3, 'v3');

  INSERT INTO c1 VALUES(111, 1112);
  INSERT INTO c2 VALUES(112, 1113);
  INSERT INTO c3 VALUES(113, 'v1113');
}
do_execsql_test 4.2.1 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {2 v3 1112 {}}
do_execsql_test 4.2.2 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}

do_execsql_test 4.2.3 {
  SELECT DISTINCT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 1112 {}}

do_execsql_test 4.2.4 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
} {2 v3 2 v3 1112 {} 1112 {}}

do_eqp_test 4.2.5 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 1 {SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?)}
  0 2 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
}
do_eqp_test 4.2.6 {
  SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
} {
  0 0 0 {SCAN TABLE c1} 
  0 1 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
}

# 2017-11-23 (Thanksgiving day)
# OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
#
do_execsql_test 4.3.0 {
  DROP TABLE IF EXISTS t1;
  DROP TABLE IF EXISTS t2;
................................................................................
    INSERT INTO t1(x) SELECT x FROM c;
  INSERT INTO t2(x) SELECT x+9 FROM t1;
  SELECT a.x, c.x
    FROM t1 AS a
    LEFT JOIN t1 AS b ON (a.x=b.x)
    LEFT JOIN t2 AS c ON (a.x=c.x);
} {1 {} 2 {} 3 {} 4 {} 5 {} 6 {} 7 {} 8 {} 9 {} 10 10}

do_execsql_test 5.0 {
  CREATE TABLE s1 (a INTEGER PRIMARY KEY);
  CREATE TABLE s2 (a INTEGER PRIMARY KEY);
  CREATE TABLE s3 (a INTEGER);
  CREATE UNIQUE INDEX ndx on s3(a);
}
do_eqp_test 5.1 {
  SELECT s1.a FROM s1 left join s2 using (a);
} {
  0 0 0 {SCAN TABLE s1}
}
do_eqp_test 5.2 {
  SELECT s1.a FROM s1 left join s3 using (a);
} {
  0 0 0 {SCAN TABLE s1}
}

finish_test