/ Check-in [3464d369]
Login

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

Overview
Comment:Multi-index OR optimizer response to ORDER BY rowid. But fix in sqlite3_stmt_status(): report a full table scan when "ORDER BY rowid" is used without constraints. (CVS 6069)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:3464d369d3b6899ec726cf5b42b68b1dac2ba982
User & Date: drh 2008-12-28 20:47:03
Context
2008-12-29
10:39
Set the OPFLG_IN1 flag on a couple of extra opcodes. (CVS 6070) check-in: 03d03504 user: danielk1977 tags: trunk
2008-12-28
20:47
Multi-index OR optimizer response to ORDER BY rowid. But fix in sqlite3_stmt_status(): report a full table scan when "ORDER BY rowid" is used without constraints. (CVS 6069) check-in: 3464d369 user: drh tags: trunk
18:35
Optimize WHERE clauses that constain AND, BETWEEN, and LIKE terms as operands of an OR. (CVS 6068) check-in: 67cf24b3 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is responsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.346 2008/12/28 18:35:09 drh Exp $
           19  +** $Id: where.c,v 1.347 2008/12/28 20:47:03 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** Trace output macros
    25     25   */
    26     26   #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
................................................................................
  1873   1873           nRow += sTermCost.nRow;
  1874   1874         }
  1875   1875         if( rTotal<pCost->rCost ){
  1876   1876           pCost->rCost = rTotal;
  1877   1877           pCost->nRow = nRow;
  1878   1878           pCost->plan.wsFlags = WHERE_MULTI_OR;
  1879   1879           pCost->plan.u.pTerm = pTerm;
         1880  +        if( pOrderBy!=0
         1881  +         && sortableByRowid(iCur, pOrderBy, pWC->pMaskSet, &rev)
         1882  +         && !rev
         1883  +        ){
         1884  +          pCost->plan.wsFlags = WHERE_ORDERBY|WHERE_MULTI_OR;
         1885  +        }
  1880   1886         }
  1881   1887       }
  1882   1888     }
  1883   1889   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  1884   1890   
  1885   1891     /* If the pSrc table is the right table of a LEFT JOIN then we may not
  1886   1892     ** use an index to satisfy IS NULL constraints on that table.  This is
................................................................................
  2422   2428         }
  2423   2429         disableTerm(pLevel, pEnd);
  2424   2430       }
  2425   2431       start = sqlite3VdbeCurrentAddr(v);
  2426   2432       pLevel->op = bRev ? OP_Prev : OP_Next;
  2427   2433       pLevel->p1 = iCur;
  2428   2434       pLevel->p2 = start;
         2435  +    pLevel->p5 = (pStart==0 && pEnd==0) ?1:0;
  2429   2436       codeRowSetEarly = regRowSet>=0 ? whereRowReadyForOutput(pWC) : 0;
  2430   2437       if( codeRowSetEarly || testOp!=OP_Noop ){
  2431   2438         int r1 = sqlite3GetTempReg(pParse);
  2432   2439         sqlite3VdbeAddOp2(v, OP_Rowid, iCur, r1);
  2433   2440         if( testOp!=OP_Noop ){
  2434   2441           sqlite3VdbeAddOp3(v, testOp, memEndValue, addrBrk, r1);
  2435   2442           sqlite3VdbeChangeP5(v, SQLITE_AFF_NUMERIC | SQLITE_JUMPIFNULL);

Changes to test/where7.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the multi-index OR clause optimizer.
    13     13   #
    14         -# $Id: where7.test,v 1.2 2008/12/28 18:35:09 drh Exp $
           14  +# $Id: where7.test,v 1.3 2008/12/28 20:47:03 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   ifcapable !or_opt {
    20     20     finish_test
    21     21     return
................................................................................
    22     22   }
    23     23   
    24     24   # Evaluate SQL.  Return the result set followed by the
    25     25   # and the number of full-scan steps.
    26     26   #
    27     27   proc count_steps {sql} {
    28     28     set r [db eval $sql]
    29         -  lappend r scan [db status step]
           29  +  lappend r scan [db status step] sort [db status sort]
    30     30   }
    31     31   
    32     32   # Build some test data
    33     33   #
    34     34   do_test where7-1.1 {
    35     35     execsql {
    36     36       CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
................................................................................
    42     42       CREATE INDEX t1b ON t1(b);
    43     43       CREATE INDEX t1c ON t1(c);
    44     44       SELECT * FROM t1;
    45     45     }
    46     46   } {1 2 3 4 2 3 4 5 3 4 6 8 4 5 10 15 5 10 100 1000}
    47     47   do_test where7-1.2 {
    48     48     count_steps {
    49         -    SELECT a FROM t1 WHERE b=3 OR c=6
           49  +    SELECT a FROM t1 WHERE b=3 OR c=6 ORDER BY a
    50     50     }
    51         -} {2 3 scan 0}
           51  +} {2 3 scan 0 sort 0}
    52     52   do_test where7-1.3 {
    53     53     count_steps {
    54         -    SELECT a FROM t1 WHERE b=3 OR +c=6
           54  +    SELECT a FROM t1 WHERE b=3 OR +c=6 ORDER BY a
    55     55     }
    56         -} {2 3 scan 4}
           56  +} {2 3 scan 4 sort 0}
    57     57   do_test where7-1.4 {
    58     58     count_steps {
    59         -    SELECT a FROM t1 WHERE +b=3 OR c=6
           59  +    SELECT a FROM t1 WHERE +b=3 OR c=6 ORDER BY 1
    60     60     }
    61         -} {2 3 scan 4}
           61  +} {2 3 scan 4 sort 0}
    62     62   do_test where7-1.5 {
    63     63     count_steps {
    64         -    SELECT a FROM t1 WHERE 3=b OR c=6
           64  +    SELECT a FROM t1 WHERE 3=b OR c=6 ORDER BY rowid
    65     65     }
    66         -} {2 3 scan 0}
           66  +} {2 3 scan 0 sort 0}
    67     67   do_test where7-1.6 {
    68     68     count_steps {
    69         -    SELECT a FROM t1 WHERE (3=b OR c=6) AND +a>0
           69  +    SELECT a FROM t1 WHERE (3=b OR c=6) AND +a>0 ORDER BY a
    70     70     }
    71         -} {2 3 scan 0}
           71  +} {2 3 scan 0 sort 0}
    72     72   do_test where7-1.7 {
    73     73     count_steps {
    74         -    SELECT a FROM t1 WHERE (b=3 OR c>10)
           74  +    SELECT a FROM t1 WHERE (b=3 OR c>10) ORDER BY a
    75     75     }
    76         -} {2 5 scan 0}
           76  +} {2 5 scan 0 sort 0}
    77     77   do_test where7-1.8 {
    78     78     count_steps {
    79         -    SELECT a FROM t1 WHERE (b=3 OR c>=10)
           79  +    SELECT a FROM t1 WHERE (b=3 OR c>=10) ORDER BY a
    80     80     }
    81         -} {2 4 5 scan 0}
           81  +} {2 4 5 scan 0 sort 0}
    82     82   do_test where7-1.9 {
    83     83     count_steps {
    84         -    SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4)
           84  +    SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4) ORDER BY a
    85     85     }
    86         -} {2 4 5 scan 0}
           86  +} {2 4 5 scan 0 sort 0}
    87     87   do_test where7-1.10 {
    88     88     count_steps {
    89         -    SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10)
           89  +    SELECT a FROM t1 WHERE (b=3 OR c>=10 OR c=4 OR b>10) ORDER BY a
    90     90     }
    91         -} {2 4 5 scan 0}
           91  +} {2 4 5 scan 0 sort 0}
    92     92   do_test where7-1.11 {
    93     93     count_steps {
    94         -    SELECT a FROM t1 WHERE (d=5 AND b=3) OR c==100;
           94  +    SELECT a FROM t1 WHERE (d=5 AND b=3) OR c==100 ORDER BY a;
    95     95     }
    96         -} {2 5 scan 0}
           96  +} {2 5 scan 0 sort 0}
    97     97   do_test where7-1.12 {
    98     98   breakpoint
    99     99     count_steps {
   100         -    SELECT a FROM t1 WHERE (b BETWEEN 2 AND 4) OR c=100
          100  +    SELECT a FROM t1 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
          101  +  }
          102  +} {1 2 3 5 scan 0 sort 0}
          103  +do_test where7-1.13 {
          104  +breakpoint
          105  +  count_steps {
          106  +    SELECT a FROM t1 WHERE (b BETWEEN 0 AND 2) OR (c BETWEEN 9 AND 999)
          107  +    ORDER BY a DESC
   101    108     }
   102         -} {1 2 3 5 scan 0}
          109  +} {5 4 1 scan 0 sort 1}
   103    110   
   104    111   
   105    112   finish_test