Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Use OpenHash instead of OpenEphemeral for the RHS of IN operators if the result is not needed for sorting. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | subquery-codegen-refactor |
Files: | files | file ages | folders |
SHA1: |
715fac7749a6b1523fe9f7de8263f0c4 |
User & Date: | drh 2014-02-06 03:31:41.891 |
Context
2014-02-06
| ||
14:59 | Change more OP_OpenEphemeral operations to OP_OpenHash. (Leaf check-in: 881164cf6e user: drh tags: subquery-codegen-refactor) | |
03:31 | Use OpenHash instead of OpenEphemeral for the RHS of IN operators if the result is not needed for sorting. (check-in: 715fac7749 user: drh tags: subquery-codegen-refactor) | |
2014-02-05
| ||
19:10 | Separate out the code generators for the RHS of an IN operator and for SELECT/EXISTS expressions. (check-in: 61c34ba71b user: drh tags: subquery-codegen-refactor) | |
Changes
Changes to src/expr.c.
︙ | |||
1505 1506 1507 1508 1509 1510 1511 | 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 | - + + | ** intkey B-Tree to store the set of IN(...) values instead of the usual ** (slower) variable length keys B-Tree. */ #ifndef SQLITE_OMIT_SUBQUERY static void sqlite3CreateInOperatorRhsTable( Parse *pParse, /* Parsing context */ Expr *pExpr, /* The IN, SELECT, or EXISTS operator */ |
︙ | |||
1557 1558 1559 1560 1561 1562 1563 | 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 | + - + | ** column is used to build the index keys. If both 'x' and the ** SELECT... statement are columns, then numeric affinity is used ** if either column has NUMERIC or INTEGER affinity. If neither ** 'x' nor the SELECT... statement are columns, then numeric affinity ** is used. */ pExpr->iTable = pParse->nTab++; addr = sqlite3VdbeAddOp2(v, bOrdered ? OP_OpenEphemeral : OP_OpenHash, |
︙ | |||
1718 1719 1720 1721 1722 1723 1724 1725 1726 | 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 | + + + - + | ** if( register==NULL ){ ** has_null = <test if data structure contains null> ** register = 1 ** } ** ** in order to avoid running the <test if data structure contains null> ** test more often than is necessary. ** ** IN_INDEX_EPH ephemeral tables must be in key order if the bOrdered flag ** is true. If bOrdered is false, the generated table can be a hash. */ #ifndef SQLITE_OMIT_SUBQUERY |
︙ | |||
1813 1814 1815 1816 1817 1818 1819 | 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 | - + | }else{ testcase( pParse->nQueryLoop>0 ); pParse->nQueryLoop = 0; if( pX->pLeft->iColumn<0 && !ExprHasProperty(pX, EP_xIsSelect) ){ eType = IN_INDEX_ROWID; } } |
︙ | |||
1939 1940 1941 1942 1943 1944 1945 | 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 | - + | /* Compute the RHS. After this step, the table with cursor ** pExpr->iTable will contains the values that make up the RHS. */ v = pParse->pVdbe; assert( v!=0 ); /* OOM detected prior to this routine */ VdbeNoopComment((v, "begin IN expr")); |
︙ |
Changes to src/sqliteInt.h.
︙ | |||
3474 3475 3476 3477 3478 3479 3480 | 3474 3475 3476 3477 3478 3479 3480 3481 3482 3483 3484 3485 3486 3487 3488 | - + | #define sqlite3EndBenignMalloc() #endif #define IN_INDEX_ROWID 1 #define IN_INDEX_EPH 2 #define IN_INDEX_INDEX_ASC 3 #define IN_INDEX_INDEX_DESC 4 |
︙ |
Changes to src/where.c.
︙ | |||
2344 2345 2346 2347 2348 2349 2350 | 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355 2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 | - + - - - + + + + | ** The current value for the constraint is left in register iReg. ** ** For a constraint of the form X=expr, the expression is evaluated and its ** result is left on the stack. For constraints of the form X IN (...) ** this routine sets up a loop that will iterate over all values of X. */ static int codeEqualityTerm( |
︙ | |||
2378 2379 2380 2381 2382 2383 2384 | 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 | - + | ){ testcase( iEq==0 ); testcase( bRev ); bRev = !bRev; } assert( pX->op==TK_IN ); iReg = iTarget; |
︙ | |||
2460 2461 2462 2463 2464 2465 2466 | 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 | - + + | ** In the example above, the index on t1(a) has TEXT affinity. But since ** the right hand side of the equality constraint (t2.b) has NONE affinity, ** no conversion should be attempted before using a t2.b value as part of ** a key to search the index. Hence the first byte in the returned affinity ** string in this example would be set to SQLITE_AFF_NONE. */ static int codeAllEqualityTerms( |
︙ | |||
2522 2523 2524 2525 2526 2527 2528 | 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 | - + | int r1; pTerm = pLoop->aLTerm[j]; assert( pTerm!=0 ); /* The following testcase is true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); |
︙ | |||
2805 2806 2807 2808 2809 2810 2811 | 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 | - + | iReg = sqlite3GetTempRange(pParse, nConstraint+2); addrNotFound = pLevel->addrBrk; for(j=0; j<nConstraint; j++){ int iTarget = iReg+j+2; pTerm = pLoop->aLTerm[j]; if( pTerm==0 ) continue; if( pTerm->eOperator & WO_IN ){ |
︙ | |||
2845 2846 2847 2848 2849 2850 2851 | 2847 2848 2849 2850 2851 2852 2853 2854 2855 2856 2857 2858 2859 2860 2861 | - + | assert( pLoop->u.btree.nEq==1 ); iReleaseReg = sqlite3GetTempReg(pParse); pTerm = pLoop->aLTerm[0]; assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); |
︙ | |||
3035 3036 3037 3038 3039 3040 3041 | 3037 3038 3039 3040 3041 3042 3043 3044 3045 3046 3047 3048 3049 3050 3051 | - + | nExtraReg = 1; } /* Generate code to evaluate all constraint terms using == or IN ** and store the values of those terms in an array of registers ** starting at regBase. */ |
︙ |
Changes to test/in3.test.
︙ | |||
25 26 27 28 29 30 31 | 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | - + | # Return the number of OpenEphemeral instructions used in the # implementation of the sql statement passed as a an argument. # proc nEphemeral {sql} { set nEph 0 foreach op [execsql "EXPLAIN $sql"] { |
︙ |
Changes to test/in5.test.
︙ | |||
61 62 63 64 65 66 67 | 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 | - + - + - + | } {0} do_test in5-2.4 { execsql { SELECT d FROM t2 WHERE a IN t3x AND b IN t3y AND c IN t3z ORDER BY d; } } {12a 56e} do_test in5-2.5.1 { |
︙ |