/ Check-in [6c8f86e4]
Login

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

Overview
Comment:Merge the CAST operator enhancements from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | threads
Files: files | file ages | folders
SHA1: 6c8f86e4e08d5d57e21496277613e0f9dcc06514
User & Date: drh 2014-08-25 22:43:17
Context
2014-08-25
23:44
Remove the SQLITE_CONFIG_WORKER_THREADS configuration parameter. The number of worker threads in the sorter is now determined only by the PRAGMA threads=N setting. check-in: e3305d4b user: drh tags: threads
22:43
Merge the CAST operator enhancements from trunk. check-in: 6c8f86e4 user: drh tags: threads
22:37
Add an assert() and five testcase() macros to the OP_Cast opcode implementation to help verify that it is fully tested. check-in: af364cce user: drh tags: trunk
15:13
Query or change the maximum number of worker threads allowed on each database connection separately using the "PRAGMA threads" command. check-in: 29c5e8a7 user: drh tags: threads
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  2591   2591       case TK_AS: {
  2592   2592         inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2593   2593         break;
  2594   2594       }
  2595   2595   #ifndef SQLITE_OMIT_CAST
  2596   2596       case TK_CAST: {
  2597   2597         /* Expressions of the form:   CAST(pLeft AS token) */
  2598         -      int aff, to_op;
  2599   2598         inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target);
  2600         -      assert( !ExprHasProperty(pExpr, EP_IntValue) );
  2601         -      aff = sqlite3AffinityType(pExpr->u.zToken, 0);
  2602         -      to_op = aff - SQLITE_AFF_TEXT + OP_ToText;
  2603         -      assert( to_op==OP_ToText    || aff!=SQLITE_AFF_TEXT    );
  2604         -      assert( to_op==OP_ToBlob    || aff!=SQLITE_AFF_NONE    );
  2605         -      assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC );
  2606         -      assert( to_op==OP_ToInt     || aff!=SQLITE_AFF_INTEGER );
  2607         -      assert( to_op==OP_ToReal    || aff!=SQLITE_AFF_REAL    );
  2608         -      testcase( to_op==OP_ToText );
  2609         -      testcase( to_op==OP_ToBlob );
  2610         -      testcase( to_op==OP_ToNumeric );
  2611         -      testcase( to_op==OP_ToInt );
  2612         -      testcase( to_op==OP_ToReal );
  2613   2599         if( inReg!=target ){
  2614   2600           sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
  2615   2601           inReg = target;
  2616   2602         }
  2617         -      sqlite3VdbeAddOp1(v, to_op, inReg);
         2603  +      sqlite3VdbeAddOp2(v, OP_Cast, target,
         2604  +                        sqlite3AffinityType(pExpr->u.zToken, 0));
  2618   2605         testcase( usedAsColumnCache(pParse, inReg, inReg) );
  2619   2606         sqlite3ExprCacheAffinityChange(pParse, inReg, 1);
  2620   2607         break;
  2621   2608       }
  2622   2609   #endif /* SQLITE_OMIT_CAST */
  2623   2610       case TK_LT:
  2624   2611       case TK_LE:

