SQLite

Check-in [231c72d9f6]
Login

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

Overview
Comment:Fix a problem handling (a, b) IN (SELECT ...) expressions when there is an index on just one of "a" or "b".
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 231c72d9f651f3a70d5c8af080f3ff181b89d939
User & Date: dan 2016-09-06 14:58:15.417
Context
2016-09-06
15:25
Fix the header comment on codeEqualityTerm(). (check-in: b7e710e406 user: drh tags: rowvalue)
14:58
Fix a problem handling (a, b) IN (SELECT ...) expressions when there is an index on just one of "a" or "b". (check-in: 231c72d9f6 user: dan tags: rowvalue)
14:37
Enhance the sqlite3GetTempRange() and sqlite3ReleaseTempRange() internal routines so that they use sqlite3GetTempReg() and sqlite3ReleaseTempReg() when nReg==1. (check-in: 4071da2f87 user: drh tags: rowvalue)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/wherecode.c.
︙︙
459
460
461
462
463
464
465
466






467


468
469
470

471
472
473
474
475
476
477

          Expr *pNewLhs = sqlite3ExprDup(db, pOrigLhs->a[iField].pExpr, 0);

          pRhs = sqlite3ExprListAppend(pParse, pRhs, pNewRhs);
          pLhs = sqlite3ExprListAppend(pParse, pLhs, pNewLhs);
        }
      }
      if( !db->mallocFailed ){
        pX->x.pSelect->pEList = pRhs;






        pX->pLeft->x.pList = pLhs;


        eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
        pX->x.pSelect->pEList = pOrigRhs;
        pX->pLeft->x.pList = pOrigLhs;

      }
      sqlite3ExprListDelete(pParse->db, pLhs);
      sqlite3ExprListDelete(pParse->db, pRhs);
    }

    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );








|
>
>
>
>
>
>
|
>
>


|
>








459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486

          Expr *pNewLhs = sqlite3ExprDup(db, pOrigLhs->a[iField].pExpr, 0);

          pRhs = sqlite3ExprListAppend(pParse, pRhs, pNewRhs);
          pLhs = sqlite3ExprListAppend(pParse, pLhs, pNewLhs);
        }
      }
      if( !db->mallocFailed ){
        Expr *pLeft = pX->pLeft;
        /* Take care here not to generate a TK_VECTOR containing only a
        ** single value. Since the parser never creates such a vector, some
        ** of the subroutines do not handle this case.  */
        if( pLhs->nExpr==1 ){
          pX->pLeft = pLhs->a[0].pExpr;
        }else{
          pLeft->x.pList = pLhs;
        }
        pX->x.pSelect->pEList = pRhs;
        eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
        pX->x.pSelect->pEList = pOrigRhs;
        pLeft->x.pList = pOrigLhs;
        pX->pLeft = pLeft;
      }
      sqlite3ExprListDelete(pParse->db, pLhs);
      sqlite3ExprListDelete(pParse->db, pRhs);
    }

    if( eType==IN_INDEX_INDEX_DESC ){
      testcase( bRev );
︙︙
Changes to test/rowvalue9.test.
︙︙
13
14
15
16
17
18
19













20
21
22
23
24
25
26

# constructors.
#


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














do_execsql_test 1.0.1 {
  CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b));
 
  INSERT INTO a1 (rowid, c, b, a) VALUES(3,  '0x03', 1, 1);
  INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2);
  INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3);








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








13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

# constructors.
#


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

# Tests:
#
#  1.*: Test that affinities are handled correctly by various row-value
#       operations without indexes.
#
#  2.*: Test an affinity bug that came up during testing.
#
#  3.*: Test a row-value version of the bug tested by 2.*.
#
#  4.*: Test that affinities are handled correctly by various row-value
#       operations with assorted indexes.
#

do_execsql_test 1.0.1 {
  CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b));
 
  INSERT INTO a1 (rowid, c, b, a) VALUES(3,  '0x03', 1, 1);
  INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2);
  INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3);
︙︙
201
202
203
204
205
206
207

















208
209
210


  do_execsql_test 4.$tn.6 {
    SELECT d1.rowid FROM d1 WHERE a = (
      SELECT y FROM d2 where d2.rowid=d1.rowid
    );
  } {2 4}
}


















finish_test









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




214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240


  do_execsql_test 4.$tn.6 {
    SELECT d1.rowid FROM d1 WHERE a = (
      SELECT y FROM d2 where d2.rowid=d1.rowid
    );
  } {2 4}
}

do_execsql_test 5.0 {
  CREATE TABLE e1(a TEXT, c NUMERIC);
  CREATE TABLE e2(x BLOB, y BLOB);

  INSERT INTO e1 VALUES(2, 2);

  INSERT INTO e2 VALUES ('2', 2);
  INSERT INTO e2 VALUES ('2', '2');
  INSERT INTO e2 VALUES ('2', '2.0');

  CREATE INDEX e1c ON e1(c);
}

do_execsql_test 5.1 {
  SELECT rowid FROM e1 WHERE (a, c) IN (SELECT x, y FROM e2);
} {1}

finish_test