/ Check-in [658b84d7]
Login

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. Fix for [9cf6c9bb].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 658b84d7f4a0886591c5aab30ed9e31c4a0f56db303eb863f24833ca37085d14
User & Date: dan 2019-05-08 11:52:13
Original Comment: Fix VDBE opcodes OP_SeekLT and OP_SeekLE so that they work on intkey tables with non-numeric text values.
References
2019-05-14
20:25
Fix a problem with the fix for [9cf6c9bb51] (commit [658b84d7]) that could cause a cursor to be left in an invalid state following a (rowid < text-value) search. check-in: bc7d2c16 user: dan tags: trunk
2019-05-09
11:19
Fix a problem in the new code introduced by [658b84d7] causing corruption and other errors to be ignored. check-in: 7ccf2e7d user: dan tags: trunk
2019-05-08
11:54 Ticket [9cf6c9bb] "<" or "<=" comparison of rowid and non-numeric text value sometimes gets the wrong answer. status still Closed with 3 other changes artifact: 8cbc4833 user: dan
Context
2019-05-08
17:27
Provide the SQLITE_OMIT_CASE_SENSITIVE_LIKE_PRAGMA compile-time option to omit the case_sensitive_like pragma. This change, in combination with documentation changes, is the current solution to ticket [a340eef47b0cad5]. check-in: eabe7f2d user: drh tags: trunk
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 Ignore Whitespace 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 */
  4017         -        VdbeBranchTaken(1,2); goto jump_to_p2;
  4018         -        break;
  4019         -      }
         4015  +        if( (pIn3->flags & MEM_Null) || oc>=OP_SeekGE ){
         4016  +          VdbeBranchTaken(1,2); goto jump_to_p2;
         4017  +          break;
         4018  +        }else{
         4019  +          sqlite3BtreeLast(pC->uc.pCursor, &res);
         4020  +          goto seek_not_found;
         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)
................................................................................
  4036   4039         ** term, substitute <= for < and > for >=.  */
  4037   4040         else if( pIn3->u.r>(double)iKey ){
  4038   4041           assert( OP_SeekLE==(OP_SeekLT+1) );
  4039   4042           assert( OP_SeekGT==(OP_SeekGE+1) );
  4040   4043           assert( (OP_SeekLT & 0x0001)==(OP_SeekGE & 0x0001) );
  4041   4044           if( (oc & 0x0001)==(OP_SeekLT & 0x0001) ) oc++;
  4042   4045         }
  4043         -    } 
         4046  +    }
  4044   4047       rc = sqlite3BtreeMovetoUnpacked(pC->uc.pCursor, 0, (u64)iKey, 0, &res);
  4045   4048       pC->movetoTarget = iKey;  /* Used by OP_Delete */
  4046   4049       if( rc!=SQLITE_OK ){
  4047   4050         goto abort_due_to_error;
  4048   4051       }
  4049   4052     }else{
  4050   4053       /* For a cursor with the BTREE_SEEK_EQ hint, only the OP_SeekGE and

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