SQLite

Check-in [c9ac3db8e0]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: c9ac3db8e08403398ec344757385334601a59374
User & Date: drh 2004-01-14 21:59:23.000
Context
2004-01-15
02:44
Reinsert the experimental sqlite_commit_hook() API. (CVS 1179) (check-in: 72bc84f2f1 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: c9ac3db8e0 user: drh tags: trunk)
13:50
Version 2.8.11 (CVS 1177) (check-in: a9f25347de user: drh tags: trunk)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/encode.c.
︙
11
12
13
14
15
16
17
18

19
20
21
22
23
24
25

11
12
13
14
15
16
17

18
19
20
21
22
23
24
25








-
+








*************************************************************************
** This file contains helper routines used to translate binary data into
** a null-terminated string (suitable for use in SQLite) and back again.
** These are convenience routines for use by people who want to store binary
** data in an SQLite database.  The code in this file is not used by any other
** part of the SQLite library.
**
** $Id: encode.c,v 1.9 2003/06/28 16:25:34 drh Exp $
** $Id: encode.c,v 1.10 2004/01/14 21:59:23 drh Exp $
*/
#include <string.h>

/*
** How This Encoder Works
**
** The output is allowed to contain any character except 0x27 (') and
︙
186
187
188
189
190
191
192

193
194
195
196
197
198
199

200
201
202

203
204
205
206
207
208
209
210
211
212
213
214
215

216
217
218
219
220
221
222

186
187
188
189
190
191
192
193
194
195
196
197
198
199

200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225








+






-
+



+













+








    }
    out[i++] = (c + e)&0xff;
  }
  return i;
}

#ifdef ENCODER_TEST
#include <stdio.h>
/*
** The subroutines above are not tested by the usual test suite.  To test
** these routines, compile just this one file with a -DENCODER_TEST=1 option
** and run the result.
*/
int main(int argc, char **argv){
  int i, j, n, m, nOut;
  int i, j, n, m, nOut, nByte;
  unsigned char in[30000];
  unsigned char out[33000];

  nByte = 0;
  for(i=0; i<sizeof(in); i++){
    printf("Test %d: ", i+1);
    n = rand() % (i+1);
    if( i%100==0 ){
      int k;
      for(j=k=0; j<n; j++){
        /* if( k==0 || k=='\'' ) k++; */
        in[j] = k;
        k = (k+1)&0xff;
      }
    }else{
      for(j=0; j<n; j++) in[j] = rand() & 0xff;
    }
    nByte += n;
    nOut = sqlite_encode_binary(in, n, out);
    if( nOut!=strlen(out) ){
      printf(" ERROR return value is %d instead of %d\n", nOut, strlen(out));
      exit(1);
    }
    m = (256*n + 1262)/253;
    printf("size %d->%d (max %d)", n, strlen(out)+1, m);
︙
237
238
239
240
241
242
243

244
245

240
241
242
243
244
245
246
247
248
249








+



    }
    if( memcmp(in, out, n)!=0 ){
      printf(" ERROR decode mismatch\n");
      exit(1);
    }
    printf(" OK\n");
  }
  fprintf(stderr, "Finished.  Total encoding: %d bytes\n", nByte);
}
#endif /* ENCODER_TEST */
Changes to src/vdbe.c.
︙
39
40
41
42
43
44
45
46

47
48
49
50
51
52
53

39
40
41
42
43
44
45

46
47
48
49
50
51
52
53








-
+








**
** Various scripts scan this source file in order to generate HTML
** documentation, headers files, or other derived files.  The formatting
** of the code in this file is, therefore, important.  See other comments
** in this file for details.  If in doubt, do not deviate from existing
** commenting and indentation practices when changing or adding code.
**
** $Id: vdbe.c,v 1.249 2004/01/07 20:37:52 drh Exp $
** $Id: vdbe.c,v 1.250 2004/01/14 21:59:23 drh Exp $
*/
#include "sqliteInt.h"
#include "os.h"
#include <ctype.h>
#include "vdbeInt.h"

