/ Check-in [f1f9b5de]
Login

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

Overview
Comment:When applying the optimization that disables WHERE clause terms that drive indexes, make sure not to do so if the term being disabled is a transitive constraint. Fix for the problem identified by forum post eb8613976a.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: f1f9b5de3c59489b94963685660b3ddc45eece5535b02fec399b6ece0e38563d
User & Date: drh 2021-05-04 23:21:35
Original Comment: When applying the optimization that disables WHERE clause terms that drive indexes, make sure not to do so if the term being disabled is a transitive constraint. Fix for the problem identified by forum post eb8613976a.
References
2021-10-04
11:10
Fix query plans created by whereShortCut() so that they always check transitive constraints that drive an index. The is analogous to the [f1f9b5de3c59489b] check-in, just for whereShortCut() rather than the full query planner. Fix for the issue described by forum post a65cacbf5e1c41ba. (check-in: 8b24c177 user: drh tags: trunk)
2021-08-30
17:02
Do not disable a rowid=? term used to drive an IPK index if it is a transitive constraint. Commit [f1f9b5de] made the same change for non-IPK indexes. (check-in: 46e28cbc user: dan tags: trunk)
Context
2021-05-05
11:47
Fix an undefined-integer-overflow problem in fts3.c. (check-in: a0bf931b user: dan tags: trunk)
2021-05-04
23:21
When applying the optimization that disables WHERE clause terms that drive indexes, make sure not to do so if the term being disabled is a transitive constraint. Fix for the problem identified by forum post eb8613976a. (check-in: f1f9b5de user: drh tags: trunk)
18:24
Also for the 0x20000 bit of ".wheretrace", show when WHERE clause terms are disabled. (check-in: 625fb253 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2642
2643
2644
2645
2646
2647
2648

2649
2650
2651
2652
2653
2654
2655
         || (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 );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pNew->u.btree.nBtm = whereRangeVectorLen(







>







2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
         || (pProbe->nKeyCol==1 && pProbe->onError && eOp==WO_EQ) 
        ){
          pNew->wsFlags |= WHERE_ONEROW;
        }else{
          pNew->wsFlags |= WHERE_UNQ_WANTED;
        }
      }
      if( scan.iEquiv>1 ) pNew->wsFlags |= WHERE_TRANSCONS;
    }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 );
      pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
      pNew->u.btree.nBtm = whereRangeVectorLen(

Changes to src/whereInt.h.

599
600
601
602
603
604
605

606
607
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
#define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */
#define WHERE_BIGNULL_SORT 0x00080000  /* Column nEq of index is BIGNULL */
#define WHERE_IN_SEEKSCAN  0x00100000  /* Seek-scan optimization for IN */


#endif /* !defined(SQLITE_WHEREINT_H) */







>


599
600
601
602
603
604
605
606
607
608
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */
#define WHERE_SKIPSCAN     0x00008000  /* Uses the skip-scan algorithm */
#define WHERE_UNQ_WANTED   0x00010000  /* WHERE_ONEROW would have been helpful*/
#define WHERE_PARTIALIDX   0x00020000  /* The automatic index is partial */
#define WHERE_IN_EARLYOUT  0x00040000  /* Perhaps quit IN loops early */
#define WHERE_BIGNULL_SORT 0x00080000  /* Column nEq of index is BIGNULL */
#define WHERE_IN_SEEKSCAN  0x00100000  /* Seek-scan optimization for IN */
#define WHERE_TRANSCONS    0x00200000  /* Uses a transitive constraint */

#endif /* !defined(SQLITE_WHEREINT_H) */

Changes to src/wherecode.c.

616
617
618
619
620
621
622













623


624
625
626
627
628
629
630
      }
    }else{
      pLevel->u.in.nIn = 0;
    }
    sqlite3DbFree(pParse->db, aiMap);
#endif
  }













  disableTerm(pLevel, pTerm);


  return iReg;
}

/*
** Generate code that will evaluate all == and IN constraints for an
** index scan.
**







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







616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
      }
    }else{
      pLevel->u.in.nIn = 0;
    }
    sqlite3DbFree(pParse->db, aiMap);
#endif
  }

  /* As an optimization, try to disable the WHERE clause term that is
  ** driving the index as it will always be true.  The correct answer is
  ** obtained regardless, but we might get the answer with fewer CPU cycles
  ** by omitting the term.
  **
  ** But do not disable the term unless we are certain that the term is
  ** not a transitive constraint.  For an example of where that does not
  ** work, see https://sqlite.org/forum/forumpost/eb8613976a (2021-05-04)
  */
  if( (pLevel->pWLoop->wsFlags & WHERE_TRANSCONS)==0
   || (pTerm->eOperator & WO_EQUIV)==0
  ){
    disableTerm(pLevel, pTerm);
  }

  return iReg;
}

/*
** Generate code that will evaluate all == and IN constraints for an
** index scan.
**

Changes to test/transitive1.test.

348
349
350
351
352
353
354
355



























356
do_execsql_test transitive1-570 {
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {}
do_execsql_test transitive1-570eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {/SEARCH c1 USING INDEX c1x/}




























finish_test








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

348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
do_execsql_test transitive1-570 {
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {}
do_execsql_test transitive1-570eqp {
  EXPLAIN QUERY PLAN
  SELECT * FROM c1 WHERE x=y AND z=y AND z='abc';
} {/SEARCH c1 USING INDEX c1x/}

# 2021-05-04 forum https://sqlite.org/forum/forumpost/eb8613976a
reset_db
do_execsql_test transitive1-600 {
  CREATE TABLE t0(a0 INT, b1 INT);
  CREATE INDEX t0b1 ON t0(b1);
  CREATE TABLE t1(w,x,y,z3 INT);
  INSERT INTO t0(a0, b1) VALUES (0,1);
  INSERT INTO t1(w,x,y,z3) VALUES (7,8,9,1);
} {}
do_execsql_test transitive1-610 {
  SELECT ALL * FROM t0,t1 WHERE b1=z3 AND a0=z3;
} {}
do_execsql_test transitive1-620 {
  SELECT ALL * FROM t0,t1 WHERE likely(b1=z3) AND a0=z3;
} {}
do_execsql_test transitive1-630 {
  DROP TABLE t0;
  DROP TABLE t1;
  CREATE TABLE t0(c0 INT, c1 INT UNIQUE);
  CREATE TABLE t1(c0 INT);
  INSERT INTO t0(c0, c1) VALUES (0, 1);
  INSERT INTO t1(c0) VALUES (1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (t1.c0=t0.c1);
  SELECT ALL * FROM t1 NATURAL JOIN t0 WHERE (likely(t1.c0=t0.c1));
  SELECT ALL * FROM t1,t0 WHERE (likely(t1.c0=t0.c1) AND t1.c0=t0.c0);
} {}

finish_test