Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not apply the optimization that omits DISTINCT if all result terms are part of a UNIQUE index if the index is also a partial index. Fix for the bug reported by forum post 66954e9ece. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
c2f940b02883e165172a4ca21c7095ff |
| User & Date: | drh 2021-04-06 23:29:41 |
Context
|
2021-04-07
| ||
| 09:31 | Fix seg-fault upon appendvfs attempt to open non-existent file. (check-in: 04f6439f user: larrybr tags: trunk) | |
|
2021-04-06
| ||
| 23:29 | Do not apply the optimization that omits DISTINCT if all result terms are part of a UNIQUE index if the index is also a partial index. Fix for the bug reported by forum post 66954e9ece. (check-in: c2f940b0 user: drh tags: trunk) | |
| 22:56 | Detect OOM earlier in the geopoly extension. dbsqlfuzz 0986e7b020e6137fc6b96331f91d15753d18e42d. (check-in: 72371175 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
572 573 574 575 576 577 578 579 580 581 582 583 584 585 |
** comparison and select-list expressions must match those of the index.
**
** 3. All of those index columns for which the WHERE clause does not
** contain a "col=X" term are subject to a NOT NULL constraint.
*/
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
if( !IsUniqueIndex(pIdx) ) continue;
for(i=0; i<pIdx->nKeyCol; i++){
if( 0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx) ){
if( findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0 ) break;
if( indexColumnNotNull(pIdx, i)==0 ) break;
}
}
if( i==pIdx->nKeyCol ){
| > | 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 |
** comparison and select-list expressions must match those of the index.
**
** 3. All of those index columns for which the WHERE clause does not
** contain a "col=X" term are subject to a NOT NULL constraint.
*/
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
if( !IsUniqueIndex(pIdx) ) continue;
if( pIdx->pPartIdxWhere ) continue;
for(i=0; i<pIdx->nKeyCol; i++){
if( 0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx) ){
if( findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0 ) break;
if( indexColumnNotNull(pIdx, i)==0 ) break;
}
}
if( i==pIdx->nKeyCol ){
|
| ︙ | ︙ |
Changes to test/distinct.test.
| ︙ | ︙ | |||
288 289 290 291 292 293 294 295 296 |
WITH t2(b) AS (
SELECT DISTINCT y FROM t5 ORDER BY y
)
SELECT * FROM
t4 CROSS JOIN t3 CROSS JOIN t1
WHERE (t1.a=t3.a) AND (SELECT count(*) FROM t2 AS y WHERE t4.x!='abc')=t1.a
} {2 2 2}
finish_test
| > > > > > > > > > | 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 |
WITH t2(b) AS (
SELECT DISTINCT y FROM t5 ORDER BY y
)
SELECT * FROM
t4 CROSS JOIN t3 CROSS JOIN t1
WHERE (t1.a=t3.a) AND (SELECT count(*) FROM t2 AS y WHERE t4.x!='abc')=t1.a
} {2 2 2}
# 2021-04-06 forum post https://sqlite.org/forum/forumpost/66954e9ece
reset_db
do_execsql_test 8.0 {
CREATE TABLE person ( pid INT) ;
CREATE UNIQUE INDEX idx ON person ( pid ) WHERE pid == 1;
INSERT INTO person VALUES (1), (10), (10);
SELECT DISTINCT pid FROM person where pid = 10;
} {10}
finish_test
|