/ Check-in [ef3a157f]
Login

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

Overview
Comment:Add comments to test cases. Improvements to the query plan test variable. (CVS 2555)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:ef3a157f469d72cbd2f713f997598ddf47f340d2
User & Date: drh 2005-07-21 03:48:20
Context
2005-07-21
18:23
Split the OP_Integer opcode into OP_Integer and OP_Int64. This allows comments to be added to OP_Integer. Cleanup in the optimizer. Allow terms of the FROM clause to be reordered automatically. (CVS 2556) check-in: e2f822ac user: drh tags: trunk
03:48
Add comments to test cases. Improvements to the query plan test variable. (CVS 2555) check-in: ef3a157f user: drh tags: trunk
03:15
In where.c, split out the code that selects an index into a separate subroutine. (CVS 2554) check-in: c30cbba9 user: drh 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
....
1488
1489
1490
1491
1492
1493
1494

















































1495
1496
1497
1498
1499
1500
1501
** 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.148 2005/07/21 03:15:00 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8-1)
................................................................................
        if( (pTerm->prereqAll & notReady)!=0 ) continue;
        assert( pTerm->pExpr );
        sqlite3ExprIfFalse(pParse, pTerm->pExpr, cont, 1);
        pTerm->flags |= TERM_CODED;
      }
    }
  }

















































  pWInfo->iContinue = cont;
  freeMaskSet(&maskSet);
  whereClauseClear(&wc);
  return pWInfo;
}

/*







|







 







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







12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
....
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
** 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.149 2005/07/21 03:48:20 drh Exp $
*/
#include "sqliteInt.h"

/*
** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
*/
#define BMS  (sizeof(Bitmask)*8-1)
................................................................................
        if( (pTerm->prereqAll & notReady)!=0 ) continue;
        assert( pTerm->pExpr );
        sqlite3ExprIfFalse(pParse, pTerm->pExpr, cont, 1);
        pTerm->flags |= TERM_CODED;
      }
    }
  }

#ifdef SQLITE_TEST  /* For testing and debugging use only */
  /* Record in the query plan information about the current table
  ** and the index used to access it (if any).  If the table itself
  ** is not used, its name is just '{}'.  If no index is used
  ** the index is listed as "{}".  If the primary key is used the
  ** index name is '*'.
  */
  for(i=0; i<pTabList->nSrc; i++){
    char *z;
    int n;
    pTabItem = &pTabList->a[i];
    pLevel = &pWInfo->a[i];
    z = pTabItem->zAlias;
    if( z==0 ) z = pTabItem->pTab->zName;
    n = strlen(z);
    if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
      if( pLevel->flags & WHERE_IDX_ONLY ){
        strcpy(&sqlite3_query_plan[nQPlan], "{}");
        nQPlan += 2;
      }else{
        strcpy(&sqlite3_query_plan[nQPlan], z);
        nQPlan += n;
      }
      sqlite3_query_plan[nQPlan++] = ' ';
    }
    if( pLevel->flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
      strcpy(&sqlite3_query_plan[nQPlan], "* ");
      nQPlan += 2;
    }else if( pLevel->pIdx==0 ){
      strcpy(&sqlite3_query_plan[nQPlan], "{} ");
      nQPlan += 3;
    }else{
      n = strlen(pLevel->pIdx->zName);
      if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
        strcpy(&sqlite3_query_plan[nQPlan], pLevel->pIdx->zName);
        nQPlan += n;
        sqlite3_query_plan[nQPlan++] = ' ';
      }
    }
  }
  while( nQPlan>0 && sqlite3_query_plan[nQPlan-1]==' ' ){
    sqlite3_query_plan[--nQPlan] = 0;
  }
  sqlite3_query_plan[nQPlan] = 0;
  nQPlan = 0;
#endif /* SQLITE_TEST // Testing and debugging use only */


  pWInfo->iContinue = cont;
  freeMaskSet(&maskSet);
  whereClauseClear(&wc);
  return pWInfo;
}

