Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improvements to the EQP display for compound select statements. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | rework-EQP |
Files: | files | file ages | folders |
SHA3-256: |
699a77e479010a331b0423f157a2fbfc |
User & Date: | drh 2018-05-02 02:22:22.993 |
Context
2018-05-02
| ||
14:24 | Fix a dangling-else problem that was causing recursive CTEs to malfunction. Begin fixing test cases to work with the new EQP output. (check-in: 82ca44b82f user: drh tags: rework-EQP) | |
02:22 | Improvements to the EQP display for compound select statements. (check-in: 699a77e479 user: drh tags: rework-EQP) | |
00:33 | Begin reengineering the EXPLAIN QUERY PLAN function to provide more intuitive output. (check-in: 70b48a7972 user: drh tags: rework-EQP) | |
Changes
Changes to src/select.c.
︙ | ︙ | |||
1310 1311 1312 1313 1314 1315 1316 | #else /* No-op versions of the explainXXX() functions and macros. */ # define explainTempTable(y,z) # define explainSetInteger(y,z) #endif | < < < < < < < < < < < < < < < < < < < < < < < < < < < | 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 | #else /* No-op versions of the explainXXX() functions and macros. */ # define explainTempTable(y,z) # define explainSetInteger(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. */ |
︙ | ︙ | |||
2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 | #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); }else /* Generate code for the left and right SELECT statements. */ | > > > > > > > > < | 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 | #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ if( p->pOrderBy ){ return multiSelectOrderBy(pParse, p, pDest); }else #ifndef SQLITE_OMIT_EXPLAIN if( pPrior->pPrior==0 ){ ExplainQueryPlan((pParse, 1, "COMPOUND QUERY")); ExplainQueryPlan((pParse, 1, "LEFT-MOST SUBQUERY")); ExplainQueryPlanSetId(pParse, pPrior); } #endif /* Generate code for the left and right SELECT statements. */ switch( p->op ){ case TK_ALL: { int addr = 0; int nLimit; assert( !pPrior->pLimit ); pPrior->iLimit = p->iLimit; pPrior->iOffset = p->iOffset; |
︙ | ︙ | |||
2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 | addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v); VdbeComment((v, "Jump ahead if LIMIT reached")); if( p->iOffset ){ sqlite3VdbeAddOp3(v, OP_OffsetLimit, p->iLimit, p->iOffset+1, p->iOffset); } } rc = sqlite3Select(pParse, p, &dest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow); if( pPrior->pLimit && sqlite3ExprIsInteger(pPrior->pLimit->pLeft, &nLimit) | > > | 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 | addr = sqlite3VdbeAddOp1(v, OP_IfNot, p->iLimit); VdbeCoverage(v); VdbeComment((v, "Jump ahead if LIMIT reached")); if( p->iOffset ){ sqlite3VdbeAddOp3(v, OP_OffsetLimit, p->iLimit, p->iOffset+1, p->iOffset); } } ExplainQueryPlan((pParse, 1, "UNION ALL")); ExplainQueryPlanSetId(pParse, p); rc = sqlite3Select(pParse, p, &dest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; p->nSelectRow = sqlite3LogEstAdd(p->nSelectRow, pPrior->nSelectRow); if( pPrior->pLimit && sqlite3ExprIsInteger(pPrior->pLimit->pLeft, &nLimit) |
︙ | ︙ | |||
2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 | assert( p->op==TK_UNION ); op = SRT_Union; } p->pPrior = 0; pLimit = p->pLimit; p->pLimit = 0; uniondest.eDest = op; rc = sqlite3Select(pParse, p, &uniondest); testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */ sqlite3ExprListDelete(db, p->pOrderBy); pDelete = p->pPrior; p->pPrior = pPrior; | > > > | 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 | assert( p->op==TK_UNION ); op = SRT_Union; } p->pPrior = 0; pLimit = p->pLimit; p->pLimit = 0; uniondest.eDest = op; ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE", selectOpName(p->op))); ExplainQueryPlanSetId(pParse, p); rc = sqlite3Select(pParse, p, &uniondest); testcase( rc!=SQLITE_OK ); /* Query flattening in sqlite3Select() might refill p->pOrderBy. ** Be sure to delete p->pOrderBy, therefore, to avoid a memory leak. */ sqlite3ExprListDelete(db, p->pOrderBy); pDelete = p->pPrior; p->pPrior = pPrior; |
︙ | ︙ | |||
2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 | addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab2, 0); assert( p->addrOpenEphm[1] == -1 ); p->addrOpenEphm[1] = addr; p->pPrior = 0; pLimit = p->pLimit; p->pLimit = 0; intersectdest.iSDParm = tab2; rc = sqlite3Select(pParse, p, &intersectdest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; | > > > | 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 | addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tab2, 0); assert( p->addrOpenEphm[1] == -1 ); p->addrOpenEphm[1] = addr; p->pPrior = 0; pLimit = p->pLimit; p->pLimit = 0; intersectdest.iSDParm = tab2; ExplainQueryPlan((pParse, 1, "%s USING TEMP B-TREE", selectOpName(p->op))); ExplainQueryPlanSetId(pParse, p); rc = sqlite3Select(pParse, p, &intersectdest); testcase( rc!=SQLITE_OK ); pDelete = p->pPrior; p->pPrior = pPrior; if( p->nSelectRow>pPrior->nSelectRow ) p->nSelectRow = pPrior->nSelectRow; sqlite3ExprDelete(db, p->pLimit); p->pLimit = pLimit; |
︙ | ︙ | |||
2714 2715 2716 2717 2718 2719 2720 | sqlite3VdbeResolveLabel(v, iBreak); sqlite3VdbeAddOp2(v, OP_Close, tab2, 0); sqlite3VdbeAddOp2(v, OP_Close, tab1, 0); break; } } | > > | > > | 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 | sqlite3VdbeResolveLabel(v, iBreak); sqlite3VdbeAddOp2(v, OP_Close, tab2, 0); sqlite3VdbeAddOp2(v, OP_Close, tab1, 0); break; } } #ifndef SQLITE_OMIT_EXPLAIN if( p->pNext==0 ){ ExplainQueryPlanPop(pParse); } #endif /* Compute collating sequences used by ** temporary tables needed to implement the compound select. ** Attach the KeyInfo structure to all temporary tables. ** ** This section is run by the right-most SELECT statement only. ** SELECT statements to the left always skip this part. The right-most |
︙ | ︙ | |||
3171 3172 3173 3174 3175 3176 3177 | regAddrA = ++pParse->nMem; regAddrB = ++pParse->nMem; regOutA = ++pParse->nMem; regOutB = ++pParse->nMem; sqlite3SelectDestInit(&destA, SRT_Coroutine, regAddrA); sqlite3SelectDestInit(&destB, SRT_Coroutine, regAddrB); | < > > > > > | 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 | 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; ExplainQueryPlan((pParse, 1, "LEFT")); ExplainQueryPlanSetId(pParse, pPrior); sqlite3Select(pParse, pPrior, &destA); sqlite3VdbeEndCoroutine(v, regAddrA); sqlite3VdbeJumpHere(v, addr1); /* Generate a coroutine to evaluate the SELECT statement on ** the right - the "B" select */ addrSelectB = sqlite3VdbeCurrentAddr(v) + 1; addr1 = sqlite3VdbeAddOp3(v, OP_InitCoroutine, regAddrB, 0, addrSelectB); VdbeComment((v, "right SELECT")); savedLimit = p->iLimit; savedOffset = p->iOffset; p->iLimit = regLimitB; p->iOffset = 0; ExplainQueryPlan((pParse, 1, "RIGHT")); ExplainQueryPlanSetId(pParse, p); sqlite3Select(pParse, p, &destB); p->iLimit = savedLimit; p->iOffset = savedOffset; sqlite3VdbeEndCoroutine(v, regAddrB); /* Generate a subroutine that outputs the current row of the A ** select as the next output row of the compound select. |
︙ | ︙ | |||
5318 5319 5320 5321 5322 5323 5324 | db = pParse->db; v = sqlite3GetVdbe(pParse); if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; } if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); | < < < < < < | 5314 5315 5316 5317 5318 5319 5320 5321 5322 5323 5324 5325 5326 5327 | db = pParse->db; v = sqlite3GetVdbe(pParse); if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; } if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); #if SELECTTRACE_ENABLED SELECTTRACE(1,pParse,p, ("begin processing:\n", pParse->addrExplain)); if( sqlite3SelectTrace & 0x100 ){ sqlite3TreeViewSelect(0, p, 0); } #endif |
︙ | ︙ | |||
5460 5461 5462 5463 5464 5465 5466 | rc = multiSelect(pParse, p, pDest); #if SELECTTRACE_ENABLED SELECTTRACE(0x1,pParse,p,("end compound-select processing\n")); if( (sqlite3SelectTrace & 0x2000)!=0 && ExplainQueryPlanParent(pParse)==0 ){ sqlite3TreeViewSelect(0, p, 0); } #endif | | | 5450 5451 5452 5453 5454 5455 5456 5457 5458 5459 5460 5461 5462 5463 5464 | rc = multiSelect(pParse, p, pDest); #if SELECTTRACE_ENABLED SELECTTRACE(0x1,pParse,p,("end compound-select processing\n")); if( (sqlite3SelectTrace & 0x2000)!=0 && ExplainQueryPlanParent(pParse)==0 ){ sqlite3TreeViewSelect(0, p, 0); } #endif if( p->pNext==0 ) ExplainQueryPlanPop(pParse); return rc; } #endif /* For each term in the FROM clause, do two things: ** (1) Authorized unreferenced tables ** (2) Generate code for all sub-queries |
︙ | ︙ |