Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add a test case demonstrating the collation problem with constant propagation. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | propagate-const-opt |
| Files: | files | file ages | folders |
| SHA3-256: | 50add839fd95665bd67a6ae5de8346fd |
| User & Date: | drh 2018-07-26 23:54:19 |
Context
|
2018-07-27
| ||
| 16:57 | Constant propagation is now restricted to just the WHERE clause. The mechanism is changed to take affinity and collation into account. This seems to give correct answers. But the search for constant propagation costs 4 million cycles in the speed test. check-in: 82c67efb user: drh tags: propagate-const-opt | |
|
2018-07-26
| ||
| 23:54 | Add a test case demonstrating the collation problem with constant propagation. check-in: 50add839 user: drh tags: propagate-const-opt | |
| 23:47 | Generalize the constant propagation optimization so that it applies on every WHERE close, not just those that contain a subquery. This then demonstrates that the current implementation is inadequate since it does not take into account collating sequences. check-in: 57eb2abd user: drh tags: propagate-const-opt | |
Changes
Changes to test/whereL.test.
46 47 48 49 50 51 52 53 54 |
ORDER BY t1.a;
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
|--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
`--SCAN TABLE t3
}
finish_test
|
> > > > > > > > > > > > > > > > |
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 |
ORDER BY t1.a;
} {
QUERY PLAN
|--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
|--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
`--SCAN TABLE t3
}
# Constant propagation in the face of collating sequences:
#
do_execsql_test 200 {
CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
CREATE INDEX c3x ON c3(x);
INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');
SELECT * FROM c3 WHERE x=y AND y=z AND z='abc';
} {ABC ABC abc}
# If the constants are blindly propagated, as shown in the following
# query, the wrong answer results:
#
do_execsql_test 201 {
SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
} {}
finish_test
|