Changes to src/vdbe.c.

  1764   1764       sqlite3VdbeMemRealify(pIn1);
  1765   1765     }
  1766   1766     break;
  1767   1767   }
  1768   1768   #endif
  1769   1769   
  1770   1770   #ifndef SQLITE_OMIT_CAST
  1771         -/* Opcode: ToText P1 * * * *
         1771  +/* Opcode: Cast P1 P2 * * *
  1772   1772   **
  1773         -** Force the value in register P1 to be text.
  1774         -** If the value is numeric, convert it to a string using the
  1775         -** equivalent of sprintf().  Blob values are unchanged and
  1776         -** are afterwards simply interpreted as text.
         1773  +** Force the value in register P1 to be the type defined by P2.
         1774  +** 
         1775  +** <ul>
         1776  +** <li value="97"> TEXT
         1777  +** <li value="98"> BLOB
         1778  +** <li value="99"> NUMERIC
         1779  +** <li value="100"> INTEGER
         1780  +** <li value="101"> REAL
         1781  +** </ul>
  1777   1782   **
  1778   1783   ** A NULL value is not changed by this routine.  It remains NULL.
  1779   1784   */
  1780         -case OP_ToText: {                  /* same as TK_TO_TEXT, in1 */
         1785  +case OP_Cast: {                  /* in1 */
         1786  +  assert( pOp->p2>=SQLITE_AFF_TEXT && pOp->p2<=SQLITE_AFF_REAL );
         1787  +  testcase( pOp->p2==SQLITE_AFF_TEXT );
         1788  +  testcase( pOp->p2==SQLITE_AFF_NONE );
         1789  +  testcase( pOp->p2==SQLITE_AFF_NUMERIC );
         1790  +  testcase( pOp->p2==SQLITE_AFF_INTEGER );
         1791  +  testcase( pOp->p2==SQLITE_AFF_REAL );
  1781   1792     pIn1 = &aMem[pOp->p1];
  1782   1793     memAboutToChange(p, pIn1);
  1783         -  if( pIn1->flags & MEM_Null ) break;
  1784         -  assert( MEM_Str==(MEM_Blob>>3) );
  1785         -  pIn1->flags |= (pIn1->flags&MEM_Blob)>>3;
  1786         -  applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding);
  1787   1794     rc = ExpandBlob(pIn1);
  1788         -  assert( pIn1->flags & MEM_Str || db->mallocFailed );
  1789         -  pIn1->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero);
  1790         -  UPDATE_MAX_BLOBSIZE(pIn1);
  1791         -  break;
  1792         -}
  1793         -
  1794         -/* Opcode: ToBlob P1 * * * *
  1795         -**
  1796         -** Force the value in register P1 to be a BLOB.
  1797         -** If the value is numeric, convert it to a string first.
  1798         -** Strings are simply reinterpreted as blobs with no change
  1799         -** to the underlying data.
  1800         -**
  1801         -** A NULL value is not changed by this routine.  It remains NULL.
  1802         -*/
  1803         -case OP_ToBlob: {                  /* same as TK_TO_BLOB, in1 */
  1804         -  pIn1 = &aMem[pOp->p1];
  1805         -  if( pIn1->flags & MEM_Null ) break;
  1806         -  if( (pIn1->flags & MEM_Blob)==0 ){
  1807         -    applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding);
  1808         -    assert( pIn1->flags & MEM_Str || db->mallocFailed );
  1809         -    MemSetTypeFlag(pIn1, MEM_Blob);
  1810         -  }else{
  1811         -    pIn1->flags &= ~(MEM_TypeMask&~MEM_Blob);
  1812         -  }
         1795  +  sqlite3VdbeMemCast(pIn1, pOp->p2, encoding);
  1813   1796     UPDATE_MAX_BLOBSIZE(pIn1);
  1814   1797     break;
  1815   1798   }
  1816         -
  1817         -/* Opcode: ToNumeric P1 * * * *
  1818         -**
  1819         -** Force the value in register P1 to be numeric (either an
  1820         -** integer or a floating-point number.)
  1821         -** If the value is text or blob, try to convert it to an using the
  1822         -** equivalent of atoi() or atof() and store 0 if no such conversion 
  1823         -** is possible.
  1824         -**
  1825         -** A NULL value is not changed by this routine.  It remains NULL.
  1826         -*/
  1827         -case OP_ToNumeric: {                  /* same as TK_TO_NUMERIC, in1 */
  1828         -  pIn1 = &aMem[pOp->p1];
  1829         -  sqlite3VdbeMemNumerify(pIn1);
  1830         -  break;
  1831         -}
  1832   1799   #endif /* SQLITE_OMIT_CAST */
  1833   1800   
  1834         -/* Opcode: ToInt P1 * * * *
  1835         -**
  1836         -** Force the value in register P1 to be an integer.  If
  1837         -** The value is currently a real number, drop its fractional part.
  1838         -** If the value is text or blob, try to convert it to an integer using the
  1839         -** equivalent of atoi() and store 0 if no such conversion is possible.
  1840         -**
  1841         -** A NULL value is not changed by this routine.  It remains NULL.
  1842         -*/
  1843         -case OP_ToInt: {                  /* same as TK_TO_INT, in1 */
  1844         -  pIn1 = &aMem[pOp->p1];
  1845         -  if( (pIn1->flags & MEM_Null)==0 ){
  1846         -    sqlite3VdbeMemIntegerify(pIn1);
  1847         -  }
  1848         -  break;
  1849         -}
  1850         -
  1851         -#if !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT)
  1852         -/* Opcode: ToReal P1 * * * *
  1853         -**
  1854         -** Force the value in register P1 to be a floating point number.
  1855         -** If The value is currently an integer, convert it.
  1856         -** If the value is text or blob, try to convert it to an integer using the
  1857         -** equivalent of atoi() and store 0.0 if no such conversion is possible.
  1858         -**
  1859         -** A NULL value is not changed by this routine.  It remains NULL.
  1860         -*/
  1861         -case OP_ToReal: {                  /* same as TK_TO_REAL, in1 */
  1862         -  pIn1 = &aMem[pOp->p1];
  1863         -  memAboutToChange(p, pIn1);
  1864         -  if( (pIn1->flags & MEM_Null)==0 ){
  1865         -    sqlite3VdbeMemRealify(pIn1);
  1866         -  }
  1867         -  break;
  1868         -}
  1869         -#endif /* !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT) */
  1870         -
  1871   1801   /* Opcode: Lt P1 P2 P3 P4 P5
  1872   1802   ** Synopsis: if r[P1]<r[P3] goto P2
  1873   1803   **
  1874   1804   ** Compare the values in register P1 and P3.  If reg(P3)<reg(P1) then
  1875   1805   ** jump to address P2.  
  1876   1806   **
  1877   1807   ** If the SQLITE_JUMPIFNULL bit of P5 is set and either reg(P1) or

Changes to src/vdbeInt.h.

   421    421   int sqlite3VdbeMemStringify(Mem*, u8, u8);
   422    422   i64 sqlite3VdbeIntValue(Mem*);
   423    423   int sqlite3VdbeMemIntegerify(Mem*);
   424    424   double sqlite3VdbeRealValue(Mem*);
   425    425   void sqlite3VdbeIntegerAffinity(Mem*);
   426    426   int sqlite3VdbeMemRealify(Mem*);
   427    427   int sqlite3VdbeMemNumerify(Mem*);
          428  +void sqlite3VdbeMemCast(Mem*,u8,u8);
   428    429   int sqlite3VdbeMemFromBtree(BtCursor*,u32,u32,int,Mem*);
   429    430   void sqlite3VdbeMemRelease(Mem *p);
   430    431   void sqlite3VdbeMemReleaseExternal(Mem *p);
   431    432   #define VdbeMemDynamic(X)  \
   432    433     (((X)->flags&(MEM_Agg|MEM_Dyn|MEM_RowSet|MEM_Frame))!=0)
   433    434   #define VdbeMemReleaseExtern(X)  \
   434    435     if( VdbeMemDynamic(X) ) sqlite3VdbeMemReleaseExternal(X);

Changes to src/vdbemem.c.

   405    405     if( flags & MEM_Int ){
   406    406       return pMem->u.i;
   407    407     }else if( flags & MEM_Real ){
   408    408       return doubleToInt64(pMem->r);
   409    409     }else if( flags & (MEM_Str|MEM_Blob) ){
   410    410       i64 value = 0;
   411    411       assert( pMem->z || pMem->n==0 );
   412         -    testcase( pMem->z==0 );
   413    412       sqlite3Atoi64(pMem->z, &value, pMem->n, pMem->enc);
   414    413       return value;
   415    414     }else{
   416    415       return 0;
   417    416     }
   418    417   }
   419    418   
................................................................................
   517    516         sqlite3VdbeIntegerAffinity(pMem);
   518    517       }
   519    518     }
   520    519     assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 );
   521    520     pMem->flags &= ~(MEM_Str|MEM_Blob);
   522    521     return SQLITE_OK;
   523    522   }
          523  +
          524  +/*
          525  +** Cast the datatype of the value in pMem according to the affinity
          526  +** "aff".  Casting is different from applying affinity in that a cast
          527  +** is forced.  In other words, the value is converted into the desired
          528  +** affinity even if that results in loss of data.  This routine is
          529  +** used (for example) to implement the SQL "cast()" operator.
          530  +*/
          531  +void sqlite3VdbeMemCast(Mem *pMem, u8 aff, u8 encoding){
          532  +  if( pMem->flags & MEM_Null ) return;
          533  +  switch( aff ){
          534  +    case SQLITE_AFF_NONE: {   /* Really a cast to BLOB */
          535  +      if( (pMem->flags & MEM_Blob)==0 ){
          536  +        sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding);
          537  +        assert( pMem->flags & MEM_Str || pMem->db->mallocFailed );
          538  +        MemSetTypeFlag(pMem, MEM_Blob);
          539  +      }else{
          540  +        pMem->flags &= ~(MEM_TypeMask&~MEM_Blob);
          541  +      }
          542  +      break;
          543  +    }
          544  +    case SQLITE_AFF_NUMERIC: {
          545  +      sqlite3VdbeMemNumerify(pMem);
          546  +      break;
          547  +    }
          548  +    case SQLITE_AFF_INTEGER: {
          549  +      sqlite3VdbeMemIntegerify(pMem);
          550  +      break;
          551  +    }
          552  +    case SQLITE_AFF_REAL: {
          553  +      sqlite3VdbeMemRealify(pMem);
          554  +      break;
          555  +    }
          556  +    default: {
          557  +      assert( aff==SQLITE_AFF_TEXT );
          558  +      assert( MEM_Str==(MEM_Blob>>3) );
          559  +      pMem->flags |= (pMem->flags&MEM_Blob)>>3;
          560  +      sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding);
          561  +      assert( pMem->flags & MEM_Str || pMem->db->mallocFailed );
          562  +      pMem->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero);
          563  +      break;
          564  +    }
          565  +  }
          566  +}
          567  +
   524    568   
   525    569   /*
   526    570   ** Delete any previous value and set the value stored in *pMem to NULL.
   527    571   */
   528    572   void sqlite3VdbeMemSetNull(Mem *pMem){
   529    573     if( pMem->flags & MEM_Frame ){
   530    574       VdbeFrame *pFrame = pMem->u.pFrame;
................................................................................
  1011   1055     const char *zNeg = "";
  1012   1056     int rc = SQLITE_OK;
  1013   1057   
  1014   1058     if( !pExpr ){
  1015   1059       *ppVal = 0;
  1016   1060       return SQLITE_OK;
  1017   1061     }
  1018         -  op = pExpr->op;
         1062  +  while( (op = pExpr->op)==TK_UPLUS ) pExpr = pExpr->pLeft;
  1019   1063     if( NEVER(op==TK_REGISTER) ) op = pExpr->op2;
         1064  +
         1065  +  if( op==TK_CAST ){
         1066  +    u8 aff = sqlite3AffinityType(pExpr->u.zToken,0);
         1067  +    rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx);
         1068  +    testcase( rc!=SQLITE_OK );
         1069  +    if( *ppVal ){
         1070  +      sqlite3VdbeMemCast(*ppVal, aff, SQLITE_UTF8);
         1071  +      sqlite3ValueApplyAffinity(*ppVal, affinity, SQLITE_UTF8);
         1072  +    }
         1073  +    return rc;
         1074  +  }
  1020   1075   
  1021   1076     /* Handle negative integers in a single step.  This is needed in the
  1022   1077     ** case when the value is -9223372036854775808.
  1023   1078     */
  1024   1079     if( op==TK_UMINUS
  1025   1080      && (pExpr->pLeft->op==TK_INTEGER || pExpr->pLeft->op==TK_FLOAT) ){
  1026   1081       pExpr = pExpr->pLeft;

Changes to src/where.c.

  2216   2216           rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
  2217   2217           if( rc==SQLITE_OK && bOk ){
  2218   2218             tRowcnt iNew;
  2219   2219             whereKeyStats(pParse, p, pRec, 0, a);
  2220   2220             iNew = a[0] + ((pLower->eOperator & WO_GT) ? a[1] : 0);
  2221   2221             if( iNew>iLower ) iLower = iNew;
  2222   2222             nOut--;
         2223  +          pLower = 0;
  2223   2224           }
  2224   2225         }
  2225   2226   
  2226   2227         /* If possible, improve on the iUpper estimate using ($P:$U). */
  2227   2228         if( pUpper ){
  2228   2229           int bOk;                    /* True if value is extracted from pExpr */
  2229   2230           Expr *pExpr = pUpper->pExpr->pRight;
................................................................................
  2231   2232           rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, aff, nEq, &bOk);
  2232   2233           if( rc==SQLITE_OK && bOk ){
  2233   2234             tRowcnt iNew;
  2234   2235             whereKeyStats(pParse, p, pRec, 1, a);
  2235   2236             iNew = a[0] + ((pUpper->eOperator & WO_LE) ? a[1] : 0);
  2236   2237             if( iNew<iUpper ) iUpper = iNew;
  2237   2238             nOut--;
         2239  +          pUpper = 0;
  2238   2240           }
  2239   2241         }
  2240   2242   
  2241   2243         pBuilder->pRec = pRec;
  2242   2244         if( rc==SQLITE_OK ){
  2243   2245           if( iUpper>iLower ){
  2244   2246             nNew = sqlite3LogEst(iUpper - iLower);
  2245   2247           }else{
  2246   2248             nNew = 10;        assert( 10==sqlite3LogEst(2) );
  2247   2249           }
  2248   2250           if( nNew<nOut ){
  2249   2251             nOut = nNew;
  2250   2252           }
  2251         -        pLoop->nOut = (LogEst)nOut;
  2252   2253           WHERETRACE(0x10, ("range scan regions: %u..%u  est=%d\n",
  2253   2254                              (u32)iLower, (u32)iUpper, nOut));
  2254         -        return SQLITE_OK;
  2255   2255         }
  2256   2256       }else{
  2257   2257         int bDone = 0;
  2258   2258         rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
  2259   2259         if( bDone ) return rc;
  2260   2260       }
  2261   2261     }
  2262   2262   #else
  2263   2263     UNUSED_PARAMETER(pParse);
  2264   2264     UNUSED_PARAMETER(pBuilder);
  2265         -#endif
  2266   2265     assert( pLower || pUpper );
         2266  +#endif
  2267   2267     assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
  2268   2268     nNew = whereRangeAdjust(pLower, nOut);
  2269   2269     nNew = whereRangeAdjust(pUpper, nNew);
  2270   2270   
  2271   2271     /* TUNING: If there is both an upper and lower limit, assume the range is
  2272   2272     ** reduced by an additional 75%. This means that, by default, an open-ended
  2273   2273     ** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the

Changes to test/alter4.test.

   141    141   do_test alter4-2.6 {
   142    142     catchsql {
   143    143       alter table t1 add column d DEFAULT CURRENT_TIME;
   144    144     }
   145    145   } {1 {Cannot add a column with non-constant default}}
   146    146   do_test alter4-2.7 {
   147    147     catchsql {
   148         -    alter table t1 add column d default (-+1);
          148  +    alter table t1 add column d default (-5+1);
   149    149     }
   150    150   } {1 {Cannot add a column with non-constant default}}
   151    151   do_test alter4-2.99 {
   152    152     execsql {
   153    153       DROP TABLE t1;
   154    154     }
   155    155   } {}

Changes to test/analyze9.test.

  1083   1083     2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
  1084   1084   
  1085   1085     3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
  1086   1086     4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
  1087   1087   } {
  1088   1088     do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
  1089   1089   }
         1090  +
         1091  +#-------------------------------------------------------------------------
         1092  +# Test that if stat4 data is available but cannot be used because the
         1093  +# rhs of a range constraint is a complex expression, the default estimates
         1094  +# are used instead.
         1095  +ifcapable stat4&&cte {
         1096  +  do_execsql_test 25.1 {
         1097  +    CREATE TABLE t6(a, b);
         1098  +    WITH ints(i,j) AS (
         1099  +      SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
         1100  +    ) INSERT INTO t6 SELECT * FROM ints;
         1101  +    CREATE INDEX aa ON t6(a);
         1102  +    CREATE INDEX bb ON t6(b);
         1103  +    ANALYZE;
         1104  +  }
         1105  +
         1106  +  # Term (b<?) is estimated at 25%. Better than (a<30) but not as
         1107  +  # good as (a<20).
         1108  +  do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } {
         1109  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)}
         1110  +  }
         1111  +  do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } {
         1112  +    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
         1113  +  }
         1114  +
         1115  +  # Term (b BETWEEN ? AND ?) is estimated at 1/64.
         1116  +  do_eqp_test 25.3.1 { 
         1117  +    SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ? 
         1118  +  } {
         1119  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
         1120  +  }
         1121  +  
         1122  +  # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
         1123  +  # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
         1124  +  # (a<20) but not as good as (a<10).
         1125  +  do_eqp_test 25.4.1 { 
         1126  +    SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
         1127  +  } {
         1128  +    0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
         1129  +  }
         1130  +  do_eqp_test 25.4.2 { 
         1131  +    SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
         1132  +  } {
         1133  +    0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
         1134  +  }
         1135  +}
  1090   1136   
  1091   1137   finish_test

Changes to test/analyzeA.test.

   113    113   foreach {tn analyze_cmd} {
   114    114     1 populate_stat4 
   115    115     2 populate_stat3
   116    116     3 populate_both
   117    117   } {
   118    118     reset_db
   119    119     do_test 1.$tn.1 {
   120         -    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c) }
          120  +    execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT, c INT) }
   121    121       for {set i 0} {$i < 100} {incr i} {
   122    122         set c [expr int(pow(1.1,$i)/100)]
   123    123         set b [expr 125 - int(pow(1.1,99-$i))/100]
   124    124         execsql {INSERT INTO t1 VALUES($i, $b, $c)}
   125    125       }
   126    126     } {}
   127    127   
................................................................................
   157    157     do_eqp_test 1.$tn.3.5 {
   158    158       SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50
   159    159     } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
   160    160   
   161    161     do_eqp_test 1.$tn.3.6 {
   162    162       SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125
   163    163     } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
          164  +
          165  +  do_eqp_test 1.$tn.3.7 {
          166  +    SELECT * FROM t1 WHERE b BETWEEN +0 AND +50 AND c BETWEEN +0 AND +50
          167  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
          168  +
          169  +  do_eqp_test 1.$tn.3.8 {
          170  +    SELECT * FROM t1
          171  +     WHERE b BETWEEN cast('0' AS int) AND cast('50.0' AS real)
          172  +       AND c BETWEEN cast('0' AS numeric) AND cast('50.0' AS real)
          173  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
          174  +
          175  +  do_eqp_test 1.$tn.3.9 {
          176  +    SELECT * FROM t1 WHERE b BETWEEN +75 AND +125 AND c BETWEEN +75 AND +125
          177  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
          178  +
          179  +  do_eqp_test 1.$tn.3.10 {
          180  +    SELECT * FROM t1
          181  +     WHERE b BETWEEN cast('75' AS int) AND cast('125.0' AS real)
          182  +       AND c BETWEEN cast('75' AS numeric) AND cast('125.0' AS real)
          183  +  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
   164    184   }
   165    185   
   166    186   finish_test
   167         -