/ Check-in [719a3b20]
Login

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

Overview
Comment:Fix some problems with multi-column IN(SELECT...) processing.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rowvalue
Files: files | file ages | folders
SHA1: 719a3b2035a335ca8b9704646b1d641011e3ea0e
User & Date: dan 2016-07-27 19:33:04
Context
2016-07-28
13:59
Merge latest trunk changes into this branch. check-in: 9685880f user: dan tags: rowvalue
2016-07-27
19:33
Fix some problems with multi-column IN(SELECT...) processing. check-in: 719a3b20 user: dan tags: rowvalue
16:03
Initialize a variable in where.c to avoid a valgrind warning. check-in: 4d59df02 user: dan tags: trunk
2016-07-26
18:15
Merge latest trunk changes into this branch. check-in: d4f3d52c user: dan tags: rowvalue
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/expr.c.

2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486


2487
2488
2489
2490



2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
....
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
      sqlite3VdbeGoto(v, destIfFalse);
    }
    sqlite3VdbeResolveLabel(v, labelOk);
    sqlite3ReleaseTempReg(pParse, regCkNull);
  }else{
  
    /* If the LHS is NULL, then the result is either false or NULL depending
    ** on whether the RHS is empty or not, respectively.
    */
    if( nVector==1 && sqlite3ExprCanBeNull(pExpr->pLeft) ){
      if( destIfNull==destIfFalse ){


        /* Shortcut for the common case where the false and NULL outcomes are
        ** the same. */
        sqlite3VdbeAddOp2(v, OP_IsNull, r1, destIfNull); VdbeCoverage(v);
      }else{



        int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v);
        sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
        VdbeCoverage(v);
        sqlite3VdbeGoto(v, destIfNull);
        sqlite3VdbeJumpHere(v, addr1);
      }
    }
  
    if( eType==IN_INDEX_ROWID ){
      /* In this case, the RHS is the ROWID of table b-tree
      */
      sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1);
      VdbeCoverage(v);
    }else if( nVector>1 && eType==IN_INDEX_EPH ){
      int regNull = sqlite3GetTempReg(pParse);
      int r2 = sqlite3GetTempReg(pParse);
      int r3 = sqlite3GetTempReg(pParse);
      int r4 = sqlite3GetTempReg(pParse);
      int addrNext;
      int addrIf;

................................................................................
        sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
      }
#endif
      break;
    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "invalid use of row value (1)");
      break;
    }

    case TK_SELECT_COLUMN: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );
      assert( pLeft->op==TK_SELECT || pLeft->op==TK_REGISTER );







|
<
<
|
>
>
|
<
|
<
>
>
>
|
|
|
|
|
<







|







 







|







2476
2477
2478
2479
2480
2481
2482
2483


2484
2485
2486
2487

2488

2489
2490
2491
2492
2493
2494
2495
2496

2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
....
3490
3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
      sqlite3VdbeGoto(v, destIfFalse);
    }
    sqlite3VdbeResolveLabel(v, labelOk);
    sqlite3ReleaseTempReg(pParse, regCkNull);
  }else{
  
    /* If the LHS is NULL, then the result is either false or NULL depending
    ** on whether the RHS is empty or not, respectively.  */


    if( destIfNull==destIfFalse ){
      for(i=0; i<nVector; i++){
        Expr *p = exprVectorField(pExpr->pLeft, i);
        if( sqlite3ExprCanBeNull(p) ){

          sqlite3VdbeAddOp2(v, OP_IsNull, r1+aiMap[i], destIfNull);

        }
      }
    }else if( nVector==1 && sqlite3ExprCanBeNull(pExpr->pLeft) ){
      int addr1 = sqlite3VdbeAddOp1(v, OP_NotNull, r1); VdbeCoverage(v);
      sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
      VdbeCoverage(v);
      sqlite3VdbeGoto(v, destIfNull);
      sqlite3VdbeJumpHere(v, addr1);

    }
  
    if( eType==IN_INDEX_ROWID ){
      /* In this case, the RHS is the ROWID of table b-tree
      */
      sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, r1);
      VdbeCoverage(v);
    }else if( nVector>1 && eType==IN_INDEX_EPH && destIfNull!=destIfFalse ){
      int regNull = sqlite3GetTempReg(pParse);
      int r2 = sqlite3GetTempReg(pParse);
      int r3 = sqlite3GetTempReg(pParse);
      int r4 = sqlite3GetTempReg(pParse);
      int addrNext;
      int addrIf;

................................................................................
        sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
      }