/*

Changes to test/intpkey.test.

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
115
116
117
118
119
120
121
122
123
124
125
126



127
128
129
130
131
132
133
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.22 2005/03/31 18:40:05 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
    SELECT * FROM t1;
  }
} {4 one two 5 hello world 6 second entry}

# Make sure SELECT statements are able to use the primary key column
# as an index.
#
do_test intpkey-1.12 {
  execsql {
    SELECT * FROM t1 WHERE a==4;
  }
} {4 one two}




# Try to insert a non-integer value into the primary key field.  This
# should result in a data type mismatch.
#
do_test intpkey-1.13.1 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES('x','y','z');







|







 







|




>
>
>







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for the special processing associated
# with INTEGER PRIMARY KEY columns.
#
# $Id: intpkey.test,v 1.23 2005/07/21 03:48:20 drh Exp $

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

# Create a table with a primary key and a datatype other than
# integer
#
................................................................................
    SELECT * FROM t1;
  }
} {4 one two 5 hello world 6 second entry}

# Make sure SELECT statements are able to use the primary key column
# as an index.
#
do_test intpkey-1.12.1 {
  execsql {
    SELECT * FROM t1 WHERE a==4;
  }
} {4 one two}
do_test intpkey-1.12.2 {
  set sqlite_query_plan
} {t1 *}

# Try to insert a non-integer value into the primary key field.  This
# should result in a data type mismatch.
#
do_test intpkey-1.13.1 {
  set r [catch {execsql {
    INSERT INTO t1 VALUES('x','y','z');

Changes to test/subquery.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
217
218
219
220
221
222
223






224
225
226
227
228


229
230
231
232
233
234
235



236
237
238
239
240
241
242
#    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 script is testing correlated subqueries
#
# $Id: subquery.test,v 1.10 2005/07/21 03:15:01 drh Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
    INSERT INTO t3 VALUES(10);

    CREATE TABLE t4(x TEXT);
    INSERT INTO t4 VALUES('10.0');
  }
} {}
do_test subquery-2.5.2 {






  execsql {
    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {10.0}
do_test subquery-2.5.3 {


  execsql {
    CREATE INDEX t4i ON t4(x);
    --pragma vdbe_listing=on; pragma vdbe_trace=on;
    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {10.0}
#exit



do_test subquery-2.5.4 {
  execsql {
    DROP TABLE t3;
    DROP TABLE t4;
  }
} {}








|







 







>
>
>
>
>
>




|
>
>


<



|
>
>
>







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
...
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238

239
240
241
242
243
244
245
246
247
248
249
250
251
252
#    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 script is testing correlated subqueries
#
# $Id: subquery.test,v 1.11 2005/07/21 03:48:20 drh Exp $
#

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

ifcapable !subquery {
  finish_test
................................................................................
    INSERT INTO t3 VALUES(10);

    CREATE TABLE t4(x TEXT);
    INSERT INTO t4 VALUES('10.0');
  }
} {}
do_test subquery-2.5.2 {
  # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
  # has text affinity and the LHS has integer affinity.  The rule is
  # that we try to convert both sides to an integer before doing the
  # comparision.  Hence, the integer value 10 in t3 will compare equal
  # to the string value '10.0' in t4 because the t4 value will be
  # converted into an integer.
  execsql {
    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {10.0}
do_test subquery-2.5.3.1 {
  # The t4i index cannot be used to resolve the "x IN (...)" constraint
  # because the constraint has integer affinity but t4i has text affinity.
  execsql {
    CREATE INDEX t4i ON t4(x);

    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
  }
} {10.0}
do_test subquery-2.5.3.2 {
  # Verify that the t4i index was not used in the previous query
  set ::sqlite_query_plan
} {t4 {}}
do_test subquery-2.5.4 {
  execsql {
    DROP TABLE t3;
    DROP TABLE t4;
  }
} {}

Changes to test/where.test.

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
#    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 the use of indices in WHERE clases.
#
# $Id: where.test,v 1.30 2005/07/15 23:24:25 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test where-1.4.2 {
   set sqlite_query_plan
} {t1 i1w}
do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
  set sqlite_query_plan
} {t1 i1w}







|







 







|







7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
..
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
#    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 the use of indices in WHERE clases.
#
# $Id: where.test,v 1.31 2005/07/21 03:48:20 drh Exp $

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

# Build some test data
#
do_test where-1.0 {
................................................................................
do_test where-1.3 {
  count {SELECT x, y FROM t1 WHERE 11=w}
} {3 144 3}
do_test where-1.4 {
  count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
} {3 144 3}
do_test where-1.4.2 {
  set sqlite_query_plan
} {t1 i1w}
do_test where-1.5 {
  count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
} {3 144 3}
do_test where-1.5.2 {
  set sqlite_query_plan
} {t1 i1w}