Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fixes and some extra test cases for count(*) optimization. (CVS 6320) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3f0baa1b63df31f7dc885fd39290ca12 |
User & Date: | danielk1977 2009-02-24 18:33:15.000 |
Context
2009-02-24
| ||
18:40 | Remove old declaration of sqlite3_pending_byte (which was used by test code). It has been replaced by sqlite3PendingByte. Ticket #3677. (CVS 6321) (check-in: 44f0162ebb user: danielk1977 tags: trunk) | |
18:33 | Fixes and some extra test cases for count(*) optimization. (CVS 6320) (check-in: 3f0baa1b63 user: danielk1977 tags: trunk) | |
16:18 | Enhanced comments on table locking logic as it relates to preparing new statements. Added assert() and testcase() but no other changes to code. (CVS 6319) (check-in: 4a12f5b818 user: drh tags: trunk) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. ** ** $Id: select.c,v 1.503 2009/02/24 18:33:15 danielk1977 Exp $ */ #include "sqliteInt.h" /* ** Delete all the content of a Select structure but do not deallocate ** the select structure itself. |
︙ | ︙ | |||
2966 2967 2968 2969 2970 2971 2972 | */ static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){ Table *pTab; Expr *pExpr; assert( !p->pGroupBy ); | | | 2966 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 | */ static Table *isSimpleCount(Select *p, AggInfo *pAggInfo){ Table *pTab; Expr *pExpr; assert( !p->pGroupBy ); if( p->pWhere || p->pEList->nExpr!=1 || p->pSrc->nSrc!=1 || p->pSrc->a[0].pSelect ){ return 0; } pTab = p->pSrc->a[0].pTab; pExpr = p->pEList->a[0].pExpr; |
︙ | ︙ | |||
4140 4141 4142 4143 4144 4145 4146 | if( !pMinMax && flag ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); VdbeComment((v, "%s() by index", (flag==WHERE_ORDERBY_MIN?"min":"max"))); } sqlite3WhereEnd(pWInfo); finalizeAggFunctions(pParse, &sAggInfo); | > > | | | | < < | 4140 4141 4142 4143 4144 4145 4146 4147 4148 4149 4150 4151 4152 4153 4154 4155 4156 4157 4158 4159 | if( !pMinMax && flag ){ sqlite3VdbeAddOp2(v, OP_Goto, 0, pWInfo->iBreak); VdbeComment((v, "%s() by index", (flag==WHERE_ORDERBY_MIN?"min":"max"))); } sqlite3WhereEnd(pWInfo); finalizeAggFunctions(pParse, &sAggInfo); } pOrderBy = 0; if( pHaving ){ sqlite3ExprIfFalse(pParse, pHaving, addrEnd, SQLITE_JUMPIFNULL); } selectInnerLoop(pParse, p, p->pEList, 0, 0, 0, -1, pDest, addrEnd, addrEnd); sqlite3ExprListDelete(db, pDel); } sqlite3VdbeResolveLabel(v, addrEnd); } /* endif aggregate query */ |
︙ | ︙ |
Changes to test/count.test.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 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. # | | > > > > > > > > > > > > > > > | | | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | # 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.2 2009/02/24 18:33:15 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test plan: # # 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 # # set iTest 0 foreach zIndex [list { /* no-op */ } { CREATE INDEX i1 ON t1(a); }] { incr iTest do_test count-1.$iTest.1 { execsql { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a, b); } execsql $zIndex execsql { SELECT count(*) FROM t1 } } {0} do_test count-1.$iTest.2 { execsql { INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); SELECT count(*) FROM t1; } } {2} do_test count-1.$iTest.3 { execsql { INSERT INTO t1 SELECT * FROM t1; -- 4 INSERT INTO t1 SELECT * FROM t1; -- 8 INSERT INTO t1 SELECT * FROM t1; -- 16 INSERT INTO t1 SELECT * FROM t1; -- 32 INSERT INTO t1 SELECT * FROM t1; -- 64 INSERT INTO t1 SELECT * FROM t1; -- 128 INSERT INTO t1 SELECT * FROM t1; -- 256 SELECT count(*) FROM t1; } } {256} do_test count-1.$iTest.4 { execsql { INSERT INTO t1 SELECT * FROM t1; -- 512 INSERT INTO t1 SELECT * FROM t1; -- 1024 INSERT INTO t1 SELECT * FROM t1; -- 2048 INSERT INTO t1 SELECT * FROM t1; -- 4096 SELECT count(*) FROM t1; } } {4096} do_test count-1.$iTest.5 { execsql { BEGIN; INSERT INTO t1 SELECT * FROM t1; -- 8192 INSERT INTO t1 SELECT * FROM t1; -- 16384 INSERT INTO t1 SELECT * FROM t1; -- 32768 INSERT INTO t1 SELECT * FROM t1; -- 65536 COMMIT; SELECT count(*) FROM t1; } } {65536} } proc uses_op_count {sql} { if {[lsearch [execsql "EXPLAIN $sql"] Count]>=0} { return 1; } return 0 } do_test count-2.1 { execsql { CREATE TABLE t2(a, b); } uses_op_count {SELECT count(*) FROM t2} } {1} do_test count-2.2 { catchsql {SELECT count(DISTINCT *) FROM t2} } {1 {near "*": syntax error}} do_test count-2.3 { uses_op_count {SELECT count(DISTINCT a) FROM t2} } {0} do_test count-2.4 { uses_op_count {SELECT count(a) FROM t2} } {0} do_test count-2.5 { uses_op_count {SELECT count() FROM t2} } {1} do_test count-2.6 { catchsql {SELECT count(DISTINCT) FROM t2} } {1 {DISTINCT aggregates must have exactly one argument}} do_test count-2.7 { uses_op_count {SELECT count(*)+1 FROM t2} } {0} do_test count-2.8 { uses_op_count {SELECT count(*) FROM t2 WHERE a IS NOT NULL} } {0} do_test count-2.9 { catchsql {SELECT count(*) FROM t2 HAVING count(*)>1} } {1 {a GROUP BY clause is required before HAVING}} do_test count-2.10 { uses_op_count {SELECT count(*) FROM (SELECT 1)} } {0} do_test count-2.11 { execsql { CREATE VIEW v1 AS SELECT 1 AS a } uses_op_count {SELECT count(*) FROM v1} } {0} do_test count-2.12 { uses_op_count {SELECT count(*), max(a) FROM t2} } {0} do_test count-2.13 { uses_op_count {SELECT count(*) FROM t1, t2} } {0} do_test count-3.1 { execsql { CREATE TABLE t3(a, b); SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==0; } } {0} do_test count-3.2 { execsql { SELECT a FROM (SELECT count(*) AS a FROM t3) WHERE a==1; } } {} finish_test |