/ 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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

    12     12   ** This module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.  This module is reponsible for
    14     14   ** generating the code that loops through a table looking for applicable
    15     15   ** rows.  Indices are selected and used to speed the search when doing
    16     16   ** so is applicable.  Because this module is responsible for selecting
    17     17   ** indices, you might also think of this module as the "query optimizer".
    18     18   **
    19         -** $Id: where.c,v 1.148 2005/07/21 03:15:00 drh Exp $
           19  +** $Id: where.c,v 1.149 2005/07/21 03:48:20 drh Exp $
    20     20   */
    21     21   #include "sqliteInt.h"
    22     22   
    23     23   /*
    24     24   ** The number of bits in a Bitmask.  "BMS" means "BitMask Size".
    25     25   */
    26     26   #define BMS  (sizeof(Bitmask)*8-1)
................................................................................
  1488   1488           if( (pTerm->prereqAll & notReady)!=0 ) continue;
  1489   1489           assert( pTerm->pExpr );
  1490   1490           sqlite3ExprIfFalse(pParse, pTerm->pExpr, cont, 1);
  1491   1491           pTerm->flags |= TERM_CODED;
  1492   1492         }
  1493   1493       }
  1494   1494     }
         1495  +
         1496  +#ifdef SQLITE_TEST  /* For testing and debugging use only */
         1497  +  /* Record in the query plan information about the current table
         1498  +  ** and the index used to access it (if any).  If the table itself
         1499  +  ** is not used, its name is just '{}'.  If no index is used
         1500  +  ** the index is listed as "{}".  If the primary key is used the
         1501  +  ** index name is '*'.
         1502  +  */
         1503  +  for(i=0; i<pTabList->nSrc; i++){
         1504  +    char *z;
         1505  +    int n;
         1506  +    pTabItem = &pTabList->a[i];
         1507  +    pLevel = &pWInfo->a[i];
         1508  +    z = pTabItem->zAlias;
         1509  +    if( z==0 ) z = pTabItem->pTab->zName;
         1510  +    n = strlen(z);
         1511  +    if( n+nQPlan < sizeof(sqlite3_query_plan)-10 ){
         1512  +      if( pLevel->flags & WHERE_IDX_ONLY ){
         1513  +        strcpy(&sqlite3_query_plan[nQPlan], "{}");
         1514  +        nQPlan += 2;
         1515  +      }else{
         1516  +        strcpy(&sqlite3_query_plan[nQPlan], z);
         1517  +        nQPlan += n;
         1518  +      }
         1519  +      sqlite3_query_plan[nQPlan++] = ' ';
         1520  +    }
         1521  +    if( pLevel->flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
         1522  +      strcpy(&sqlite3_query_plan[nQPlan], "* ");
         1523  +      nQPlan += 2;
         1524  +    }else if( pLevel->pIdx==0 ){
         1525  +      strcpy(&sqlite3_query_plan[nQPlan], "{} ");
         1526  +      nQPlan += 3;
         1527  +    }else{
         1528  +      n = strlen(pLevel->pIdx->zName);
         1529  +      if( n+nQPlan < sizeof(sqlite3_query_plan)-2 ){
         1530  +        strcpy(&sqlite3_query_plan[nQPlan], pLevel->pIdx->zName);
         1531  +        nQPlan += n;
         1532  +        sqlite3_query_plan[nQPlan++] = ' ';
         1533  +      }
         1534  +    }
         1535  +  }
         1536  +  while( nQPlan>0 && sqlite3_query_plan[nQPlan-1]==' ' ){
         1537  +    sqlite3_query_plan[--nQPlan] = 0;
         1538  +  }
         1539  +  sqlite3_query_plan[nQPlan] = 0;
         1540  +  nQPlan = 0;
         1541  +#endif /* SQLITE_TEST // Testing and debugging use only */
         1542  +
         1543  +
  1495   1544     pWInfo->iContinue = cont;
  1496   1545     freeMaskSet(&maskSet);
  1497   1546     whereClauseClear(&wc);
  1498   1547     return pWInfo;
  1499   1548   }
  1500   1549   
  1501   1550   /*

Changes to test/intpkey.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the special processing associated
    14     14   # with INTEGER PRIMARY KEY columns.
    15     15   #
    16         -# $Id: intpkey.test,v 1.22 2005/03/31 18:40:05 drh Exp $
           16  +# $Id: intpkey.test,v 1.23 2005/07/21 03:48:20 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Create a table with a primary key and a datatype other than
    22     22   # integer
    23     23   #
................................................................................
   115    115       SELECT * FROM t1;
   116    116     }
   117    117   } {4 one two 5 hello world 6 second entry}
   118    118   
   119    119   # Make sure SELECT statements are able to use the primary key column
   120    120   # as an index.
   121    121   #
   122         -do_test intpkey-1.12 {
          122  +do_test intpkey-1.12.1 {
   123    123     execsql {
   124    124       SELECT * FROM t1 WHERE a==4;
   125    125     }
   126    126   } {4 one two}
          127  +do_test intpkey-1.12.2 {
          128  +  set sqlite_query_plan
          129  +} {t1 *}
   127    130   
   128    131   # Try to insert a non-integer value into the primary key field.  This
   129    132   # should result in a data type mismatch.
   130    133   #
   131    134   do_test intpkey-1.13.1 {
   132    135     set r [catch {execsql {
   133    136       INSERT INTO t1 VALUES('x','y','z');

Changes to test/subquery.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #*************************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this script is testing correlated subqueries
    13     13   #
    14         -# $Id: subquery.test,v 1.10 2005/07/21 03:15:01 drh Exp $
           14  +# $Id: subquery.test,v 1.11 2005/07/21 03:48:20 drh Exp $
    15     15   #
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   ifcapable !subquery {
    21     21     finish_test
................................................................................
   217    217       INSERT INTO t3 VALUES(10);
   218    218   
   219    219       CREATE TABLE t4(x TEXT);
   220    220       INSERT INTO t4 VALUES('10.0');
   221    221     }
   222    222   } {}
   223    223   do_test subquery-2.5.2 {
          224  +  # In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator
          225  +  # has text affinity and the LHS has integer affinity.  The rule is
          226  +  # that we try to convert both sides to an integer before doing the
          227  +  # comparision.  Hence, the integer value 10 in t3 will compare equal
          228  +  # to the string value '10.0' in t4 because the t4 value will be
          229  +  # converted into an integer.
   224    230     execsql {
   225    231       SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
   226    232     }
   227    233   } {10.0}
   228         -do_test subquery-2.5.3 {
          234  +do_test subquery-2.5.3.1 {
          235  +  # The t4i index cannot be used to resolve the "x IN (...)" constraint
          236  +  # because the constraint has integer affinity but t4i has text affinity.
   229    237     execsql {
   230    238       CREATE INDEX t4i ON t4(x);
   231         -    --pragma vdbe_listing=on; pragma vdbe_trace=on;
   232    239       SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
   233    240     }
   234    241   } {10.0}
   235         -#exit
          242  +do_test subquery-2.5.3.2 {
          243  +  # Verify that the t4i index was not used in the previous query
          244  +  set ::sqlite_query_plan
          245  +} {t4 {}}
   236    246   do_test subquery-2.5.4 {
   237    247     execsql {
   238    248       DROP TABLE t3;
   239    249       DROP TABLE t4;
   240    250     }
   241    251   } {}
   242    252   

Changes to test/where.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the use of indices in WHERE clases.
    13     13   #
    14         -# $Id: where.test,v 1.30 2005/07/15 23:24:25 drh Exp $
           14  +# $Id: where.test,v 1.31 2005/07/21 03:48:20 drh Exp $
    15     15   
    16     16   set testdir [file dirname $argv0]
    17     17   source $testdir/tester.tcl
    18     18   
    19     19   # Build some test data
    20     20   #
    21     21   do_test where-1.0 {
................................................................................
    74     74   do_test where-1.3 {
    75     75     count {SELECT x, y FROM t1 WHERE 11=w}
    76     76   } {3 144 3}
    77     77   do_test where-1.4 {
    78     78     count {SELECT x, y FROM t1 WHERE 11=w AND x>2}
    79     79   } {3 144 3}
    80     80   do_test where-1.4.2 {
    81         -   set sqlite_query_plan
           81  +  set sqlite_query_plan
    82     82   } {t1 i1w}
    83     83   do_test where-1.5 {
    84     84     count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2}
    85     85   } {3 144 3}
    86     86   do_test where-1.5.2 {
    87     87     set sqlite_query_plan
    88     88   } {t1 i1w}