/ Check-in [ddb4d0af]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Disable the "SELECT max(rowid) ..." optimization for virtual tables. Ticket #2250. (CVS 3669)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ddb4d0af5770c7030fe6e92119972c9508724b9a
User & Date: danielk1977 2007-03-02 07:27:00
Context
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
06:24
Minor fixes so that testfixture builds without IO tracing enabled. (CVS 3668) check-in: 8d3829cd user: danielk1977 tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

     8      8   **    May you find forgiveness for yourself and forgive others.
     9      9   **    May you share freely, never taking more than you give.
    10     10   **
    11     11   *************************************************************************
    12     12   ** This file contains C code routines that are called by the parser
    13     13   ** to handle SELECT statements in SQLite.
    14     14   **
    15         -** $Id: select.c,v 1.329 2007/02/24 13:23:53 drh Exp $
           15  +** $Id: select.c,v 1.330 2007/03/02 07:27:00 danielk1977 Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   
    20     20   /*
    21     21   ** Delete all the content of a Select structure but do not deallocate
    22     22   ** the select structure itself.
................................................................................
  2363   2363       return 0;
  2364   2364     }
  2365   2365     pExpr = pList->a[0].pExpr;
  2366   2366     if( pExpr->op!=TK_COLUMN ) return 0;
  2367   2367     iCol = pExpr->iColumn;
  2368   2368     pTab = pSrc->a[0].pTab;
  2369   2369   
         2370  +  /* This optimization cannot be used with virtual tables. */
         2371  +  if( IsVirtual(pTab) ) return 0;
  2370   2372   
  2371   2373     /* If we get to here, it means the query is of the correct form.
  2372   2374     ** Check to make sure we have an index and make pIdx point to the
  2373   2375     ** appropriate index.  If the min() or max() is on an INTEGER PRIMARY
  2374   2376     ** key column, no index is necessary so set pIdx to NULL.  If no
  2375   2377     ** usable index is found, return 0.
  2376   2378     */

Added test/vtab8.test.

            1  +# 2006 August 29
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file inserting into virtual tables from a SELECT
           13  +# statement.
           14  +#
           15  +# $Id: vtab8.test,v 1.1 2007/03/02 07:27:01 danielk1977 Exp $
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +
           20  +ifcapable !vtab {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +register_echo_module [sqlite3_connection_pointer db]
           26  +
           27  +# See ticket #2244
           28  +#
           29  +do_test vtab1.2244-1 {
           30  +  execsql {
           31  +    CREATE TABLE t2244(a, b);
           32  +    CREATE VIRTUAL TABLE t2244e USING echo(t2244);
           33  +    INSERT INTO t2244 VALUES('AA', 'BB');
           34  +    INSERT INTO t2244 VALUES('CC', 'DD');
           35  +    SELECT rowid, * FROM t2244e;
           36  +  }
           37  +} {1 AA BB 2 CC DD}
           38  +do_test vtab1.2244-2 {
           39  +  execsql {
           40  +    SELECT * FROM t2244e WHERE rowid = 10;
           41  +  }
           42  +} {}
           43  +do_test vtab1.2244-3 {
           44  +  execsql {
           45  +    UPDATE t2244e SET a = 'hello world' WHERE 0;
           46  +    SELECT rowid, * FROM t2244e;
           47  +  }
           48  +} {1 AA BB 2 CC DD}
           49  +
           50  +do_test vtab1-2250-2 {
           51  +  execsql {
           52  +    CREATE TABLE t2250(a, b);
           53  +    INSERT INTO t2250 VALUES(10, 20);
           54  +    CREATE VIRTUAL TABLE t2250e USING echo(t2250);
           55  +    select max(rowid) from t2250;
           56  +    select max(rowid) from t2250e;
           57  +  }
           58  +} {1 1}
           59  +
           60  +# See ticket #2260 (note: this test doesn't trigger the bug yet - it's a
           61  +# work in progress).
           62  +#
           63  +do_test vtab1.2260-1 {
           64  +  execsql {
           65  +    CREATE TABLE t2260a_real(a, b);
           66  +    CREATE TABLE t2260b_real(a, b);
           67  +
           68  +    CREATE INDEX i2260 ON t2260a_real(a);
           69  +    CREATE INDEX i2260x ON t2260b_real(a);
           70  +
           71  +    CREATE VIRTUAL TABLE t2260a USING echo(t2260a_real);
           72  +    CREATE VIRTUAL TABLE t2260b USING echo(t2260b_real);
           73  +
           74  +    SELECT * FROM t2260a, t2260b WHERE t2260a.a = t2260b.a AND t2260a.a > 101;
           75  +  }
           76  +} {}
           77  +
           78  +unset -nocomplain echo_module_begin_fail
           79  +finish_test