/*
︙
1213
1214
1215
1216
1217
1218
1219
1220

1221
1222
1223
1224
1225
1226







1227
1228

1229
1230

1231
1232
1233
1234
1235





1236
1237

1238
1239






1240




1241
1242
1243
1244
1245
1246
1247

1213
1214
1215
1216
1217
1218
1219

1220
1221





1222
1223
1224
1225
1226
1227
1228
1229

1230
1231

1232
1233




1234
1235
1236
1237
1238
1239

1240


1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258








-
+

-
-
-
-
-
+
+
+
+
+
+
+

-
+

-
+

-
-
-
-
+
+
+
+
+

-
+
-
-
+
+
+
+
+
+

+
+
+
+








  int tos = p->tos;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  Integerify(p, tos);
  aStack[tos].i += pOp->p1;
  break;
}

/* Opcode: IsNumeric P1 P2 *
/* Opcode: ForceInt P1 P2 *
**
** Check the top of the stack to see if it is a numeric value.  A numeric
** value is an integer, a real number, or a string that looks like an 
** integer or a real number.  When P1==0, pop the stack and jump to P2
** if the value is numeric.  Otherwise fall through and leave the stack
** unchanged.  The sense of the test is inverted when P1==1.
** Convert the top of the stack into an integer.  If the current top of
** the stack is not numeric (meaning that is is a NULL or a string that
** does not look like an integer or floating point number) then pop the
** stack and jump to P2.  If the top of the stack is numeric then
** convert it into the least integer that is greater than or equal to its
** current value if P1==0, or to the least integer that is strictly
** greater than its current value if P1==1.
*/
case OP_IsNumeric: {
case OP_ForceInt: {
  int tos = p->tos;
  int r;
  int v;
  VERIFY( if( tos<0 ) goto not_enough_stack; )
  r = (aStack[tos].flags & (STK_Int|STK_Real))!=0 
           || (zStack[tos] && sqliteIsNumber(zStack[tos]));
  if( pOp->p1 ){
    r = !r;
  if( (aStack[tos].flags & (STK_Int|STK_Real))==0
         && (zStack[tos]==0 || sqliteIsNumber(zStack[tos])==0) ){
    POPSTACK;
    pc = pOp->p2 - 1;
    break;
  }
  if( r ){
  if( aStack[tos].flags & STK_Int ){
    POPSTACK;
    pc = pOp->p2 - 1;
    v = aStack[tos].i + (pOp->p1!=0);
  }else{
    Realify(p, tos);
    v = (int)aStack[tos].r;
    if( aStack[tos].r>(double)v ) v++;
    if( pOp->p1 && aStack[tos].r==(double)v ) v++;
  }
  if( aStack[tos].flags & STK_Dyn ) sqliteFree(zStack[tos]);
  zStack[tos] = 0;
  aStack[tos].i = v;
  aStack[tos].flags = STK_Int;
  break;
}

/* Opcode: MustBeInt P1 P2 *
** 
** Force the top of the stack to be an integer.  If the top of the
** stack is not an integer and cannot be converted into an integer
︙
Changes to src/where.c.
︙
8
9
10
11
12
13
14
15

16
17
18
19
20
21
22

8
9
10
11
12
13
14

15
16
17
18
19
20
21
22








-
+








**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This module contains C code that generates VDBE code used to process
** the WHERE clause of SQL statements.
**
** $Id: where.c,v 1.86 2004/01/07 20:37:52 drh Exp $
** $Id: where.c,v 1.87 2004/01/14 21:59:24 drh Exp $
*/
#include "sqliteInt.h"

/*
** The query generator uses an array of instances of this structure to
** help it analyze the subexpressions of the WHERE clause.  Each WHERE
** clause subexpression is separated from the others by an AND operator.
︙
851
852
853
854
855
856
857
858
859


860
861
862
863
864
865
866
867
868

851
852
853
854
855
856
857


858
859


860
861
862
863
864
865
866








-
-
+
+
-
-








        assert( aExpr[k].p!=0 );
        assert( aExpr[k].idxLeft==iCur || aExpr[k].idxRight==iCur );
        if( aExpr[k].idxLeft==iCur ){
          sqliteExprCode(pParse, aExpr[k].p->pRight);
        }else{
          sqliteExprCode(pParse, aExpr[k].p->pLeft);
        }
        sqliteVdbeAddOp(v, OP_IsNumeric, 1, brk);
        if( aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT ){
        sqliteVdbeAddOp(v, OP_ForceInt,
          aExpr[k].p->op==TK_LT || aExpr[k].p->op==TK_GT, brk);
          sqliteVdbeAddOp(v, OP_AddImm, 1, 0);
        }
        sqliteVdbeAddOp(v, OP_MoveTo, iCur, brk);
        aExpr[k].p = 0;
      }else{
        sqliteVdbeAddOp(v, OP_Rewind, iCur, brk);
      }
      if( iDirectLt[i]>=0 ){
        k = iDirectLt[i];
︙
Changes to test/misc3.test.
︙
9
10
11
12
13
14
15
16

17
18
19
20
21
22
23

9
10
11
12
13
14
15

16
17
18
19
20
21
22
23








-
+








#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file implements tests for miscellanous features that were
# left out of other test files.
#
# $Id: misc3.test,v 1.5 2004/01/14 13:38:54 drh Exp $
# $Id: misc3.test,v 1.6 2004/01/14 21:59:24 drh Exp $

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

# Ticket #529.  Make sure an ABORT does not damage the in-memory cache
# that will be used by subsequent statements in the same transaction.
#
︙
202
203
204
205
206
207
208
209

210

202
203
204
205
206
207
208
209
210
211









+


  }
} {64}
do_test misc3-4.3 {
  execsql {
    SELECT count(a) FROM t3 WHERE b IN (SELECT b FROM t3 ORDER BY a+1);
  }
} {64}


finish_test
Changes to test/rowid.test.
︙
8
9
10
11
12
13
14
15

16
17
18
19
20
21
22

8
9
10
11
12
13
14

15
16
17
18
19
20
21
22








-
+








#    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 magic ROWID column that is
# found on all tables.
#
# $Id: rowid.test,v 1.12 2003/07/06 17:22:25 drh Exp $
# $Id: rowid.test,v 1.13 2004/01/14 21:59:24 drh Exp $

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

# Basic ROWID functionality tests.
#
do_test rowid-1.1 {
︙
466
467
468
469
470
471
472
473


474





























































































































































475

466
467
468
469
470
471
472
473
474
475

476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633









+
+
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+


  }
} {123 124}
do_test rowid-9.10 {
  execsql {
    SELECT * FROM t3 WHERE a>=122.9 AND a<=123.1
  }
} {123}

# Ticket #567.  Comparisons of ROWID or integery primary key against
# floating point numbers still do not always work.

#
do_test rowid-10.1 {
  execsql {
    CREATE TABLE t5(a);
    INSERT INTO t5 VALUES(1);
    INSERT INTO t5 VALUES(2);
    INSERT INTO t5 SELECT a+2 FROM t5;
    INSERT INTO t5 SELECT a+4 FROM t5;
    SELECT rowid, * FROM t5;
  }
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
do_test rowid-10.2 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5}
} {6 6 7 7 8 8}
do_test rowid-10.3 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0}
} {5 5 6 6 7 7 8 8}
do_test rowid-10.4 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5}
} {6 6 7 7 8 8}
do_test rowid-10.3.2 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0}
} {6 6 7 7 8 8}
do_test rowid-10.5 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid}
} {6 6 7 7 8 8}
do_test rowid-10.6 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid}
} {6 6 7 7 8 8}
do_test rowid-10.7 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5}
} {1 1 2 2 3 3 4 4 5 5}
do_test rowid-10.8 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5}
} {1 1 2 2 3 3 4 4 5 5}
do_test rowid-10.9 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid}
} {1 1 2 2 3 3 4 4 5 5}
do_test rowid-10.10 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid}
} {1 1 2 2 3 3 4 4 5 5}
do_test rowid-10.11 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.5 ORDER BY rowid DESC}
} {8 8 7 7 6 6}
do_test rowid-10.11.2 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>=5.0 ORDER BY rowid DESC}
} {8 8 7 7 6 6 5 5}
do_test rowid-10.12 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.5 ORDER BY rowid DESC}
} {8 8 7 7 6 6}
do_test rowid-10.12.2 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>5.0 ORDER BY rowid DESC}
} {8 8 7 7 6 6}
do_test rowid-10.13 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5<=rowid ORDER BY rowid DESC}
} {8 8 7 7 6 6}
do_test rowid-10.14 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5<rowid ORDER BY rowid DESC}
} {8 8 7 7 6 6}
do_test rowid-10.15 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<=5.5 ORDER BY rowid DESC}
} {5 5 4 4 3 3 2 2 1 1}
do_test rowid-10.16 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<5.5 ORDER BY rowid DESC}
} {5 5 4 4 3 3 2 2 1 1}
do_test rowid-10.17 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5>=rowid ORDER BY rowid DESC}
} {5 5 4 4 3 3 2 2 1 1}
do_test rowid-10.18 {
  execsql {SELECT rowid, a FROM t5 WHERE 5.5>rowid ORDER BY rowid DESC}
} {5 5 4 4 3 3 2 2 1 1}

do_test rowid-10.30 {
  execsql {
    CREATE TABLE t6(a);
    INSERT INTO t6(rowid,a) SELECT -a,a FROM t5;
    SELECT rowid, * FROM t6;
  }
} {-8 8 -7 7 -6 6 -5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.31.1 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5}
} {-5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.31.2 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0}
} {-5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.32.1 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.5 ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4 -5 5}
do_test rowid-10.32.1 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>=-5.0 ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4 -5 5}
do_test rowid-10.33 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid}
} {-5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.34 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5<=rowid ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4 -5 5}
do_test rowid-10.35.1 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5}
} {-5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.35.2 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0}
} {-4 4 -3 3 -2 2 -1 1}
do_test rowid-10.36.1 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.5 ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4 -5 5}
do_test rowid-10.36.2 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid>-5.0 ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4}
do_test rowid-10.37 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid}
} {-5 5 -4 4 -3 3 -2 2 -1 1}
do_test rowid-10.38 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5<rowid ORDER BY rowid DESC}
} {-1 1 -2 2 -3 3 -4 4 -5 5}
do_test rowid-10.39 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5}
} {-8 8 -7 7 -6 6}
do_test rowid-10.40 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid<=-5.5 ORDER BY rowid DESC}
} {-6 6 -7 7 -8 8}
do_test rowid-10.41 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid}
} {-8 8 -7 7 -6 6}
do_test rowid-10.42 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5>=rowid ORDER BY rowid DESC}
} {-6 6 -7 7 -8 8}
do_test rowid-10.43 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5}
} {-8 8 -7 7 -6 6}
do_test rowid-10.44 {
  execsql {SELECT rowid, a FROM t6 WHERE rowid<-5.5 ORDER BY rowid DESC}
} {-6 6 -7 7 -8 8}
do_test rowid-10.44 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid}
} {-8 8 -7 7 -6 6}
do_test rowid-10.46 {
  execsql {SELECT rowid, a FROM t6 WHERE -5.5>rowid ORDER BY rowid DESC}
} {-6 6 -7 7 -8 8}

# Comparison of rowid against string values.
#
do_test rowid-11.1 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc'}
} {}
do_test rowid-11.2 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc'}
} {}
do_test rowid-11.3 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
do_test rowid-11.4 {
  execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}



finish_test