/ Check-in [20eeee4c]
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:Import recent bug fixes from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sessions
Files: files | file ages | folders
SHA1: 20eeee4cd34a9bffe6bf65962126ddf8ca04eb3e
User & Date: drh 2013-11-12 15:39:01
Context
2013-11-14
19:18
Merge the skip-scan optimization into the sessions branch. check-in: 7596d1bf user: drh tags: sessions
2013-11-12
15:39
Import recent bug fixes from trunk. check-in: 20eeee4c user: drh tags: sessions
15:33
A better (simpler) fix to the count(*) problem addressed in the previous check-in. check-in: 0f924c6e user: drh tags: trunk
2013-11-11
23:02
Fix two test cases (due to changes in the formatting of constraint errors) so that all tests now pass. check-in: 129e2b69 user: drh tags: sessions
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/misc/spellfix.c.

  2668   2668       if( zWord==0 ){
  2669   2669         /* Inserts of the form:  INSERT INTO table(command) VALUES('xyzzy');
  2670   2670         ** cause zWord to be NULL, so we look at the "command" column to see
  2671   2671         ** what special actions to take */
  2672   2672         const char *zCmd = 
  2673   2673            (const char*)sqlite3_value_text(argv[SPELLFIX_COL_COMMAND+2]);
  2674   2674         if( zCmd==0 ){
  2675         -        pVTab->zErrMsg = sqlite3_mprintf("%s.word may not be NULL",
         2675  +        pVTab->zErrMsg = sqlite3_mprintf("NOT NULL constraint failed: %s.word",
  2676   2676                                            p->zTableName);
  2677   2677           return SQLITE_CONSTRAINT_NOTNULL;
  2678   2678         }
  2679   2679         if( strcmp(zCmd,"reset")==0 ){
  2680   2680           /* Reset the  edit cost table (if there is one). */
  2681   2681           editDist3ConfigDelete(p->pConfig3);
  2682   2682           p->pConfig3 = 0;

Changes to ext/rtree/rtreeC.test.

   103    103   
   104    104   do_eqp_test 2.5 {
   105    105     SELECT * FROM t, r_tree
   106    106   } {
   107    107     0 0 1 {SCAN TABLE r_tree VIRTUAL TABLE INDEX 2:}
   108    108     0 1 0 {SCAN TABLE t} 
   109    109   }
          110  +
          111  +#-------------------------------------------------------------------------
          112  +# Test that the special CROSS JOIN handling works with rtree tables.
          113  +#
          114  +do_execsql_test 3.1 {
          115  +  CREATE TABLE t1(x);
          116  +  CREATE TABLE t2(y);
          117  +  CREATE VIRTUAL TABLE t3 USING rtree(z, x1,x2, y1,y2);
          118  +}
          119  +
          120  +do_eqp_test 3.2.1 { SELECT * FROM t1 CROSS JOIN t2 } {
          121  +  0 0 0 {SCAN TABLE t1} 
          122  +  0 1 1 {SCAN TABLE t2}
          123  +}
          124  +do_eqp_test 3.2.2 { SELECT * FROM t2 CROSS JOIN t1 } {
          125  +  0 0 0 {SCAN TABLE t2} 0 1 1 {SCAN TABLE t1}
          126  +}
          127  +
          128  +do_eqp_test 3.3.1 { SELECT * FROM t1 CROSS JOIN t3 } {
          129  +  0 0 0 {SCAN TABLE t1}
          130  +  0 1 1 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
          131  +}
          132  +do_eqp_test 3.3.2 { SELECT * FROM t3 CROSS JOIN t1 } {
          133  +  0 0 0 {SCAN TABLE t3 VIRTUAL TABLE INDEX 2:} 
          134  +  0 1 1 {SCAN TABLE t1}
          135  +}
          136  +
          137  +#--------------------------------------------------------------------
          138  +# Test that LEFT JOINs are not reordered if the right-hand-side is
          139  +# a virtual table.
          140  +#
          141  +reset_db
          142  +do_execsql_test 4.1 {
          143  +  CREATE TABLE t1(a);
          144  +  CREATE VIRTUAL TABLE t2 USING rtree(b, x1,x2);
          145  +
          146  +  INSERT INTO t1 VALUES(1);
          147  +  INSERT INTO t1 VALUES(2);
          148  +
          149  +  INSERT INTO t2 VALUES(1, 0.0, 0.1);
          150  +  INSERT INTO t2 VALUES(3, 0.0, 0.1);
          151  +}
          152  +
          153  +do_execsql_test 4.2 {
          154  +  SELECT a, b FROM t1 LEFT JOIN t2 ON (+a = +b);
          155  +} {1 1 2 {}}
          156  +
          157  +do_execsql_test 4.3 {
          158  +  SELECT b, a FROM t2 LEFT JOIN t1 ON (+a = +b);
          159  +} {1 1 3 {}}
   110    160   
   111    161   finish_test
   112    162   

Changes to src/select.c.

  4666   4666           sqlite3CodeVerifySchema(pParse, iDb);
  4667   4667           sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  4668   4668   
  4669   4669           /* Search for the index that has the lowest scan cost.
  4670   4670           **
  4671   4671           ** (2011-04-15) Do not do a full scan of an unordered index.
  4672   4672           **
  4673         -        ** (2013-10-03) Do not count the entires in a partial index.
         4673  +        ** (2013-10-03) Do not count the entries in a partial index.
  4674   4674           **
  4675   4675           ** In practice the KeyInfo structure will not be used. It is only 
  4676   4676           ** passed to keep OP_OpenRead happy.
  4677   4677           */
         4678  +        if( !HasRowid(pTab) ) pBest = sqlite3PrimaryKeyIndex(pTab);
  4678   4679           for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  4679   4680             if( pIdx->bUnordered==0
  4680   4681              && pIdx->szIdxRow<pTab->szTabRow
  4681   4682              && pIdx->pPartIdxWhere==0
  4682   4683              && (!pBest || pIdx->szIdxRow<pBest->szIdxRow)
  4683   4684             ){
  4684   4685               pBest = pIdx;

Changes to src/sqlite.h.in.

  5327   5327       unsigned char omit;      /* Do not code a test for this constraint */
  5328   5328     } *aConstraintUsage;
  5329   5329     int idxNum;                /* Number used to identify the index */
  5330   5330     char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
  5331   5331     int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
  5332   5332     int orderByConsumed;       /* True if output is already ordered */
  5333   5333     double estimatedCost;           /* Estimated cost of using this index */
         5334  +  /* Fields below are only available in SQLite 3.8.2 and later */
  5334   5335     sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
  5335   5336   };
  5336   5337   
  5337   5338   /*
  5338   5339   ** CAPI3REF: Virtual Table Constraint Operator Codes
  5339   5340   **
  5340   5341   ** These macros defined the allowed values for the

Changes to src/where.c.

  4698   4698   
  4699   4699   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4700   4700   /*
  4701   4701   ** Add all WhereLoop objects for a table of the join identified by
  4702   4702   ** pBuilder->pNew->iTab.  That table is guaranteed to be a virtual table.
  4703   4703   */
  4704   4704   static int whereLoopAddVirtual(
  4705         -  WhereLoopBuilder *pBuilder   /* WHERE clause information */
         4705  +  WhereLoopBuilder *pBuilder,  /* WHERE clause information */
         4706  +  Bitmask mExtra
  4706   4707   ){
  4707   4708     WhereInfo *pWInfo;           /* WHERE analysis context */
  4708   4709     Parse *pParse;               /* The parsing context */
  4709   4710     WhereClause *pWC;            /* The WHERE clause */
  4710   4711     struct SrcList_item *pSrc;   /* The FROM clause term to search */
  4711   4712     Table *pTab;
  4712   4713     sqlite3 *db;
................................................................................
  4788   4789       pIdxInfo->needToFreeIdxStr = 0;
  4789   4790       pIdxInfo->orderByConsumed = 0;
  4790   4791       pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
  4791   4792       pIdxInfo->estimatedRows = 25;
  4792   4793       rc = vtabBestIndex(pParse, pTab, pIdxInfo);
  4793   4794       if( rc ) goto whereLoopAddVtab_exit;
  4794   4795       pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
  4795         -    pNew->prereq = 0;
         4796  +    pNew->prereq = mExtra;
  4796   4797       mxTerm = -1;
  4797   4798       assert( pNew->nLSlot>=nConstraint );
  4798   4799       for(i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
  4799   4800       pNew->u.vtab.omitMask = 0;
  4800   4801       for(i=0; i<nConstraint; i++, pIdxCons++){
  4801   4802         if( (iTerm = pUsage[i].argvIndex - 1)>=0 ){
  4802   4803           j = pIdxCons->iTermOffset;
................................................................................
  4915   4916             sSubBuild.pWC = &tempWC;
  4916   4917           }else{
  4917   4918             continue;
  4918   4919           }
  4919   4920           sCur.n = 0;
  4920   4921   #ifndef SQLITE_OMIT_VIRTUALTABLE
  4921   4922           if( IsVirtual(pItem->pTab) ){
  4922         -          rc = whereLoopAddVirtual(&sSubBuild);
  4923         -          for(i=0; i<sCur.n; i++) sCur.a[i].prereq |= mExtra;
         4923  +          rc = whereLoopAddVirtual(&sSubBuild, mExtra);
  4924   4924           }else
  4925   4925   #endif
  4926   4926           {
  4927   4927             rc = whereLoopAddBtree(&sSubBuild, mExtra);
  4928   4928           }
  4929   4929           assert( rc==SQLITE_OK || sCur.n==0 );
  4930   4930           if( sCur.n==0 ){
................................................................................
  4986   4986       pNew->iTab = iTab;
  4987   4987       pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
  4988   4988       if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
  4989   4989         mExtra = mPrior;
  4990   4990       }
  4991   4991       priorJoinType = pItem->jointype;
  4992   4992       if( IsVirtual(pItem->pTab) ){
  4993         -      rc = whereLoopAddVirtual(pBuilder);
         4993  +      rc = whereLoopAddVirtual(pBuilder, mExtra);
  4994   4994       }else{
  4995   4995         rc = whereLoopAddBtree(pBuilder, mExtra);
  4996   4996       }
  4997   4997       if( rc==SQLITE_OK ){
  4998   4998         rc = whereLoopAddOr(pBuilder, mExtra);
  4999   4999       }
  5000   5000       mPrior |= pNew->maskSelf;

Changes to test/count.test.

     1         -# 2009 February 24
            1  +# 2009-02-24
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     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 "SELECT count(*)" statements.
    13     13   #
    14         -# $Id: count.test,v 1.6 2009/06/05 17:09:12 drh Exp $
    15     14   
    16     15   set testdir [file dirname $argv0]
    17     16   source $testdir/tester.tcl
    18     17   
    19     18   # Test plan:
    20     19   #
    21     20   #  count-0.*: Make sure count(*) works on an empty database.  (Ticket #3774)
    22     21   #
    23     22   #  count-1.*: Test that the OP_Count instruction appears to work on both
    24     23   #             tables and indexes. Test both when they contain 0 entries,
    25     24   #             when all entries are on the root page, and when the b-tree
    26     25   #             forms a structure 2 and 3 levels deep.
    27     26   #            
    28         -#  count-2.*: Test that 
    29         -#
    30     27   #
    31     28   
    32     29   do_test count-0.1 {
    33     30     db eval {
    34     31        SELECT count(*) FROM sqlite_master;
    35     32     }
    36     33   } {0}
................................................................................
   184    181     execsql {
   185    182       DROP INDEX t4i1;
   186    183       CREATE INDEX t4i1 ON t4(b, a);
   187    184       SELECT count(*) FROM t4;
   188    185     }
   189    186   } {1}
   190    187   
          188  +do_execsql_test count-5.1 {
          189  +  CREATE TABLE t5(a TEXT PRIMARY KEY, b VARCHAR(50)) WITHOUT ROWID;
          190  +  INSERT INTO t5 VALUES('bison','jazz');
          191  +  SELECT count(*) FROM t5;
          192  +} {1}
   191    193   
   192    194   finish_test

Changes to test/tester.tcl.

  1043   1043     #
  1044   1044     set R "\033\[31;1m"        ;# Red fg
  1045   1045     set G "\033\[32;1m"        ;# Green fg
  1046   1046     set B "\033\[34;1m"        ;# Red fg
  1047   1047     set D "\033\[39;0m"        ;# Default fg
  1048   1048     foreach opcode {
  1049   1049         Seek SeekGe SeekGt SeekLe SeekLt NotFound Last Rewind
  1050         -      NoConflict Next Prev
         1050  +      NoConflict Next Prev VNext VPrev VFilter
  1051   1051     } {
  1052   1052       set color($opcode) $B
  1053   1053     }
  1054   1054     foreach opcode {ResultRow} {
  1055   1055       set color($opcode) $G
  1056   1056     }
  1057   1057     foreach opcode {IdxInsert Insert Delete IdxDelete} {
................................................................................
  1064   1064       set op($addr) $opcode
  1065   1065   
  1066   1066       if {$opcode == "Goto" && ($bSeenGoto==0 || ($p2 > $addr+10))} {
  1067   1067         set linebreak($p2) 1
  1068   1068         set bSeenGoto 1
  1069   1069       }
  1070   1070   
  1071         -    if {$opcode == "Next" || $opcode=="Prev"} {
         1071  +    if {$opcode=="Next"  || $opcode=="Prev" 
         1072  +     || $opcode=="VNext" || $opcode=="VPrev"
         1073  +    } {
  1072   1074         for {set i $p2} {$i<$addr} {incr i} {
  1073   1075           incr x($i) 2
  1074   1076         }
  1075   1077       }
  1076   1078   
  1077   1079       if {$opcode == "Goto" && $p2<$addr && $op($p2)=="Yield"} {
  1078   1080         for {set i [expr $p2+1]} {$i<$addr} {incr i} {