SQLite

Check-in [52885ed8b7]
Login

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

Overview
Comment:Handle the case where the estimated cost of a virtual table scan is larger than SQLITE_BIG_DBL. Ticket #2253. (CVS 3670)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 52885ed8b76a06588acf202a38b4feabfca1cfd1
User & Date: danielk1977 2007-03-02 08:12:22.000
Context
2007-03-04
13:15
Changes to the btree and pager that reduce the amount of I/O when dealing with the freelist. (1) Avoid journaling pages of a table that is being deleted. (2) Do not read the original content of pages being pulled off of the freelist. (CVS 3671) (check-in: 2ba5be3119 user: drh tags: trunk)
2007-03-02
08:12
Handle the case where the estimated cost of a virtual table scan is larger than SQLITE_BIG_DBL. Ticket #2253. (CVS 3670) (check-in: 52885ed8b7 user: danielk1977 tags: trunk)
07:27
Disable the "SELECT max(rowid) ..." optimization for virtual tables. Ticket #2250. (CVS 3669) (check-in: ddb4d0af57 user: danielk1977 tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.238 2007/02/23 23:13:34 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)







|







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.  This module is reponsible for
** generating the code that loops through a table looking for applicable
** rows.  Indices are selected and used to speed the search when doing
** so is applicable.  Because this module is responsible for selecting
** indices, you might also think of this module as the "query optimizer".
**
** $Id: where.c,v 1.239 2007/03/02 08:12:22 danielk1977 Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8)
1353
1354
1355
1356
1357
1358
1359

1360
1361
1362
1363
1364
1365
1366
      sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
    }
    sqlite3SafetyOn(pParse->db);
  }else{
    rc = sqlite3SafetyOn(pParse->db);
  }
  *(int*)&pIdxInfo->nOrderBy = nOrderBy;

  return pIdxInfo->estimatedCost;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** Find the best index for accessing a particular table.  Return a pointer
** to the index, flags that describe how the index should be used, the







>







1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
      sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
    }
    sqlite3SafetyOn(pParse->db);
  }else{
    rc = sqlite3SafetyOn(pParse->db);
  }
  *(int*)&pIdxInfo->nOrderBy = nOrderBy;

  return pIdxInfo->estimatedCost;
}
#endif /* SQLITE_OMIT_VIRTUALTABLE */

/*
** Find the best index for accessing a particular table.  Return a pointer
** to the index, flags that describe how the index should be used, the
2039
2040
2041
2042
2043
2044
2045








2046
2047
2048
2049
2050
2051
2052
        flags = WHERE_VIRTUALTABLE;
        pIndex = *ppIdxInfo;
        if( pIndex && pIndex->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;








      }else 
#endif
      {
        cost = bestIndex(pParse, &wc, pTabItem, notReady,
                         (i==0 && ppOrderBy) ? *ppOrderBy : 0,
                         &pIdx, &flags, &nEq);
        pIndex = 0;







>
>
>
>
>
>
>
>







2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
        flags = WHERE_VIRTUALTABLE;
        pIndex = *ppIdxInfo;
        if( pIndex && pIndex->orderByConsumed ){
          flags = WHERE_VIRTUALTABLE | WHERE_ORDERBY;
        }
        pIdx = 0;
        nEq = 0;
        if( (SQLITE_BIG_DBL/2.0)<cost ){
          /* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
          ** inital value of lowestCost in this loop. If it is, then
          ** the (cost<lowestCost) test below will never be true and
          ** pLevel->pBestIdx never set.
          */ 
          cost = (SQLITE_BIG_DBL/2.0);
        }
      }else 
#endif
      {
        cost = bestIndex(pParse, &wc, pTabItem, notReady,
                         (i==0 && ppOrderBy) ? *ppOrderBy : 0,
                         &pIdx, &flags, &nEq);
        pIndex = 0;
Changes to test/vtab1.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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 creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.40 2007/02/21 16:52:12 danielk1977 Exp $

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

ifcapable !vtab||!schema_pragmas {
  finish_test
  return













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 2006 June 10
#
# 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 creating and dropping virtual tables.
#
# $Id: vtab1.test,v 1.41 2007/03/02 08:12:23 danielk1977 Exp $

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

ifcapable !vtab||!schema_pragmas {
  finish_test
  return
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
} {0 1}
do_test vtab1.11-5 {
  execsql {
    SELECT  glob(a,'2') FROM e
  }
} {{2 1} {2 2}}
 
# See ticket #2244
#
do_test vtab1.2244-1 {
  execsql {
    CREATE TABLE t2244(a, b);
    CREATE VIRTUAL TABLE t2244e USING echo(t2244);
    INSERT INTO t2244 VALUES('AA', 'BB');
    INSERT INTO t2244 VALUES('CC', 'DD');
    SELECT rowid, * FROM t2244e;
  }
} {1 AA BB 2 CC DD}
do_test vtab1.2244-2 {
  execsql {
    SELECT * FROM t2244e WHERE rowid = 10;
  }
} {}
do_test vtab1.2244-3 {
  execsql {
    UPDATE t2244e SET a = 'hello world' WHERE 0;
    SELECT rowid, * FROM t2244e;
  }
} {1 AA BB 2 CC DD}

unset -nocomplain echo_module_begin_fail
finish_test







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<



886
887
888
889
890
891
892






















893
894
895
} {0 1}
do_test vtab1.11-5 {
  execsql {
    SELECT  glob(a,'2') FROM e
  }
} {{2 1} {2 2}}
 























unset -nocomplain echo_module_begin_fail
finish_test
Changes to test/vtab8.test.
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file inserting into virtual tables from a SELECT
# statement.
#
# $Id: vtab8.test,v 1.1 2007/03/02 07:27:01 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return







|







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file inserting into virtual tables from a SELECT
# statement.
#
# $Id: vtab8.test,v 1.2 2007/03/02 08:12:23 danielk1977 Exp $

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

ifcapable !vtab {
  finish_test
  return
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
    INSERT INTO t2250 VALUES(10, 20);
    CREATE VIRTUAL TABLE t2250e USING echo(t2250);
    select max(rowid) from t2250;
    select max(rowid) from t2250e;
  }
} {1 1}

# See ticket #2260 (note: this test doesn't trigger the bug yet - it's a
# work in progress).
#
do_test vtab1.2260-1 {
  execsql {
    CREATE TABLE t2260a_real(a, b);
    CREATE TABLE t2260b_real(a, b);

    CREATE INDEX i2260 ON t2260a_real(a);







|
<







53
54
55
56
57
58
59
60

61
62
63
64
65
66
67
    INSERT INTO t2250 VALUES(10, 20);
    CREATE VIRTUAL TABLE t2250e USING echo(t2250);
    select max(rowid) from t2250;
    select max(rowid) from t2250e;
  }
} {1 1}

# See ticket #2260.

#
do_test vtab1.2260-1 {
  execsql {
    CREATE TABLE t2260a_real(a, b);
    CREATE TABLE t2260b_real(a, b);

    CREATE INDEX i2260 ON t2260a_real(a);