SQLite

Check-in [73c93f5a2a]
Login

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

Overview
Comment:Further enhancements and fixes for explain query plan.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 73c93f5a2a32ee8c5d07c9ba33b2641e72626627
User & Date: dan 2010-11-09 14:49:00.000
Context
2010-11-09
17:35
Add missing comments and fix other issues with routines used by new EQP features. (check-in: 925f35c535 user: dan tags: experimental)
14:49
Further enhancements and fixes for explain query plan. (check-in: 73c93f5a2a user: dan tags: experimental)
2010-11-08
19:01
Experimental changes to EXPLAIN QUERY PLAN. (check-in: f4747eb83d user: dan tags: experimental)
Changes
Side-by-Side Diff Ignore Whitespace Patch
Changes to src/expr.c.
1595
1596
1597
1598
1599
1600
1601










1602
1603
1604
1605
1606
1607
1608
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618







+
+
+
+
+
+
+
+
+
+







  */
  if( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->pTriggerTab ){
    int mem = ++pParse->nMem;
    sqlite3VdbeAddOp1(v, OP_If, mem);
    testAddr = sqlite3VdbeAddOp2(v, OP_Integer, 1, mem);
    assert( testAddr>0 || pParse->db->mallocFailed );
  }

#ifndef SQLITE_OMIT_EXPLAIN
  if( pParse->explain==2 ){
    char *zMsg = sqlite3MPrintf(
        pParse->db, "EXECUTE %s%s SUBQUERY %d", testAddr?"":"CORRELATED ",
        pExpr->op==TK_IN?"LIST":"SCALAR", pParse->iNextSelectId
    );
    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  }
#endif

  switch( pExpr->op ){
    case TK_IN: {
      char affinity;              /* Affinity of the LHS of the IN */
      KeyInfo keyInfo;            /* Keyinfo for the generated table */
      int addr;                   /* Address of OP_OpenEphemeral instruction */
      Expr *pLeft = pExpr->pLeft; /* the LHS of the IN operator */
Changes to src/select.c.
776
777
778
779
780
781
782

783
784
785

786
787
788
789
790
791
792
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794







+



+







  if( pParse->explain==2 ){
    Vdbe *v = pParse->pVdbe;
    char *zMsg = sqlite3MPrintf(pParse->db, "USE TEMP B-TREE FOR %s", zUsage);
    sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
  }
}
# define explainRestoreSelectId() pParse->iSelectId = iRestoreSelectId
# define explainAssignSelectId(pItem, id) pItem->iSelectId = id
#else
# define explainRestoreSelectId()
# define explainTempTable(y,z)
# define explainAssignSelectId(y,z)
#endif

/*
** If the inner loop was generated using a non-null pOrderBy argument,
** then the results were placed in a sorter.  After the loop is terminated
** we need to run the sorter and output the results.  The following
** routine generates the code needed to do that.
3675
3676
3677
3678
3679
3680
3681

3682
3683
3684
3685
3686
3687
3688
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
3690
3691







+







        isAgg = 1;
        p->selFlags |= SF_Aggregate;
      }
      i = -1;
    }else{
      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      assert( pItem->isPopulated==0 );
      explainAssignSelectId(pItem, pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);
      pItem->isPopulated = 1;
    }
    if( /*pParse->nErr ||*/ db->mallocFailed ){
      goto select_end;
    }
    pParse->nHeight -= sqlite3SelectExprHeight(p);
Changes to src/sqliteInt.h.
1823
1824
1825
1826
1827
1828
1829



1830
1831
1832
1833
1834
1835
1836
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839







+
+
+







    u8 notIndexed;    /* True if there is a NOT INDEXED clause */
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
    char *zIndex;     /* Identifier from "INDEXED BY <zIndex>" clause */
    Index *pIndex;    /* Index structure corresponding to zIndex, if any */
#ifndef SQLITE_OMIT_EXPLAIN
    int iSelectId;    /* If pSelect!=0, the id of the sub-select in EQP */
#endif
  } a[1];             /* One entry for each identifier on the list */
};

