SQLite

Check-in [ca2a5a2c77]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ca2a5a2c770fa94cd8db1b1b241ede058a7c58e2
User & Date: drh 2014-07-22 14:58:12.596
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: 48e3780295 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: ca2a5a2c77 user: drh tags: trunk)
14:42
Expire prepared statements after running ANALYZE. (check-in: b083a961f8 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/analyze.c.
1424
1425
1426
1427
1428
1429
1430
1431

1432
1433
1434
1435
1436
1437
1438


1439
1440
1441
1442
1443
1444
1445
    if( *z==' ' ) z++;
  }
#ifndef SQLITE_ENABLE_STAT3_OR_STAT4
  assert( pIndex!=0 );
#else
  if( pIndex )
#endif
  {

    if( strcmp(z, "unordered")==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
      int v32 = 0;
      sqlite3GetInt32(z+3, &v32);
      pIndex->szIdxRow = sqlite3LogEst(v32);
    }


  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  
**







<
>
|






>
>







1424
1425
1426
1427
1428
1429
1430

1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
    if( *z==' ' ) z++;
  }
#ifndef SQLITE_ENABLE_STAT3_OR_STAT4
  assert( pIndex!=0 );
#else
  if( pIndex )
#endif

  while( z[0] ){
    if( sqlite3_strglob("unordered*", z)==0 ){
      pIndex->bUnordered = 1;
    }else if( sqlite3_strglob("sz=[0-9]*", z)==0 ){
      int v32 = 0;
      sqlite3GetInt32(z+3, &v32);
      pIndex->szIdxRow = sqlite3LogEst(v32);
    }
    while( z[0]!=0 && z[0]!=' ' ) z++;
    while( z[0]==' ' ) z++;
  }
}

/*
** This callback is invoked once for each index when reading the
** sqlite_stat1 table.  
**
1472
1473
1474
1475
1476
1477
1478

1479
1480
1481
1482
1483
1484
1485
    pIndex = sqlite3PrimaryKeyIndex(pTable);
  }else{
    pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
  }
  z = argv[2];

  if( pIndex ){

    decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex);
    if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
  }else{
    Index fakeIdx;
    fakeIdx.szIdxRow = pTable->szTabRow;
    decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
    pTable->szTabRow = fakeIdx.szIdxRow;







>







1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
    pIndex = sqlite3PrimaryKeyIndex(pTable);
  }else{
    pIndex = sqlite3FindIndex(pInfo->db, argv[1], pInfo->zDatabase);
  }
  z = argv[2];

  if( pIndex ){
    pIndex->bUnordered = 0;
    decodeIntArray((char*)z, pIndex->nKeyCol+1, 0, pIndex->aiRowLogEst, pIndex);
    if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
  }else{
    Index fakeIdx;
    fakeIdx.szIdxRow = pTable->szTabRow;
    decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
    pTable->szTabRow = fakeIdx.szIdxRow;
Changes to src/vdbe.c.
5790
5791
5792
5793
5794
5795
5796
5797
5798
5799


5800
5801
5802
5803
5804
5805
5806
5807
5808
5809
  }
  break;
}
#endif

/* Opcode: Expire P1 * * * *
**
** Cause precompiled statements to become expired. An expired statement
** fails with an error code of SQLITE_SCHEMA if it is ever executed 
** (via sqlite3_step()).


** 
** If P1 is 0, then all SQL statements become expired. If P1 is non-zero,
** then only the currently executing statement is affected. 
*/
case OP_Expire: {
  if( !pOp->p1 ){
    sqlite3ExpirePreparedStatements(db);
  }else{
    p->expired = 1;
  }







|
<
|
>
>


|







5790
5791
5792
5793
5794
5795
5796
5797

5798
5799
5800
5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
  }
  break;
}
#endif

/* Opcode: Expire P1 * * * *
**
** Cause precompiled statements to expire.  When an expired statement

** is executed using sqlite3_step() it will either automatically
** reprepare itself (if it was originally created using sqlite3_prepare_v2())
** or it will fail with SQLITE_SCHEMA.
** 
** If P1 is 0, then all SQL statements become expired. If P1 is non-zero,
** then only the currently executing statement is expired.
*/
case OP_Expire: {
  if( !pOp->p1 ){
    sqlite3ExpirePreparedStatements(db);
  }else{
    p->expired = 1;
  }
Changes to test/analyze9.test.
1085
1086
1087
1088
1089
1090
1091
1092
1093
  3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
} {
  do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}

finish_test









<
<
1085
1086
1087
1088
1089
1090
1091


  3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
} {
  do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
}

finish_test


