Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch rework-EQP Excluding Merge-Ins
This is equivalent to a diff from 853f316359 to 956fef361a
2018-05-03
| ||
19:56 | Overhaul of EXPLAIN QUERY PLAN. The output is now in the form of a tree. More details of the query plan are shown, and what is shown is truer to what actually happens. (check-in: ff01bbdabc user: drh tags: trunk) | |
19:47 | Optimizations to the new EQP framework. (Closed-Leaf check-in: 956fef361a user: drh tags: rework-EQP) | |
01:37 | Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. (check-in: c75eee69fa user: drh tags: rework-EQP) | |
2018-05-02
| ||
03:01 | Add sqlite3_win32_set_directory8() and sqlite3_win32_set_directory16() functions. (check-in: 22089ea2bb user: mistachkin tags: trunk) | |
00:33 | Begin reengineering the EXPLAIN QUERY PLAN function to provide more intuitive output. (check-in: 70b48a7972 user: drh tags: rework-EQP) | |
2018-05-01
| ||
18:39 | The SQLITE_ALLOW_SQLITE_MASTER_INDEX compile-time option allows a CREATE INDEX statement against the sqlite_master table. Once created, the index works, and is usable by legacy instances of SQLite. (check-in: 853f316359 user: drh tags: trunk) | |
2018-04-28
| ||
19:08 | Test cases added for SQLITE_DBCONFIG_RESET_DATABASE. (check-in: 08665a9e2e user: drh tags: trunk) | |
Changes to ext/expert/expert1.test.
︙ | |||
91 92 93 94 95 96 97 | 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 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 | - + - + - + - + - + - + - + - + - + - + - + - + - + - - + + - - + + - + - + - + - - + + - - + + - + | eval $setup do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } { SELECT * FROM t1 } { (no new indexes) |
︙ |
Changes to ext/expert/sqlite3expert.c.
︙ | |||
1119 1120 1121 1122 1123 1124 1125 | 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 | - - - + + + | IdxHashEntry *pEntry; sqlite3_stmt *pExplain = 0; idxHashClear(&hIdx); rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr, "EXPLAIN QUERY PLAN %s", pStmt->zSql ); while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){ |
︙ | |||
1148 1149 1150 1151 1152 1153 1154 | 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 | - + - - | idxHashAdd(&rc, &hIdx, zSql, 0); if( rc ) goto find_indexes_out; } break; } } |
︙ |
Changes to ext/fts5/test/fts5plan.test.
︙ | |||
25 26 27 28 29 30 31 | 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 | + - - + + + - - + + + - - + + + - - + + - - + - - - - | CREATE TABLE t1(x, y); CREATE VIRTUAL TABLE f1 USING fts5(ff); } do_eqp_test 1.1 { SELECT * FROM t1, f1 WHERE f1 MATCH t1.x } { QUERY PLAN |
Changes to ext/rtree/rtree6.test.
︙ | |||
70 71 72 73 74 75 76 | 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 | + - - + + + - - + + + - - + + + - - + + + - - + + + - - + + | do_test rtree6-1.5 { rtree_strategy {SELECT * FROM t1,t2 WHERE k=+ii AND x1<10} } {C0} do_eqp_test rtree6.2.1 { SELECT * FROM t1,t2 WHERE k=+ii AND x1<10 } { QUERY PLAN |
︙ |
Changes to ext/rtree/rtreeC.test.
︙ | |||
25 26 27 28 29 30 31 | 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 | + - - + + + - - + + + - - + + + - - + + | CREATE TABLE t(x, y); } do_eqp_test 1.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |
︙ | |||
78 79 80 81 82 83 84 | 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 | + - - + + + - - + + + - - + + + - - + + + - - + + + - + + + - - + + + - - + + | db close sqlite3 db test.db do_eqp_test 2.1 { SELECT * FROM r_tree, t WHERE t.x>=min_x AND t.x<=max_x AND t.y>=min_y AND t.x<=max_y } { QUERY PLAN |
︙ | |||
185 186 187 188 189 190 191 | 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 | + - - + + + - - + + + - - + + | # First test a query with no ANALYZE data at all. The outer loop is # real table "t1". # do_eqp_test 5.2 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |
︙ | |||
237 238 239 240 241 242 243 | 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 | + - - + + | db close sqlite3 db test.db execsql { ATTACH 'test.db2' AS aux; } } {} do_eqp_test 5.8 { SELECT * FROM t1, rt WHERE x==id; } { QUERY PLAN |
︙ | |||
295 296 297 298 299 300 301 | 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 | - - - + + + + - - - + + + + + - - - - + + + + + - - - + + + + + - - - + + + + | INSERT INTO rt VALUES(1, 2, 7, 12, 14); -- Not a hit INSERT INTO rt VALUES(2, 2, 7, 8, 12); -- A hit! INSERT INTO rt VALUES(3, 7, 11, 8, 12); -- Not a hit! INSERT INTO rt VALUES(4, 5, 5, 10, 10); -- A hit! } |
Changes to src/expr.c.
︙ | |||
2406 2407 2408 2409 2410 2411 2412 | 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 | - - - + + - - | if( aiMap ) aiMap[i] = j; } assert( i==nExpr || colUsed!=(MASKBIT(nExpr)-1) ); if( colUsed==(MASKBIT(nExpr)-1) ){ /* If we reach this point, that means the index pIdx is usable */ int iAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); |
︙ | |||
2642 2643 2644 2645 2646 2647 2648 | 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 | - - - - + + - - + - - - - - | ** ** Generate code to write the results of the select into the temporary ** table allocated and opened above. */ Select *pSelect = pExpr->x.pSelect; ExprList *pEList = pSelect->pEList; |
︙ | |||
2773 2774 2775 2776 2777 2778 2779 | 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 | - + - - - + + - - - - - - - - | Expr *pLimit; /* New limit expression */ testcase( pExpr->op==TK_EXISTS ); testcase( pExpr->op==TK_SELECT ); assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT ); assert( ExprHasProperty(pExpr, EP_xIsSelect) ); |
︙ |
Changes to src/prepare.c.
︙ | |||
612 613 614 615 616 617 618 | 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 | - + | } rc = sParse.rc; #ifndef SQLITE_OMIT_EXPLAIN if( rc==SQLITE_OK && sParse.pVdbe && sParse.explain ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", |
︙ |
Changes to src/select.c.
︙ | |||
17 18 19 20 21 22 23 | 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | - + | /* ** Trace output macros */ #if SELECTTRACE_ENABLED /***/ int sqlite3SelectTrace = 0; # define SELECTTRACE(K,P,S,X) \ if(sqlite3SelectTrace&(K)) \ |
︙ | |||
1289 1290 1291 1292 1293 1294 1295 | 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 | - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | ** ** "USE TEMP B-TREE FOR xxx" ** ** where xxx is one of "DISTINCT", "ORDER BY" or "GROUP BY". Exactly which ** is determined by the zUsage argument. */ static void explainTempTable(Parse *pParse, const char *zUsage){ |
︙ | |||
2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336 | 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 | + | } /* Detach the ORDER BY clause from the compound SELECT */ p->pOrderBy = 0; /* Store the results of the setup-query in Queue. */ pSetup->pNext = 0; ExplainQueryPlan((pParse, 1, "SETUP")); rc = sqlite3Select(pParse, pSetup, &destQueue); pSetup->pNext = p; if( rc ) goto end_of_recursive_query; /* Find the next row in the Queue and output that row */ addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iQueue, addrBreak); VdbeCoverage(v); |
︙ | |||
2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 | 2318 2319 2320 2321 2322 2323 2324 2325 2326 2327 2328 2329 2330 2331 2332 | + | /* Execute the recursive SELECT taking the single row in Current as ** the value for the recursive-table. Store the results in the Queue. */ if( p->selFlags & SF_Aggregate ){ sqlite3ErrorMsg(pParse, "recursive aggregate queries not supported"); }else{ p->pPrior = 0; ExplainQueryPlan((pParse, 1, "RECURSIVE STEP")); sqlite3Select(pParse, p, &destQueue); assert( p->pPrior==0 ); p->pPrior = pSetup; } /* Keep running the loop until the Queue is empty */ sqlite3VdbeGoto(v, addrTop); |
︙ | |||
2402 2403 2404 2405 2406 2407 2408 | 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 | - - + - + + + - - - + - - + | ** Since the limit is exactly 1, we only need to evalutes the left-most VALUES. */ static int multiSelectValues( Parse *pParse, /* Parsing context */ Select *p, /* The right-most of SELECTs to be coded */ SelectDest *pDest /* What to do with query results */ ){ |
︙ | |||
2470 2471 2472 2473 2474 2475 2476 | 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 | - - - - | ){ int rc = SQLITE_OK; /* Success code from a subroutine */ Select *pPrior; /* Another SELECT immediately to our left */ Vdbe *v; /* Generate code to this VDBE */ SelectDest dest; /* Alternative data destination */ Select *pDelete = 0; /* Chain of simple selects to delete */ sqlite3 *db; /* Database connection */ |
︙ | |||
2524 2525 2526 2527 2528 2529 2530 | 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 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 | - + + + + + + + + - - - - - - - - - - + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | }else #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); |
︙ | |||
3072 3073 3074 3075 3076 3077 3078 | 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 3052 3053 3054 3055 | - - - - | int op; /* One of TK_ALL, TK_UNION, TK_EXCEPT, TK_INTERSECT */ KeyInfo *pKeyDup = 0; /* Comparison information for duplicate removal */ KeyInfo *pKeyMerge; /* Comparison information for merging rows */ sqlite3 *db; /* Database connection */ ExprList *pOrderBy; /* The ORDER BY clause */ int nOrderBy; /* Number of terms in the ORDER BY clause */ int *aPermute; /* Mapping from ORDER BY terms to result set columns */ |
︙ | |||
3195 3196 3197 3198 3199 3200 3201 3202 3203 3204 3205 3206 3207 3208 | 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 | + + - + - + | regAddrA = ++pParse->nMem; regAddrB = ++pParse->nMem; regOutA = ++pParse->nMem; regOutB = ++pParse->nMem; sqlite3SelectDestInit(&destA, SRT_Coroutine, regAddrA); sqlite3SelectDestInit(&destB, SRT_Coroutine, regAddrB); ExplainQueryPlan((pParse, 1, "MERGE (%s)", selectOpName(p->op))); /* Generate a coroutine to evaluate the SELECT statement to the ** left of the compound operator - the "A" select. */ addrSelectA = sqlite3VdbeCurrentAddr(v) + 1; addr1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrA, 0, addrSelectA); VdbeComment((v, "left SELECT")); pPrior->iLimit = regLimitA; |
︙ | |||
3329 3330 3331 3332 3333 3334 3335 | 3297 3298 3299 3300 3301 3302 3303 3304 3305 3306 3307 3308 3309 3310 3311 | - + | sqlite3SelectDelete(db, p->pPrior); } p->pPrior = pPrior; pPrior->pNext = p; /*** TBD: Insert subroutine calls to close cursors on incomplete **** subqueries ****/ |
︙ | |||
5117 5118 5119 5120 5121 5122 5123 | 5085 5086 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 5102 5103 | - + - - - | static void explainSimpleCount( Parse *pParse, /* Parse context */ Table *pTab, /* Table being queried */ Index *pIdx /* Index used to optimize scan, or NULL */ ){ if( pParse->explain==2 ){ int bCover = (pIdx!=0 && (HasRowid(pTab) || !IsPrimaryKeyIndex(pIdx))); |
︙ | |||
5337 5338 5339 5340 5341 5342 5343 | 5302 5303 5304 5305 5306 5307 5308 5309 5310 5311 5312 5313 5314 5315 5316 5317 5318 5319 5320 5321 5322 5323 5324 | - - - - - + - - - - + | SortCtx sSort; /* Info on how to code the ORDER BY clause */ AggInfo sAggInfo; /* Information used by aggregate queries */ int iEnd; /* Address of the end of the query */ sqlite3 *db; /* The database connection */ ExprList *pMinMaxOrderBy = 0; /* Added ORDER BY for min/max queries */ u8 minMaxFlag; /* Flag for min/max queries */ |
︙ | |||
5389 5390 5391 5392 5393 5394 5395 | 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357 5358 5359 5360 | - - - - | #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x104 ){ SELECTTRACE(0x104,pParse,p, ("after name resolution:\n")); sqlite3TreeViewSelect(0, p, 0); } #endif |
︙ | |||
5487 5488 5489 5490 5491 5492 5493 | 5441 5442 5443 5444 5445 5446 5447 5448 5449 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 | - + - + | /* Handle compound SELECT statements using the separate multiSelect() ** procedure. */ if( p->pPrior ){ rc = multiSelect(pParse, p, pDest); #if SELECTTRACE_ENABLED SELECTTRACE(0x1,pParse,p,("end compound-select processing\n")); |
︙ | |||
5603 5604 5605 5606 5607 5608 5609 | 5557 5558 5559 5560 5561 5562 5563 5564 5565 5566 5567 5568 5569 5570 5571 | - + | int addrTop = sqlite3VdbeCurrentAddr(v)+1; pItem->regReturn = ++pParse->nMem; sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop); VdbeComment((v, "%s", pItem->pTab->zName)); pItem->addrFillSub = addrTop; sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn); |
︙ | |||
5638 5639 5640 5641 5642 5643 5644 | 5592 5593 5594 5595 5596 5597 5598 5599 5600 5601 5602 5603 5604 5605 5606 5607 5608 5609 5610 | - - + | VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName)); }else{ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName)); } pPrior = isSelfJoinView(pTabList, pItem); if( pPrior ){ sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor); |
︙ | |||
6281 6282 6283 6284 6285 6286 6287 | 6234 6235 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 6246 6247 | - + - + | */ select_end: sqlite3ExprListDelete(db, pMinMaxOrderBy); sqlite3DbFree(db, sAggInfo.aCol); sqlite3DbFree(db, sAggInfo.aFunc); #if SELECTTRACE_ENABLED SELECTTRACE(0x1,pParse,p,("end processing\n")); |
Changes to src/shell.c.in.
︙ | |||
977 978 979 980 981 982 983 | 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 | - + + | sqlite3expert *pExpert; int bVerbose; }; /* A single line in the EQP output */ typedef struct EQPGraphRow EQPGraphRow; struct EQPGraphRow { |
︙ | |||
999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 | 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 | + | ** instance of the following structure. */ typedef struct ShellState ShellState; struct ShellState { sqlite3 *db; /* The database */ u8 autoExplain; /* Automatically turn on .explain mode */ u8 autoEQP; /* Run EXPLAIN QUERY PLAN prior to seach SQL stmt */ u8 autoEQPtest; /* autoEQP is in test mode */ u8 statsOn; /* True to display memory stats before each finalize */ u8 scanstatsOn; /* True to display scan stats before each finalize */ u8 openMode; /* SHELL_OPEN_NORMAL, _APPENDVFS, or _ZIPFILE */ u8 doXdgOpen; /* Invoke start/open/xdg-open in output_reset() */ u8 nEqpLevel; /* Depth of the EQP output graph */ unsigned mEqpLines; /* Mask of veritical lines in the EQP output graph */ int outCount; /* Revert to stdout when reaching zero */ |
︙ | |||
1049 1050 1051 1052 1053 1054 1055 | 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 | - - - - + + + + | #endif ExpertInfo expert; /* Valid if previous command was ".expert OPT..." */ }; /* Allowed values for ShellState.autoEQP */ |
︙ | |||
1663 1664 1665 1666 1667 1668 1669 | 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 | - + + + + + - + | } return 1; } /* ** Add a new entry to the EXPLAIN QUERY PLAN data */ |
︙ | |||
1692 1693 1694 1695 1696 1697 1698 | 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 | - + - + - + - + - + - - - + + - + - - + - - - - - - - - - - - - - - - | for(pRow = p->sGraph.pRow; pRow; pRow = pNext){ pNext = pRow->pNext; sqlite3_free(pRow); } memset(&p->sGraph, 0, sizeof(p->sGraph)); } |
︙ | |||
2110 2111 2112 2113 2114 2115 2116 | 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 | - + | if( i>0 ) utf8_printf(p->out, "%s", p->colSeparator); utf8_printf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue); } utf8_printf(p->out, "%s", p->rowSeparator); break; } case MODE_EQP: { |
︙ | |||
2952 2953 2954 2955 2956 2957 2958 | 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 2958 | - + + - + | sqlite3_db_config(db, SQLITE_DBCONFIG_TRIGGER_EQP, 1, 0); } zEQP = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zStmtSql); rc = sqlite3_prepare_v2(db, zEQP, -1, &pExplain, 0); if( rc==SQLITE_OK ){ while( sqlite3_step(pExplain)==SQLITE_ROW ){ const char *zEQPLine = (const char*)sqlite3_column_text(pExplain,3); |
︙ | |||
5910 5911 5912 5913 5914 5915 5916 5917 5918 5919 5920 5921 5922 5923 5924 5925 5926 5927 | 5900 5901 5902 5903 5904 5905 5906 5907 5908 5909 5910 5911 5912 5913 5914 5915 5916 5917 5918 5919 5920 5921 | + + + + | raw_printf(stderr, "Usage: .echo on|off\n"); rc = 1; } }else if( c=='e' && strncmp(azArg[0], "eqp", n)==0 ){ if( nArg==2 ){ p->autoEQPtest = 0; if( strcmp(azArg[1],"full")==0 ){ p->autoEQP = AUTOEQP_full; }else if( strcmp(azArg[1],"trigger")==0 ){ p->autoEQP = AUTOEQP_trigger; }else if( strcmp(azArg[1],"test")==0 ){ p->autoEQP = AUTOEQP_on; p->autoEQPtest = 1; }else{ p->autoEQP = (u8)booleanValue(azArg[1]); } }else{ raw_printf(stderr, "Usage: .eqp off|on|trigger|full\n"); rc = 1; } |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
2605 2606 2607 2608 2609 2610 2611 | 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 | - - - | unsigned notIndexed :1; /* True if there is a NOT INDEXED clause */ unsigned isIndexedBy :1; /* True if there is an INDEXED BY clause */ unsigned isTabFunc :1; /* True if table-valued-function syntax */ unsigned isCorrelated :1; /* True if sub-query is correlated */ unsigned viaCoroutine :1; /* Implemented as a co-routine */ unsigned isRecursive :1; /* True for recursive reference in WITH */ } fg; |
︙ | |||
2779 2780 2781 2782 2783 2784 2785 | 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 | - | ExprList *pEList; /* The fields of the result */ u8 op; /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */ LogEst nSelectRow; /* Estimated number of result rows */ u32 selFlags; /* Various SF_* values */ int iLimit, iOffset; /* Memory registers holding LIMIT & OFFSET counters */ #if SELECTTRACE_ENABLED char zSelName[12]; /* Symbolic name of this SELECT use for debugging */ |
︙ | |||
2820 2821 2822 2823 2824 2825 2826 | 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 | - + - | #define SF_NestedFrom 0x00800 /* Part of a parenthesized FROM clause */ #define SF_MinMaxAgg 0x01000 /* Aggregate containing min() or max() */ #define SF_Recursive 0x02000 /* The recursive part of a recursive CTE */ #define SF_FixedLimit 0x04000 /* nSelectRow set by a constant LIMIT */ #define SF_MaybeConvert 0x08000 /* Need convertCompoundSelectToSubquery() */ #define SF_Converted 0x10000 /* By convertCompoundSelectToSubquery() */ #define SF_IncludeHidden 0x20000 /* Include hidden columns in output */ |
︙ | |||
3091 3092 3093 3094 3095 3096 3097 | 3086 3087 3088 3089 3090 3091 3092 3093 3094 3095 3096 3097 3098 3099 3100 | - + - | u8 explain; /* True if the EXPLAIN flag is found on the query */ #ifndef SQLITE_OMIT_VIRTUALTABLE u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ #endif int nHeight; /* Expression tree height of current sub-select */ #ifndef SQLITE_OMIT_EXPLAIN |
︙ |
Changes to src/treeview.c.
︙ | |||
137 138 139 140 141 142 143 | 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 | - + - + | sqlite3TreeViewWith(pView, p->pWith, 1); cnt = 1; sqlite3TreeViewPush(pView, 1); } do{ #if SELECTTRACE_ENABLED sqlite3TreeViewLine(pView, |
︙ |
Changes to src/vdbe.h.
︙ | |||
193 194 195 196 197 198 199 | 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 | - + + + + + + + + + + + + + | #if defined(SQLITE_DEBUG) && !defined(SQLITE_TEST_REALLOC_STRESS) void sqlite3VdbeVerifyNoMallocRequired(Vdbe *p, int N); void sqlite3VdbeVerifyNoResultRow(Vdbe *p); #else # define sqlite3VdbeVerifyNoMallocRequired(A,B) # define sqlite3VdbeVerifyNoResultRow(A) #endif |
︙ |
Changes to src/vdbeaux.c.
︙ | |||
299 300 301 302 303 304 305 306 307 308 309 310 311 312 | 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 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + | int p4type /* P4 operand type */ ){ char *p4copy = sqlite3DbMallocRawNN(sqlite3VdbeDb(p), 8); if( p4copy ) memcpy(p4copy, zP4, 8); return sqlite3VdbeAddOp4(p, op, p1, p2, p3, p4copy, p4type); } #ifndef SQLITE_OMIT_EXPLAIN /* ** Return the address of the current EXPLAIN QUERY PLAN baseline. ** 0 means "none". */ int sqlite3VdbeExplainParent(Parse *pParse){ VdbeOp *pOp; if( pParse->addrExplain==0 ) return 0; pOp = sqlite3VdbeGetOp(pParse->pVdbe, pParse->addrExplain); return pOp->p2; } /* ** Add a new OP_Explain opcode. ** ** If the bPush flag is true, then make this opcode the parent for ** subsequent Explains until sqlite3VdbeExplainPop() is called. */ void sqlite3VdbeExplain(Parse *pParse, u8 bPush, const char *zFmt, ...){ if( pParse->explain==2 ){ char *zMsg; Vdbe *v = pParse->pVdbe; va_list ap; int iThis; va_start(ap, zFmt); zMsg = sqlite3VMPrintf(pParse->db, zFmt, ap); va_end(ap); v = pParse->pVdbe; iThis = v->nOp; sqlite3VdbeAddOp4(v, OP_Explain, iThis, pParse->addrExplain, 0, zMsg, P4_DYNAMIC); if( bPush) pParse->addrExplain = iThis; } } /* ** Pop the EXPLAIN QUERY PLAN stack one level. */ void sqlite3VdbeExplainPop(Parse *pParse){ pParse->addrExplain = sqlite3VdbeExplainParent(pParse); } #endif /* SQLITE_OMIT_EXPLAIN */ /* ** Add an OP_ParseSchema opcode. This routine is broken out from ** sqlite3VdbeAddOp4() since it needs to also needs to mark all btrees ** as having been used. ** ** The zWhere string must have been obtained from sqlite3_malloc(). ** This routine will take ownership of the allocated memory. |
︙ |
Changes to src/where.c.
︙ | |||
4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 | 4588 4589 4590 4591 4592 4593 4594 4595 4596 4597 4598 4599 4600 4601 4602 | + | /* Special case: No FROM clause */ if( nTabList==0 ){ if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr; if( wctrlFlags & WHERE_WANT_DISTINCT ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW")); }else{ /* Assign a bit from the bitmask to every term in the FROM clause. ** ** The N-th term of the FROM clause is assigned a bitmask of 1<<N. ** ** The rule of the previous sentence ensures thta if X is the bitmask for ** a table T, then X-1 is the bitmask for all other tables to the left of T. |
︙ |
Changes to src/wherecode.c.
︙ | |||
130 131 132 133 134 135 136 | 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 | - - + | #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS) if( sqlite3ParseToplevel(pParse)->explain==2 ) #endif { struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; Vdbe *v = pParse->pVdbe; /* VM being constructed */ sqlite3 *db = pParse->db; /* Database handle */ |
︙ | |||
210 211 212 213 214 215 216 | 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 | - + + | if( pLoop->nOut>=10 ){ sqlite3XPrintf(&str, " (~%llu rows)", sqlite3LogEstToInt(pLoop->nOut)); }else{ sqlite3StrAccumAppend(&str, " (~1 row)", 9); } #endif zMsg = sqlite3StrAccumFinish(&str); |
︙ |
Changes to test/analyze3.test.
︙ | |||
114 115 116 117 118 119 120 | 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 | - + - + - + - + - + - + - + | # The first of the following two SELECT statements visits 99 rows. So # it is better to use the index. But the second visits every row in # the table (1000 in total) so it is better to do a full-table scan. # do_eqp_test analyze3-1.1.2 { SELECT sum(y) FROM t1 WHERE x>200 AND x<300 |
︙ | |||
197 198 199 200 201 202 203 | 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 | - + - + | } {} do_execsql_test analyze3-2.1.x { SELECT count(*) FROM t2 WHERE x>1 AND x<2; SELECT count(*) FROM t2 WHERE x>0 AND x<99; } {200 990} do_eqp_test analyze3-1.2.2 { SELECT sum(y) FROM t2 WHERE x>1 AND x<2 |
︙ | |||
249 250 251 252 253 254 255 | 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 | - + - + | } {} do_execsql_test analyze3-1.3.x { SELECT count(*) FROM t3 WHERE x>200 AND x<300; SELECT count(*) FROM t3 WHERE x>0 AND x<1100 } {99 1000} do_eqp_test analyze3-1.3.2 { SELECT sum(y) FROM t3 WHERE x>200 AND x<300 |
︙ | |||
304 305 306 307 308 309 310 | 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 | - + - + | append t [lindex {a b c d e f g h i j} [expr ($i%10)]] execsql { INSERT INTO t1 VALUES($i, $t) } } execsql COMMIT } {} do_eqp_test analyze3-2.2 { SELECT count(a) FROM t1 WHERE b LIKE 'a%' |
︙ | |||
694 695 696 697 698 699 700 | 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 | - + - + | } execsql COMMIT execsql ANALYZE } {} do_eqp_test analyze3-6-3 { SELECT * FROM t1 WHERE a = 5 AND c = 13; |
︙ |
Changes to test/analyze4.test.
︙ | |||
34 35 36 37 38 39 40 | 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | - + | INSERT INTO t1 SELECT a+32, b FROM t1; INSERT INTO t1 SELECT a+64, b FROM t1; ANALYZE; } # Should choose the t1a index since it is more specific than t1b. db eval {EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=5 AND b IS NULL} |
︙ |
Changes to test/analyze6.test.
︙ | |||
57 58 59 60 61 62 63 | 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 | - + - - - + + + + + + + - + - + - + - + - + - + - + - + - + | # The lowest cost plan is to scan CAT and for each integer there, do a single # lookup of the first corresponding entry in EV then read off the equal values # in EV. (Prior to the 2011-03-04 enhancement to where.c, this query would # have used EV for the outer loop instead of CAT - which was about 3x slower.) # do_test analyze6-1.1 { eqp {SELECT count(*) FROM ev, cat WHERE x=y} |
Changes to test/analyze7.test.
︙ | |||
33 34 35 36 37 38 39 | 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 | - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - + | CREATE INDEX t1b ON t1(b); CREATE INDEX t1cd ON t1(c,d); CREATE VIRTUAL TABLE nums USING wholenumber; INSERT INTO t1 SELECT value, value, value/100, value FROM nums WHERE value BETWEEN 1 AND 256; EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=123; } |
Changes to test/analyze8.test.
︙ | |||
57 58 59 60 61 62 63 | 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 | - + - + - + - + - + - + - + - + - + - + - + | # with a==100. And so for those cases, choose the t1b index. # # Buf ro a==99 and a==101, there are far fewer rows so choose # the t1a index. # do_test 1.1 { eqp {SELECT * FROM t1 WHERE a=100 AND b=55} |
Changes to test/analyze9.test.
︙ | |||
983 984 985 986 987 988 989 | 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 | + - + | } {/*USING INTEGER PRIMARY KEY*/} #------------------------------------------------------------------------- # reset_db do_execsql_test 22.0 { CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; SELECT * FROM t3; |
︙ | |||
1051 1052 1053 1054 1055 1056 1057 | 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 | - - + - - - + - | do_eqp_test 23.1 { SELECT * FROM t4 WHERE (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300 -- Formerly used index i41. But i41 is not a covering index whereas -- the PRIMARY KEY is a covering index, and so as of 2017-10-15, the -- PRIMARY KEY is preferred. |
︙ | |||
1104 1105 1106 1107 1108 1109 1110 | 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 | - - + + - - - + + - - - + - - - - + + - - + - | CREATE INDEX aa ON t6(a); CREATE INDEX bb ON t6(b); ANALYZE; } # Term (b<?) is estimated at 25%. Better than (a<30) but not as # good as (a<20). |
︙ | |||
1186 1187 1188 1189 1190 1191 1192 | 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 | - - + - | # no more than that. Guessing less than 20 is therefore unreasonable. # # At one point though, due to a problem in whereKeyStats(), the planner was # estimating that (x=10000 AND y<50) would match only 2 rows. # do_eqp_test 26.1.4 { SELECT * FROM t1 WHERE x = 10000 AND y < 50 AND z = 444; |
︙ | |||
1237 1238 1239 1240 1241 1242 1243 | 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 | - - + - | UPDATE t1 SET z = (rowid / 95); ANALYZE; COMMIT; } do_eqp_test 26.2.2 { SELECT * FROM t1 WHERE x='B' AND y>25 AND z=?; |
Changes to test/analyzeA.test.
︙ | |||
132 133 134 135 136 137 138 | 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 | - + - + - + - + - + - + - + - + | do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1 do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0 } 49 do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125 } 49 do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16 } 1 do_eqp_test 1.$tn.2.5 { SELECT * FROM t1 WHERE b = 31 AND c = 0; |
Changes to test/analyzeD.test.
︙ | |||
59 60 61 62 63 64 65 | 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 | - - + - - - + - - - + - - - - + + - | } {} # With full ANALYZE data, SQLite sees that c=150 (5 rows) is better than # a=3001 (7 rows). # do_eqp_test 1.2 { SELECT * FROM t1 WHERE a=3001 AND c=150; |
Changes to test/analyzeF.test.
︙ | |||
58 59 60 61 62 63 64 | 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | - + | 9 "x = str('19') AND y = str('4')" {t1y (y=?)} 10 "x = str('4') AND y = str('19')" {t1y (y=?)} 11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)} 12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)} } { |
︙ | |||
88 89 90 91 92 93 94 | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | - + | foreach {tn where idx} { 1 "x = det4() AND y = det19()" {t1x (x=?)} 2 "x = det19() AND y = det4()" {t1y (y=?)} 3 "x = nondet4() AND y = nondet19()" {t1y (y=?)} 4 "x = nondet19() AND y = nondet4()" {t1y (y=?)} } { |
︙ |
Changes to test/autoindex1.test.
︙ | |||
173 174 175 176 177 178 179 | 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 | - + + + - - - + + + - + - + - - - + + + - + - + - - - + + + - | # do_execsql_test autoindex1-500 { CREATE TABLE t501(a INTEGER PRIMARY KEY, b); CREATE TABLE t502(x INTEGER PRIMARY KEY, y); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); ANALYZE sqlite_master; |
︙ | |||
253 254 255 256 257 258 259 | 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 | - + + + + - - - - - - + + + + + + + + - + + - - + + | ON flock_owner (owner_change_date); CREATE INDEX fo_owner_person_id_index ON flock_owner (owner_person_id); CREATE INDEX sheep_org_flock_index ON sheep (originating_flock); CREATE INDEX sheep_reg_flock_index ON sheep (registering_flock); |
︙ |
Changes to test/autoindex3.test.
︙ | |||
80 81 82 83 84 85 86 | 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | + - - + + | # on the basis that the real index "uab" must be better than the automatic # index. This is not right - a skip-scan is not necessarily better than an # automatic index scan. # do_eqp_test 220 { select count(*) from u, v where u.b = v.b and v.e > 34; } { QUERY PLAN |
Changes to test/autoindex5.test.
︙ | |||
80 81 82 83 84 85 86 | 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 | - + - - + | AND debian_bugs.note = package_notes.id ORDER BY debian_bugs.bug; } {} # The following query should use an automatic index for the view # in FROM clause of the subquery of the second result column. # |
︙ |
Changes to test/bestindex1.test.
︙ | |||
47 48 49 50 51 52 53 | 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | - - + - - - + - | do_execsql_test 1.0 { CREATE VIRTUAL TABLE x1 USING tcl(vtab_command); } {} do_eqp_test 1.1 { SELECT * FROM x1 WHERE a = 'abc' |
︙ | |||
140 141 142 143 144 145 146 | 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 | + - - + + + - - + + + - - + + - + | do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} do_execsql_test 2.2.$mode.5 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } {1 4} set plan(use) { QUERY PLAN |
︙ |
Changes to test/bestindex2.test.
︙ | |||
85 86 87 88 89 90 91 | 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 | - - - + + - - - + + - - - + + + - - + + + - - - + + + + - - - + + + + - - - - + + + + | CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}"); CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}"); CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}"); } do_eqp_test 1.1 { SELECT * FROM t1 WHERE a='abc' |
Changes to test/bestindex3.test.
︙ | |||
75 76 77 78 79 80 81 | 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 | - - + - - - + - + - - + + + - - + + | do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0"); } do_eqp_test 1.1 { SELECT * FROM t1 WHERE a LIKE 'abc'; |
︙ | |||
143 144 145 146 147 148 149 | 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 | + - - - - + + + + | CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT); CREATE INDEX t2x ON t2(x COLLATE nocase); CREATE INDEX t2y ON t2(y); } do_eqp_test 2.2 { SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' } [string map {"\n " \n} { |
︙ |
Changes to test/bigmmap.test.
︙ | |||
88 89 90 91 92 93 94 | 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | + - - - - - + + + + + - | ORDER BY b, c; " {} do_eqp_test 2.$i.$t.3 " SELECT * FROM t$t AS o WHERE NOT EXISTS( SELECT * FROM t$t AS i WHERE a=o.a AND +b=o.b AND +c=o.c ) ORDER BY b, c; " [string map {"\n " "\n"} " |
Changes to test/cost.test.
︙ | |||
20 21 22 23 24 25 26 | 20 21 22 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 | + - - + + - - + - + - - - - + + + + - - - + + - - + - + - - + + + - - + + + - - - + + + + - - - + + + - - + - - - + - | CREATE TABLE t4(c, d, e); CREATE UNIQUE INDEX i3 ON t3(b); CREATE UNIQUE INDEX i4 ON t4(c, d); } do_eqp_test 1.2 { SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d; } { QUERY PLAN |
︙ | |||
190 191 192 193 194 195 196 | 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 | + - - - - + + + + | do_eqp_test 8.2 { SELECT DISTINCT aname FROM album, composer, track WHERE cname LIKE '%bach%' AND unlikely(composer.cid=track.cid) AND unlikely(album.aid=track.aid); } { QUERY PLAN |
︙ | |||
259 260 261 262 263 264 265 | 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 | - - + - - - + - - - + - - - + - | execsql { INSERT INTO t6 VALUES($i%4, 'xyz', $i%8) } } execsql ANALYZE } {} do_eqp_test 10.3 { SELECT rowid FROM t6 WHERE a=0 AND c=0 |
Changes to test/coveridxscan.test.
︙ | |||
105 106 107 108 109 110 111 | 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 | - - + - - - + - - | CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols); CREATE INDEX i2 ON t2($cols); " do_eqp_test 5.1.1 { SELECT * FROM t1 ORDER BY c1, c2; |
Changes to test/e_createtable.test.
︙ | |||
1381 1382 1383 1384 1385 1386 1387 | 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 | - + - + - + | # do_execsql_test 4.10.0 { CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2(a, b, c, UNIQUE(b, c)); } do_createtable_tests 4.10 { 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" |
︙ |
Changes to test/eqp.test.
︙ | |||
39 40 41 42 43 44 45 | 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 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 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 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 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 | + - - - + + + + - - - + + + + - + + - - + + + - + + - - - + + + + + + - - + + + + + + + - - - + + + + + + + + - - - - + + + + + + + + + + + - + - - - + + + + + + + + - + - - - + + + - - - - + + + + + - - - + + + + - - + + + - - - + + + + - - - - + + + + + - - + + + - + + - + + - + + - + + - - - + + + + - - - + + + + - - - - + + + + + - - - + + + + + - - - - + + + + + + - - - - - - + + + + + + + + - - - + + + + - - - + + + + - - - + + + + + + - - + + + - + + + - - - - + + + + + - + + + - - - - + + + + + - + + + - - - - + + + + + - + + + - - - - + + + + + - + + + - - - + + + + - + + + - - - - + + + + + - + + + - - - - + + + + + - + + + - - - - + + + + + - + + + - - + + + - + + + - - - - + + + + + - + + + + + - - - + + + + - - - + + + - + - + - + - + - + - + - + - + - + - + - + - + - + - + - | CREATE TABLE t2(a INT, b INT, ex TEXT); CREATE TABLE t3(a INT, b INT, ex TEXT); } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { QUERY PLAN |
︙ | |||
553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 | 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 | + + - + + - + + - + + - + + - + + - + + - + + - + + - + + - + + - + | } [string trimleft { 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 2 0 0 SCAN TABLE t2 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) }] } } #------------------------------------------------------------------------- # The following tests - eqp-7.* - test that queries that use the OP_Count # optimization return something sensible with EQP. # drop_all_tables do_execsql_test 7.0 { CREATE TABLE t1(a INT, b INT, ex CHAR(100)); CREATE TABLE t2(a INT, b INT, ex CHAR(100)); CREATE INDEX i1 ON t2(a); } det 7.1 "SELECT count(*) FROM t1" { QUERY PLAN |
Changes to test/fts3aux1.test.
︙ | |||
101 102 103 104 105 106 107 | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 | - + - + | db func rec rec # Use EQP to show that the WHERE expression "term='braid'" uses a different # index number (1) than "+term='braid'" (0). # do_execsql_test 2.1.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' |
︙ | |||
150 151 152 153 154 155 156 | 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 | - + - + - + - + - + - + | # Special case: term=NULL # do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} do_execsql_test 2.2.1.1 { EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' |
︙ | |||
331 332 333 334 335 336 337 | 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 | - - + + + | 5 1 "ORDER BY documents" 6 1 "ORDER BY documents DESC" 7 1 "ORDER BY occurrences ASC" 8 1 "ORDER BY occurrences" 9 1 "ORDER BY occurrences DESC" } { |
︙ | |||
399 400 401 402 403 404 405 | 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 | - - + + + + - - + + + + - - + + + + - - + + + + - - + + + | INSERT INTO x1 VALUES('f g h i j'); INSERT INTO x1 VALUES('k k l l a'); INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; } |
︙ |
Changes to test/fts3join.test.
︙ | |||
92 93 94 95 96 97 98 | 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | + + - - - + + + | do_eqp_test 4.2 { SELECT * FROM t4 LEFT JOIN ( SELECT docid, * FROM ft4 WHERE ft4 MATCH ? ) AS rr ON t4.rowid=rr.docid WHERE t4.y = ?; } { QUERY PLAN |--MATERIALIZE xxxxxx |
Changes to test/fts3query.test.
︙ | |||
114 115 116 117 118 119 120 | 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 | + - - + + + - - + + + - - + + + - - + + | CREATE VIRTUAL TABLE ft USING fts3(title); CREATE TABLE bt(title); } } {} do_eqp_test fts3query-4.2 { SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date } { QUERY PLAN |
︙ |
Changes to test/index6.test.
︙ | |||
314 315 316 317 318 319 320 | 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 | + - - + + | INSERT INTO t8b VALUES('value', 3); INSERT INTO t8b VALUES('dummy', 4); } {} do_eqp_test index6-8.1 { SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a) } { QUERY PLAN |
︙ |
Changes to test/index7.test.
︙ | |||
317 318 319 320 321 322 323 | 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 | - - - + + | INSERT INTO t4 VALUES('def', 'xyz'); SELECT * FROM v4 WHERE d='xyz' AND c='def' } { def xyz } do_eqp_test index7-6.4 { SELECT * FROM v4 WHERE d='xyz' AND c='def' |
Changes to test/indexedby.test.
︙ | |||
36 37 38 39 40 41 42 | 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 | - - - - - - - - + + + + + + + + + - - + + | # proc EQP {sql} { uplevel "execsql {EXPLAIN QUERY PLAN $sql}" } # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. # |
︙ | |||
111 112 113 114 115 116 117 | 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 | - - + + - - - - - + + + + + - + - - - + + - - + - + - - - + + - - + - - + + + - - + + - - + + + - - + + - + - + - - - - - - + + + + + + - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + - - - - - - - - - - + + + + + + + + + + - - - + + - - + | # # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no # index shall be used when accessing the preceding table, including # implied indices create by UNIQUE and PRIMARY KEY constraints. However, # the rowid can still be used to look up entries even when "NOT INDEXED" # is specified. # |
︙ | |||
337 338 339 340 341 342 343 | 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | - + - + | SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3'; } {1 1 3} do_execsql_test 11.4 { SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0'; } {1 1 3} do_eqp_test 11.5 { SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0'; |
︙ |
Changes to test/indexexpr2.test.
︙ | |||
87 88 89 90 91 92 93 | 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 | + - - - - + + + + + - - - - + + + + | ifcapable json1 { do_eqp_test 3.3.1 { SELECT json_extract(x, '$.b') FROM t2 WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; } [string map {"\n " \n} { |
︙ |
Changes to test/join2.test.
︙ | |||
108 109 110 111 112 113 114 | 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 | + - - + + + - - + + | CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID; CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID; } do_eqp_test 3.1 { SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3); } { QUERY PLAN |
︙ | |||
154 155 156 157 158 159 160 | 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 | + - - - + + + + - - + + | do_execsql_test 4.1.4 { SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); } {2 v3 2 v3 1112 {} 1112 {}} do_eqp_test 4.1.5 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); } { QUERY PLAN |
︙ | |||
199 200 201 202 203 204 205 | 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 | + - - - + + + + - - + + | do_execsql_test 4.2.4 { SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1); } {2 v3 2 v3 1112 {} 1112 {}} do_eqp_test 4.2.5 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); } { QUERY PLAN |
︙ | |||
241 242 243 244 245 246 247 | 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 | - - - + + - - + - - - + - | CREATE TABLE s1 (a INTEGER PRIMARY KEY); CREATE TABLE s2 (a INTEGER PRIMARY KEY); CREATE TABLE s3 (a INTEGER); CREATE UNIQUE INDEX ndx on s3(a); } do_eqp_test 5.1 { SELECT s1.a FROM s1 left join s2 using (a); |
︙ |
Changes to test/join5.test.
︙ | |||
260 261 262 263 264 265 266 | 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 | + - - - + + + + - - + + - | } do_eqp_test 7.2 { SELECT * FROM t1 LEFT JOIN t2 ON ( t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL)) ); } { QUERY PLAN |
Changes to test/mallocK.test.
︙ | |||
117 118 119 120 121 122 123 | 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 | + - - - - + + + + | SELECT 'x' > '.'; } {1} ifcapable stat4 { do_eqp_test 6.1 { SELECT DISTINCT c FROM t3 WHERE b BETWEEN '.xx..' AND '.xxxx'; } [string map {"\n " \n} { |
︙ |
Changes to test/orderby1.test.
︙ | |||
450 451 452 453 454 455 456 | 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 | - - - + + + + + + | SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y; } } {1 13 1 14 1 15 1 16} # No sorting of queries that omit the FROM clause. # |
︙ | |||
508 509 510 511 512 513 514 | 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 | + - - + + | CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); } do_eqp_test 8.1 { SELECT * FROM t1 ORDER BY a, b; } { QUERY PLAN |
︙ |
Changes to test/rollback2.test.
︙ | |||
97 98 99 100 101 102 103 | 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | - + | } #-------------------------------------------------------------------- # Try with some index scans # do_eqp_test 3.1 { SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC; |
︙ | |||
127 128 129 130 131 132 133 | 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | - + | # Now with some index scans that feature overflow keys. # set leader [string repeat "abcdefghij" 70] do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; } do_eqp_test 4.2 { SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC; |
︙ |
Changes to test/rowvalue.test.
︙ | |||
171 172 173 174 175 176 177 | 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 | - + - + - + - + - + | INSERT INTO xy VALUES(3, 3, 3); INSERT INTO xy VALUES(4, 4, 4); } foreach {tn sql res eqp} { 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} |
︙ |
Changes to test/rowvalue4.test.
︙ | |||
180 181 182 183 184 185 186 | 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 | - - - - - - - - - + + + + + + + + + - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + | INSERT INTO c1(c, d) SELECT a, b FROM c1; CREATE INDEX c1ab ON c1(a, b); CREATE INDEX c1cd ON c1(c, d); ANALYZE; } |
︙ | |||
230 231 232 233 234 235 236 | 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 | + - - - - - + + + + + - - - + + - - - + + - - - + + - - + - - - - + - | } do_eqp_test 5.1 { SELECT * FROM d2 WHERE (a, b) IN (SELECT x, y FROM d1) AND (c) IN (SELECT y FROM d1) } { QUERY PLAN |
︙ |
Changes to test/scanstatus.test.
︙ | |||
324 325 326 327 328 329 330 | 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 | - + + - - + + + - - + + | do_scanstatus_test 5.2.2 { nLoop 1 nVisit 2 nEst 2.0 zName sqlite_autoindex_t1_1 zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)} } do_eqp_test 5.3.1 { SELECT count(*) FROM t2 WHERE y = 'j'; |
︙ |
Changes to test/selectA.test.
︙ | |||
1332 1333 1334 1335 1336 1337 1338 | 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 | + + + - - - - + + + + + - | do_eqp_test 4.1.2 { SELECT c, d FROM t5 UNION ALL SELECT a, b FROM t4 WHERE f()==f() ORDER BY 1,2 } { QUERY PLAN `--MERGE (UNION ALL) |--LEFT |
︙ |
Changes to test/selectD.test.
︙ | |||
165 166 167 168 169 170 171 | 165 166 167 168 169 170 171 172 173 174 | - + | SELECT * FROM t41 LEFT JOIN (SELECT count(*) AS cnt, x1.d FROM (t42 INNER JOIN t43 ON d=g) AS x1 WHERE x1.d>5 GROUP BY x1.d) AS x2 ON t41.b=x2.d; |
Changes to test/skipscan2.test.
︙ | |||
195 196 197 198 199 200 201 | 195 196 197 198 199 200 201 202 203 204 205 | - + | for {set i 0} {$i < 1000} {incr i} { execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') } } execsql { ANALYZE } } {} do_eqp_test skipscan2-3.3eqp { SELECT * FROM t3 WHERE b=42; |
Changes to test/skipscan6.test.
︙ | |||
175 176 177 178 179 180 181 | 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | - - + - - - + - - - - - - | t3 t3_ba {100 20 1 1} } # Use index "t3_a", as (a=?) is expected to match only a single row. # do_eqp_test 3.1 { SELECT * FROM t3 WHERE a = ? AND c = ? |
Changes to test/tester.tcl.
︙ | |||
955 956 957 958 959 960 961 962 | 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 | + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - + + + + | uplevel do_test [list $testname] [list "catchsql {$sql}"] [list $result] } proc do_timed_execsql_test {testname sql {result {}}} { fix_testname testname uplevel do_test [list $testname] [list "execsql_timed {$sql}"]\ [list [list {*}$result]] } # Run an EXPLAIN QUERY PLAN $sql in database "db". Then rewrite the output # as an ASCII-art graph and return a string that is that graph. # # Hexadecimal literals in the output text are converted into "xxxxxx" since those # literals are pointer values that might very from one run of the test to the # next, yet we want the output to be consistent. # proc query_plan_graph {sql} { db eval "EXPLAIN QUERY PLAN $sql" { set dx($id) $detail lappend cx($parent) $id } set a "\n QUERY PLAN\n" append a [append_graph " " dx cx 0] return [regsub -all { 0x[A-F0-9]+\y} $a { xxxxxx}] } # Helper routine for [query_plan_graph SQL]: # # Output rows of the graph that are children of $level. # # prefix: Prepend to every output line # # dxname: Name of an array variable that stores text describe # The description for $id is $dx($id) # # cxname: Name of an array variable holding children of item. # Children of $id are $cx($id) # # level: Render all lines that are children of $level # proc append_graph {prefix dxname cxname level} { upvar $dxname dx $cxname cx set a "" set x $cx($level) set n [llength $x] for {set i 0} {$i<$n} {incr i} { set id [lindex $x $i] if {$i==$n-1} { set p1 "`--" set p2 " " } else { set p1 "|--" set p2 "| " } append a $prefix$p1$dx($id)\n if {[info exists cx($id)]} { append a [append_graph "$prefix$p2" dx cx $id] } } return $a } # Do an EXPLAIN QUERY PLAN test on input $sql with expected results $res # # If $res begins with a "\s+QUERY PLAN\n" then it is assumed to be the # complete graph which must match the output of [query_plan_graph $sql] # exactly. # # If $res does not begin with "\s+QUERY PLAN\n" then take it is a string # that must be found somewhere in the query plan output. # proc do_eqp_test {name sql res} { if {[regexp {^\s+QUERY PLAN\n} $res]} { uplevel do_test $name [list [list query_plan_graph $sql]] [list $res] } else { if {[string index $res 0]!="/"} { set res "/*$res*/" } |
︙ |
Changes to test/tkt-385a5b56b9.test.
︙ | |||
30 31 32 33 34 35 36 | 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | - - - + + + - - - - + + + - - - - + + + - - - - + + + | do_execsql_test 2.0 { CREATE TABLE t2(x, y NOT NULL); CREATE UNIQUE INDEX t2x ON t2(x); CREATE UNIQUE INDEX t2y ON t2(y); } |
Changes to test/tkt-78e04e52ea.test.
︙ | |||
37 38 39 40 41 42 43 | 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 | - - + - - + - + - + | } {0 {} {} 0 {} 0 1 x CHAR(100) 0 {} 0} do_test tkt-78e04-1.3 { execsql { CREATE INDEX i1 ON ""("" COLLATE nocase); } } {} do_test tkt-78e04-1.4 { |
Changes to test/tkt-b75a9ca6b0.test.
︙ | |||
28 29 30 31 32 33 34 | 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 | - - - - + + + + - + - + - + - + - + - + - + | INSERT INTO t1 VALUES (3, 1); } do_execsql_test 1.1 { CREATE INDEX i1 ON t1(x, y); } |
︙ |
Changes to test/tkt3442.test.
︙ | |||
30 31 32 33 34 35 36 | 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 | - - - - - - - - - - - - - - - + + + + + + - - - - - - + + + + | id TEXT, node INTEGER ); CREATE UNIQUE INDEX ididx ON listhash(id); } } {} |
Changes to test/tpch01.test.
︙ | |||
161 162 163 164 165 166 167 | 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 | - + | and p_type = 'LARGE PLATED STEEL' ) as all_nations group by o_year order by o_year;}] set ::eqpres |
︙ | |||
183 184 185 186 187 188 189 | 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 | + + - - + + + + + + + + | c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1994-08-01' and o_orderdate < date('1994-08-01', '+3 month') and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc; } { QUERY PLAN |
Changes to test/unordered.test.
︙ | |||
36 37 38 39 40 41 42 | 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 | - - + + - - + + - - + + - - - + + - - + + - - + + - - + + | if {$idxmode == "unordered"} { execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' } } db close sqlite3 db test.db foreach {tn sql r(ordered) r(unordered)} { 1 "SELECT * FROM t1 ORDER BY a" |
Changes to test/where3.test.
︙ | |||
231 232 233 234 235 236 237 | 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 | + + - + + - - + + - + - + - - + + | CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c); CREATE INDEX t301c ON t301(c); INSERT INTO t301 VALUES(1,2,3); INSERT INTO t301 VALUES(2,2,3); CREATE TABLE t302(x, y); INSERT INTO t302 VALUES(4,5); ANALYZE; } do_eqp_test where3-3.0a { |
︙ | |||
304 305 306 307 308 309 310 | 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 | - - + + + - - - + + + - + - + - - - + + + - + - + - - - + + + - + - + - - - + + + | fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER); CREATE INDEX bbb_111 ON bbb (fk, type); CREATE INDEX bbb_222 ON bbb (parent, position); CREATE INDEX bbb_333 ON bbb (fk, lastModified); |
︙ |
Changes to test/where7.test.
︙ | |||
23337 23338 23339 23340 23341 23342 23343 | 23337 23338 23339 23340 23341 23342 23343 23344 23345 23346 23347 23348 23349 23350 23351 23352 23353 23354 23355 23356 23357 23358 23359 23360 23361 | - - + + + - - - - + + + + | c2 INTEGER, c4 INTEGER, FOREIGN KEY (c8) REFERENCES t301(c8) ); CREATE INDEX t302_c3 on t302(c3); CREATE INDEX t302_c8_c3 on t302(c8, c3); CREATE INDEX t302_c5 on t302(c5); |
Changes to test/where9.test.
︙ | |||
353 354 355 356 357 358 359 | 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 | - + - + - - - - - - + + + + + + - + - - - - - + + + + + | WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80) ORDER BY 1, 2, 3 } } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1} ifcapable explain { |
︙ | |||
442 443 444 445 446 447 448 | 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 | - - - - - - - - - - + + + + + + + + + + - - - - + + + + - - - + + - - - - - - + + + + + - - + - - | SELECT a FROM t1 INDEXED BY t1d WHERE b>1000 AND (c=31031 OR d IS NULL) ORDER BY +a } } {1 {no query solution}} |
︙ |
Changes to test/whereG.test.
︙ | |||
62 63 64 65 66 67 68 | 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | - + | } {} do_eqp_test whereG-1.1 { SELECT DISTINCT aname FROM album, composer, track WHERE unlikely(cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; |
︙ | |||
191 192 193 194 195 196 197 | 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 | - + - + - + - + - + - + - + - + - + | do_execsql_test 5.1 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a, b); } do_eqp_test 5.1.2 { SELECT * FROM t1 WHERE a>? |
︙ |
Changes to test/whereI.test.
︙ | |||
25 26 27 28 29 30 31 | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | + - - + + | CREATE INDEX i1 ON t1(b); CREATE INDEX i2 ON t1(c); } do_eqp_test 1.1 { SELECT a FROM t1 WHERE b='b' OR c='x' } { QUERY PLAN |
︙ | |||
53 54 55 56 57 58 59 | 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | + - - + + | CREATE INDEX i3 ON t2(b); CREATE INDEX i4 ON t2(c); } do_eqp_test 2.1 { SELECT a FROM t2 WHERE b='b' OR c='x' } { QUERY PLAN |
︙ |
Changes to test/whereJ.test.
︙ | |||
398 399 400 401 402 403 404 | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 | - - + - - - + - | # This one should use index "idx_c". do_eqp_test 3.4 { SELECT * FROM t1 WHERE a = 4 AND b BETWEEN 20 AND 80 -- Matches 80 rows AND c BETWEEN 150 AND 160 -- Matches 10 rows |
︙ |
Changes to test/with1.test.
︙ | |||
988 989 990 991 992 993 994 | 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 | - + - + + + + + + - + + + | FROM xyz ORDER BY 1 ) SELECT 1 FROM xyz; } 1 # EXPLAIN QUERY PLAN on a self-join of a CTE # |
︙ |
Changes to test/with3.test.
︙ | |||
75 76 77 78 79 80 81 | 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 | + - - + + + + + + - - - - + + + + - - + + + + + + - - - - + + + + + + + - - - - - - - + + + + + + + + | ANALYZE; } do_eqp_test 3.1.2 { WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1) SELECT * FROM cnt, y1 WHERE i=a } [string map {"\n " \n} { |
Changes to test/without_rowid1.test.
︙ | |||
234 235 236 237 238 239 240 | 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 | - + - - - - - + + + + + | INSERT INTO t45 VALUES(5, 'two', 'x'); INSERT INTO t45 VALUES(7, 'two', 'x'); INSERT INTO t45 VALUES(9, 'two', 'x'); } do_eqp_test 5.1 { SELECT * FROM t45 WHERE b=? AND a>? |
︙ |