/*
** Permitted values of the SrcList.a.jointype field
*/
#define JT_INNER     0x0001    /* Any kind of inner or cross join */
Changes to src/where.c.
3161
3162
3163
3164
3165
3166
3167
3168


3169
3170
3171
3172
3173
3174
3175


3176
3177

3178



3179



3180
3181
3182
3183
3184
3185

3186
3187
3188
3189
3190
3191
3192
3193
3194

3195
3196
3197
3198
3199
3200
3201
3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212





3213

3214
3215
3216

3217
3218
3219
3220
3221

3222
3223
3224
3225
3226
3227
3228
3161
3162
3163
3164
3165
3166
3167

3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179

3180
3181
3182
3183
3184

3185
3186
3187
3188
3189
3190
3191
3192

3193
3194
3195
3196
3197
3198
3199
3200
3201

3202
3203
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225

3226



3227

3228
3229
3230

3231
3232
3233
3234
3235
3236
3237
3238







-
+
+







+
+

-
+

+
+
+
-
+
+
+





-
+








-
+


















+
+
+
+
+
-
+
-
-
-
+
-



-
+







}

static void codeOneLoopExplain(
  Parse *pParse,                  /* Parse context */
  SrcList *pTabList,              /* Table list this loop refers to */
  WhereLevel *pLevel,             /* Scan to write OP_Explain opcode for */
  int iLevel,                     /* Value for "level" column of output */
  int iFrom                       /* Value for "from" column of output */
  int iFrom,                      /* Value for "from" column of output */
  u16 wctrlFlags                  /* Flags passed to sqlite3WhereBegin() */
){
  if( pParse->explain==2 ){
    u32 flags = pLevel->plan.wsFlags;
    struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom];
    Vdbe *v = pParse->pVdbe;
    sqlite3 *db = pParse->db;
    char *zMsg;
    sqlite3_int64 nRow;           /* Expected number of rows visited by scan */
    int iId = pParse->iSelectId;  /* Select id (left-most output column) */

    if( flags & WHERE_MULTI_OR ) return;
    if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return;

    if( pItem->pSelect ){
      zMsg = sqlite3MPrintf(db, "SCAN SUBQUERY %d", pItem->iSelectId);
    }else{
    zMsg = sqlite3MPrintf(db, "TABLE %s", pItem->zName);
      zMsg = sqlite3MPrintf(db, "SCAN TABLE %s", pItem->zName);
    }

    if( pItem->zAlias ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
    }
    if( (flags & WHERE_INDEXED)!=0 ){
      char *zWhere = indexRangeText(db, pLevel, pItem->pTab);
      zMsg = sqlite3MAppendf(db, zMsg, "%s WITH %s%sINDEX%s%s%s", zMsg, 
      zMsg = sqlite3MAppendf(db, zMsg, "%s BY %s%sINDEX%s%s%s", zMsg, 
          ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""),
          ((flags & WHERE_IDX_ONLY)?"COVERING ":""),
          ((flags & WHERE_TEMP_INDEX)?"":" "),
          ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName),
          zWhere
      );
      sqlite3DbFree(db, zWhere);
    }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){
      zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
      zMsg = sqlite3MAppendf(db, zMsg, "%s BY INTEGER PRIMARY KEY", zMsg);

      if( flags&WHERE_ROWID_EQ ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
      }else if( flags&WHERE_TOP_LIMIT ){
        zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
      }
    }
#ifndef SQLITE_OMIT_VIRTUALTABLE
    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
      sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx;
      zMsg = sqlite3MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
                  pVtabIdx->idxNum, pVtabIdx->idxStr);
    }
#endif
    if( wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX) ){
      nRow = 1;
    }else{
      nRow = (sqlite3_int64)pLevel->plan.nRow;
    }
    zMsg = sqlite3MAppendf(db, zMsg, 
    zMsg = sqlite3MAppendf(db, zMsg, "%s (~%lld rows)", zMsg, nRow);
        "%s (~%lld rows)", zMsg, (sqlite3_int64)(pLevel->plan.nRow)
    );
    sqlite3VdbeAddOp4(
    sqlite3VdbeAddOp4(v, OP_Explain, iId, iLevel, iFrom, zMsg, P4_DYNAMIC);
        v, OP_Explain, pParse->iSelectId, iLevel, iFrom, zMsg, P4_DYNAMIC);
  }
}
#else
# define codeOneLoopExplain(w,x,y.z)
# define codeOneLoopExplain(u,v,w,x,y,z)
#endif /* SQLITE_OMIT_EXPLAIN */


