/ Check-in [c9ac3db8]
Login

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

Overview
Comment:Fix comparisons of ROWID against floating point numbers so that they work correctly. Ticket #377 and #567. (CVS 1178)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c9ac3db8e08403398ec344757385334601a59374
User & Date: drh 2004-01-14 21:59:23
Context
2004-01-15
02:44
Reinsert the experimental sqlite_commit_hook() API. (CVS 1179) check-in: 72bc84f2 user: drh tags: trunk
2004-01-14
21:59
Fix comparisons of ROWID against floating point numbers so that they work correctly. Ticket #377 and #567. (CVS 1178) check-in: c9ac3db8 user: drh tags: trunk
13:50
Version 2.8.11 (CVS 1177) check-in: a9f25347 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/encode.c.

    11     11   *************************************************************************
    12     12   ** This file contains helper routines used to translate binary data into
    13     13   ** a null-terminated string (suitable for use in SQLite) and back again.
    14     14   ** These are convenience routines for use by people who want to store binary
    15     15   ** data in an SQLite database.  The code in this file is not used by any other
    16     16   ** part of the SQLite library.
    17     17   **
    18         -** $Id: encode.c,v 1.9 2003/06/28 16:25:34 drh Exp $
           18  +** $Id: encode.c,v 1.10 2004/01/14 21:59:23 drh Exp $
    19     19   */
    20     20   #include <string.h>
    21     21   
    22     22   /*
    23     23   ** How This Encoder Works
    24     24   **
    25     25   ** The output is allowed to contain any character except 0x27 (') and
................................................................................
   186    186       }
   187    187       out[i++] = (c + e)&0xff;
   188    188     }
   189    189     return i;
   190    190   }
   191    191   
   192    192   #ifdef ENCODER_TEST
          193  +#include <stdio.h>
   193    194   /*
   194    195   ** The subroutines above are not tested by the usual test suite.  To test
   195    196   ** these routines, compile just this one file with a -DENCODER_TEST=1 option
   196    197   ** and run the result.
   197    198   */
   198    199   int main(int argc, char **argv){
   199         -  int i, j, n, m, nOut;
          200  +  int i, j, n, m, nOut, nByte;
   200    201     unsigned char in[30000];
   201    202     unsigned char out[33000];
   202    203   
          204  +  nByte = 0;
   203    205     for(i=0; i<sizeof(in); i++){
   204    206       printf("Test %d: ", i+1);
   205    207       n = rand() % (i+1);
   206    208       if( i%100==0 ){
   207    209         int k;
   208    210         for(j=k=0; j<n; j++){
   209    211           /* if( k==0 || k=='\'' ) k++; */
   210    212           in[j] = k;
   211    213           k = (k+1)&0xff;
   212    214         }
   213    215       }else{
   214    216         for(j=0; j<n; j++) in[j] = rand() & 0xff;
   215    217       }
          218  +    nByte += n;
   216    219       nOut = sqlite_encode_binary(in, n, out);
   217    220       if( nOut!=strlen(out) ){
   218    221         printf(" ERROR return value is %d instead of %d\n", nOut, strlen(out));
   219    222         exit(1);
   220    223       }
   221    224       m = (256*n + 1262)/253;
   222    225       printf("size %d->%d (max %d)", n, strlen(out)+1, m);
................................................................................
   237    240       }
   238    241       if( memcmp(in, out, n)!=0 ){
   239    242         printf(" ERROR decode mismatch\n");
   240    243         exit(1);
   241    244       }
   242    245       printf(" OK\n");
   243    246     }
          247  +  fprintf(stderr, "Finished.  Total encoding: %d bytes\n", nByte);
   244    248   }
   245    249   #endif /* ENCODER_TEST */

