/ Check-in [2a417367]
Login
Home Timeline Branches Tags Tickets Wiki More...

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

Overview
Comment:If there is data in both the sqlite_stat4 and sqlite_stat3 tables for a single index, ignore the sqlite_stat3 records.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sqlite_stat4
Files: files | file ages | folders
SHA1: 2a41736728d83a777ea8112da927cb047ec6684e
User & Date: dan 2013-08-12 17:31:32
Context
2013-08-12
20:14
If ENABLE_STAT3 is defined but ENABLE_STAT4 is not, have ANALYZE create and populate the sqlite_stat3 table instead of sqlite_stat4. (check-in: cca8bf43 user: dan tags: sqlite_stat4)
17:31
If there is data in both the sqlite_stat4 and sqlite_stat3 tables for a single index, ignore the sqlite_stat3 records. (check-in: 2a417367 user: dan tags: sqlite_stat4)
17:00
Handle a NULL input to decodeIntArray() that can result from a prior OOM. (check-in: fa1588ad user: drh tags: sqlite_stat4)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/analyze.c. 

1311
1312
1313
1314
1315
1316
1317
1318
1319


1320
1321
1322
1323
1324
1325
1326
....
1364
1365
1366
1367
1368
1369
1370



1371
1372
1373
1374
1375
1376
1377

    int i;          /* Bytes of space required */
    tRowcnt *pSpace;

    zIndex = (char *)sqlite3_column_text(pStmt, 0);
    if( zIndex==0 ) continue;
    nSample = sqlite3_column_int(pStmt, 1);
    pIdx = sqlite3FindIndex(db, zIndex, zDb);
    if( pIdx==0 ) continue;
    assert( pIdx->nSample==0 );


    if( bStat3==0 ){
      nIdxCol = pIdx->nColumn+1;
      nAvgCol = pIdx->nColumn;
    }
    pIdx->nSample = nSample;
    nByte = sizeof(IndexSample) * nSample;
    nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
................................................................................
    if( pIdx==pPrevIdx ){
      idx++;
    }else{
      pPrevIdx = pIdx;
      idx = 0;
    }
    assert( idx<pIdx->nSample );



    pSample = &pIdx->aSample[idx];

    if( bStat3==0 ){
      nCol = pIdx->nColumn+1;
    }
    decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0);
    decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0);








|
|
>
>







 







>
>
>








1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
....
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382

    int i;          /* Bytes of space required */
    tRowcnt *pSpace;

    zIndex = (char *)sqlite3_column_text(pStmt, 0);
    if( zIndex==0 ) continue;
    nSample = sqlite3_column_int(pStmt, 1);
    pIdx = sqlite3FindIndex(db, zIndex, zDb);
    assert( pIdx==0 || bStat3 || pIdx->nSample==0 );
    /* Index.nSample is non-zero at this point if data has already been
    ** loaded from the stat4 table. In this case ignore stat3 data.  */
    if( pIdx==0 || pIdx->nSample ) continue;
    if( bStat3==0 ){
      nIdxCol = pIdx->nColumn+1;
      nAvgCol = pIdx->nColumn;
    }
    pIdx->nSample = nSample;
    nByte = sizeof(IndexSample) * nSample;
    nByte += sizeof(tRowcnt) * nIdxCol * 3 * nSample;
................................................................................
    if( pIdx==pPrevIdx ){
      idx++;
    }else{
      pPrevIdx = pIdx;
      idx = 0;
    }
    assert( idx<pIdx->nSample );
    /* This next condition is true if data has already been loaded from 
    ** the sqlite_stat4 table. In this case ignore stat3 data.  */
    if( bStat3 && pIdx->aSample[idx].anEq[0] ) continue;
    pSample = &pIdx->aSample[idx];

    if( bStat3==0 ){
      nCol = pIdx->nColumn+1;
    }
    decodeIntArray((char*)sqlite3_column_text(pStmt,1), nCol, pSample->anEq, 0);
    decodeIntArray((char*)sqlite3_column_text(pStmt,2), nCol, pSample->anLt, 0);

Changes to test/analyzeA.test. 

19
20
21
22
23
24
25


26
27
28
29
30
31
32
33
..
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
..
73
74
75
76
77
78
79
80




81
82
83
84
85
86
87
...
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

set testprefix analyzeA

ifcapable !stat4&&!stat3 {
  finish_test
  return
}



proc populate_stat3 {} {
  # Open a second connection on database "test.db" and run ANALYZE. If this
  # is an ENABLE_STAT3 build, this is all that is required to create and
  # populate the sqlite_stat3 table. 
  # 
  sqlite3 db2 test.db
  execsql { ANALYZE }

................................................................................
  ifcapable stat4 {
    db2 func lindex lindex
    execsql {
      PRAGMA writable_schema = on;
      CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
      INSERT INTO sqlite_stat3 
      SELECT DISTINCT tbl, idx, 
      lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0)
      FROM sqlite_stat4;

      DROP TABLE sqlite_stat4;
      PRAGMA writable_schema = off;
    } db2
  }

  # Modify the database schema cookie to ensure that the other connection
  # reloads the schema.
  #
  execsql {
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2
  db2 close
}

