#endif
      break;
    }

    case TK_VECTOR: {
      sqlite3ErrorMsg(pParse, "invalid use of row value");
      break;
    }

    case TK_SELECT_COLUMN: {
      Expr *pLeft = pExpr->pLeft;
      assert( pLeft );
      assert( pLeft->op==TK_SELECT || pLeft->op==TK_REGISTER );

Changes to src/resolve.c.

763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
          pNC->ncFlags |= NC_VarSelect;
        }

        if( pExpr->op==TK_SELECT && pExpr->x.pSelect->pEList->nExpr>1 ){
          if( !ExprHasProperty(pExpr, EP_VectorOk) && 0 ){
            sqlite3ErrorMsg(pParse, "invalid use of row value");
          }else{
            ExprSetProperty(pExpr, EP_Vector);
          }
        }
        if( pExpr->op==TK_IN ){
          ExprSetProperty(pExpr->pLeft, EP_VectorOk);
        }
      }
      break;
    }
    case TK_VARIABLE: {
      notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr);
      break;
    }

    case TK_BETWEEN: {
      ExprSetProperty(pExpr->pLeft, EP_VectorOk);
      ExprSetProperty(pExpr->x.pList->a[0].pExpr, EP_VectorOk);
      ExprSetProperty(pExpr->x.pList->a[1].pExpr, EP_VectorOk);
      break;
    }

    case TK_EQ: case TK_NE: case TK_IS: case TK_ISNOT: 
    case TK_LE: case TK_LT: case TK_GE: case TK_GT: 
    {
      ExprSetProperty(pExpr->pLeft, EP_VectorOk);
      ExprSetProperty(pExpr->pRight, EP_VectorOk);
      break;
    };

    case TK_VECTOR: {
      if( !ExprHasProperty(pExpr, EP_VectorOk) ){
        sqlite3ErrorMsg(pParse, "invalid use of row value");
      }else{
        ExprSetProperty(pExpr, EP_Vector);
      }
      break;
    }
  }
  return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}

/*







<
<
<
|
<
<
<
<









<
<
<
<
<
<
<
<
<
<
<
<
<
<
<

<
<
<
|
<







763
764
765
766
767
768
769



770




771
772
773
774
775
776
777
778
779















780



781

782
783
784
785
786
787
788
        assert( pNC->nRef>=nRef );
        if( nRef!=pNC->nRef ){
          ExprSetProperty(pExpr, EP_VarSelect);
          pNC->ncFlags |= NC_VarSelect;
        }

        if( pExpr->op==TK_SELECT && pExpr->x.pSelect->pEList->nExpr>1 ){



          ExprSetProperty(pExpr, EP_Vector);




        }
      }
      break;
    }
    case TK_VARIABLE: {
      notValid(pParse, pNC, "parameters", NC_IsCheck|NC_PartIdx|NC_IdxExpr);
      break;
    }
















    case TK_VECTOR: {



      ExprSetProperty(pExpr, EP_Vector);

      break;
    }
  }
  return (pParse->nErr || pParse->db->mallocFailed) ? WRC_Abort : WRC_Continue;
}

/*

Changes to src/sqliteInt.h.

2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
#define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
#define EP_NoReduce  0x020000 /* Cannot EXPRDUP_REDUCE this Expr */
#define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias     0x400000 /* Is an alias for a result set column */
#define EP_VectorOk  0x800000 /* This expression may be a row value */
#define EP_Vector    0x1000000/* This expression is a row value */

/*
** Combinations of two or more EP_* flags
*/
#define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */

/*







<
|







2323
2324
2325
2326
2327
2328
2329

2330
2331
2332
2333
2334
2335
2336
2337
#define EP_MemToken  0x010000 /* Need to sqlite3DbFree() Expr.zToken */
#define EP_NoReduce  0x020000 /* Cannot EXPRDUP_REDUCE this Expr */
#define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
#define EP_ConstFunc 0x080000 /* A SQLITE_FUNC_CONSTANT or _SLOCHNG function */
#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias     0x400000 /* Is an alias for a result set column */

#define EP_Vector    0x800000 /* This expression is a row value */

/*
** Combinations of two or more EP_* flags
*/
#define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */

