Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | More test cases to help ensure that partial indexes do not get used if their qualifing constraint is inside the ON clause of a LEFT JOIN. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | tkt-2326c258 |
| Files: | files | file ages | folders |
| SHA1: |
c6399958a17e8b7c1798a9240fb06bff |
| User & Date: | drh 2015-02-24 18:39:00 |
Context
|
2015-02-24
| ||
| 20:04 | Make sure partial indexes are not qualified incorrectly by a constraint that is inside the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead33]. check-in: 491cfe9b user: drh tags: trunk | |
| 18:39 | More test cases to help ensure that partial indexes do not get used if their qualifing constraint is inside the ON clause of a LEFT JOIN. Closed-Leaf check-in: c6399958 user: drh tags: tkt-2326c258 | |
| 16:48 | This additional fix prevents a partial index from being qualified for use if the constraint that qualifies the partial index is part of the ON clause of a LEFT JOIN. check-in: 1a1516e4 user: drh tags: tkt-2326c258 | |
Changes
Changes to test/index6.test.
263 264 265 266 267 268 269 270 271 |
UPDATE OR REPLACE t6 SET b=789;
SELECT * FROM t6;
} {123 789}
do_execsql_test index6-6.2 {
PRAGMA integrity_check;
} {ok}
finish_test
|
> > > > | > > > > > > > > > > > > > > > > > > > > > > > > |
263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 |
UPDATE OR REPLACE t6 SET b=789;
SELECT * FROM t6;
} {123 789}
do_execsql_test index6-6.2 {
PRAGMA integrity_check;
} {ok}
# Test case for ticket [2326c258d02ead33d69faa63de8f4686b9b1b9d9] on
# 2015-02-24. Any use of a partial index qualifying constraint inside
# the ON clause of a LEFT JOIN was causing incorrect results for all
# versions of SQLite 3.8.0 through 3.8.8.
#
do_execsql_test index6-7.0 {
CREATE TABLE t7a(x);
CREATE TABLE t7b(y);
INSERT INTO t7a(x) VALUES(1);
CREATE INDEX t7ax ON t7a(x) WHERE x=99;
PRAGMA automatic_index=OFF;
SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
} {1 {}}
do_execsql_test index6-7.1 {
INSERT INTO t7b(y) VALUES(2);
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {}
do_execsql_test index6-7.2 {
INSERT INTO t7a(x) VALUES(99);
SELECT * FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
} {1 {} 99 2}
do_execsql_test index6-7.3 {
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
EXPLAIN QUERY PLAN
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}
finish_test
|