/ Check-in [c21bc5a2]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:The content columns of the index-btree that implements a WITHOUT ROWID table are not ordered and so the query planner should not assume they are ordered. Fix for the issue identified by forum post 6c8960f545.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c21bc5a2353e660f2acf5ed916921a4ee416910d0b3f2deb512a05c54138d1c0
User & Date: drh 2021-05-13 13:43:40
References
2021-06-16
19:23
Add an ALWAYS on a branch in STAT4-only logic that is now always true due to check-in [c21bc5a2353e660f]. (check-in: 7bd55eee user: drh tags: trunk)
Context
2021-05-13
18:24
Modify the sqlite3_stmt_readonly() interface so that it returns false for CREATE TABLE IF NOT EXISTS statements even if the table already exists and the statement is really a read-only no-op. Likewise for DROP TABLE, CREATE INDEX, and DROP INDEX. Update the documentation for sqlite3_stmt_readonly() to reflect this new behavior. (check-in: cf8eb465 user: drh tags: trunk)
13:43
The content columns of the index-btree that implements a WITHOUT ROWID table are not ordered and so the query planner should not assume they are ordered. Fix for the issue identified by forum post 6c8960f545. (check-in: c21bc5a2 user: drh tags: trunk)
2021-05-12
22:15
Further simplification of the reverse-order scan logic of the previous check-in. (check-in: b2b0e23b user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

2509
2510
2511
2512
2513
2514
2515


2516
2517
2518
2519
2520
2521
2522
  }else{
    assert( pNew->u.btree.nBtm==0 );
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  }
  if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);

  assert( pNew->u.btree.nEq<pProbe->nColumn );



  saved_nEq = pNew->u.btree.nEq;
  saved_nBtm = pNew->u.btree.nBtm;
  saved_nTop = pNew->u.btree.nTop;
  saved_nSkip = pNew->nSkip;
  saved_nLTerm = pNew->nLTerm;
  saved_wsFlags = pNew->wsFlags;







>
>







2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
  }else{
    assert( pNew->u.btree.nBtm==0 );
    opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
  }
  if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);

  assert( pNew->u.btree.nEq<pProbe->nColumn );
  assert( pNew->u.btree.nEq<pProbe->nKeyCol
       || pProbe->idxType!=SQLITE_IDXTYPE_PRIMARYKEY );

  saved_nEq = pNew->u.btree.nEq;
  saved_nBtm = pNew->u.btree.nBtm;
  saved_nTop = pNew->u.btree.nTop;
  saved_nSkip = pNew->nSkip;
  saved_nLTerm = pNew->nLTerm;
  saved_wsFlags = pNew->wsFlags;
2786
2787
2788
2789
2790
2791
2792


2793
2794
2795
2796
2797
2798
2799
      pNew->nOut = saved_nOut;
    }else{
      pNew->nOut = nOutUnadjusted;
    }

    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<pProbe->nColumn


    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
    }
    pNew->nOut = saved_nOut;
#ifdef SQLITE_ENABLE_STAT4
    pBuilder->nRecValid = nRecValid;
#endif







>
>







2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
      pNew->nOut = saved_nOut;
    }else{
      pNew->nOut = nOutUnadjusted;
    }

    if( (pNew->wsFlags & WHERE_TOP_LIMIT)==0
     && pNew->u.btree.nEq<pProbe->nColumn
     && (pNew->u.btree.nEq<pProbe->nKeyCol ||
           pProbe->idxType!=SQLITE_IDXTYPE_PRIMARYKEY)
    ){
      whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
    }
    pNew->nOut = saved_nOut;
#ifdef SQLITE_ENABLE_STAT4
    pBuilder->nRecValid = nRecValid;
#endif

Changes to test/without_rowid1.test.

448
449
450
451
452
453
454
455













456
    c1 UNIQUE,
    PRIMARY KEY(c1, c1)
  ) WITHOUT ROWID;
  INSERT INTO t1 SELECT * FROM t0;
  PRAGMA integrity_check;
  SELECT * FROM t0, t1;
} {ok abc xyz abc xyz}
  













finish_test







|
>
>
>
>
>
>
>
>
>
>
>
>
>

448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
    c1 UNIQUE,
    PRIMARY KEY(c1, c1)
  ) WITHOUT ROWID;
  INSERT INTO t1 SELECT * FROM t0;
  PRAGMA integrity_check;
  SELECT * FROM t0, t1;
} {ok abc xyz abc xyz}

# 2021-05-13 https://sqlite.org/forum/forumpost/6c8960f545
reset_db
do_execsql_test 14.1 {
  CREATE TABLE t1(a INT PRIMARY KEY) WITHOUT ROWID;
  INSERT INTO t1(a) VALUES(10);
  ALTER TABLE t1 ADD COLUMN b INT;
  SELECT * FROM t1 WHERE a=20 OR (a=10 AND b=10);
} {}
do_execsql_test 14.2 {
  CREATE TABLE dual AS SELECT 'X' AS dummy;
  EXPLAIN QUERY PLAN SELECT * FROM dual, t1 WHERE a=10 AND b=10;
} {~/b=/}
  
finish_test