/*

Changes to src/where.c.

3473
3474
3475
3476
3477
3478
3479
3480
3481






3482
3483
3484
3485
3486
3487
3488






3489
3490
3491
3492
3493










3494
3495
3496
3497
3498
3499
3500
....
3515
3516
3517
3518
3519
3520
3521
3522
3523
3524
3525
3526
3527
3528
3529
....
4008
4009
4010
4011
4012
4013
4014
4015
4016
4017
4018
4019
4020
4021
4022

      /* Loop through all columns of the index and deal with the ones
      ** that are not constrained by == or IN.
      */
      rev = revSet = 0;
      distinctColumns = 0;
      for(j=0; j<nColumn; j++){
        u8 bOnce;   /* True to run the ORDER BY search loop */







        /* Skip over == and IS and ISNULL terms.
        ** (Also skip IN terms when doing WHERE_ORDERBY_LIMIT processing)
        */
        if( j<pLoop->u.btree.nEq
         && pLoop->nSkip==0
         && ((i = pLoop->aLTerm[j]->eOperator) & eqOpMask)!=0
        ){






          if( i & WO_ISNULL ){
            testcase( isOrderDistinct );
            isOrderDistinct = 0;
          }
          continue;  










        }

        /* Get the column number in the table (iColumn) and sort order
        ** (revIdx) for the j-th column of the index.
        */
        if( pIndex ){
          iColumn = pIndex->aiColumn[j];
................................................................................
        ){
          isOrderDistinct = 0;
        }

        /* Find the ORDER BY term that corresponds to the j-th column
        ** of the index and mark that ORDER BY term off 
        */
        bOnce = 1;
        isMatch = 0;
        for(i=0; bOnce && i<nOrderBy; i++){
          if( MASKBIT(i) & obSat ) continue;
          pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
          testcase( wctrlFlags & WHERE_GROUPBY );
          testcase( wctrlFlags & WHERE_DISTINCTBY );
          if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
................................................................................
      }
    }else{
      pWInfo->nOBSat = pFrom->isOrdered;
      pWInfo->revMask = pFrom->revLoop;
      if( pWInfo->nOBSat<=0 ){
        pWInfo->nOBSat = 0;
        if( nLoop>0 ){
          Bitmask m;
          int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
                      WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
          if( rc==pWInfo->pOrderBy->nExpr ){
            pWInfo->bOrderedInnerLoop = 1;
            pWInfo->revMask = m;
          }
        }







|

>
>
>
>
>
>
|
|
|
<
<
<
<
>
>
>
>
>
>
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>







 







<







 







|







3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
3486
3487
3488
3489
3490




3491
3492
3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517
3518
....
3533
3534
3535
3536
3537
3538
3539

3540
3541
3542
3543
3544
3545
3546
....
4025
4026
4027
4028
4029
4030
4031
4032
4033
4034
4035
4036
4037
4038
4039

      /* Loop through all columns of the index and deal with the ones
      ** that are not constrained by == or IN.
      */
      rev = revSet = 0;
      distinctColumns = 0;
      for(j=0; j<nColumn; j++){
        u8 bOnce = 1; /* True to run the ORDER BY search loop */

        assert( j>=pLoop->u.btree.nEq 
            || (pLoop->aLTerm[j]==0)==(j<pLoop->nSkip)
        );
        if( j<pLoop->u.btree.nEq && j>=pLoop->nSkip ){
          u16 eOp = pLoop->aLTerm[j]->eOperator;

          /* Skip over == and IS and ISNULL terms.  (Also skip IN terms when
          ** doing WHERE_ORDERBY_LIMIT processing). 
          **




          ** If the current term is a column of an ((?,?) IN (SELECT...)) 
          ** expression for which the SELECT returns more than one column,
          ** check that it is the only column used by this loop. Otherwise,
          ** if it is one of two or more, none of the columns can be
          ** considered to match an ORDER BY term.  */
          if( (eOp & eqOpMask)!=0 ){
            if( eOp & WO_ISNULL ){
              testcase( isOrderDistinct );
              isOrderDistinct = 0;
            }
            continue;  
          }else if( eOp & WO_IN ){
            Expr *pX = pLoop->aLTerm[j]->pExpr;
            for(i=j+1; i<pLoop->u.btree.nEq; i++){
              if( pLoop->aLTerm[i]->pExpr==pX ){
                assert( (pLoop->aLTerm[i]->eOperator & WO_IN) );
                bOnce = 0;
                break;
              }
            }
          }
        }

        /* Get the column number in the table (iColumn) and sort order
        ** (revIdx) for the j-th column of the index.
        */
        if( pIndex ){
          iColumn = pIndex->aiColumn[j];
................................................................................
        ){
          isOrderDistinct = 0;
        }

        /* Find the ORDER BY term that corresponds to the j-th column
        ** of the index and mark that ORDER BY term off 
        */

        isMatch = 0;
        for(i=0; bOnce && i<nOrderBy; i++){
          if( MASKBIT(i) & obSat ) continue;
          pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
          testcase( wctrlFlags & WHERE_GROUPBY );
          testcase( wctrlFlags & WHERE_DISTINCTBY );
          if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0;
................................................................................
      }
    }else{
      pWInfo->nOBSat = pFrom->isOrdered;
      pWInfo->revMask = pFrom->revLoop;
      if( pWInfo->nOBSat<=0 ){
        pWInfo->nOBSat = 0;
        if( nLoop>0 ){
          Bitmask m = 0;
          int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
                      WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
          if( rc==pWInfo->pOrderBy->nExpr ){
            pWInfo->bOrderedInnerLoop = 1;
            pWInfo->revMask = m;
          }
        }

Changes to test/in.test.

636
637
638
639
640
641
642
643











644
  }
} {1 {sub-select returns 2 columns - expected 1}}


do_test in-13.X {
  db nullvalue ""
} {}












finish_test








>
>
>
>
>
>
>
>
>
>
>

636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
  }
} {1 {sub-select returns 2 columns - expected 1}}


