/ Check-in [c2f940b0]
Login

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

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