/ Check-in [37e19008]
Login

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: 37e1900880b70be6802eaf43b0e568fda709a1dd6083d8be11e5a7a7d1fda41a
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3685   3685               if( pOBExpr->iColumn!=iColumn ) continue;
  3686   3686             }else{
  3687   3687               if( sqlite3ExprCompare(0,
  3688   3688                     pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){
  3689   3689                 continue;
  3690   3690               }
  3691   3691             }
  3692         -          if( iColumn>=0 ){
         3692  +          if( iColumn!=XN_ROWID ){
  3693   3693               pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
  3694   3694               if( !pColl ) pColl = db->pDfltColl;
  3695   3695               if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
  3696   3696             }
  3697   3697             pLoop->u.btree.nIdxCol = j+1;
  3698   3698             isMatch = 1;
  3699   3699             break;

Changes to test/indexexpr2.test.

    35     35   do_execsql_test 2.0 {
    36     36     CREATE INDEX i2 ON t1(a+1);
    37     37   }
    38     38   
    39     39   do_execsql_test 2.1 {
    40     40     SELECT a+1, quote(a+1) FROM t1 ORDER BY 1;
    41     41   } {2 2 3 3 4 4}
           42  +
           43  +#-------------------------------------------------------------------------
           44  +# At one point SQLite was incorrectly using indexes on expressions to
           45  +# optimize ORDER BY and GROUP BY clauses even when the collation
           46  +# sequences of the query and index did not match (ticket [e20dd54ab0e4]).
           47  +# The following tests - 3.* - attempt to verify that this has been fixed.
           48  +#
           49  +
           50  +reset_db
           51  +do_execsql_test 3.1.0 {
           52  +  CREATE TABLE t1(a, b);
           53  +  CREATE INDEX i1 ON t1(a, b);
           54  +} {}
           55  +
           56  +do_eqp_test 3.1.1 {
           57  +  SELECT b FROM t1 WHERE b IS NOT NULL AND a IS NULL 
           58  +  GROUP BY b COLLATE nocase
           59  +  ORDER BY b COLLATE nocase;
           60  +} {
           61  +  0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=? AND b>?)}
           62  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           63  +}
           64  +
           65  +do_execsql_test 3.2.0 {
           66  +  CREATE TABLE t2(x);
           67  +
           68  +  INSERT INTO t2 VALUES('.ABC');
           69  +  INSERT INTO t2 VALUES('.abcd');
           70  +  INSERT INTO t2 VALUES('.defg');
           71  +  INSERT INTO t2 VALUES('.DEF');
           72  +} {}
           73  +
           74  +do_execsql_test 3.2.1 {
           75  +  SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
           76  +} {
           77  +  .ABC .abcd .DEF .defg
           78  +}
           79  +
           80  +do_execsql_test 3.2.2 {
           81  +  CREATE INDEX i2 ON t2( substr(x, 2) );
           82  +  SELECT x FROM t2 ORDER BY substr(x, 2) COLLATE nocase;
           83  +} {
           84  +  .ABC .abcd .DEF .defg
           85  +}
           86  +
           87  +do_execsql_test 3.3.0 {
           88  +  CREATE TABLE t3(x);
           89  +}
           90  +
           91  +do_eqp_test 3.3.1 {
           92  +  SELECT json_extract(x, '$.b') FROM t2 
           93  +  WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 
           94  +  GROUP BY json_extract(x, '$.b') COLLATE nocase
           95  +  ORDER BY json_extract(x, '$.b') COLLATE nocase;
           96  +} {
           97  +  0 0 0 {SCAN TABLE t2} 
           98  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
           99  +}
          100  +
          101  +do_execsql_test 3.3.2 {
          102  +  CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b'));
          103  +} {}
          104  +
          105  +do_eqp_test 3.3.3 {
          106  +  SELECT json_extract(x, '$.b') FROM t3 
          107  +  WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 
          108  +  GROUP BY json_extract(x, '$.b') COLLATE nocase
          109  +  ORDER BY json_extract(x, '$.b') COLLATE nocase;
          110  +} {
          111  +  0 0 0 {SEARCH TABLE t3 USING INDEX i3 (<expr>=?)} 
          112  +  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
          113  +}
          114  +
          115  +do_execsql_test 3.4.0 {
          116  +  CREATE TABLE t4(a, b);
          117  +  INSERT INTO t4 VALUES('.ABC', 1);
          118  +  INSERT INTO t4 VALUES('.abc', 2);
          119  +  INSERT INTO t4 VALUES('.ABC', 3);
          120  +  INSERT INTO t4 VALUES('.abc', 4);
          121  +}
          122  +
          123  +do_execsql_test 3.4.1 {
          124  +  SELECT * FROM t4 
          125  +  WHERE substr(a, 2) = 'abc' COLLATE NOCASE
          126  +  ORDER BY substr(a, 2), b;
          127  +} {
          128  +  .ABC 1   .ABC 3   .abc 2   .abc 4
          129  +}
          130  +
          131  +do_execsql_test 3.4.2 {
          132  +  CREATE INDEX i4 ON t4( substr(a, 2) COLLATE NOCASE, b );
          133  +  SELECT * FROM t4 
          134  +  WHERE substr(a, 2) = 'abc' COLLATE NOCASE
          135  +  ORDER BY substr(a, 2), b;
          136  +} {
          137  +  .ABC 1   .ABC 3   .abc 2   .abc 4
          138  +}
    42    139   
    43    140   finish_test