do_test in-13.X {
  db nullvalue ""
} {}

# At one point the following was causing valgrind to report a "jump
# depends on unitialized location" problem.
#
do_execsql_test in-14.0 {
  CREATE TABLE c1(a);
  INSERT INTO c1 VALUES(1), (2), (4), (3);
}
do_execsql_test in-14.1 {
  SELECT * FROM c1 WHERE a IN (SELECT a FROM c1) ORDER BY 1
} {1 2 3 4}

finish_test

Changes to test/rowvalue3.test.

13
14
15
16
17
18
19








20
21
22
23
24
25
26
..
56
57
58
59
60
61
62
63
64

65
66
67
68
69
70
71
..
83
84
85
86
87
88
89
90

































91





92


93
94




95




96








97






















































# where the SELECT statement returns more than one column.
#

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










do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
................................................................................
  INSERT INTO kk VALUES('d', 'e');
  -- INSERT INTO kk VALUES('x', 'x');

}

foreach {tn idx} {
  1 { }
  2 {  CREATE INDEX z1idx ON z1(x, y) }
  3 {  CREATE UNIQUE INDEX z1idx ON z1(x, y) }

} {
  execsql "DROP INDEX IF EXISTS z1idx"
  execsql $idx

  do_execsql_test 2.$tn.1 {
    SELECT * FROM z1 WHERE x IN (SELECT a FROM kk)
  } {d e f}
................................................................................
  } {}

  do_execsql_test 2.$tn.5 {
    SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk)
  } {d e f}
}

explain_i {

































  SELECT * FROM z1 WHERE (x, y) IN (SELECT a, b FROM kk)





} 



finish_test
















































































>
>
>
>
>
>
>
>







 







|
|
>







 







<
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
|
>
>

<
>
>
>
>

>
>
>
>

>
>
>
>
>
>
>
>

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
..
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
..
92
93
94
95
96
97
98

99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141

142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
# where the SELECT statement returns more than one column.
#

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

# Drop all auxiliary indexes from the main database opened by handle [db].
#
proc drop_all_indexes {} {
  set L [db eval {
    SELECT name FROM sqlite_master WHERE type='index' AND sql LIKE 'create%'
  }]
  foreach idx $L { db eval "DROP INDEX $idx" }
}

do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
................................................................................
  INSERT INTO kk VALUES('d', 'e');
  -- INSERT INTO kk VALUES('x', 'x');

}

foreach {tn idx} {
  1 { }
  2 { CREATE INDEX z1idx ON z1(x, y) }
  3 { CREATE UNIQUE INDEX z1idx ON z1(x, y) }
  4 { CREATE INDEX z1idx ON kk(a, b) }
} {
  execsql "DROP INDEX IF EXISTS z1idx"
  execsql $idx

  do_execsql_test 2.$tn.1 {
    SELECT * FROM z1 WHERE x IN (SELECT a FROM kk)
  } {d e f}
................................................................................
  } {}

  do_execsql_test 2.$tn.5 {
    SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk)
  } {d e f}
}


#-------------------------------------------------------------------------
#

