Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Case should not be significant when comparing function names. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-expr |
| Files: | files | file ages | folders |
| SHA1: |
e2f1caf117b0a9632d52246717ab2028 |
| User & Date: | drh 2015-08-31 18:13:01 |
Context
|
2015-08-31
| ||
| 19:38 | Always assume that indexed expressions can generate a NULL. Get indexed expressions working for the case of two or more expressions in the same index. (check-in: cc60321a user: drh tags: index-expr) | |
| 18:13 | Case should not be significant when comparing function names. (check-in: e2f1caf1 user: drh tags: index-expr) | |
| 17:34 | Make the distinction between truly deterministic functions and date/time functions which only return the same answer for a single query. Only truly deterministic functions are allowed in indexes. Add new expression index test cases. (check-in: c77554b5 user: drh tags: index-expr) | |
Changes
Changes to src/expr.c.
3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 |
}
if( pB->op==TK_COLLATE && sqlite3ExprCompare(pA, pB->pLeft, iTab)<2 ){
return 1;
}
return 2;
}
if( pA->op!=TK_COLUMN && ALWAYS(pA->op!=TK_AGG_COLUMN) && pA->u.zToken ){
if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
return pA->op==TK_COLLATE ? 1 : 2;
}
}
if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){
if( combinedFlags & EP_xIsSelect ) return 2;
if( sqlite3ExprCompare(pA->pLeft, pB->pLeft, iTab) ) return 2;
|
> > | |
3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 |
}
if( pB->op==TK_COLLATE && sqlite3ExprCompare(pA, pB->pLeft, iTab)<2 ){
return 1;
}
return 2;
}
if( pA->op!=TK_COLUMN && ALWAYS(pA->op!=TK_AGG_COLUMN) && pA->u.zToken ){
if( pA->op==TK_FUNCTION ){
if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
}else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
return pA->op==TK_COLLATE ? 1 : 2;
}
}
if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){
if( combinedFlags & EP_xIsSelect ) return 2;
if( sqlite3ExprCompare(pA->pLeft, pB->pLeft, iTab) ) return 2;
|
Changes to test/indexexpr1.test.
14 15 16 17 18 19 20 21 22 23 24 25 26 27 .. 69 70 71 72 73 74 75 76 77 78 79 80 81 82 ... 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
set testdir [file dirname $argv0]
source $testdir/tester.tcl
do_execsql_test indexexpr1-100 {
CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c)
VALUES('In_the_beginning_was_the_Word',1,1),
('and_the_Word_was_with_God',1,2),
('and_the_Word_was_God',1,3),
('The_same_was_in_the_beginning_with_God',2,1),
('All_things_were_made_by_him',3,1),
('and_without_him_was_not_any_thing_made_that_was_made',3,2);
CREATE INDEX t1a1 ON t1(substr(a,1,12));
................................................................................
ORDER BY +rowid;
} {2 3 5}
do_execsql_test indexexpr1-150eqp {
EXPLAIN QUERY PLAN
SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
ORDER BY +rowid;
} {/USING INDEX t1abx/}
do_execsql_test indexexpr1-200 {
DROP TABLE t1;
CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID;
INSERT INTO t1(id,a,b,c)
VALUES(1,'In_the_beginning_was_the_Word',1,1),
(2,'and_the_Word_was_with_God',1,2),
................................................................................
ORDER BY +id;
} {2 3 5}
do_execsql_test indexexpr1-250eqp {
EXPLAIN QUERY PLAN
SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
ORDER BY +id;
} {/USING INDEX t1abx/}
do_catchsql_test indexexpr1-300 {
CREATE TABLE t2(a,b,c);
CREATE INDEX t2x1 ON t2(a,b+random());
} {1 {non-deterministic functions prohibited in index expressions}}
do_catchsql_test indexexpr1-301 {
CREATE INDEX t2x1 ON t2(a+julianday('now'));
} {1 {non-deterministic functions prohibited in index expressions}}
do_catchsql_test indexexpr1-310 {
CREATE INDEX t2x2 ON t2(a,b+(SELECT 15));
} {1 {subqueries prohibited in index expressions}}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 .. 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 ... 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 184 185 186 187 188 189 |
set testdir [file dirname $argv0]
source $testdir/tester.tcl
do_execsql_test indexexpr1-100 {
CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c)
/* 123456789 123456789 123456789 123456789 123456789 123456789 */
VALUES('In_the_beginning_was_the_Word',1,1),
('and_the_Word_was_with_God',1,2),
('and_the_Word_was_God',1,3),
('The_same_was_in_the_beginning_with_God',2,1),
('All_things_were_made_by_him',3,1),
('and_without_him_was_not_any_thing_made_that_was_made',3,2);
CREATE INDEX t1a1 ON t1(substr(a,1,12));
................................................................................
ORDER BY +rowid;
} {2 3 5}
do_execsql_test indexexpr1-150eqp {
EXPLAIN QUERY PLAN
SELECT rowid FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
ORDER BY +rowid;
} {/USING INDEX t1abx/}
do_execsql_test indexexpr1-160 {
ALTER TABLE t1 ADD COLUMN d;
UPDATE t1 SET d=length(a);
CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29;
SELECT rowid, b, c FROM t1
WHERE substr(a,27,3)=='ord' AND d>=29;
} {1 1 1}
do_execsql_test indexexpr1-160eqp {
EXPLAIN QUERY PLAN
SELECT rowid, b, c FROM t1
WHERE substr(a,27,3)=='ord' AND d>=29;
} {/USING INDEX t1a2/}
do_execsql_test indexexpr1-200 {
DROP TABLE t1;
CREATE TABLE t1(id ANY PRIMARY KEY, a,b,c) WITHOUT ROWID;
INSERT INTO t1(id,a,b,c)
VALUES(1,'In_the_beginning_was_the_Word',1,1),
(2,'and_the_Word_was_with_God',1,2),
................................................................................
ORDER BY +id;
} {2 3 5}
do_execsql_test indexexpr1-250eqp {
EXPLAIN QUERY PLAN
SELECT id FROM t1 WHERE substr(a,b,3) IN ('and','l_t','xyz')
ORDER BY +id;
} {/USING INDEX t1abx/}
do_execsql_test indexexpr1-260 {
ALTER TABLE t1 ADD COLUMN d;
UPDATE t1 SET d=length(a);
CREATE INDEX t1a2 ON t1(SUBSTR(a, 27, 3)) WHERE d>=29;
SELECT id, b, c FROM t1
WHERE substr(a,27,3)=='ord' AND d>=29;
} {1 1 1}
do_execsql_test indexexpr1-260eqp {
EXPLAIN QUERY PLAN
SELECT id, b, c FROM t1
WHERE substr(a,27,3)=='ord' AND d>=29;
} {/USING INDEX t1a2/}
do_catchsql_test indexexpr1-300 {
CREATE TABLE t2(a,b,c);
CREATE INDEX t2x1 ON t2(a,b+random());
} {1 {non-deterministic functions prohibited in index expressions}}
do_catchsql_test indexexpr1-301 {
CREATE INDEX t2x1 ON t2(a+julianday('now'));
} {1 {non-deterministic functions prohibited in index expressions}}
do_catchsql_test indexexpr1-310 {
CREATE INDEX t2x2 ON t2(a,b+(SELECT 15));
} {1 {subqueries prohibited in index expressions}}
do_catchsql_test indexexpr1-320 {
CREATE TABLE e1(x,y,UNIQUE(y,substr(x,1,5)));
} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
do_catchsql_test indexexpr1-330 {
CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5)));
} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
do_catchsql_test indexexpr1-331 {
CREATE TABLE e1(x,y,PRIMARY KEY(y,substr(x,1,5))) WITHOUT ROWID;
} {1 {expressions prohibited in PRIMARY KEY and UNIQUE constraints}}
do_catchsql_test indexexpr1-340 {
CREATE TABLE e1(x,y,FOREIGN KEY(substr(y,1,5)) REFERENCES t1);
} {1 {near "(": syntax error}}
finish_test
|