/ Check-in [a3695b98]
Login

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

Overview
Comment:Minor changes and coverge tests for "SELECT count(*)" optimization. (CVS 6324)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:a3695b98f63fb776c3b7f77f0553e8a38bcc6f78
User & Date: danielk1977 2009-02-25 08:56:47
Context
2009-02-25
15:22
Add the 'genfkey' functionality to the sqlite3 shell. Accessed using a new dot-command - ".genfkey". (CVS 6325) check-in: 0a59fb28 user: danielk1977 tags: trunk
08:56
Minor changes and coverge tests for "SELECT count(*)" optimization. (CVS 6324) check-in: a3695b98 user: danielk1977 tags: trunk
2009-02-24
19:21
Additional commands and another procedure name changes for clarity of presentation. No logic changes. (CVS 6323) check-in: 91d9d51e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/select.c.

8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980


2981
2982
2983
2984
2985
2986
2987
2988
**    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.
................................................................................
  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;


  if( !pTab || pTab->pSelect || IsVirtual(pTab) ) return 0;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0;
  if( pExpr->flags&EP_Distinct ) return 0;

  return pTab;
}








|







 







<


>
>
|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
....
2971
2972
2973
2974
2975
2976
2977

2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
**    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.504 2009/02/25 08:56:47 danielk1977 Exp $
*/
#include "sqliteInt.h"


/*
** Delete all the content of a Select structure but do not deallocate
** the select structure itself.
................................................................................
  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;
  assert( pTab && !pTab->pSelect && pExpr );

  if( IsVirtual(pTab) ) return 0;
  if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  if( (pAggInfo->aFunc[0].pFunc->flags&SQLITE_FUNC_COUNT)==0 ) return 0;
  if( pExpr->flags&EP_Distinct ) return 0;

  return pTab;
}

Changes to test/count.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
133
134
135
136
137
138
139








140
141
142
143
144
145
146
147
148
149
150
151























152
153
154
#    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
................................................................................
} {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








|







 







>
>
>
>
>
>
>
>












>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
#    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.3 2009/02/25 08:56:47 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
................................................................................
} {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}

ifcapable vtab {
  register_echo_module [sqlite3_connection_pointer db]
  do_test count-2.14 {
    execsql { CREATE VIRTUAL TABLE techo USING echo(t1); }
    uses_op_count {SELECT count(*) FROM techo}
  } {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;
  }
} {}

do_test count-4.1 {
  execsql {
    CREATE TABLE t4(a, b);
    INSERT INTO t4 VALUES('a', 'b');
    CREATE INDEX t4i1 ON t4(b, a);
    SELECT count(*) FROM t4;
  }
} {1}
do_test count-4.2 {
  execsql {
    CREATE INDEX t4i2 ON t4(b);
    SELECT count(*) FROM t4;
  }
} {1}
do_test count-4.3 {
  execsql {
    DROP INDEX t4i1;
    CREATE INDEX t4i1 ON t4(b, a);
    SELECT count(*) FROM t4;
  }
} {1}


finish_test

Changes to test/malloc.test.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
786
787
788
789
790
791
792


























793
794
795
796
797
798
799
800
801
# This file attempts to check the behavior of the SQLite library in 
# an out-of-memory situation. When compiled with -DSQLITE_DEBUG=1, 
# the SQLite library accepts a special command (sqlite3_memdebug_fail N C)
# which causes the N-th malloc to fail.  This special feature is used
# to see what happens in the library if a malloc were to really fail
# due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.75 2009/02/04 08:17:57 danielk1977 Exp $

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


# Only run these tests if memory debugging is turned on.
#
................................................................................
  set zRepeat "transient"
  if {$::iRepeat} {set zRepeat "persistent"}
  do_test malloc-32.$zRepeat.${::n}.integrity {
    execsql {PRAGMA integrity_check} db2
  } {ok}
  db2 close
}



























# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}

puts open-file-count=$sqlite_open_file_count
finish_test







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>









12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
...
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
# This file attempts to check the behavior of the SQLite library in 
# an out-of-memory situation. When compiled with -DSQLITE_DEBUG=1, 
# the SQLite library accepts a special command (sqlite3_memdebug_fail N C)
# which causes the N-th malloc to fail.  This special feature is used
# to see what happens in the library if a malloc were to really fail
# due to an out-of-memory situation.
#
# $Id: malloc.test,v 1.76 2009/02/25 08:56:47 danielk1977 Exp $

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


# Only run these tests if memory debugging is turned on.
#
................................................................................
  set zRepeat "transient"
  if {$::iRepeat} {set zRepeat "persistent"}
  do_test malloc-32.$zRepeat.${::n}.integrity {
    execsql {PRAGMA integrity_check} db2
  } {ok}
  db2 close
}

# The following two OOM tests verify that OOM handling works in the
# code used to optimize "SELECT count(*) FROM <tbl>".
#
do_malloc_test 33 -tclprep {
  db eval { PRAGMA cache_size = 10 }
  db transaction {
    db eval { CREATE TABLE abc(a, b) }
    for {set i 0} {$i<500} {incr i} {
      db eval {INSERT INTO abc VALUES(randstr(100,100), randstr(1000,1000))}
    }
  }
} -sqlbody {
  SELECT count(*) FROM abc;
}
do_malloc_test 34 -tclprep {
  db eval { PRAGMA cache_size = 10 }
  db transaction {
    db eval { CREATE TABLE abc(a PRIMARY KEY, b) }
    for {set i 0} {$i<500} {incr i} {
      db eval {INSERT INTO abc VALUES(randstr(100,100), randstr(1000,1000))}
    }
  }
} -sqlbody {
  SELECT count(*) FROM abc;
}

# Ensure that no file descriptors were leaked.
do_test malloc-99.X {
  catch {db close}
  set sqlite_open_file_count
} {0}

puts open-file-count=$sqlite_open_file_count
finish_test