Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Only use indexes on expressions to optimize ORDER BY and GROUP BY if the collation sequence matches. Possible fix for [e20dd54a]. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
37e1900880b70be6802eaf43b0e568fd |
User & Date: | dan 2017-08-18 08:29:37 |
Context
2017-08-18
| ||
14:34 | Combine the OP_CreateTable and OP_CreateIndex opcodes of the bytecode engine into a single OP_CreateBtree opcode. This simplifies the implementation and makes the bytecode programs clearer. (check-in: eb1202b5 user: drh tags: trunk) | |
08:29 | Only use indexes on expressions to optimize ORDER BY and GROUP BY if the collation sequence matches. Possible fix for [e20dd54a]. (check-in: 37e19008 user: dan tags: trunk) | |
2017-08-17
| ||
20:53 | Use the __builtin_clzll() function of gcc to improve the performance and reduce the size of the sqlite3LogEst() routine. (check-in: a42a438c user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3685 3686 3687 3688 3689 3690 3691 | if( pOBExpr->iColumn!=iColumn ) continue; }else{ if( sqlite3ExprCompare(0, pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){ continue; } } | | | 3685 3686 3687 3688 3689 3690 3691 3692 3693 3694 3695 3696 3697 3698 3699 | if( pOBExpr->iColumn!=iColumn ) continue; }else{ if( sqlite3ExprCompare(0, pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){ continue; } } if( iColumn!=XN_ROWID ){ 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; |
︙ | ︙ |
Changes to test/indexexpr2.test.
︙ | ︙ | |||
35 36 37 38 39 40 41 42 43 | do_execsql_test 2.0 { CREATE INDEX i2 ON t1(a+1); } do_execsql_test 2.1 { SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; } {2 2 3 3 4 4} finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | do_execsql_test 2.0 { CREATE INDEX i2 ON t1(a+1); } do_execsql_test 2.1 { SELECT a+1, quote(a+1) FROM t1 ORDER BY 1; } {2 2 3 3 4 4} #------------------------------------------------------------------------- # At one point SQLite was incorrectly using indexes on expressions to # optimize ORDER BY and GROUP BY clauses even when the collation # sequences of the query and index did not match (ticket [e20dd54ab0e4]). # The following tests - 3.* - attempt to verify that this has been fixed. # reset_db do_execsql_test 3.1.0 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); } {} do_eqp_test 3.1.1 { SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL GROUP BY b COLLATE nocase ORDER BY b COLLATE nocase; } { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=? AND b>?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } do_execsql_test 3.2.0 { CREATE TABLE t2(x); INSERT INTO t2 VALUES('.ABC'); INSERT INTO t2 VALUES('.abcd'); INSERT INTO t2 VALUES('.defg'); INSERT INTO t2 VALUES('.DEF'); } {} do_execsql_test 3.2.1 { SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.2.2 { CREATE INDEX i2 ON t2( substr(x, 2) ); SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase; } { .ABC .abcd .DEF .defg } do_execsql_test 3.3.0 { CREATE TABLE t3(x); } 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; } { 0 0 0 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } do_execsql_test 3.3.2 { CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); } {} do_eqp_test 3.3.3 { SELECT json_extract(x, '$.b') FROM t3 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; } { 0 0 0 {SEARCH TABLE t3 USING INDEX i3 (<expr>=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } do_execsql_test 3.4.0 { CREATE TABLE t4(a, b); INSERT INTO t4 VALUES('.ABC', 1); INSERT INTO t4 VALUES('.abc', 2); INSERT INTO t4 VALUES('.ABC', 3); INSERT INTO t4 VALUES('.abc', 4); } do_execsql_test 3.4.1 { SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } do_execsql_test 3.4.2 { CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b ); SELECT * FROM t4 WHERE substr(a, 2) = 'abc' COLLATE NOCASE ORDER BY substr(a, 2), b; } { .ABC 1 .ABC 3 .abc 2 .abc 4 } finish_test |