SQLite

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 Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

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>=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;







|







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