SQLite

Check-in [9117477978]
Login

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

Overview
Comment:Make sure the count(*) optimization works correctly on WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 91174779786be07d63f3c4a5277602ddc5f0ba26
User & Date: drh 2013-11-12 14:55:40.075
Context
2013-11-12
15:33
A better (simpler) fix to the count(*) problem addressed in the previous check-in. (check-in: 0f924c6ef6 user: drh tags: trunk)
14:55
Make sure the count(*) optimization works correctly on WITHOUT ROWID tables. (check-in: 9117477978 user: drh tags: trunk)
12:30
Update test command [explain_i] to handle the opcodes used by virtual tables (VNext, VFilter etc.). (check-in: 1b215ee321 user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
4666
4667
4668
4669
4670
4671
4672
4673
4674
4675
4676
4677
4678
4679
4680
4681
4682
4683


4684
4685
4686
4687
4688
4689
4690

        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

        /* Search for the index that has the lowest scan cost.
        **
        ** (2011-04-15) Do not do a full scan of an unordered index.
        **
        ** (2013-10-03) Do not count the entires in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->szIdxRow<pTab->szTabRow
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->szIdxRow<pBest->szIdxRow)
          ){


            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3KeyInfoOfIndex(pParse, pBest);
        }








|










>
>








4666
4667
4668
4669
4670
4671
4672
4673
4674
4675
4676
4677
4678
4679
4680
4681
4682
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692

        sqlite3CodeVerifySchema(pParse, iDb);
        sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);

        /* Search for the index that has the lowest scan cost.
        **
        ** (2011-04-15) Do not do a full scan of an unordered index.
        **
        ** (2013-10-03) Do not count the entries in a partial index.
        **
        ** In practice the KeyInfo structure will not be used. It is only 
        ** passed to keep OP_OpenRead happy.
        */
        for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
          if( pIdx->bUnordered==0
           && pIdx->szIdxRow<pTab->szTabRow
           && pIdx->pPartIdxWhere==0
           && (!pBest || pIdx->szIdxRow<pBest->szIdxRow)
          ){
            pBest = pIdx;
          }else if( pIdx->autoIndex==2 && pBest==0 && !HasRowid(pTab) ){
            pBest = pIdx;
          }
        }
        if( pBest ){
          iRoot = pBest->tnum;
          pKeyInfo = sqlite3KeyInfoOfIndex(pParse, pBest);
        }
︙︙
Changes to test/count.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

# 2009 February 24
#
# 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 implements regression tests for SQLite library.  The
# focus of this file is testing "SELECT count(*)" statements.
#
# $Id: count.test,v 1.6 2009/06/05 17:09:12 drh Exp $

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

# Test plan:
#
#  count-0.*: Make sure count(*) works on an empty database.  (Ticket #3774)
#
#  count-1.*: Test that the OP_Count instruction appears to work on both
#             tables and indexes. Test both when they contain 0 entries,
#             when all entries are on the root page, and when the b-tree
#             forms a structure 2 and 3 levels deep.
#            
#  count-2.*: Test that 
#
#

do_test count-0.1 {
  db eval {
     SELECT count(*) FROM sqlite_master;
  }

|












<












<
<








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

# 2009-02-24
#
# 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 implements regression tests for SQLite library.  The
# focus of this file is testing "SELECT count(*)" statements.
#


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

# Test plan:
#
#  count-0.*: Make sure count(*) works on an empty database.  (Ticket #3774)
#
#  count-1.*: Test that the OP_Count instruction appears to work on both
#             tables and indexes. Test both when they contain 0 entries,
#             when all entries are on the root page, and when the b-tree
#             forms a structure 2 and 3 levels deep.


#
#

do_test count-0.1 {
  db eval {
     SELECT count(*) FROM sqlite_master;
  }
︙︙
184
185
186
187
188
189
190





191
192

  execsql {
    DROP INDEX t4i1;
    CREATE INDEX t4i1 ON t4(b, a);
    SELECT count(*) FROM t4;
  }
} {1}







finish_test








>
>
>
>
>



181
182
183
184
185
186
187
188
189
190
191
192
193
194

  execsql {
    DROP INDEX t4i1;
    CREATE INDEX t4i1 ON t4(b, a);
    SELECT count(*) FROM t4;
  }
} {1}

do_execsql_test count-5.1 {
  CREATE TABLE t5(a TEXT PRIMARY KEY, b VARCHAR(50)) WITHOUT ROWID;
  INSERT INTO t5 VALUES('bison','jazz');
  SELECT count(*) FROM t5;
} {1}

finish_test