/ Check-in [a870c196]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix VDBE opcodes OP_SeekLT and OP_SeekLE so that they work on intkey tables with non-numeric text values.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | tkt-9cf6c9bb
Files: files | file ages | folders
SHA3-256: a870c196d78d8b72c7353fa0015e96b2abd4be154541d76328e3a4f9e5da5d04
User & Date: dan 2019-05-08 11:42:49
Context
2019-05-08
11:52
Fix VDBE opcodes OP_SeekLT and OP_SeekLE so that they work on intkey tables with non-numeric text values. Fix for [9cf6c9bb]. check-in: 658b84d7 user: dan tags: trunk
11:42
Fix VDBE opcodes OP_SeekLT and OP_SeekLE so that they work on intkey tables with non-numeric text values. Closed-Leaf check-in: a870c196 user: dan tags: tkt-9cf6c9bb
04:33
Remove an ALWAYS() that was previously added by check-in [a0819086] but which turns out can sometimes be false. check-in: ad8fc5d8 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/vdbe.c.

  4008   4008       }
  4009   4009       iKey = sqlite3VdbeIntValue(pIn3);
  4010   4010   
  4011   4011       /* If the P3 value could not be converted into an integer without
  4012   4012       ** loss of information, then special processing is required... */
  4013   4013       if( (pIn3->flags & (MEM_Int|MEM_IntReal))==0 ){
  4014   4014         if( (pIn3->flags & MEM_Real)==0 ){
  4015         -        /* If the P3 value cannot be converted into any kind of a number,
  4016         -        ** then the seek is not possible, so jump to P2 */
         4015  +        if( (pIn3->flags & MEM_Null) || oc>=OP_SeekGE ){
  4017   4016           VdbeBranchTaken(1,2); goto jump_to_p2;
  4018   4017           break;
         4018  +        }else{
         4019  +          sqlite3BtreeLast(pC->uc.pCursor, &res);
         4020  +          goto seek_not_found;
  4019   4021         }
         4022  +      }else
  4020   4023   
  4021   4024         /* If the approximation iKey is larger than the actual real search
  4022   4025         ** term, substitute >= for > and < for <=. e.g. if the search term
  4023   4026         ** is 4.9 and the integer approximation 5:
  4024   4027         **
  4025   4028         **        (x >  4.9)    ->     (x >= 5)
  4026   4029         **        (x <= 4.9)    ->     (x <  5)

Changes to test/rowid.test.

   655    655   do_test rowid-11.3 {
   656    656     execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc'}
   657    657   } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
   658    658   do_test rowid-11.4 {
   659    659     execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc'}
   660    660   } {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
   661    661   
          662  +do_test rowid-11.asc.1 {
          663  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 ASC}
          664  +} {}
          665  +do_test rowid-11.asc.2 {
          666  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 ASC}
          667  +} {}
          668  +do_test rowid-11.asc.3 {
          669  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 ASC}
          670  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
          671  +do_test rowid-11.asc.4 {
          672  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 ASC}
          673  +} {1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8}
          674  +
          675  +do_test rowid-11.desc.1 {
          676  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>'abc' ORDER BY 1 DESC}
          677  +} {}
          678  +do_test rowid-11.desc.2 {
          679  +  execsql {SELECT rowid, a FROM t5 WHERE rowid>='abc' ORDER BY 1 DESC}
          680  +} {}
          681  +do_test rowid-11.desc.3 {
          682  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<'abc' ORDER BY 1 DESC}
          683  +} {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1}
          684  +do_test rowid-11.desc.4 {
          685  +  execsql {SELECT rowid, a FROM t5 WHERE rowid<='abc' ORDER BY 1 DESC}
          686  +} {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1}
          687  +
   662    688   # Test the automatic generation of rowids when the table already contains
   663    689   # a rowid with the maximum value.
   664    690   #
   665    691   # Once the maximum rowid is taken, rowids are normally chosen at
   666    692   # random.  By by reseting the random number generator, we can cause
   667    693   # the rowid guessing loop to collide with prior rowids, and test the
   668    694   # loop out to its limit of 100 iterations.  After 100 collisions, the
................................................................................
   714    740   db function addrow rowid_addrow_func
   715    741   do_execsql_test rowid-13.1 {
   716    742     CREATE TABLE t13(x);
   717    743     INSERT INTO t13(rowid,x) VALUES(1234,5);
   718    744     SELECT rowid, x, addrow(rowid+1000), '|' FROM t13 LIMIT 3;
   719    745     SELECT last_insert_rowid();
   720    746   } {1234 5 2234 | 2234 4990756 3234 | 3234 10458756 4234 | 4234}
          747  +
          748  +#-------------------------------------------------------------------------
          749  +do_execsql_test rowid-14.0 {
          750  +  CREATE TABLE t14(x INTEGER PRIMARY KEY);
          751  +  INSERT INTO t14(x) VALUES (100);
          752  +}
          753  +do_execsql_test rowid-14.1 {
          754  +  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
          755  +} {100}
          756  +do_execsql_test rowid-14.2 {
          757  +  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
          758  +} {100}
          759  +
          760  +do_execsql_test rowid-14.3 {
          761  +  DELETE FROM t14;
          762  +  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid ASC;
          763  +} {}
          764  +do_execsql_test rowid-14.4 {
          765  +  SELECT * FROM t14 WHERE x < 'a' ORDER BY rowid DESC;
          766  +} {}
   721    767   
   722    768   finish_test