/ Check-in [f5e49855]
Login

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

Overview
Comment:Consider the affinity of "b" when using an "a IN (SELECT b ...)" expression with an index on "a". Fix for [199df416].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1:f5e49855412e389a8a410db5d7ffb2e3634c5fa3
User & Date: dan 2016-09-03 15:31:20
Context
2016-09-03
16:24
Merge the fuzzershell enhancement from trunk. check-in: ed206048 user: drh tags: rowvalue
15:31
Consider the affinity of "b" when using an "a IN (SELECT b ...)" expression with an index on "a". Fix for [199df416]. check-in: f5e49855 user: dan tags: rowvalue
01:46
Performance optimizations. check-in: f1d06c49 user: drh tags: rowvalue
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   645    645           regBase = r1;
   646    646         }else{
   647    647           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
   648    648         }
   649    649       }
   650    650       testcase( pTerm->eOperator & WO_ISNULL );
   651    651       testcase( pTerm->eOperator & WO_IN );
   652         -    if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
   653         -      Expr *pRight = pTerm->pExpr->pRight;
   654         -      if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
   655         -        sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
   656         -        VdbeCoverage(v);
          652  +    if( (pTerm->eOperator & WO_ISNULL)==0 ){
          653  +      Expr *pRight = 0;
          654  +      if( pTerm->eOperator & WO_IN ){
          655  +        if( pTerm->pExpr->flags & EP_xIsSelect ){
          656  +          int iField = pTerm->iField ? pTerm->iField-1 : 0;
          657  +          pRight = pTerm->pExpr->x.pSelect->pEList->a[iField].pExpr;
          658  +        }
          659  +      }else{
          660  +        pRight = pTerm->pExpr->pRight;
          661  +        if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){
          662  +          sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
          663  +          VdbeCoverage(v);
          664  +        }
   657    665         }
   658         -      if( zAff ){
          666  +      if( pRight && zAff ){
   659    667           if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){
   660    668             zAff[j] = SQLITE_AFF_BLOB;
   661    669           }
   662    670           if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){
   663    671             zAff[j] = SQLITE_AFF_BLOB;
   664    672           }
   665    673         }

