/ Check-in [52885ed8]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:52885ed8b76a06588acf202a38b4feabfca1cfd1
User & Date: danielk1977 2007-03-02 08:12:22
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: 2ba5be31 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: 52885ed8 user: danielk1977 tags: trunk
07:27
Disable the "SELECT max(rowid) ..." optimization for virtual tables. Ticket #2250. (CVS 3669) check-in: ddb4d0af user: danielk1977 tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1353
1354
1355
1356
1357
1358
1359

1360
1361
1362
1363
1364
1365
1366
....
2039
2040
2041
2042
2043
2044
2045








2046
2047
2048
2049
2050
2051
2052
** 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)
................................................................................
      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
................................................................................
        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;







|







 







>







 







>
>
>
>
>
>
>
>







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
....
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
** 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)
................................................................................
      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
................................................................................
        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.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
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
#    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
................................................................................
} {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







|







 







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



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
886
887
888
889
890
891
892






















893
894
895
#    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
................................................................................
} {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
..
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#    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
................................................................................
    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);







|







 







|
<







8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
..
53
54
55
56
57
58
59
60

61
62
63
64
65
66
67
#    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
................................................................................
    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);