Changes to src/vdbe.c.

    39     39   **
    40     40   ** Various scripts scan this source file in order to generate HTML
    41     41   ** documentation, headers files, or other derived files.  The formatting
    42     42   ** of the code in this file is, therefore, important.  See other comments
    43     43   ** in this file for details.  If in doubt, do not deviate from existing
    44     44   ** commenting and indentation practices when changing or adding code.
    45     45   **
    46         -** $Id: vdbe.c,v 1.249 2004/01/07 20:37:52 drh Exp $
           46  +** $Id: vdbe.c,v 1.250 2004/01/14 21:59:23 drh Exp $
    47     47   */
    48     48   #include "sqliteInt.h"
    49     49   #include "os.h"
    50     50   #include <ctype.h>
    51     51   #include "vdbeInt.h"
    52     52   
    53     53   /*
................................................................................
  1213   1213     int tos = p->tos;
  1214   1214     VERIFY( if( tos<0 ) goto not_enough_stack; )
  1215   1215     Integerify(p, tos);
  1216   1216     aStack[tos].i += pOp->p1;
  1217   1217     break;
  1218   1218   }
  1219   1219   
  1220         -/* Opcode: IsNumeric P1 P2 *
         1220  +/* Opcode: ForceInt P1 P2 *
  1221   1221   **
  1222         -** Check the top of the stack to see if it is a numeric value.  A numeric
  1223         -** value is an integer, a real number, or a string that looks like an 
  1224         -** integer or a real number.  When P1==0, pop the stack and jump to P2
  1225         -** if the value is numeric.  Otherwise fall through and leave the stack
  1226         -** unchanged.  The sense of the test is inverted when P1==1.
         1222  +** Convert the top of the stack into an integer.  If the current top of
         1223  +** the stack is not numeric (meaning that is is a NULL or a string that
         1224  +** does not look like an integer or floating point number) then pop the
         1225  +** stack and jump to P2.  If the top of the stack is numeric then
         1226  +** convert it into the least integer that is greater than or equal to its
         1227  +** current value if P1==0, or to the least integer that is strictly
         1228  +** greater than its current value if P1==1.
  1227   1229   */
  1228         -case OP_IsNumeric: {
         1230  +case OP_ForceInt: {
  1229   1231     int tos = p->tos;
  1230         -  int r;
         1232  +  int v;
  1231   1233     VERIFY( if( tos<0 ) goto not_enough_stack; )
  1232         -  r = (aStack[tos].flags & (STK_Int|STK_Real))!=0 
  1233         -           || (zStack[tos] && sqliteIsNumber(zStack[tos]));
  1234         -  if( pOp->p1 ){
  1235         -    r = !r;
  1236         -  }
  1237         -  if( r ){
         1234  +  if( (aStack[tos].flags & (STK_Int|STK_Real))==0
         1235  +         && (zStack[tos]==0 || sqliteIsNumber(zStack[tos])==0) ){
  1238   1236       POPSTACK;
  1239   1237       pc = pOp->p2 - 1;
         1238  +    break;
  1240   1239     }
         1240  +  if( aStack[tos].flags & STK_Int ){
         1241  +    v = aStack[tos].i + (pOp->p1!=0);
         1242  +  }else{
         1243  +    Realify(p, tos);
         1244  +    v = (int)aStack[tos].r;
         1245  +    if( aStack[tos].r>(double)v ) v++;
         1246  +    if( pOp->p1 && aStack[tos].r==(double)v ) v++;
         1247  +  }
         1248  +  if( aStack[tos].flags & STK_Dyn ) sqliteFree(zStack[tos]);
         1249  +  zStack[tos] = 0;
         1250  +  aStack[tos].i = v;
         1251  +  aStack[tos].flags = STK_Int;
  1241   1252     break;
  1242   1253   }
  1243   1254   
  1244   1255   /* Opcode: MustBeInt P1 P2 *
  1245   1256   ** 
  1246   1257   ** Force the top of the stack to be an integer.  If the top of the
  1247   1258   ** stack is not an integer and cannot be converted into an integer

Changes to src/where.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 module contains C code that generates VDBE code used to process
    13     13   ** the WHERE clause of SQL statements.
    14     14   **
    15         -** $Id: where.c,v 1.86 2004/01/07 20:37:52 drh Exp $
           15  +** $Id: where.c,v 1.87 2004/01/14 21:59:24 drh Exp $
    16     16   */
    17     17   #include "sqliteInt.h"
    18     18   
    19     19   /*
    20     20   ** The query generator uses an array of instances of this structure to
    21     21   ** help it analyze the subexpressions of the WHERE clause.  Each WHERE
    22     22   ** clause subexpression is separated from the others by an AND operator.
................................................................................
   851    851           assert( aExpr[k].p!=0 );
   852    852           assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
   853    853           if( aExpr[k].idxLeft==iCur ){
   854    854             sqliteExprCode(pParse, aExpr[k].p->pRight);
   855    855           }else{
   856    856             sqliteExprCode(pParse, aExpr[k].p->pLeft);
   857    857           }
   858         -        sqliteVdbeAddOp(v, OP_IsNumeric, 1, brk);
   859         -        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
   860         -          sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
   861         -        }
          858  +        sqliteVdbeAddOp(v, OP_ForceInt,
          859  +          aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT, brk);
   862    860           sqliteVdbeAddOp(v, OP_MoveTo, iCur, brk);
   863    861           aExpr[k].p = 0;
   864    862         }else{
   865    863           sqliteVdbeAddOp(v, OP_Rewind, iCur, brk);
   866    864         }
   867    865         if( iDirectLt[i]>=0 ){
   868    866           k = iDirectLt[i];

Changes to test/misc3.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for miscellanous features that were
    14     14   # left out of other test files.
    15     15   #
    16         -# $Id: misc3.test,v 1.5 2004/01/14 13:38:54 drh Exp $
           16  +# $Id: misc3.test,v 1.6 2004/01/14 21:59:24 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Ticket #529.  Make sure an ABORT does not damage the in-memory cache
    22     22   # that will be used by subsequent statements in the same transaction.
    23     23   #
................................................................................
   202    202     }
   203    203   } {64}
   204    204   do_test misc3-4.3 {
   205    205     execsql {
   206    206       SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
   207    207     }
   208    208   } {64}
          209  +
   209    210   
   210    211   finish_test

Changes to test/rowid.test.

     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 magic ROWID column that is
    13     13   # found on all tables.
    14     14   #
    15         -# $Id: rowid.test,v 1.12 2003/07/06 17:22:25 drh Exp $
           15  +# $Id: rowid.test,v 1.13 2004/01/14 21:59:24 drh Exp $
    16     16   
    17     17   set testdir [file dirname $argv0]
    18     18   source $testdir/tester.tcl
    19     19   
    20     20   # Basic ROWID functionality tests.
    21     21   #
    22     22   do_test rowid-1.1 {
................................................................................
   466    466     }
   467    467   } {123 124}
   468    468   do_test rowid-9.10 {
   469    469     execsql {
   470    470       SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
   471    471     }
   472    472   } {123}
          473  +
          474  +# Ticket #567.  Comparisons of ROWID or integery primary key against
          475  +# floating point numbers still do not always work.
          476  +#
          477  +do_test rowid-10.1 {
          478  +  execsql {
          479  +    CREATE TABLE t5(a);
          480  +    INSERT INTO t5 VALUES(1);
          481  +    INSERT INTO t5 VALUES(2);
          482  +    INSERT INTO t5 SELECT a+2 FROM t5;
          483  +    INSERT INTO t5 SELECT a+4 FROM t5;
          484  +    SELECT rowid, * FROM t5;
          485  +  }
          486  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
          487  +do_test rowid-10.2 {
          488  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
          489  +} {6 6 7 7 8 8}
          490  +do_test rowid-10.3 {
          491  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
          492  +} {5 5 6 6 7 7 8 8}
          493  +do_test rowid-10.4 {
          494  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
          495  +} {6 6 7 7 8 8}
          496  +do_test rowid-10.3.2 {
          497  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
          498  +} {6 6 7 7 8 8}
          499  +do_test rowid-10.5 {
          500  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
          501  +} {6 6 7 7 8 8}
          502  +do_test rowid-10.6 {
          503  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
          504  +} {6 6 7 7 8 8}
          505  +do_test rowid-10.7 {
          506  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
          507  +} {1 1 2 2 3 3 4 4 5 5}
          508  +do_test rowid-10.8 {
          509  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
          510  +} {1 1 2 2 3 3 4 4 5 5}
          511  +do_test rowid-10.9 {
          512  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
          513  +} {1 1 2 2 3 3 4 4 5 5}
          514  +do_test rowid-10.10 {
          515  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
          516  +} {1 1 2 2 3 3 4 4 5 5}
          517  +do_test rowid-10.11 {
          518  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
          519  +} {8 8 7 7 6 6}
          520  +do_test rowid-10.11.2 {
          521  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
          522  +} {8 8 7 7 6 6 5 5}
          523  +do_test rowid-10.12 {
          524  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
          525  +} {8 8 7 7 6 6}
          526  +do_test rowid-10.12.2 {
          527  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
          528  +} {8 8 7 7 6 6}
          529  +do_test rowid-10.13 {
          530  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
          531  +} {8 8 7 7 6 6}
          532  +do_test rowid-10.14 {
          533  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
          534  +} {8 8 7 7 6 6}
          535  +do_test rowid-10.15 {
          536  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
          537  +} {5 5 4 4 3 3 2 2 1 1}
          538  +do_test rowid-10.16 {
          539  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
          540  +} {5 5 4 4 3 3 2 2 1 1}
          541  +do_test rowid-10.17 {
          542  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
          543  +} {5 5 4 4 3 3 2 2 1 1}
          544  +do_test rowid-10.18 {
          545  +  execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
          546  +} {5 5 4 4 3 3 2 2 1 1}
          547  +
          548  +do_test rowid-10.30 {
          549  +  execsql {
          550  +    CREATE TABLE t6(a);
          551  +    INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
          552  +    SELECT rowid, * FROM t6;
          553  +  }
          554  +} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
          555  +do_test rowid-10.31.1 {
          556  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
          557  +} {-5 5 -4 4 -3 3 -2 2 -1 1}
          558  +do_test rowid-10.31.2 {
          559  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
          560  +} {-5 5 -4 4 -3 3 -2 2 -1 1}
          561  +do_test rowid-10.32.1 {
          562  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
          563  +} {-1 1 -2 2 -3 3 -4 4 -5 5}
          564  +do_test rowid-10.32.1 {
          565  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
          566  +} {-1 1 -2 2 -3 3 -4 4 -5 5}
          567  +do_test rowid-10.33 {
          568  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
          569  +} {-5 5 -4 4 -3 3 -2 2 -1 1}
          570  +do_test rowid-10.34 {
          571  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
          572  +} {-1 1 -2 2 -3 3 -4 4 -5 5}
          573  +do_test rowid-10.35.1 {
          574  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
          575  +} {-5 5 -4 4 -3 3 -2 2 -1 1}
          576  +do_test rowid-10.35.2 {
          577  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
          578  +} {-4 4 -3 3 -2 2 -1 1}
          579  +do_test rowid-10.36.1 {
          580  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
          581  +} {-1 1 -2 2 -3 3 -4 4 -5 5}
          582  +do_test rowid-10.36.2 {
          583  +  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
          584  +} {-1 1 -2 2 -3 3 -4 4}
          585  +do_test rowid-10.37 {
          586  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
          587  +} {-5 5 -4 4 -3 3 -2 2 -1 1}
          588  +do_test rowid-10.38 {
          589  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
          590  +} {-1 1 -2 2 -3 3 -4 4 -5 5}
          591  +do_test rowid-10.39 {
          592  +  execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
          593  +} {-8 8 -7 7 -6 6}
          594  +do_test rowid-10.40 {
          595  +  execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
          596  +} {-6 6 -7 7 -8 8}
          597  +do_test rowid-10.41 {
          598  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
          599  +} {-8 8 -7 7 -6 6}
          600  +do_test rowid-10.42 {
          601  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
          602  +} {-6 6 -7 7 -8 8}
          603  +do_test rowid-10.43 {
          604  +  execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
          605  +} {-8 8 -7 7 -6 6}
          606  +do_test rowid-10.44 {
          607  +  execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
          608  +} {-6 6 -7 7 -8 8}
          609  +do_test rowid-10.44 {
          610  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
          611  +} {-8 8 -7 7 -6 6}
          612  +do_test rowid-10.46 {
          613  +  execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
          614  +} {-6 6 -7 7 -8 8}
          615  +
          616  +# Comparison of rowid against string values.
          617  +#
          618  +do_test rowid-11.1 {
          619  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
          620  +} {}
          621  +do_test rowid-11.2 {
          622  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
          623  +} {}
          624  +do_test rowid-11.3 {
          625  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
          626  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
          627  +do_test rowid-11.4 {
          628  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
          629  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
          630  +
   473    631   
   474    632   
   475    633   finish_test