/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
3760
3761
3762
3763
3764
3765
3766
3767

3768
3769
3770
3771
3772
3773
3774
3770
3771
3772
3773
3774
3775
3776

3777
3778
3779
3780
3781
3782
3783
3784







-
+







        WhereInfo *pSubWInfo;          /* Info for single OR-term scan */
        /* Loop through table entries that match term pOrTerm. */
        pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrTerm->pExpr, 0,
                        WHERE_OMIT_OPEN | WHERE_OMIT_CLOSE |
                        WHERE_FORCE_TABLE | WHERE_ONETABLE_ONLY);
        if( pSubWInfo ){
          codeOneLoopExplain(
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom
              pParse, pOrTab, &pSubWInfo->a[0], iLevel, pLevel->iFrom, 0
          );
          if( (wctrlFlags & WHERE_DUPLICATES_OK)==0 ){
            int iSet = ((ii==pOrWc->nTerm-1)?-1:ii);
            int r;
            r = sqlite3ExprCodeGetColumn(pParse, pTabItem->pTab, -1, iCur, 
                                         regRowid);
            sqlite3VdbeAddOp4Int(v, OP_RowSetTest, regRowset,
4417
4418
4419
4420
4421
4422
4423
4424
4425


4426
4427
4428

4429
4430
4431
4432
4433
4434
4435
4427
4428
4429
4430
4431
4432
4433


4434
4435

4436

4437
4438
4439
4440
4441
4442
4443
4444







-
-
+
+
-

-
+








  /* Generate the code to do the search.  Each iteration of the for
  ** loop below generates code for a single nested loop of the VM
  ** program.
  */
  notReady = ~(Bitmask)0;
  for(i=0; i<nTabList; i++){
    if( (wctrlFlags&WHERE_ONETABLE_ONLY)==0 ){
      codeOneLoopExplain(pParse, pTabList, &pWInfo->a[i],i,pWInfo->a[i].iFrom);
    WhereLevel *pLevel = &pWInfo->a[i];
    codeOneLoopExplain(pParse, pTabList, pLevel, i, pLevel->iFrom, wctrlFlags);
    }
    notReady = codeOneLoopStart(pWInfo, i, wctrlFlags, notReady);
    pWInfo->iContinue = pWInfo->a[i].addrCont;
    pWInfo->iContinue = pLevel->addrCont;
  }

#ifdef SQLITE_TEST  /* For testing and debugging use only */
  /* Record in the query plan information about the current table
  ** and the index used to access it (if any).  If the table itself
  ** is not used, its name is just '{}'.  If no index is used
  ** the index is listed as "{}".  If the primary key is used the
Changes to test/eqp.test.
23
24
25
26
27
28
29

30
31
32
33
34
35
36
37
38
39
40
41
42
43
44



45
46
47
48
49
50
51



52
53
54
55
56

57
58
59
60
61

62
63
64
65
66
67

68
69
70
71
72

73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

90
91

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42



43
44
45
46
47
48
49



50
51
52
53
54
55
56

57
58
59
60
61

62
63
64
65
66
67

68
69
70
71
72

73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88


89


90
91
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
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
241
242

243
244

245
246
247
248
249
250

251
252

253
254
255
256
257
258

259
260

261
262
263
264
265
266
267
268
269
270
271
272
273







+












-
-
-
+
+
+




-
-
-
+
+
+




-
+




-
+





-
+




-
+















-
-
+
-
-
+




+
+
+
+
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+



+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







-
-
+
+
+

+
+
+
+
+
+
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

-
+




-
-
+
+




-
+

-
+





-
+

-
+





-
+

-
+





-
+

-
+






-
+

-
+

-
-



-
+

-
+





-
+

-
+





-
+

-
+



+
+
+
+
+
+



# eqp-4.*:        Compound select statements.
#

proc do_eqp_test {name sql res} {
  set res [list {*}$res]
  uplevel do_execsql_test $name [list "EXPLAIN QUERY PLAN $sql"] [list $res]
}
proc det {args} { uplevel do_eqp_test $args }

do_execsql_test 1.1 {
  CREATE TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a);
  CREATE INDEX i2 ON t1(b);
  CREATE TABLE t2(a, b);
  CREATE TABLE t3(a, b);
}

do_eqp_test 1.2 {
  SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
  0 0 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
  0 1 0 {TABLE t2 (~1000000 rows)}
  0 0 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
  0 0 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 
  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
}
do_eqp_test 1.3 {
  SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
} {
  0 0 0 {TABLE t2 (~1000000 rows)}
  0 1 1 {TABLE t1 WITH INDEX i1 (a=?) (~10 rows)} 
  0 1 1 {TABLE t1 WITH INDEX i2 (b=?) (~10 rows)} 
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
  0 1 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 
  0 1 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 
}
do_eqp_test 1.3 {
  SELECT a FROM t1 ORDER BY a
} {
  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (~1000000 rows)}
  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (~1000000 rows)}
}
do_eqp_test 1.4 {
  SELECT a FROM t1 ORDER BY +a
} {
  0 0 0 {TABLE t1 (~1000000 rows)}
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 1.5 {
  SELECT a FROM t1 WHERE a=4
} {
  0 0 0 {TABLE t1 WITH COVERING INDEX i1 (a=?) (~10 rows)}
  0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}
}
do_eqp_test 1.6 {
  SELECT DISTINCT count(*) FROM t3 GROUP BY a;
} {
  0 0 0 {TABLE t3 (~1000000 rows)}
  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}

#-------------------------------------------------------------------------
# Test cases eqp-2.* - tests for single select statements.
#
drop_all_tables
do_execsql_test 2.1 {
  CREATE TABLE t1(x, y);

  CREATE TABLE t2(x, y);
  CREATE INDEX t2i1 ON t2(x);
}

do_eqp_test 2.2.1 {
  SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1
det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
} {
  0 0 0 {TABLE t1 (~1000000 rows)}
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}

do_eqp_test 2.2.2 {
  SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1
} {
  0 0 0 {TABLE t2 WITH COVERING INDEX t2i1 (~1000000 rows)}
}
det 2.2.3 "SELECT DISTINCT * FROM t1" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
}
det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
  0 0 1 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
}

det 2.3.1 "SELECT max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.2 "SELECT min(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)}
}
det 2.3.3 "SELECT min(x), max(x) FROM t2" {
  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
}

det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
  0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
}



#-------------------------------------------------------------------------
# Test cases eqp-3.* - tests for select statements that use sub-selects.
#
do_eqp_test 3.1.1 {
  SELECT (SELECT x FROM t1 AS sub) FROM t1;
} {
  0 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {TABLE t1 AS sub (~1000000 rows)}
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
}
do_eqp_test 3.1.2 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}

}
do_eqp_test 3.1.3 {
  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 3.1.4 {
  SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
} {
  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
  1 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
}

det 3.2.1 {
  SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
det 3.2.2 {
  SELECT * FROM 
    (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
    (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
  ORDER BY x2.y LIMIT 5
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
  0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 
  0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

#-------------------------------------------------------------------------
# Test cases eqp-4.* - tests for select statements that use sub-selects.
# Test cases eqp-4.* - tests for composite select statements.
#
do_eqp_test 4.1.1 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  2 0 0 {TABLE t2 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
}
do_eqp_test 4.1.2 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 4.1.3 {
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 4.1.4 {
  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 4.1.5 {
  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

do_eqp_test 4.2.2 {
  SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 WITH INDEX t2i1 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
}

# Todo: Why are the following not the same as the UNION ALL case above?
do_eqp_test 4.2.3 {
  SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 4.2.4 {
  SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
do_eqp_test 4.2.5 {
  SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
} {
  1 0 0 {TABLE t1 (~1000000 rows)} 
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
  2 0 0 {TABLE t2 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
  2 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

do_eqp_test 4.3.1 {
  SELECT x FROM t1 UNION SELECT x FROM t2
} {
  1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
}

finish_test