/ Check-in [81b9f0f5]
Login

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

Overview
Comment:Fix the OP_SeekGE, OP_SeekGT, OP_SeekLE, and OP_SeekLT opcodes so that they preserve the datatype of the value in the register used as the key. Ticket [d9f584e936c7a8d0]
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 81b9f0f55042777b37de78069f7089041a9ee21dd0a0c86b879053fb46e140b4
User & Date: drh 2019-08-22 00:53:16
Context
2019-08-22
11:11
Fix a false-positive in sqlite3ExprNeedsNoAffinityChange(). Ticket [ac184eb571d5e6e0] check-in: e62eddbb user: drh tags: trunk
00:53
Fix the OP_SeekGE, OP_SeekGT, OP_SeekLE, and OP_SeekLT opcodes so that they preserve the datatype of the value in the register used as the key. Ticket [d9f584e936c7a8d0] check-in: 81b9f0f5 user: drh tags: trunk
2019-08-21
14:54
Avoid assuming that "column IS ?", where column is declared UNIQUE, matches only a single row (as "?" might be NULL). Fix for [b8689402]. check-in: d02490a2 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

4123
4124
4125
4126
4127
4128
4129

4130
4131
4132
4133
4134
4135
4136
4137

4138
4139
4140
4141


4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
#ifdef SQLITE_DEBUG
  pC->seekOp = pOp->opcode;
#endif

  pC->deferredMoveto = 0;
  pC->cacheStatus = CACHE_STALE;
  if( pC->isTable ){

    /* The BTREE_SEEK_EQ flag is only set on index cursors */
    assert( sqlite3BtreeCursorHasHint(pC->uc.pCursor, BTREE_SEEK_EQ)==0
              || CORRUPT_DB );

    /* The input value in P3 might be of any type: integer, real, string,
    ** blob, or NULL.  But it needs to be an integer before we can do
    ** the seek, so convert it. */
    pIn3 = &aMem[pOp->p3];

    if( (pIn3->flags & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Str))==MEM_Str ){
      applyNumericAffinity(pIn3, 0);
    }
    iKey = sqlite3VdbeIntValue(pIn3);



    /* If the P3 value could not be converted into an integer without
    ** loss of information, then special processing is required... */
    if( (pIn3->flags & (MEM_Int|MEM_IntReal))==0 ){
      if( (pIn3->flags & MEM_Real)==0 ){
        if( (pIn3->flags & MEM_Null) || oc>=OP_SeekGE ){
          VdbeBranchTaken(1,2);
          goto jump_to_p2;
        }else{
          rc = sqlite3BtreeLast(pC->uc.pCursor, &res);
          if( rc!=SQLITE_OK ) goto abort_due_to_error;
          goto seek_not_found;
        }







>








>
|


|
>
>



|
|
|







4123
4124
4125
4126
4127
4128
4129
4130
4131
4132
4133
4134
4135
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
4153
4154
4155
4156
4157
4158
#ifdef SQLITE_DEBUG
  pC->seekOp = pOp->opcode;
#endif

  pC->deferredMoveto = 0;
  pC->cacheStatus = CACHE_STALE;
  if( pC->isTable ){
    u16 flags3, newType;
    /* The BTREE_SEEK_EQ flag is only set on index cursors */
    assert( sqlite3BtreeCursorHasHint(pC->uc.pCursor, BTREE_SEEK_EQ)==0
              || CORRUPT_DB );

    /* The input value in P3 might be of any type: integer, real, string,
    ** blob, or NULL.  But it needs to be an integer before we can do
    ** the seek, so convert it. */
    pIn3 = &aMem[pOp->p3];
    flags3 = pIn3->flags;
    if( (flags3 & (MEM_Int|MEM_Real|MEM_IntReal|MEM_Str))==MEM_Str ){
      applyNumericAffinity(pIn3, 0);
    }
    iKey = sqlite3VdbeIntValue(pIn3); /* Get the integer key value */
    newType = pIn3->flags; /* Record the type after applying numeric affinity */
    pIn3->flags = flags3;  /* But convert the type back to its original */

    /* If the P3 value could not be converted into an integer without
    ** loss of information, then special processing is required... */
    if( (newType & (MEM_Int|MEM_IntReal))==0 ){
      if( (newType & MEM_Real)==0 ){
        if( (newType & MEM_Null) || oc>=OP_SeekGE ){
          VdbeBranchTaken(1,2);
          goto jump_to_p2;
        }else{
          rc = sqlite3BtreeLast(pC->uc.pCursor, &res);
          if( rc!=SQLITE_OK ) goto abort_due_to_error;
          goto seek_not_found;
        }

Changes to test/where.test.

1533
1534
1535
1536
1537
1538
1539
1540

































1541
do_catchsql_test where-25.4 {
  SELECT * FROM t1 WHERE c='iii'
} {0 {}}
do_catchsql_test where-25.5 {
  INSERT INTO t1 VALUES(4, 'four', 'iii') 
    ON CONFLICT(c) DO UPDATE SET b=NULL
} {1 {corrupt database}}


































finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
do_catchsql_test where-25.4 {
  SELECT * FROM t1 WHERE c='iii'
} {0 {}}
do_catchsql_test where-25.5 {
  INSERT INTO t1 VALUES(4, 'four', 'iii') 
    ON CONFLICT(c) DO UPDATE SET b=NULL
} {1 {corrupt database}}

# 2019-08-21 Ticket https://www.sqlite.org/src/info/d9f584e936c7a8d0
#
db close
sqlite3 db :memory:
do_execsql_test where-26.1 {
  CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT);
  INSERT INTO t0(c0, c1) VALUES (1, 'a');
  CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT);
  INSERT INTO t1(c0, c1) VALUES (1, 'a');
  SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0;
} {1 a}
do_execsql_test where-26.2 {
  SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0;
} {1 a}
do_execsql_test where-26.3 {
  SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0;
} {1 a}
do_execsql_test where-26.4 {
  SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0;
} {1 a}
do_execsql_test where-26.5 {
  SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0;
} {1}
do_execsql_test where-26.6 {
  SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1;
} {1}
do_execsql_test where-26.7 {
  SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0;
} {1}
do_execsql_test where-26.8 {
  SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1;
} {1}

finish_test