/ Check-in [ca2a5a2c]
Login

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

Overview
Comment:Enhance the sqlite_stat1.stat parsing to allow additional text parameters at the end. Unrecognized parameters are silently ignored.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ca2a5a2c770fa94cd8db1b1b241ede058a7c58e2
User & Date: drh 2014-07-22 14:58:12
Context
2014-07-22
15:33
Correction: The maximum SQLITE_MAX_ATTACHED value to avoid overflowing a signed 8-bit integer is 125, not 127. check-in: 48e37802 user: drh tags: trunk
14:58
Enhance the sqlite_stat1.stat parsing to allow additional text parameters at the end. Unrecognized parameters are silently ignored. check-in: ca2a5a2c user: drh tags: trunk
14:42
Expire prepared statements after running ANALYZE. check-in: b083a961 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/analyze.c.

  1424   1424       if( *z==' ' ) z++;
  1425   1425     }
  1426   1426   #ifndef SQLITE_ENABLE_STAT3_OR_STAT4
  1427   1427     assert( pIndex!=0 );
  1428   1428   #else
  1429   1429     if( pIndex )
  1430   1430   #endif
  1431         -  {
  1432         -    if( strcmp(z, "unordered")==0 ){
         1431  +  while( z[0] ){
         1432  +    if( sqlite3_strglob("unordered*", z)==0 ){
  1433   1433         pIndex->bUnordered = 1;
  1434   1434       }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
  1435   1435         int v32 = 0;
  1436   1436         sqlite3GetInt32(z+3, &v32);
  1437   1437         pIndex->szIdxRow = sqlite3LogEst(v32);
  1438   1438       }
         1439  +    while( z[0]!=0 && z[0]!=' ' ) z++;
         1440  +    while( z[0]==' ' ) z++;
  1439   1441     }
  1440   1442   }
  1441   1443   
  1442   1444   /*
  1443   1445   ** This callback is invoked once for each index when reading the
  1444   1446   ** sqlite_stat1 table.  
  1445   1447   **
................................................................................
  1472   1474       pIndex = sqlite3PrimaryKeyIndex(pTable);
  1473   1475     }else{
  1474   1476       pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
  1475   1477     }
  1476   1478     z = argv[2];
  1477   1479   
  1478   1480     if( pIndex ){
         1481  +    pIndex->bUnordered = 0;
  1479   1482       decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex);
  1480   1483       if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
  1481   1484     }else{
  1482   1485       Index fakeIdx;
  1483   1486       fakeIdx.szIdxRow = pTable->szTabRow;
  1484   1487       decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
  1485   1488       pTable->szTabRow = fakeIdx.szIdxRow;

Changes to src/vdbe.c.

  5790   5790     }
  5791   5791     break;
  5792   5792   }
  5793   5793   #endif
  5794   5794   
  5795   5795   /* Opcode: Expire P1 * * * *
  5796   5796   **
  5797         -** Cause precompiled statements to become expired. An expired statement
  5798         -** fails with an error code of SQLITE_SCHEMA if it is ever executed 
  5799         -** (via sqlite3_step()).
         5797  +** Cause precompiled statements to expire.  When an expired statement
         5798  +** is executed using sqlite3_step() it will either automatically
         5799  +** reprepare itself (if it was originally created using sqlite3_prepare_v2())
         5800  +** or it will fail with SQLITE_SCHEMA.
  5800   5801   ** 
  5801   5802   ** If P1 is 0, then all SQL statements become expired. If P1 is non-zero,
  5802         -** then only the currently executing statement is affected. 
         5803  +** then only the currently executing statement is expired.
  5803   5804   */
  5804   5805   case OP_Expire: {
  5805   5806     if( !pOp->p1 ){
  5806   5807       sqlite3ExpirePreparedStatements(db);
  5807   5808     }else{
  5808   5809       p->expired = 1;
  5809   5810     }

Changes to test/analyze9.test.

  1085   1085     3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  1086   1086     4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
  1087   1087   } {
  1088   1088     do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
  1089   1089   }
  1090   1090   
  1091   1091   finish_test
  1092         -
  1093         -