proc populate_stat4 {} {
  execsql { ANALYZE }
#  ifcapable stat3 {
#    execsql {
#      PRAGMA writable_schema = on;
#      CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
#      INSERT INTO sqlite_stat4 SELECT 
................................................................................
#      DROP TABLE sqlite_stat4;
#      PRAGMA writable_schema = off;
#      ANALYZE sqlite_master;
#    }
#  }
}

foreach {tn analyze_cmd} {1 populate_stat4 2 populate_stat3} {




  reset_db
  do_test 1.$tn.1 {
    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
    for {set i 0} {$i < 100} {incr i} {
      set c [expr int(pow(1.1,$i)/100)]
      set b [expr 125 - int(pow(1.1,99-$i))/100]
      execsql {INSERT INTO t1 VALUES($i, $b, $c)}
................................................................................

  do_execsql_test 1.$tn.3.1 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50
  } {6}
  do_execsql_test 1.$tn.3.2 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50
  } {90}

  do_execsql_test 1.$tn.3.3 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125
  } {90}
  do_execsql_test 1.$tn.3.4 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125
  } {6}









>
>
|







 







|

>
|
|
<












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







 







|
>
>
>
>







 







<








19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
..
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
..
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
...
135
136
137
138
139
140
141

142
143
144
145
146
147
148

set testprefix analyzeA

ifcapable !stat4&&!stat3 {
  finish_test
  return
}

# Populate the stat3 table according to the current contents of the db
#
proc populate_stat3 {{bDropTable 1}} {
  # Open a second connection on database "test.db" and run ANALYZE. If this
  # is an ENABLE_STAT3 build, this is all that is required to create and
  # populate the sqlite_stat3 table. 
  # 
  sqlite3 db2 test.db
  execsql { ANALYZE }

................................................................................
  ifcapable stat4 {
    db2 func lindex lindex
    execsql {
      PRAGMA writable_schema = on;
      CREATE TABLE sqlite_stat3(tbl,idx,neq,nlt,ndlt,sample);
      INSERT INTO sqlite_stat3 
      SELECT DISTINCT tbl, idx, 
        lindex(neq,0), lindex(nlt,0), lindex(ndlt,0), test_extract(sample, 0)
      FROM sqlite_stat4;
    } db2
    if {$bDropTable} { execsql {DROP TABLE sqlite_stat4} db2 }
    execsql { PRAGMA writable_schema = off }

  }

  # Modify the database schema cookie to ensure that the other connection
  # reloads the schema.
  #
  execsql {
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2
  db2 close
}

# Populate the stat4 table according to the current contents of the db.
# Leave deceptive data in the stat3 table. This data should be ignored
# in favour of that from the stat4 table.
#
proc populate_both {} {
  populate_stat3 0

  sqlite3 db2 test.db
  execsql {
    PRAGMA writable_schema = on;
    UPDATE sqlite_stat3 SET idx = 
      CASE idx WHEN 't1b' THEN 't1c' ELSE 't1b'
    END;
    PRAGMA writable_schema = off;
    CREATE TABLE obscure_tbl_nm(x);
    DROP TABLE obscure_tbl_nm;
  } db2

  db2 close
}


# Populate the stat4 table according to the current contents of the db
#
proc populate_stat4 {} {
  execsql { ANALYZE }
#  ifcapable stat3 {
#    execsql {
#      PRAGMA writable_schema = on;
#      CREATE TABLE sqlite_stat4(tbl,idx,neq,nlt,ndlt,sample);
#      INSERT INTO sqlite_stat4 SELECT 
................................................................................
#      DROP TABLE sqlite_stat4;
#      PRAGMA writable_schema = off;
#      ANALYZE sqlite_master;
#    }
#  }
}

foreach {tn analyze_cmd} {
  1 populate_stat4 
  2 populate_stat3
  3 populate_both
} {
  reset_db
  do_test 1.$tn.1 {
    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
    for {set i 0} {$i < 100} {incr i} {
      set c [expr int(pow(1.1,$i)/100)]
      set b [expr 125 - int(pow(1.1,99-$i))/100]
      execsql {INSERT INTO t1 VALUES($i, $b, $c)}
................................................................................

  do_execsql_test 1.$tn.3.1 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50
  } {6}
  do_execsql_test 1.$tn.3.2 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50
  } {90}

  do_execsql_test 1.$tn.3.3 { 
    SELECT count(*) FROM t1 WHERE b BETWEEN 75 AND 125
  } {90}
  do_execsql_test 1.$tn.3.4 { 
    SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125
  } {6}

This page was generated in about 0.008s by Fossil 2.11 [4dbd4e33c4] 2020-02-16 15:25:00