Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Use "COMPOUND" instead of "COMPOSITE" in the EXPLAIN QUERY PLAN output to describe UNION, UNION ALL, EXCEPT and INTERSECT operations. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
28643b85d93d27a44b9370e4087efa8f |
| User & Date: | dan 2010-11-11 17:48:51 |
Context
|
2010-11-12
| ||
| 15:36 | Reduce the number of branches that need to be tested in the explainIndexRange() function of where.c. check-in: 6fdae9a6 user: drh tags: experimental | |
|
2010-11-11
| ||
| 17:48 | Use "COMPOUND" instead of "COMPOSITE" in the EXPLAIN QUERY PLAN output to describe UNION, UNION ALL, EXCEPT and INTERSECT operations. check-in: 28643b85 user: dan tags: experimental | |
| 16:46 | Add a row of EXPLAIN QUERY PLAN output for each composite select operation (UNION, EXCEPT etc.) in the query. check-in: 00fb8468 user: dan tags: experimental | |
Changes
Changes to src/select.c.
827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 |
int iSub2, /* Subquery id 2 */
int bUseTmp /* True if a temp table was used */
){
assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
if( pParse->explain==2 ){
Vdbe *v = pParse->pVdbe;
char *zMsg = sqlite3MPrintf(
pParse->db, "COMPOSITE SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
);
sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
}
}
/*
|
| |
827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 |
int iSub2, /* Subquery id 2 */
int bUseTmp /* True if a temp table was used */
){
assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
if( pParse->explain==2 ){
Vdbe *v = pParse->pVdbe;
char *zMsg = sqlite3MPrintf(
pParse->db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
);
sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
}
}
/*
|
Changes to test/eqp.test.
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 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 |
# 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 {SCAN TABLE t1 (~1000000 rows)}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.1.2 {
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.1.3 {
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)}
}
do_eqp_test 4.1.4 {
SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)}
}
do_eqp_test 4.1.5 {
SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)}
}
do_eqp_test 4.2.2 {
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
} {
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 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.2.3 {
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)}
}
do_eqp_test 4.2.4 {
SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)}
}
do_eqp_test 4.2.5 {
SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)}
}
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)}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.2 {
SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
} {
2 0 0 {SCAN TABLE t1 (~1000000 rows)}
3 0 0 {SCAN TABLE t2 (~1000000 rows)}
1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
} {
2 0 0 {SCAN TABLE t1 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
4 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 (UNION)}
}
finish_test
|
| | | | | | | | | | | | | | |
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 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 |
# 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 {SCAN TABLE t1 (~1000000 rows)}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.1.2 {
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.1.3 {
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
}
do_eqp_test 4.1.4 {
SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
}
do_eqp_test 4.1.5 {
SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}
do_eqp_test 4.2.2 {
SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
} {
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 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
}
do_eqp_test 4.2.3 {
SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
}
do_eqp_test 4.2.4 {
SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
}
do_eqp_test 4.2.5 {
SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
} {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
1 0 0 {USE TEMP B-TREE FOR ORDER BY}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}
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)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.2 {
SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
} {
2 0 0 {SCAN TABLE t1 (~1000000 rows)}
3 0 0 {SCAN TABLE t2 (~1000000 rows)}
1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
}
do_eqp_test 4.3.3 {
SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
} {
2 0 0 {SCAN TABLE t1 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
4 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
}
finish_test
|