Added test/analyzeC.test.

            1  +# 2014-07-22
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# This file contains automated tests used to verify that the text terms
           13  +# at the end of sqlite_stat1.stat are processed correctly.
           14  +#
           15  +#  (1) "unordered" means that the index cannot be used for ORDER BY
           16  +#      or for range queries
           17  +#
           18  +#  (2) "sz=NNN" sets the relative size of the index entries
           19  +#
           20  +#  (3) All other fields are silently ignored
           21  +#
           22  +
           23  +set testdir [file dirname $argv0]
           24  +source $testdir/tester.tcl
           25  +set testprefix analyzeC
           26  +
           27  +# Baseline case.  Range queries work OK.  Indexes can be used for
           28  +# ORDER BY.
           29  +#
           30  +do_execsql_test 1.0 {
           31  +  CREATE TABLE t1(a,b,c);
           32  +  INSERT INTO t1(a,b,c)
           33  +    VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111);
           34  +  CREATE INDEX t1a ON t1(a);
           35  +  CREATE INDEX t1b ON t1(b);
           36  +  ANALYZE;
           37  +  DELETE FROM sqlite_stat1;
           38  +  INSERT INTO sqlite_stat1(tbl,idx,stat)
           39  +    VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4');
           40  +  ANALYZE sqlite_master;
           41  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           42  +} {4 5 6 # 7 8 9 # 4 8 12 #}
           43  +do_execsql_test 1.1 {
           44  +  EXPLAIN QUERY PLAN
           45  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           46  +} {/.* USING INDEX t1a .a>. AND a<...*/}
           47  +do_execsql_test 1.2 {
           48  +  SELECT c FROM t1 ORDER BY a;
           49  +} {3 111 6 12 9 12}
           50  +do_execsql_test 1.3 {
           51  +  EXPLAIN QUERY PLAN
           52  +  SELECT c FROM t1 ORDER BY a;
           53  +} {/.*SCAN TABLE t1 USING INDEX t1a.*/}
           54  +do_execsql_test 1.3x {
           55  +  EXPLAIN QUERY PLAN
           56  +  SELECT c FROM t1 ORDER BY a;
           57  +} {~/.*B-TREE FOR ORDER BY.*/}
           58  +
           59  +# Now mark the t1a index as "unordered".  Range queries and ORDER BY no
           60  +# longer use the index, but equality queries do.
           61  +#
           62  +do_execsql_test 2.0 {
           63  +  UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a';
           64  +  ANALYZE sqlite_master;
           65  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           66  +} {4 5 6 # 7 8 9 # 4 8 12 #}
           67  +do_execsql_test 2.1 {
           68  +  EXPLAIN QUERY PLAN
           69  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           70  +} {~/.*USING INDEX.*/}
           71  +do_execsql_test 2.2 {
           72  +  SELECT c FROM t1 ORDER BY a;
           73  +} {3 111 6 12 9 12}
           74  +do_execsql_test 2.3 {
           75  +  EXPLAIN QUERY PLAN
           76  +  SELECT c FROM t1 ORDER BY a;
           77  +} {~/.*USING INDEX.*/}
           78  +do_execsql_test 2.3x {
           79  +  EXPLAIN QUERY PLAN
           80  +  SELECT c FROM t1 ORDER BY a;
           81  +} {/.*B-TREE FOR ORDER BY.*/}
           82  +
           83  +# Ignore extraneous text parameters in the sqlite_stat1.stat field.
           84  +#
           85  +do_execsql_test 3.0 {
           86  +  UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11'
           87  +   WHERE idx='t1a';
           88  +  ANALYZE sqlite_master;
           89  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           90  +} {4 5 6 # 7 8 9 # 4 8 12 #}
           91  +do_execsql_test 3.1 {
           92  +  EXPLAIN QUERY PLAN
           93  +  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
           94  +} {~/.*USING INDEX.*/}
           95  +do_execsql_test 3.2 {
           96  +  SELECT c FROM t1 ORDER BY a;
           97  +} {3 111 6 12 9 12}
           98  +do_execsql_test 3.3 {
           99  +  EXPLAIN QUERY PLAN
          100  +  SELECT c FROM t1 ORDER BY a;
          101  +} {~/.*USING INDEX.*/}
          102  +do_execsql_test 3.3x {
          103  +  EXPLAIN QUERY PLAN
          104  +  SELECT c FROM t1 ORDER BY a;
          105  +} {/.*B-TREE FOR ORDER BY.*/}
          106  +
          107  +# The sz=NNN parameter determines which index to scan
          108  +#
          109  +do_execsql_test 4.0 {
          110  +  DROP INDEX t1a;
          111  +  CREATE INDEX t1ab ON t1(a,b);
          112  +  CREATE INDEX t1ca ON t1(c,a);
          113  +  DELETE FROM sqlite_stat1;
          114  +  INSERT INTO sqlite_stat1(tbl,idx,stat)
          115  +    VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20');
          116  +  ANALYZE sqlite_master;
          117  +  SELECT count(a) FROM t1;
          118  +} {6}
          119  +do_execsql_test 4.1 {
          120  +  EXPLAIN QUERY PLAN
          121  +  SELECT count(a) FROM t1;
          122  +} {/.*INDEX t1ab.*/}
          123  +do_execsql_test 4.2 {
          124  +  DELETE FROM sqlite_stat1;
          125  +  INSERT INTO sqlite_stat1(tbl,idx,stat)
          126  +    VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10');
          127  +  ANALYZE sqlite_master;
          128  +  SELECT count(a) FROM t1;
          129  +} {6}
          130  +do_execsql_test 4.3 {
          131  +  EXPLAIN QUERY PLAN
          132  +  SELECT count(a) FROM t1;
          133  +} {/.*INDEX t1ca.*/}
          134  +
          135  +
          136  +# The sz=NNN parameter works even if there is other extraneous text
          137  +# in the sqlite_stat1.stat column.
          138  +#
          139  +do_execsql_test 5.0 {
          140  +  DELETE FROM sqlite_stat1;
          141  +  INSERT INTO sqlite_stat1(tbl,idx,stat)
          142  +    VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'),
          143  +          ('t1','t1ca','12345 3 2 whatever sz=20 junk');
          144  +  ANALYZE sqlite_master;
          145  +  SELECT count(a) FROM t1;
          146  +} {6}
          147  +do_execsql_test 5.1 {
          148  +  EXPLAIN QUERY PLAN
          149  +  SELECT count(a) FROM t1;
          150  +} {/.*INDEX t1ab.*/}
          151  +do_execsql_test 5.2 {
          152  +  DELETE FROM sqlite_stat1;
          153  +  INSERT INTO sqlite_stat1(tbl,idx,stat)
          154  +    VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'),
          155  +          ('t1','t1ab','12345 3 2 whatever sz=20 junk');
          156  +  ANALYZE sqlite_master;
          157  +  SELECT count(a) FROM t1;
          158  +} {6}
          159  +do_execsql_test 5.3 {
          160  +  EXPLAIN QUERY PLAN
          161  +  SELECT count(a) FROM t1;
          162  +} {/.*INDEX t1ca.*/}
          163  +
          164  +
          165  +
          166  +
          167  +finish_test