Added test/analyzeC.test.














































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
# 2014-07-22
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
# This file contains automated tests used to verify that the text terms
# at the end of sqlite_stat1.stat are processed correctly.
#
#  (1) "unordered" means that the index cannot be used for ORDER BY
#      or for range queries
#
#  (2) "sz=NNN" sets the relative size of the index entries
#
#  (3) All other fields are silently ignored
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix analyzeC

# Baseline case.  Range queries work OK.  Indexes can be used for
# ORDER BY.
#
do_execsql_test 1.0 {
  CREATE TABLE t1(a,b,c);
  INSERT INTO t1(a,b,c)
    VALUES(1,2,3),(7,8,9),(4,5,6),(10,11,12),(4,8,12),(1,11,111);
  CREATE INDEX t1a ON t1(a);
  CREATE INDEX t1b ON t1(b);
  ANALYZE;
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1a','12345 2'),('t1','t1b','12345 4');
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {4 5 6 # 7 8 9 # 4 8 12 #}
do_execsql_test 1.1 {
  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {/.* USING INDEX t1a .a>. AND a<...*/}
do_execsql_test 1.2 {
  SELECT c FROM t1 ORDER BY a;
} {3 111 6 12 9 12}
do_execsql_test 1.3 {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {/.*SCAN TABLE t1 USING INDEX t1a.*/}
do_execsql_test 1.3x {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {~/.*B-TREE FOR ORDER BY.*/}

# Now mark the t1a index as "unordered".  Range queries and ORDER BY no
# longer use the index, but equality queries do.
#
do_execsql_test 2.0 {
  UPDATE sqlite_stat1 SET stat='12345 2 unordered' WHERE idx='t1a';
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {4 5 6 # 7 8 9 # 4 8 12 #}
do_execsql_test 2.1 {
  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {~/.*USING INDEX.*/}
do_execsql_test 2.2 {
  SELECT c FROM t1 ORDER BY a;
} {3 111 6 12 9 12}
do_execsql_test 2.3 {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {~/.*USING INDEX.*/}
do_execsql_test 2.3x {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {/.*B-TREE FOR ORDER BY.*/}

# Ignore extraneous text parameters in the sqlite_stat1.stat field.
#
do_execsql_test 3.0 {
  UPDATE sqlite_stat1 SET stat='12345 2 whatever=5 unordered xyzzy=11'
   WHERE idx='t1a';
  ANALYZE sqlite_master;
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {4 5 6 # 7 8 9 # 4 8 12 #}
do_execsql_test 3.1 {
  EXPLAIN QUERY PLAN
  SELECT *, '#' FROM t1 WHERE a BETWEEN 3 AND 8 ORDER BY c;
} {~/.*USING INDEX.*/}
do_execsql_test 3.2 {
  SELECT c FROM t1 ORDER BY a;
} {3 111 6 12 9 12}
do_execsql_test 3.3 {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {~/.*USING INDEX.*/}
do_execsql_test 3.3x {
  EXPLAIN QUERY PLAN
  SELECT c FROM t1 ORDER BY a;
} {/.*B-TREE FOR ORDER BY.*/}

# The sz=NNN parameter determines which index to scan
#
do_execsql_test 4.0 {
  DROP INDEX t1a;
  CREATE INDEX t1ab ON t1(a,b);
  CREATE INDEX t1ca ON t1(c,a);
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 sz=10'),('t1','t1ca','12345 3 2 sz=20');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;
} {6}
do_execsql_test 4.1 {
  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;
} {/.*INDEX t1ab.*/}
do_execsql_test 4.2 {
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 sz=20'),('t1','t1ca','12345 3 2 sz=10');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;
} {6}
do_execsql_test 4.3 {
  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;
} {/.*INDEX t1ca.*/}


# The sz=NNN parameter works even if there is other extraneous text
# in the sqlite_stat1.stat column.
#
do_execsql_test 5.0 {
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ab','12345 3 2 x=5 sz=10 y=10'),
          ('t1','t1ca','12345 3 2 whatever sz=20 junk');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;
} {6}
do_execsql_test 5.1 {
  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;
} {/.*INDEX t1ab.*/}
do_execsql_test 5.2 {
  DELETE FROM sqlite_stat1;
  INSERT INTO sqlite_stat1(tbl,idx,stat)
    VALUES('t1','t1ca','12345 3 2 x=5 sz=10 y=10'),
          ('t1','t1ab','12345 3 2 whatever sz=20 junk');
  ANALYZE sqlite_master;
  SELECT count(a) FROM t1;
} {6}
do_execsql_test 5.3 {
  EXPLAIN QUERY PLAN
  SELECT count(a) FROM t1;
} {/.*INDEX t1ca.*/}




finish_test