Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch skip-ahead-distinct Excluding Merge-Ins
This is equivalent to a diff from 8e7b611863 to e50fd48969
2017-04-15
| ||
11:53 | In the skip-ahead-distinct optimization, fix a bug in the logic that determines when to invoke the optimization based on sqlite_stat1 statistics. (check-in: 89f9e4363a user: drh tags: trunk) | |
2017-04-14
| ||
22:41 | Fix to the decision logic for when to use the skip-ahead-distinct optimization. (Closed-Leaf check-in: e50fd48969 user: drh tags: skip-ahead-distinct) | |
00:45 | Fix a couple of unreachable branches. (check-in: 1aa0ea8db7 user: drh tags: skip-ahead-distinct) | |
2017-04-13
| ||
09:45 | Allow a user column name to be used on the LHS of a MATCH operator in FTS5. (check-in: 6f54ffd151 user: dan tags: trunk) | |
01:19 | Forward port the skip-ahead-distinct branch which was abandoned for some reason that I do not recall. This port should have been achived by a merge of trunk into the previous head of skip-ahead-distinct, but that did not work. So I had to manually "rebase" the changes. (check-in: 132339a1fb user: drh tags: skip-ahead-distinct) | |
00:12 | Fix a regression caused by the fix for ticket [6c9b5514077fed34551f98e64c09a1] - control characters allowed in JSON. (check-in: 8e7b611863 user: drh tags: trunk) | |
2017-04-12
| ||
17:50 | Update fts5 to support "<colset> : ( <expr> )" for column filtering, as well as "<colset> : NEAR(...)" and "<colset> : <phrase>". (check-in: c847543f8b user: dan tags: trunk) | |
Changes to src/select.c.
︙ | ︙ | |||
1686 1687 1688 1689 1690 1691 1692 | }else{ Expr *pColExpr = p; /* The expression that is the result column name */ Table *pTab; /* Table associated with this expression */ while( pColExpr->op==TK_DOT ){ pColExpr = pColExpr->pRight; assert( pColExpr!=0 ); } | | | 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 | }else{ Expr *pColExpr = p; /* The expression that is the result column name */ Table *pTab; /* Table associated with this expression */ while( pColExpr->op==TK_DOT ){ pColExpr = pColExpr->pRight; assert( pColExpr!=0 ); } if( pColExpr->op==TK_COLUMN && pColExpr->pTab!=0 ){ /* For columns use the column name name */ int iCol = pColExpr->iColumn; pTab = pColExpr->pTab; if( iCol<0 ) iCol = pTab->iPKey; zName = iCol>=0 ? pTab->aCol[iCol].zName : "rowid"; }else if( pColExpr->op==TK_ID ){ assert( !ExprHasProperty(pColExpr, EP_IntValue) ); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
3433 3434 3435 3436 3437 3438 3439 | } whereLoopClear(db, pNew); return rc; } /* | | | 3433 3434 3435 3436 3437 3438 3439 3440 3441 3442 3443 3444 3445 3446 3447 | } whereLoopClear(db, pNew); return rc; } /* ** Examine a WherePath (with the addition of the extra WhereLoop of the 6th ** parameters) to see if it outputs rows in the requested ORDER BY ** (or GROUP BY) without requiring a separate sort operation. Return N: ** ** N>0: N terms of the ORDER BY clause are satisfied ** N==0: No terms of the ORDER BY clause are satisfied ** N<0: Unknown yet how many terms of ORDER BY might be satisfied. ** |
︙ | ︙ | |||
3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 | if( wctrlFlags & WHERE_ORDERBY_LIMIT ) continue; }else{ pLoop = pLast; } if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){ if( pLoop->u.vtab.isOrdered ) obSat = obDone; break; } iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor; /* Mark off any ORDER BY term X that is a column in the table of ** the current loop for which there is term in the WHERE ** clause of the form X IS NULL or X=? that reference only outer ** loops. | > > | 3528 3529 3530 3531 3532 3533 3534 3535 3536 3537 3538 3539 3540 3541 3542 3543 | if( wctrlFlags & WHERE_ORDERBY_LIMIT ) continue; }else{ pLoop = pLast; } if( pLoop->wsFlags & WHERE_VIRTUALTABLE ){ if( pLoop->u.vtab.isOrdered ) obSat = obDone; break; }else{ pLoop->u.btree.nIdxCol = 0; } iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor; /* Mark off any ORDER BY term X that is a column in the table of ** the current loop for which there is term in the WHERE ** clause of the form X IS NULL or X=? that reference only outer ** loops. |
︙ | ︙ | |||
3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 | } } if( iColumn>=0 ){ pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); if( !pColl ) pColl = db->pDfltColl; if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue; } isMatch = 1; break; } if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){ /* Make sure the sort order is compatible in an ORDER BY clause. ** Sort order is irrelevant for a GROUP BY clause. */ if( revSet ){ | > | 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 | } } if( iColumn>=0 ){ pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); if( !pColl ) pColl = db->pDfltColl; if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue; } pLoop->u.btree.nIdxCol = j+1; isMatch = 1; break; } if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){ /* Make sure the sort order is compatible in an ORDER BY clause. ** Sort order is irrelevant for a GROUP BY clause. */ if( revSet ){ |
︙ | ︙ | |||
4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 | assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */ } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK { u64 colUsed = 0; | > | 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 | assert( iIndexCur>=0 ); if( op ){ sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb); sqlite3VdbeSetP4KeyInfo(pParse, pIx); if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0 && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED ){ sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */ } VdbeComment((v, "%s", pIx->zName)); #ifdef SQLITE_ENABLE_COLUMN_USED_MASK { u64 colUsed = 0; |
︙ | ︙ | |||
4842 4843 4844 4845 4846 4847 4848 | */ VdbeModuleComment((v, "End WHERE-core")); sqlite3ExprCacheClear(pParse); for(i=pWInfo->nLevel-1; i>=0; i--){ int addr; pLevel = &pWInfo->a[i]; pLoop = pLevel->pWLoop; | < > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 4888 4889 4890 4891 4892 4893 4894 4895 4896 | */ VdbeModuleComment((v, "End WHERE-core")); sqlite3ExprCacheClear(pParse); for(i=pWInfo->nLevel-1; i>=0; i--){ int addr; pLevel = &pWInfo->a[i]; pLoop = pLevel->pWLoop; if( pLevel->op!=OP_Noop ){ #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT int addrSeek = 0; Index *pIdx; int n; if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED && (pLoop->wsFlags & WHERE_INDEXED)!=0 && (pIdx = pLoop->u.btree.pIndex)->hasStat1 && (n = pLoop->u.btree.nIdxCol)>0 && pIdx->aiRowLogEst[n]>=36 ){ int r1 = pParse->nMem+1; int j, op; for(j=0; j<n; j++){ sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j); } pParse->nMem += n+1; op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT; addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n); VdbeCoverageIf(v, op==OP_SeekLT); VdbeCoverageIf(v, op==OP_SeekGT); sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2); } #endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */ /* The common case: Advance to the next row */ sqlite3VdbeResolveLabel(v, pLevel->addrCont); sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3); sqlite3VdbeChangeP5(v, pLevel->p5); VdbeCoverage(v); VdbeCoverageIf(v, pLevel->op==OP_Next); VdbeCoverageIf(v, pLevel->op==OP_Prev); VdbeCoverageIf(v, pLevel->op==OP_VNext); #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT if( addrSeek ) sqlite3VdbeJumpHere(v, addrSeek); #endif }else{ sqlite3VdbeResolveLabel(v, pLevel->addrCont); } if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){ struct InLoop *pIn; int j; sqlite3VdbeResolveLabel(v, pLevel->addrNxt); for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){ sqlite3VdbeJumpHere(v, pIn->addrInTop+1); |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
120 121 122 123 124 125 126 127 128 129 130 131 132 133 | LogEst rRun; /* Cost of running each loop */ LogEst nOut; /* Estimated number of output rows */ union { struct { /* Information for internal btree tables */ u16 nEq; /* Number of equality constraints */ u16 nBtm; /* Size of BTM vector */ u16 nTop; /* Size of TOP vector */ Index *pIndex; /* Index used, or NULL */ } btree; struct { /* Information for virtual tables */ int idxNum; /* Index number */ u8 needFree; /* True if sqlite3_free(idxStr) is needed */ i8 isOrdered; /* True if satisfies ORDER BY */ u16 omitMask; /* Terms that may be omitted */ | > | 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | LogEst rRun; /* Cost of running each loop */ LogEst nOut; /* Estimated number of output rows */ union { struct { /* Information for internal btree tables */ u16 nEq; /* Number of equality constraints */ u16 nBtm; /* Size of BTM vector */ u16 nTop; /* Size of TOP vector */ u16 nIdxCol; /* Index column used for ORDER BY */ Index *pIndex; /* Index used, or NULL */ } btree; struct { /* Information for virtual tables */ int idxNum; /* Index number */ u8 needFree; /* True if sqlite3_free(idxStr) is needed */ i8 isOrdered; /* True if satisfies ORDER BY */ u16 omitMask; /* Terms that may be omitted */ |
︙ | ︙ |
Added test/distinct2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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 171 172 173 174 175 176 177 178 179 180 181 182 183 | # 2016-04-15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script is DISTINCT queries using the skip-ahead # optimization. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix distinct2 do_execsql_test 100 { CREATE TABLE t1(x INTEGER PRIMARY KEY); INSERT INTO t1 VALUES(0),(1),(2); CREATE TABLE t2 AS SELECT DISTINCT a.x AS aa, b.x AS bb FROM t1 a, t1 b; SELECT *, '|' FROM t2 ORDER BY aa, bb; } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |} do_execsql_test 110 { DROP TABLE t2; CREATE TABLE t2 AS SELECT DISTINCT a.x AS aa, b.x AS bb FROM t1 a, t1 b WHERE a.x IN t1 AND b.x IN t1; SELECT *, '|' FROM t2 ORDER BY aa, bb; } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |} do_execsql_test 120 { CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL); INSERT INTO t102 VALUES ('0'),('1'),('2'); DROP TABLE t2; CREATE TABLE t2 AS SELECT DISTINCT * FROM t102 AS t0 JOIN t102 AS t4 ON (t2.i0 IN t102) NATURAL JOIN t102 AS t3 JOIN t102 AS t1 ON (t0.i0 IN t102) JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0)); SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5; } {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |} do_execsql_test 400 { CREATE TABLE t4(a,b,c,d,e,f,g,h,i,j); INSERT INTO t4 VALUES(0,1,2,3,4,5,6,7,8,9); INSERT INTO t4 SELECT * FROM t4; INSERT INTO t4 SELECT * FROM t4; CREATE INDEX t4x ON t4(c,d,e); SELECT DISTINCT a,b,c FROM t4 WHERE a=0 AND b=1; } {0 1 2} do_execsql_test 410 { SELECT DISTINCT a,b,c,d FROM t4 WHERE a=0 AND b=1; } {0 1 2 3} do_execsql_test 411 { SELECT DISTINCT d,a,b,c FROM t4 WHERE a=0 AND b=1; } {3 0 1 2} do_execsql_test 420 { SELECT DISTINCT a,b,c,d,e FROM t4 WHERE a=0 AND b=1; } {0 1 2 3 4} do_execsql_test 430 { SELECT DISTINCT a,b,c,d,e,f FROM t4 WHERE a=0 AND b=1; } {0 1 2 3 4 5} do_execsql_test 500 { CREATE TABLE t5(a INT, b INT); CREATE UNIQUE INDEX t5x ON t5(a+b); INSERT INTO t5(a,b) VALUES(0,0),(1,0),(1,1),(0,3); CREATE TEMP TABLE out AS SELECT DISTINCT a+b FROM t5; SELECT * FROM out ORDER BY 1; } {0 1 2 3} do_execsql_test 600 { CREATE TABLE t6a(x INTEGER PRIMARY KEY); INSERT INTO t6a VALUES(1); CREATE TABLE t6b(y INTEGER PRIMARY KEY); INSERT INTO t6b VALUES(2),(3); SELECT DISTINCT x, x FROM t6a, t6b; } {1 1} do_execsql_test 700 { CREATE TABLE t7(a, b, c); WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<200 ) INSERT INTO t7 SELECT i/100, i/50, i FROM s; } do_execsql_test 710 { SELECT DISTINCT a, b FROM t7; } { 0 0 0 1 1 2 1 3 } do_execsql_test 720 { SELECT DISTINCT a, b+1 FROM t7; } { 0 1 0 2 1 3 1 4 } do_execsql_test 730 { CREATE INDEX i7 ON t7(a, b+1); ANALYZE; SELECT DISTINCT a, b+1 FROM t7; } { 0 1 0 2 1 3 1 4 } do_execsql_test 800 { CREATE TABLE t8(a, b, c); WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<100 ) INSERT INTO t8 SELECT i/40, i/20, i/40 FROM s; } do_execsql_test 820 { SELECT DISTINCT a, b, c FROM t8; } { 0 0 0 0 1 0 1 2 1 1 3 1 2 4 2 } do_execsql_test 820 { SELECT DISTINCT a, b, c FROM t8 WHERE b=3; } {1 3 1} do_execsql_test 830 { CREATE INDEX i8 ON t8(a, c); ANALYZE; SELECT DISTINCT a, b, c FROM t8 WHERE b=3; } {1 3 1} do_execsql_test 900 { CREATE TABLE t9(v); INSERT INTO t9 VALUES ('abcd'), ('Abcd'), ('aBcd'), ('ABcd'), ('abCd'), ('AbCd'), ('aBCd'), ('ABCd'), ('abcD'), ('AbcD'), ('aBcD'), ('ABcD'), ('abCD'), ('AbCD'), ('aBCD'), ('ABCD'), ('wxyz'), ('Wxyz'), ('wXyz'), ('WXyz'), ('wxYz'), ('WxYz'), ('wXYz'), ('WXYz'), ('wxyZ'), ('WxyZ'), ('wXyZ'), ('WXyZ'), ('wxYZ'), ('WxYZ'), ('wXYZ'), ('WXYZ'); } do_execsql_test 910 { SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v; } { ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD AbCD AbCd AbCd AbcD AbcD Abcd Abcd WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD abCD abCd abCd abcD abcD abcd abcd wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz } do_execsql_test 920 { CREATE INDEX i9 ON t9(v COLLATE NOCASE, v); ANALYZE; SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v; } { ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD AbCD AbCd AbCd AbcD AbcD Abcd Abcd WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD abCD abCd abCd abcD abcD abcd abcd wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz } finish_test |