/ Check-in [7e4ed8b5]
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:Fix a problem with bytecode generation when a query involves two or more indexes on expressions connected by OR. Ticket [d96eba87698a428c1ddd0790ea04]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 7e4ed8b5c2047b69bbb037497111e56e465a4d9f8afca3b337f9ac744471dc4a
User & Date: drh 2018-12-14 11:16:52
Context
2018-12-14
13:18
When saving the position of a cursor at the b-tree layer, allocate a few extra bytes at the end of the buffer used to save the key. Otherwise, if the key is corrupt, the code that restores the cursor position may overread the buffer by a little. check-in: 160b1e31 user: dan tags: trunk
11:16
Fix a problem with bytecode generation when a query involves two or more indexes on expressions connected by OR. Ticket [d96eba87698a428c1ddd0790ea04] check-in: 7e4ed8b5 user: drh tags: trunk
08:40
Possible fix for [d96eba87]. Closed-Leaf check-in: 89a25abf user: dan tags: tkt-d96eba87
03:16
Report corruption when an attempt is made to write a pointer-map page that is also being used as a btree page. Also, fix a bug in fuzzcheck that cause it to overlook a pointer-map bug that was trigger by the fuzzcheck test data, and also fix the pointer-map bug. check-in: cc42dd15 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Show Whitespace Changes Patch

Changes to src/whereexpr.c.

774
775
776
777
778
779
780

781
782
783
784
785
786
787
...
796
797
798
799
800
801
802

803
804
805
806
807
808
809
...
815
816
817
818
819
820
821
822


823
824
825
826
827
828
829
    ** other of the == operator in every subterm.  That table and column
    ** will be recorded in iCursor and iColumn.  There might not be any
    ** such table and column.  Set okToChngToIN if an appropriate table
    ** and column is found but leave okToChngToIN false if not found.
    */
    for(j=0; j<2 && !okToChngToIN; j++){
      pOrTerm = pOrWc->a;

      for(i=pOrWc->nTerm-1; i>=0; i--, pOrTerm++){
        assert( pOrTerm->eOperator & WO_EQ );
        pOrTerm->wtFlags &= ~TERM_OR_OK;
        if( pOrTerm->leftCursor==iCursor ){
          /* This is the 2-bit case and we are on the second iteration and
          ** current term is from the first iteration.  So skip this term. */
          assert( j==1 );
................................................................................
          testcase( pOrTerm->wtFlags & TERM_COPIED );
          testcase( pOrTerm->wtFlags & TERM_VIRTUAL );
          assert( pOrTerm->wtFlags & (TERM_COPIED|TERM_VIRTUAL) );
          continue;
        }
        iColumn = pOrTerm->u.leftColumn;
        iCursor = pOrTerm->leftCursor;

        break;
      }
      if( i<0 ){
        /* No candidate table+column was found.  This can only occur
        ** on the second iteration */
        assert( j==1 );
        assert( IsPowerOfTwo(chngToIN) );
................................................................................
      /* We have found a candidate table and column.  Check to see if that
      ** table and column is common to every term in the OR clause */
      okToChngToIN = 1;
      for(; i>=0 && okToChngToIN; i--, pOrTerm++){
        assert( pOrTerm->eOperator & WO_EQ );
        if( pOrTerm->leftCursor!=iCursor ){
          pOrTerm->wtFlags &= ~TERM_OR_OK;
        }else if( pOrTerm->u.leftColumn!=iColumn ){


          okToChngToIN = 0;
        }else{
          int affLeft, affRight;
          /* If the right-hand side is also a column, then the affinities
          ** of both right and left sides must be such that no type
          ** conversions are required on the right.  (Ticket #2249)
          */







>







 







>







 







|
>
>







774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
...
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
...
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
    ** other of the == operator in every subterm.  That table and column
    ** will be recorded in iCursor and iColumn.  There might not be any
    ** such table and column.  Set okToChngToIN if an appropriate table
    ** and column is found but leave okToChngToIN false if not found.
    */
    for(j=0; j<2 && !okToChngToIN; j++){
      pOrTerm = pOrWc->a;
      Expr *pLeft = 0;
      for(i=pOrWc->nTerm-1; i>=0; i--, pOrTerm++){
        assert( pOrTerm->eOperator & WO_EQ );
        pOrTerm->wtFlags &= ~TERM_OR_OK;
        if( pOrTerm->leftCursor==iCursor ){
          /* This is the 2-bit case and we are on the second iteration and
          ** current term is from the first iteration.  So skip this term. */
          assert( j==1 );
................................................................................
          testcase( pOrTerm->wtFlags & TERM_COPIED );
          testcase( pOrTerm->wtFlags & TERM_VIRTUAL );
          assert( pOrTerm->wtFlags & (TERM_COPIED|TERM_VIRTUAL) );
          continue;
        }
        iColumn = pOrTerm->u.leftColumn;
        iCursor = pOrTerm->leftCursor;
        pLeft = pOrTerm->pExpr->pLeft;
        break;
      }
      if( i<0 ){
        /* No candidate table+column was found.  This can only occur
        ** on the second iteration */
        assert( j==1 );
        assert( IsPowerOfTwo(chngToIN) );
................................................................................
      /* We have found a candidate table and column.  Check to see if that
      ** table and column is common to every term in the OR clause */
      okToChngToIN = 1;
      for(; i>=0 && okToChngToIN; i--, pOrTerm++){
        assert( pOrTerm->eOperator & WO_EQ );
        if( pOrTerm->leftCursor!=iCursor ){
          pOrTerm->wtFlags &= ~TERM_OR_OK;
        }else if( pOrTerm->u.leftColumn!=iColumn || (iColumn==XN_EXPR 
               && sqlite3ExprCompare(pParse, pOrTerm->pExpr->pLeft, pLeft, -1)
        )){
          okToChngToIN = 0;
        }else{
          int affLeft, affRight;
          /* If the right-hand side is also a column, then the affinities
          ** of both right and left sides must be such that no type
          ** conversions are required on the right.  (Ticket #2249)
          */

Changes to test/indexexpr2.test.

226
227
228
229
230
231
232



















233
234
235
    SELECT sqlite_master.name 
      FROM sqlite_master, explain('UPDATE t2 SET c=c+1, f=NULL')
     WHERE explain.opcode LIKE 'Open%'
       AND sqlite_master.rootpage=explain.p2
     ORDER BY 1;
  } {t2 t2abc t2cd t2def}
}





















finish_test







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



226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
    SELECT sqlite_master.name 
      FROM sqlite_master, explain('UPDATE t2 SET c=c+1, f=NULL')
     WHERE explain.opcode LIKE 'Open%'
       AND sqlite_master.rootpage=explain.p2
     ORDER BY 1;
  } {t2 t2abc t2cd t2def}
}

#-------------------------------------------------------------------------
# Test that ticket [d96eba87] has been fixed.
#
do_execsql_test 5.0 {
  CREATE TABLE t5(a INTEGER, b INTEGER);
  INSERT INTO t5 VALUES(2, 4), (3, 9);
}
do_execsql_test 5.1 {
  SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9;
} {2 4 3 9}
do_execsql_test 5.2 {
  CREATE INDEX t5a ON t5( abs(a) );
  CREATE INDEX t5b ON t5( abs(b) );
}
do_execsql_test 5.4 {
  SELECT * FROM t5 WHERE abs(a)=2 or abs(b)=9;
} {2 4 3 9}



finish_test