do_execsql_test 3.0 {
  CREATE TABLE c1(a, b, c, d);
  INSERT INTO c1(rowid, a, b) VALUES(1,   NULL, 1);
  INSERT INTO c1(rowid, a, b) VALUES(2,   2, NULL);
  INSERT INTO c1(rowid, a, b) VALUES(3,   2, 2);
  INSERT INTO c1(rowid, a, b) VALUES(4,   3, 3);

  INSERT INTO c1(rowid, a, b, c, d) VALUES(101, 'a', 'b', 1, 1);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(102, 'a', 'b', 1, 2);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(103, 'a', 'b', 1, 3);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(104, 'a', 'b', 2, 1);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(105, 'a', 'b', 2, 2);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(106, 'a', 'b', 2, 3);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(107, 'a', 'b', 3, 1);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(108, 'a', 'b', 3, 2);
  INSERT INTO c1(rowid, a, b, c, d) VALUES(109, 'a', 'b', 3, 3);
}


foreach {tn idx} {
  1 { }
  2 { CREATE INDEX c1ab ON c1(a, b); }
  3 { CREATE INDEX c1ba ON c1(b, a); }

  4 { CREATE INDEX c1cd ON c1(c, d); }
  5 { CREATE INDEX c1dc ON c1(d, c); }
} {
  drop_all_indexes

  foreach {tn2 sql res} {
    1 "SELECT (1, 2) IN (SELECT a, b FROM c1)" {0}
    2 "SELECT (1, 1) IN (SELECT a, b FROM c1)" {{}}
    3 "SELECT (2, 1) IN (SELECT a, b FROM c1)" {{}}
    4 "SELECT (2, 2) IN (SELECT a, b FROM c1)" {1}
    5 "SELECT c, d FROM c1 WHERE (c, d) IN (SELECT d, c FROM c1)"
      { 1 1 1 2 1 3   2 1 2 2 2 3   3 1 3 2 3 3 }

    6 "SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) ORDER BY c DESC"
      { 3 1 3 2 3 3   2 1 2 2 2 3   1 1 1 2 1 3 }


    7 {
        SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 
        ORDER BY c DESC, d ASC
      } { 3 1 3 2 3 3   2 1 2 2 2 3   1 1 1 2 1 3 }

    8 {
        SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 
        ORDER BY c ASC, d DESC
      } { 1 3 1 2 1 1   2 3 2 2 2 1   3 3 3 2 3 1 }

    9 {
        SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 
        ORDER BY c ASC, d ASC
      } { 1 1 1 2 1 3   2 1 2 2 2 3   3 1 3 2 3 3 }
    10 {
        SELECT c, d FROM c1 WHERE (c,d) IN (SELECT d, c FROM c1) 
        ORDER BY c DESC, d DESC
      } { 3 3 3 2 3 1   2 3 2 2 2 1   1 3 1 2 1 1 }

  } {
    do_execsql_test 3.$tn.$tn2 $sql $res
  }
}

#-------------------------------------------------------------------------

do_execsql_test 4.0 {
  CREATE TABLE hh(a, b, c);

  INSERT INTO hh VALUES('a', 'a', 1);
  INSERT INTO hh VALUES('a', 'b', 2);
  INSERT INTO hh VALUES('b', 'a', 3);
  INSERT INTO hh VALUES('b', 'b', 4);

  CREATE TABLE k1(x, y);
  INSERT INTO k1 VALUES('a', 'a');
  INSERT INTO k1 VALUES('b', 'b');
  INSERT INTO k1 VALUES('a', 'b');
  INSERT INTO k1 VALUES('b', 'a');
}

foreach {tn idx} {
  1 { }
  2 { CREATE INDEX h1 ON hh(a, b); }
  3 { CREATE UNIQUE INDEX k1idx ON k1(x, y) }
  4 { CREATE UNIQUE INDEX k1idx ON k1(x, y DESC) }
  5 { 
    CREATE INDEX h1 ON hh(a, b);
    CREATE UNIQUE INDEX k1idx ON k1(x, y); 
  }
  6 { 
    CREATE INDEX h1 ON hh(a, b);
    CREATE UNIQUE INDEX k1idx ON k1(x, y DESC); 
  }
} {
  drop_all_indexes
  execsql $idx
  foreach {tn2 orderby res} {
    1 "a ASC, b ASC"  {1 2 3 4}
    2 "a ASC, b DESC" {2 1 4 3}
    3 "a DESC, b ASC" {3 4 1 2}
    4 "a DESC, b DESC" {4 3 2 1}
  } {
    do_execsql_test 4.$tn.$tn2 "
      SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby
    " $res
  }
}


finish_test