Added test/rowvalue9.test.

            1  +# 2016 September 3
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing SQL statements that use row value
           13  +# constructors.
           14  +#
           15  +
           16  +
           17  +set testdir [file dirname $argv0]
           18  +source $testdir/tester.tcl
           19  +set ::testprefix rowvalue9
           20  +
           21  +do_execsql_test 1.0.1 {
           22  +  CREATE TABLE a1(c, b INTEGER, a TEXT, PRIMARY KEY(a, b));
           23  + 
           24  +  INSERT INTO a1 (rowid, c, b, a) VALUES(3,  '0x03', 1, 1);
           25  +  INSERT INTO a1 (rowid, c, b, a) VALUES(14, '0x0E', 2, 2);
           26  +  INSERT INTO a1 (rowid, c, b, a) VALUES(15, '0x0F', 3, 3);
           27  +  INSERT INTO a1 (rowid, c, b, a) VALUES(92, '0x5C', 4, 4);
           28  +
           29  +  CREATE TABLE a2(x BLOB, y BLOB);
           30  +  INSERT INTO a2(x, y) VALUES(1, 1);
           31  +  INSERT INTO a2(x, y) VALUES(2, '2');
           32  +  INSERT INTO a2(x, y) VALUES('3', 3);
           33  +  INSERT INTO a2(x, y) VALUES('4', '4');
           34  +}
           35  +
           36  +do_execsql_test 1.0.2 { 
           37  +  SELECT x, typeof(x), y, typeof(y) FROM a2 ORDER BY rowid
           38  +} {
           39  +  1 integer 1 integer 
           40  +  2 integer 2 text 
           41  +  3 text    3 integer 
           42  +  4 text    4 text
           43  +}
           44  +
           45  +do_execsql_test 1.1.1 {
           46  +  SELECT (SELECT rowid FROM a1 WHERE a=x AND b=y) FROM a2
           47  +} {{} {} 15 92}
           48  +do_execsql_test 1.1.2 {
           49  +  SELECT (SELECT rowid FROM a1 WHERE (a, b) = (x, y)) FROM a2
           50  +} {{} {} 15 92}
           51  +
           52  +do_execsql_test 1.2.3 {
           53  +  SELECT a1.rowid FROM a1, a2 WHERE a=x AND b=y;
           54  +} {15 92}
           55  +do_execsql_test 1.2.4 {
           56  +  SELECT a1.rowid FROM a1, a2 WHERE (a, b) = (x, y)
           57  +} {15 92}
           58  +
           59  +
           60  +do_execsql_test 1.3.1 {
           61  +  SELECT a1.rowid FROM a1, a2 WHERE coalesce(NULL,x)=a AND coalesce(NULL,y)=b
           62  +} {3 14 15 92}
           63  +do_execsql_test 1.3.2 {
           64  +  SELECT a1.rowid FROM a1, a2 
           65  +  WHERE (coalesce(NULL,x), coalesce(NULL,y)) = (a, b)
           66  +} {3 14 15 92}
           67  +
           68  +do_execsql_test 1.4.1 {
           69  +  SELECT a1.rowid FROM a1, a2 WHERE +x=a AND +y=b
           70  +} {3 14 15 92}
           71  +do_execsql_test 1.4.2 {
           72  +  SELECT a1.rowid FROM a1, a2 WHERE (+x, +y) = (a, b)
           73  +} {3 14 15 92}
           74  +
           75  +do_execsql_test 1.5.1 {
           76  +  SELECT (SELECT rowid FROM a1 WHERE a=+x AND b=+y) FROM a2
           77  +} {3 14 15 92}
           78  +do_execsql_test 1.5.2 {
           79  +  SELECT (SELECT rowid FROM a1 WHERE (a, b) = (+x, +y)) FROM a2
           80  +} {3 14 15 92}
           81  +do_execsql_test 1.5.3 {
           82  +  SELECT (SELECT rowid FROM a1 WHERE (+x, +y) = (a, b)) FROM a2
           83  +} {3 14 15 92}
           84  +
           85  +do_execsql_test 1.6.1 {
           86  +  SELECT a1.rowid FROM a1 WHERE (a, b) IN (SELECT x, y FROM a2)
           87  +} {15 92}
           88  +
           89  +do_execsql_test 1.6.2 {
           90  +  SELECT a1.rowid FROM a1, a2 WHERE EXISTS (
           91  +    SELECT 1 FROM a1 WHERE a=x AND b=y
           92  +  )
           93  +} {3 14 15 92 3 14 15 92}
           94  +
           95  +do_execsql_test 2.1 {
           96  +  CREATE TABLE b1(a TEXT);
           97  +  CREATE TABLE b2(x BLOB);
           98  +
           99  +  INSERT INTO b1 VALUES(1);
          100  +  INSERT INTO b2 VALUES(1);
          101  +}
          102  +
          103  +do_execsql_test 2.2 { SELECT * FROM b1, b2 WHERE a=x; } {}
          104  +do_execsql_test 2.3 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}
          105  +
          106  +do_execsql_test 2.4 {
          107  +  CREATE UNIQUE INDEX b1a ON b1(a);
          108  +}
          109  +do_execsql_test 2.5 { SELECT * FROM b1 WHERE a IN (SELECT x FROM b2) } {}
          110  +
          111  +do_execsql_test 3.1 {
          112  +  CREATE TABLE c1(a INTEGER, b TEXT);
          113  +  INSERT INTO c1 VALUES(1, 1);
          114  +
          115  +  CREATE TABLE c2(x BLOB, y BLOB);
          116  +  INSERT INTO c2 VALUES(1, 1);
          117  +}
          118  +do_execsql_test 3.2 {
          119  +  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
          120  +} {}
          121  +do_execsql_test 3.3 {
          122  +  CREATE UNIQUE INDEX c1ab ON c1(a, b);
          123  +  SELECT * FROM c1 WHERE (a, b) IN (SELECT x, y FROM c2)
          124  +} {}
          125  +
          126  +do_execsql_test 4.0 {
          127  +  CREATE TABLE d1(a TEXT);
          128  +  CREATE TABLE d2(x BLOB);
          129  +  INSERT INTO d1 VALUES(1);
          130  +  INSERT INTO d2 VALUES(1);
          131  +}
          132  +do_execsql_test 4.1 { 
          133  +  SELECT * FROM d1 WHERE a IN (SELECT x FROM b2) 
          134  +} {}
          135  +do_execsql_test 4.2 { 
          136  +  CREATE UNIQUE INDEX d1a ON d1(a);
          137  +}
          138  +do_execsql_test 4.3 { 
          139  +  SELECT * FROM d1 WHERE a IN (SELECT x FROM d2) 
          140  +} {}
          141  +
          142  +
          143  +finish_test
          144  +