aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ext/fts5/fts5_main.c9
-rw-r--r--ext/fts5/test/fts5conflict.test40
-rw-r--r--ext/rtree/geopoly.c8
-rw-r--r--ext/rtree/rtree.c292
-rw-r--r--ext/rtree/rtree8.test2
-rw-r--r--ext/rtree/rtreeA.test4
-rw-r--r--ext/rtree/rtreedoc.test20
-rw-r--r--ext/rtree/rtreefuzz001.test2
-rw-r--r--manifest37
-rw-r--r--manifest.uuid2
-rw-r--r--src/where.c21
-rw-r--r--src/whereInt.h2
-rw-r--r--test/subquery.test43
-rw-r--r--test/with3.test4
14 files changed, 207 insertions, 279 deletions
diff --git a/ext/fts5/fts5_main.c b/ext/fts5/fts5_main.c
index 0af997f9e..6a8ed37b5 100644
--- a/ext/fts5/fts5_main.c
+++ b/ext/fts5/fts5_main.c
@@ -405,6 +405,10 @@ static int fts5InitVtab(
pConfig->pzErrmsg = 0;
}
+ if( rc==SQLITE_OK && pConfig->eContent==FTS5_CONTENT_NORMAL ){
+ rc = sqlite3_vtab_config(db, SQLITE_VTAB_CONSTRAINT_SUPPORT, (int)1);
+ }
+
if( rc!=SQLITE_OK ){
fts5FreeVtab(pTab);
pTab = 0;
@@ -1689,7 +1693,7 @@ static int fts5UpdateMethod(
assert( nArg!=1 || eType0==SQLITE_INTEGER );
/* Filter out attempts to run UPDATE or DELETE on contentless tables.
- ** This is not suported. Except - DELETE is supported if the CREATE
+ ** This is not suported. Except - they are both supported if the CREATE
** VIRTUAL TABLE statement contained "contentless_delete=1". */
if( eType0==SQLITE_INTEGER
&& pConfig->eContent==FTS5_CONTENT_NONE
@@ -1718,7 +1722,8 @@ static int fts5UpdateMethod(
}
else if( eType0!=SQLITE_INTEGER ){
- /* If this is a REPLACE, first remove the current entry (if any) */
+ /* An INSERT statement. If the conflict-mode is REPLACE, first remove
+ ** the current entry (if any). */
if( eConflict==SQLITE_REPLACE && eType1==SQLITE_INTEGER ){
i64 iNew = sqlite3_value_int64(apVal[1]); /* Rowid to delete */
rc = sqlite3Fts5StorageDelete(pTab->pStorage, iNew, 0);
diff --git a/ext/fts5/test/fts5conflict.test b/ext/fts5/test/fts5conflict.test
index 644db53a1..b5bf0a116 100644
--- a/ext/fts5/test/fts5conflict.test
+++ b/ext/fts5/test/fts5conflict.test
@@ -65,4 +65,44 @@ do_execsql_test 2.1 {
INSERT INTO fts_idx(fts_idx) VALUES('integrity-check');
}
+#-------------------------------------------------------------------------
+# Tests for OR IGNORE conflict handling.
+#
+reset_db
+foreach_detail_mode $::testprefix {
+
+ do_execsql_test 3.0 {
+ CREATE VIRTUAL TABLE t1 USING fts5(xyz, detail=%DETAIL%);
+
+ BEGIN;
+ INSERT INTO t1(rowid, xyz) VALUES(13, 'thirteen documents');
+ INSERT INTO t1(rowid, xyz) VALUES(14, 'fourteen documents');
+ INSERT INTO t1(rowid, xyz) VALUES(15, 'fifteen documents');
+ COMMIT;
+ }
+
+ set db_cksum [cksum]
+ foreach {tn sql} {
+ 1 {
+ INSERT OR IGNORE INTO t1(rowid, xyz) VALUES(14, 'new text');
+ }
+ 2 {
+ UPDATE OR IGNORE t1 SET rowid=13 WHERE rowid=15;
+ }
+ 3 {
+ INSERT OR IGNORE INTO t1(rowid, xyz)
+ SELECT 13, 'some text'
+ UNION ALL
+ SELECT 14, 'some text'
+ UNION ALL
+ SELECT 15, 'some text'
+ }
+ } {
+ do_execsql_test 3.1.$tn.1 $sql
+ do_test 3.1.$tn.2 { cksum } $db_cksum
+ }
+
+}
+
+
finish_test
diff --git a/ext/rtree/geopoly.c b/ext/rtree/geopoly.c
index 640cb86b2..a0194680c 100644
--- a/ext/rtree/geopoly.c
+++ b/ext/rtree/geopoly.c
@@ -1256,20 +1256,23 @@ static int geopolyInit(
/* Allocate the sqlite3_vtab structure */
nDb = strlen(argv[1]);
nName = strlen(argv[2]);
- pRtree = (Rtree *)sqlite3_malloc64(sizeof(Rtree)+nDb+nName+2);
+ pRtree = (Rtree *)sqlite3_malloc64(sizeof(Rtree)+nDb+nName*2+8);
if( !pRtree ){
return SQLITE_NOMEM;
}
- memset(pRtree, 0, sizeof(Rtree)+nDb+nName+2);
+ memset(pRtree, 0, sizeof(Rtree)+nDb+nName*2+8);
pRtree->nBusy = 1;
pRtree->base.pModule = &rtreeModule;
pRtree->zDb = (char *)&pRtree[1];
pRtree->zName = &pRtree->zDb[nDb+1];
+ pRtree->zNodeName = &pRtree->zName[nName+1];
pRtree->eCoordType = RTREE_COORD_REAL32;
pRtree->nDim = 2;
pRtree->nDim2 = 4;
memcpy(pRtree->zDb, argv[1], nDb);
memcpy(pRtree->zName, argv[2], nName);
+ memcpy(pRtree->zNodeName, argv[2], nName);
+ memcpy(&pRtree->zNodeName[nName], "_node", 6);
/* Create/Connect to the underlying relational database schema. If
@@ -1683,7 +1686,6 @@ static int geopolyUpdate(
}
if( rc==SQLITE_OK ){
int rc2;
- pRtree->iReinsertHeight = -1;
rc = rtreeInsertCell(pRtree, pLeaf, &cell, 0);
rc2 = nodeRelease(pRtree, pLeaf);
if( rc==SQLITE_OK ){
diff --git a/ext/rtree/rtree.c b/ext/rtree/rtree.c
index c665951f3..8f01be37f 100644
--- a/ext/rtree/rtree.c
+++ b/ext/rtree/rtree.c
@@ -166,6 +166,7 @@ struct Rtree {
int iDepth; /* Current depth of the r-tree structure */
char *zDb; /* Name of database containing r-tree table */
char *zName; /* Name of r-tree table */
+ char *zNodeName; /* Name of the %_node table */
u32 nBusy; /* Current number of users of this structure */
i64 nRowEst; /* Estimated number of rows in this table */
u32 nCursor; /* Number of open cursors */
@@ -178,7 +179,6 @@ struct Rtree {
** headed by the node (leaf nodes have RtreeNode.iNode==0).
*/
RtreeNode *pDeleted;
- int iReinsertHeight; /* Height of sub-trees Reinsert() has run on */
/* Blob I/O on xxx_node */
sqlite3_blob *pNodeBlob;
@@ -200,7 +200,7 @@ struct Rtree {
/* Statement for writing to the "aux:" fields, if there are any */
sqlite3_stmt *pWriteAux;
- RtreeNode *aHash[HASHSIZE]; /* Hash table of in-memory nodes. */
+ RtreeNode *aHash[HASHSIZE]; /* Hash table of in-memory nodes. */
};
/* Possible values for Rtree.eCoordType: */
@@ -737,11 +737,9 @@ static int nodeAcquire(
}
}
if( pRtree->pNodeBlob==0 ){
- char *zTab = sqlite3_mprintf("%s_node", pRtree->zName);
- if( zTab==0 ) return SQLITE_NOMEM;
- rc = sqlite3_blob_open(pRtree->db, pRtree->zDb, zTab, "data", iNode, 0,
+ rc = sqlite3_blob_open(pRtree->db, pRtree->zDb, pRtree->zNodeName,
+ "data", iNode, 0,
&pRtree->pNodeBlob);
- sqlite3_free(zTab);
}
if( rc ){
nodeBlobReset(pRtree);
@@ -2082,8 +2080,12 @@ static int rtreeBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo){
pIdxInfo->idxNum = 2;
pIdxInfo->needToFreeIdxStr = 1;
- if( iIdx>0 && 0==(pIdxInfo->idxStr = sqlite3_mprintf("%s", zIdxStr)) ){
- return SQLITE_NOMEM;
+ if( iIdx>0 ){
+ pIdxInfo->idxStr = sqlite3_malloc( iIdx+1 );
+ if( pIdxInfo->idxStr==0 ){
+ return SQLITE_NOMEM;
+ }
+ memcpy(pIdxInfo->idxStr, zIdxStr, iIdx+1);
}
nRow = pRtree->nRowEst >> (iIdx/2);
@@ -2162,31 +2164,22 @@ static void cellUnion(Rtree *pRtree, RtreeCell *p1, RtreeCell *p2){
*/
static int cellContains(Rtree *pRtree, RtreeCell *p1, RtreeCell *p2){
int ii;
- int isInt = (pRtree->eCoordType==RTREE_COORD_INT32);
- for(ii=0; ii<pRtree->nDim2; ii+=2){
- RtreeCoord *a1 = &p1->aCoord[ii];
- RtreeCoord *a2 = &p2->aCoord[ii];
- if( (!isInt && (a2[0].f<a1[0].f || a2[1].f>a1[1].f))
- || ( isInt && (a2[0].i<a1[0].i || a2[1].i>a1[1].i))
- ){
- return 0;
+ if( pRtree->eCoordType==RTREE_COORD_INT32 ){
+ for(ii=0; ii<pRtree->nDim2; ii+=2){
+ RtreeCoord *a1 = &p1->aCoord[ii];
+ RtreeCoord *a2 = &p2->aCoord[ii];
+ if( a2[0].i<a1[0].i || a2[1].i>a1[1].i ) return 0;
+ }
+ }else{
+ for(ii=0; ii<pRtree->nDim2; ii+=2){
+ RtreeCoord *a1 = &p1->aCoord[ii];
+ RtreeCoord *a2 = &p2->aCoord[ii];
+ if( a2[0].f<a1[0].f || a2[1].f>a1[1].f ) return 0;
}
}
return 1;
}
-/*
-** Return the amount cell p would grow by if it were unioned with pCell.
-*/
-static RtreeDValue cellGrowth(Rtree *pRtree, RtreeCell *p, RtreeCell *pCell){
- RtreeDValue area;
- RtreeCell cell;
- memcpy(&cell, p, sizeof(RtreeCell));
- area = cellArea(pRtree, &cell);
- cellUnion(pRtree, &cell, pCell);
- return (cellArea(pRtree, &cell)-area);
-}
-
static RtreeDValue cellOverlap(
Rtree *pRtree,
RtreeCell *p,
@@ -2233,38 +2226,52 @@ static int ChooseLeaf(
for(ii=0; rc==SQLITE_OK && ii<(pRtree->iDepth-iHeight); ii++){
int iCell;
sqlite3_int64 iBest = 0;
-
+ int bFound = 0;
RtreeDValue fMinGrowth = RTREE_ZERO;
RtreeDValue fMinArea = RTREE_ZERO;
-
int nCell = NCELL(pNode);
- RtreeCell cell;
RtreeNode *pChild = 0;
- RtreeCell *aCell = 0;
-
- /* Select the child node which will be enlarged the least if pCell
- ** is inserted into it. Resolve ties by choosing the entry with
- ** the smallest area.
+ /* First check to see if there is are any cells in pNode that completely
+ ** contains pCell. If two or more cells in pNode completely contain pCell
+ ** then pick the smallest.
*/
for(iCell=0; iCell<nCell; iCell++){
- int bBest = 0;
- RtreeDValue growth;
- RtreeDValue area;
+ RtreeCell cell;
nodeGetCell(pRtree, pNode, iCell, &cell);
- growth = cellGrowth(pRtree, &cell, pCell);
- area = cellArea(pRtree, &cell);
- if( iCell==0||growth<fMinGrowth||(growth==fMinGrowth && area<fMinArea) ){
- bBest = 1;
+ if( cellContains(pRtree, &cell, pCell) ){
+ RtreeDValue area = cellArea(pRtree, &cell);
+ if( bFound==0 || area<fMinArea ){
+ iBest = cell.iRowid;
+ fMinArea = area;
+ bFound = 1;
+ }
}
- if( bBest ){
- fMinGrowth = growth;
- fMinArea = area;
- iBest = cell.iRowid;
+ }
+ if( !bFound ){
+ /* No cells of pNode will completely contain pCell. So pick the
+ ** cell of pNode that grows by the least amount when pCell is added.
+ ** Break ties by selecting the smaller cell.
+ */
+ for(iCell=0; iCell<nCell; iCell++){
+ RtreeCell cell;
+ RtreeDValue growth;
+ RtreeDValue area;
+ nodeGetCell(pRtree, pNode, iCell, &cell);
+ area = cellArea(pRtree, &cell);
+ cellUnion(pRtree, &cell, pCell);
+ growth = cellArea(pRtree, &cell)-area;
+ if( iCell==0
+ || growth<fMinGrowth
+ || (growth==fMinGrowth && area<fMinArea)
+ ){
+ fMinGrowth = growth;
+ fMinArea = area;
+ iBest = cell.iRowid;
+ }
}
}
- sqlite3_free(aCell);
rc = nodeAcquire(pRtree, iBest, pNode, &pChild);
nodeRelease(pRtree, pNode);
pNode = pChild;
@@ -2337,77 +2344,6 @@ static int parentWrite(Rtree *pRtree, sqlite3_int64 iNode, sqlite3_int64 iPar){
static int rtreeInsertCell(Rtree *, RtreeNode *, RtreeCell *, int);
-/*
-** Arguments aIdx, aDistance and aSpare all point to arrays of size
-** nIdx. The aIdx array contains the set of integers from 0 to
-** (nIdx-1) in no particular order. This function sorts the values
-** in aIdx according to the indexed values in aDistance. For
-** example, assuming the inputs:
-**
-** aIdx = { 0, 1, 2, 3 }
-** aDistance = { 5.0, 2.0, 7.0, 6.0 }
-**
-** this function sets the aIdx array to contain:
-**
-** aIdx = { 0, 1, 2, 3 }
-**
-** The aSpare array is used as temporary working space by the
-** sorting algorithm.
-*/
-static void SortByDistance(
- int *aIdx,
- int nIdx,
- RtreeDValue *aDistance,
- int *aSpare
-){
- if( nIdx>1 ){
- int iLeft = 0;
- int iRight = 0;
-
- int nLeft = nIdx/2;
- int nRight = nIdx-nLeft;
- int *aLeft = aIdx;
- int *aRight = &aIdx[nLeft];
-
- SortByDistance(aLeft, nLeft, aDistance, aSpare);
- SortByDistance(aRight, nRight, aDistance, aSpare);
-
- memcpy(aSpare, aLeft, sizeof(int)*nLeft);
- aLeft = aSpare;
-
- while( iLeft<nLeft || iRight<nRight ){
- if( iLeft==nLeft ){
- aIdx[iLeft+iRight] = aRight[iRight];
- iRight++;
- }else if( iRight==nRight ){
- aIdx[iLeft+iRight] = aLeft[iLeft];
- iLeft++;
- }else{
- RtreeDValue fLeft = aDistance[aLeft[iLeft]];
- RtreeDValue fRight = aDistance[aRight[iRight]];
- if( fLeft<fRight ){
- aIdx[iLeft+iRight] = aLeft[iLeft];
- iLeft++;
- }else{
- aIdx[iLeft+iRight] = aRight[iRight];
- iRight++;
- }
- }
- }
-
-#if 0
- /* Check that the sort worked */
- {
- int jj;
- for(jj=1; jj<nIdx; jj++){
- RtreeDValue left = aDistance[aIdx[jj-1]];
- RtreeDValue right = aDistance[aIdx[jj]];
- assert( left<=right );
- }
- }
-#endif
- }
-}
/*
** Arguments aIdx, aCell and aSpare all point to arrays of size
@@ -2891,108 +2827,7 @@ static int deleteCell(Rtree *pRtree, RtreeNode *pNode, int iCell, int iHeight){
return rc;
}
-
-static int Reinsert(
- Rtree *pRtree,
- RtreeNode *pNode,
- RtreeCell *pCell,
- int iHeight
-){
- int *aOrder;
- int *aSpare;
- RtreeCell *aCell;
- RtreeDValue *aDistance;
- int nCell;
- RtreeDValue aCenterCoord[RTREE_MAX_DIMENSIONS];
- int iDim;
- int ii;
- int rc = SQLITE_OK;
- int n;
-
- memset(aCenterCoord, 0, sizeof(RtreeDValue)*RTREE_MAX_DIMENSIONS);
-
- nCell = NCELL(pNode)+1;
- n = (nCell+1)&(~1);
-
- /* Allocate the buffers used by this operation. The allocation is
- ** relinquished before this function returns.
- */
- aCell = (RtreeCell *)sqlite3_malloc64(n * (
- sizeof(RtreeCell) + /* aCell array */
- sizeof(int) + /* aOrder array */
- sizeof(int) + /* aSpare array */
- sizeof(RtreeDValue) /* aDistance array */
- ));
- if( !aCell ){
- return SQLITE_NOMEM;
- }
- aOrder = (int *)&aCell[n];
- aSpare = (int *)&aOrder[n];
- aDistance = (RtreeDValue *)&aSpare[n];
-
- for(ii=0; ii<nCell; ii++){
- if( ii==(nCell-1) ){
- memcpy(&aCell[ii], pCell, sizeof(RtreeCell));
- }else{
- nodeGetCell(pRtree, pNode, ii, &aCell[ii]);
- }
- aOrder[ii] = ii;
- for(iDim=0; iDim<pRtree->nDim; iDim++){
- aCenterCoord[iDim] += DCOORD(aCell[ii].aCoord[iDim*2]);
- aCenterCoord[iDim] += DCOORD(aCell[ii].aCoord[iDim*2+1]);
- }
- }
- for(iDim=0; iDim<pRtree->nDim; iDim++){
- aCenterCoord[iDim] = (aCenterCoord[iDim]/(nCell*(RtreeDValue)2));
- }
-
- for(ii=0; ii<nCell; ii++){
- aDistance[ii] = RTREE_ZERO;
- for(iDim=0; iDim<pRtree->nDim; iDim++){
- RtreeDValue coord = (DCOORD(aCell[ii].aCoord[iDim*2+1]) -
- DCOORD(aCell[ii].aCoord[iDim*2]));
- aDistance[ii] += (coord-aCenterCoord[iDim])*(coord-aCenterCoord[iDim]);
- }
- }
-
- SortByDistance(aOrder, nCell, aDistance, aSpare);
- nodeZero(pRtree, pNode);
-
- for(ii=0; rc==SQLITE_OK && ii<(nCell-(RTREE_MINCELLS(pRtree)+1)); ii++){
- RtreeCell *p = &aCell[aOrder[ii]];
- nodeInsertCell(pRtree, pNode, p);
- if( p->iRowid==pCell->iRowid ){
- if( iHeight==0 ){
- rc = rowidWrite(pRtree, p->iRowid, pNode->iNode);
- }else{
- rc = parentWrite(pRtree, p->iRowid, pNode->iNode);
- }
- }
- }
- if( rc==SQLITE_OK ){
- rc = fixBoundingBox(pRtree, pNode);
- }
- for(; rc==SQLITE_OK && ii<nCell; ii++){
- /* Find a node to store this cell in. pNode->iNode currently contains
- ** the height of the sub-tree headed by the cell.
- */
- RtreeNode *pInsert;
- RtreeCell *p = &aCell[aOrder[ii]];
- rc = ChooseLeaf(pRtree, p, iHeight, &pInsert);
- if( rc==SQLITE_OK ){
- int rc2;
- rc = rtreeInsertCell(pRtree, pInsert, p, iHeight);
- rc2 = nodeRelease(pRtree, pInsert);
- if( rc==SQLITE_OK ){
- rc = rc2;
- }
- }
- }
-
- sqlite3_free(aCell);
- return rc;
-}
-
+
/*
** Insert cell pCell into node pNode. Node pNode is the head of a
** subtree iHeight high (leaf nodes have iHeight==0).
@@ -3013,12 +2848,7 @@ static int rtreeInsertCell(
}
}
if( nodeInsertCell(pRtree, pNode, pCell) ){
- if( iHeight<=pRtree->iReinsertHeight || pNode->iNode==1){
- rc = SplitNode(pRtree, pNode, pCell, iHeight);
- }else{
- pRtree->iReinsertHeight = iHeight;
- rc = Reinsert(pRtree, pNode, pCell, iHeight);
- }
+ rc = SplitNode(pRtree, pNode, pCell, iHeight);
}else{
rc = AdjustTree(pRtree, pNode, pCell);
if( ALWAYS(rc==SQLITE_OK) ){
@@ -3361,7 +3191,6 @@ static int rtreeUpdate(
}
if( rc==SQLITE_OK ){
int rc2;
- pRtree->iReinsertHeight = -1;
rc = rtreeInsertCell(pRtree, pLeaf, &cell, 0);
rc2 = nodeRelease(pRtree, pLeaf);
if( rc==SQLITE_OK ){
@@ -3790,18 +3619,21 @@ static int rtreeInit(
/* Allocate the sqlite3_vtab structure */
nDb = (int)strlen(argv[1]);
nName = (int)strlen(argv[2]);
- pRtree = (Rtree *)sqlite3_malloc64(sizeof(Rtree)+nDb+nName+2);
+ pRtree = (Rtree *)sqlite3_malloc64(sizeof(Rtree)+nDb+nName*2+8);
if( !pRtree ){
return SQLITE_NOMEM;
}
- memset(pRtree, 0, sizeof(Rtree)+nDb+nName+2);
+ memset(pRtree, 0, sizeof(Rtree)+nDb+nName*2+8);
pRtree->nBusy = 1;
pRtree->base.pModule = &rtreeModule;
pRtree->zDb = (char *)&pRtree[1];
pRtree->zName = &pRtree->zDb[nDb+1];
+ pRtree->zNodeName = &pRtree->zName[nName+1];
pRtree->eCoordType = (u8)eCoordType;
memcpy(pRtree->zDb, argv[1], nDb);
memcpy(pRtree->zName, argv[2], nName);
+ memcpy(pRtree->zNodeName, argv[2], nName);
+ memcpy(&pRtree->zNodeName[nName], "_node", 6);
/* Create/Connect to the underlying relational database schema. If
diff --git a/ext/rtree/rtree8.test b/ext/rtree/rtree8.test
index 12e75a685..51bd40416 100644
--- a/ext/rtree/rtree8.test
+++ b/ext/rtree/rtree8.test
@@ -75,7 +75,7 @@ do_test rtree8-1.2.2 { nested_select 1 } {51}
#
populate_t1 1500
do_rtree_integrity_test rtree8-1.3.0 t1
-do_execsql_test rtree8-1.3.1 { SELECT max(nodeno) FROM t1_node } {164}
+do_execsql_test rtree8-1.3.1 { SELECT max(nodeno) FROM t1_node } {183}
do_test rtree8-1.3.2 {
set rowids [execsql {SELECT min(rowid) FROM t1_rowid GROUP BY nodeno}]
set stmt_list [list]
diff --git a/ext/rtree/rtreeA.test b/ext/rtree/rtreeA.test
index 301cd4fc6..0b52070c4 100644
--- a/ext/rtree/rtreeA.test
+++ b/ext/rtree/rtreeA.test
@@ -113,7 +113,7 @@ do_execsql_test rtreeA-1.1.1 {
SELECT rtreecheck('main', 't1')
} {{Node 1 missing from database
Wrong number of entries in %_rowid table - expected 0, actual 500
-Wrong number of entries in %_parent table - expected 0, actual 23}}
+Wrong number of entries in %_parent table - expected 0, actual 25}}
do_execsql_test rtreeA-1.2.0 { DROP TABLE t1_node } {}
do_corruption_tests rtreeA-1.2 -error "database disk image is malformed" {
@@ -191,7 +191,7 @@ do_execsql_test rtreeA-3.3.3.4 {
SELECT rtreecheck('main', 't1')
} {{Rtree depth out of range (65535)
Wrong number of entries in %_rowid table - expected 0, actual 499
-Wrong number of entries in %_parent table - expected 0, actual 23}}
+Wrong number of entries in %_parent table - expected 0, actual 25}}
#-------------------------------------------------------------------------
# Set the "number of entries" field on some nodes incorrectly.
diff --git a/ext/rtree/rtreedoc.test b/ext/rtree/rtreedoc.test
index b64faa2e9..4e610db8a 100644
--- a/ext/rtree/rtreedoc.test
+++ b/ext/rtree/rtreedoc.test
@@ -601,11 +601,21 @@ do_execsql_test 2.5.2 {
SELECT A.id FROM demo_index AS A, demo_index AS B
WHERE A.maxX>=B.minX AND A.minX<=B.maxX
AND A.maxY>=B.minY AND A.minY<=B.maxY
- AND B.id=28269;
+ AND B.id=28269 ORDER BY +A.id;
} {
- 28293 28216 28322 28286 28269
- 28215 28336 28262 28291 28320
- 28313 28298 28287
+ 28215
+ 28216
+ 28262
+ 28269
+ 28286
+ 28287
+ 28291
+ 28293
+ 28298
+ 28313
+ 28320
+ 28322
+ 28336
}
# EVIDENCE-OF: R-02723-34107 Note that it is not necessary for all
@@ -1575,7 +1585,7 @@ execsql BEGIN
do_test 3.6 {
execsql { INSERT INTO rt2_parent VALUES(1000, 1000) }
execsql { SELECT rtreecheck('rt2') }
-} {{Wrong number of entries in %_parent table - expected 9, actual 10}}
+} {{Wrong number of entries in %_parent table - expected 10, actual 11}}
execsql ROLLBACK
diff --git a/ext/rtree/rtreefuzz001.test b/ext/rtree/rtreefuzz001.test
index 58fd179ab..c81c41da3 100644
--- a/ext/rtree/rtreefuzz001.test
+++ b/ext/rtree/rtreefuzz001.test
@@ -1043,7 +1043,7 @@ do_test rtreefuzz001-500 {
| end crash-2e81f5dce5cbd4.db}]
execsql { PRAGMA writable_schema = 1;}
catchsql {UPDATE t1 SET ex= ex ISNULL}
-} {1 {database disk image is malformed}}
+} {0 {}}
do_test rtreefuzz001-600 {
sqlite3 db {}
diff --git a/manifest b/manifest
index 5225d9555..b894595ed 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Add\sa\sNEVER()\sto\san\sunreachable\sbranch.
-D 2023-09-16T16:39:27.998
+C Fix\sresolution\sof\s"rowid"\sand\ssimilar\sidentifiers\sin\squeries\sthat\suse\snested\sjoins.
+D 2023-09-16T18:18:57.522
F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -95,7 +95,7 @@ F ext/fts5/fts5_config.c 054359543566cbff1ba65a188330660a5457299513ac71c53b3a07d
F ext/fts5/fts5_expr.c bd3b81ce669c4104e34ffe66570af1999a317b142c15fccb112de9fb0caa57a6
F ext/fts5/fts5_hash.c 65e7707bc8774706574346d18c20218facf87de3599b995963c3e6d6809f203d
F ext/fts5/fts5_index.c a86bcd5637625ce1037649d55974ab8da1fa8d1375cb334aae47ef376642e93b
-F ext/fts5/fts5_main.c 2249d21bb384e2af55fab48e309c6adb9d83f83a10b2ac34788da93270064368
+F ext/fts5/fts5_main.c 799ec88d2309055f6406bddb0bd6ed80148c5da5eb14594c3c5309a6e944d489
F ext/fts5/fts5_storage.c 3c9b41fce41b6410f2e8f82eb035c6a29b2560483f773e6dc98cf3cb2e4ddbb5
F ext/fts5/fts5_tcl.c b1445cbe69908c411df8084a10b2485500ac70a9c747cdc8cda175a3da59d8ae
F ext/fts5/fts5_test_mi.c 08c11ec968148d4cb4119d96d819f8c1f329812c568bac3684f5464be177d3ee
@@ -131,7 +131,7 @@ F ext/fts5/test/fts5circref.test f880dfd0d99f6fb73b88ccacb0927d18e833672fd906cc4
F ext/fts5/test/fts5colset.test 7031ce84fb4d312df5a99fc4e7b324e660ccb513c97eccdef469bfd52d3d0f8f
F ext/fts5/test/fts5columnsize.test 45459ce4dd9fd853b6044cdc9674921bff89e3d840f348ca8c1630f9edbf5482
F ext/fts5/test/fts5config.test 60094712debc59286c59aef0e6cf511c37d866802776a825ce437d26afe0817f
-F ext/fts5/test/fts5conflict.test 655925678e630d3cdf145d18725a558971806416f453ac8410ca8c04d934238d
+F ext/fts5/test/fts5conflict.test bf6030a77dbb1bedfcc42e589ed7980846c995765d77460551e448b56d741244
F ext/fts5/test/fts5connect.test 08030168fc96fc278fa81f28654fb7e90566f33aff269c073e19b3ae9126b2f4
F ext/fts5/test/fts5content.test 219a4e49386b9b197b9b7cadca97ea10ddff858ecd8b763a1cb8bb07575afc2a
F ext/fts5/test/fts5contentless.test 1cd1237894eeff11feb1ff8180044eac0b17dde22c181f7a722f2dcbfdb3377c
@@ -472,8 +472,8 @@ F ext/repair/test/checkfreelist01.test 3e8aa6aeb4007680c94a8d07b41c339aa635cc782
F ext/repair/test/checkindex01.test b530f141413b587c9eb78ff734de6bb79bc3515c335096108c12c01bddbadcec
F ext/repair/test/test.tcl 686d76d888dffd021f64260abf29a55c57b2cedfa7fc69150b42b1d6119aac3c
F ext/rtree/README 6315c0d73ebf0ec40dedb5aa0e942bc8b54e3761
-F ext/rtree/geopoly.c 971e0b5bd9adaf0811feb8c0842a310811159da10319eb0e74fdb42bf26b99ca
-F ext/rtree/rtree.c 2da7e570a4782c6e9a306d7d1cebdfc3c3a1b690725ce90fdbe09650b86db79c
+F ext/rtree/geopoly.c e969a9afaa603728a553af6b945b5459fbd3b8d112a7eda9e73a6790606c7a41
+F ext/rtree/rtree.c b3b1c96e46fc820b57851b4fbab546c5317d40d1a2d54e23c9bb50be6090b3e0
F ext/rtree/rtree.h 4a690463901cb5e6127cf05eb8e642f127012fd5003830dbc974eca5802d9412
F ext/rtree/rtree1.test 877d40b8b61b1f88cec9d4dc0ff8334f5b05299fac12a35141532e2881860e9d
F ext/rtree/rtree2.test 9d9deddbb16fd0c30c36e6b4fdc3ee3132d765567f0f9432ee71e1303d32603d
@@ -482,9 +482,9 @@ F ext/rtree/rtree4.test 304de65d484540111b896827e4261815e5dca4ce28eeecd58be648cd
F ext/rtree/rtree5.test 49c9041d713d54560b315c2c7ef7207ee287eba1b20f8266968a06f2e55d3142
F ext/rtree/rtree6.test 2f5ffc69670395c1a84fad7924e2d49e82a25460c5293fb1e54e1aa906f04945
F ext/rtree/rtree7.test c8fb2e555b128dd0f0bdb520c61380014f497f8a23c40f2e820acc9f9e4fdce5
-F ext/rtree/rtree8.test 2d99006a1386663978c9e1df167554671e4f711c419175b39f332719deb1ce0e
+F ext/rtree/rtree8.test 4da84c7f328bbdca15052fa13da6e8b8d426433347bf75fc85574c2f5a411a02
F ext/rtree/rtree9.test fd3c9384ef8aabbc127b3878764070398f136eebc551cd20484b570f2cc1956a
-F ext/rtree/rtreeA.test a7fd235d8194115fa2e14d300337931eb2e960fe8a46cdfb66add2206412ea41
+F ext/rtree/rtreeA.test 14e67fccc5b41efbad7ea99d21d11aaa66d2067da7d5b296ee86e4de64391d82
F ext/rtree/rtreeB.test 4cec297f8e5c588654bbf3c6ed0903f10612be8a2878055dd25faf8c71758bc9
F ext/rtree/rtreeC.test 2978b194d09b13e106bdb0e1c5b408b9d42eb338c1082bf43c87ef43bd626147
F ext/rtree/rtreeD.test fe46aa7f012e137bd58294409b16c0d43976c3bb92c8f710481e577c4a1100dc
@@ -498,10 +498,10 @@ F ext/rtree/rtree_util.tcl 202ca70df1f0645ef9d5a2170e62d378a28098d9407f0569e85c9
F ext/rtree/rtreecheck.test 934546ad9b563e090ee0c5cbdc69ad014189ad76e5df7320526797a9a345661f
F ext/rtree/rtreecirc.test aec664eb21ae943aeb344191407afff5d392d3ae9d12b9a112ced0d9c5de298e
F ext/rtree/rtreeconnect.test 225ad3fcb483d36cbee423a25052a6bbae762c9576ae9268332360c68c170d3d
-F ext/rtree/rtreedoc.test 27a5703cb1200f6f69051de68da546cef3dfdcf59be73afadfc50b9f9c9960d9
+F ext/rtree/rtreedoc.test d633982d61542f3bc0a0a2df0382a02cc699ac56cbda01130cde6da44a228490
F ext/rtree/rtreedoc2.test 194ebb7d561452dcdc10bf03f44e30c082c2f0c14efeb07f5e02c7daf8284d93
F ext/rtree/rtreedoc3.test 555a878c4d79c4e37fa439a1c3b02ee65d3ebaf75d9e8d96a9c55d66db3efbf8
-F ext/rtree/rtreefuzz001.test 0fc793f67897c250c5fde96cefee455a5e2fb92f4feeabde5b85ea02040790ee
+F ext/rtree/rtreefuzz001.test 44f680a23dbe00d1061dbde381d711119099846d166580c4381e402b9d62cb74
F ext/rtree/sqlite3rtree.h 03c8db3261e435fbddcfc961471795cbf12b24e03001d0015b2636b0f3881373
F ext/rtree/test_rtreedoc.c de76b3472bc74b788d079342fdede22ff598796dd3d97acffe46e09228af83a3
F ext/rtree/tkt3363.test 142ab96eded44a3615ec79fba98c7bde7d0f96de
@@ -795,8 +795,8 @@ F src/vxworks.h d2988f4e5a61a4dfe82c6524dd3d6e4f2ce3cdb9
F src/wal.c 01e051a1e713d9eabdb25df38602837cec8f4c2cae448ce2cf6accc87af903e9
F src/wal.h ba252daaa94f889f4b2c17c027e823d9be47ce39da1d3799886bbd51f0490452
F src/walker.c 7c7ea0115345851c3da4e04e2e239a29983b61fb5b038b94eede6aba462640e2
-F src/where.c 86f901cdbc80bd8fd50c89f72c16a0ae8501c5df03caaa9a99ca72676ad79b56
-F src/whereInt.h c7d19902863beadec1d04e66aca39c0bcd60b74f05f0eaa7422c7005dfc5d51a
+F src/where.c b05f3e60d576a0415948ca1e86754a3c564c0d9e89e3011e35f849cc4d818ef8
+F src/whereInt.h 4b38c5889514e3aead3f27d0ee9a26e47c3f150efc59e2a8b4e3bc8835e4d7a1
F src/wherecode.c 5d77db30a2a3dd532492ae882de114edba2fae672622056b1c7fd61f5917a8f1
F src/whereexpr.c dc5096eca5ed503999be3bdee8a90c51361289a678d396a220912e9cb73b3c00
F src/window.c b7ad9cff3ce8ae6f8cc25e18e1a258426cb6bd2999aace6f5248d781b2a74098
@@ -1602,7 +1602,7 @@ F test/stmtvtab1.test 6873dfb24f8e79cbb5b799b95c2e4349060eb7a3b811982749a84b3594
F test/strict1.test 4d2b492152b984fd7e8196d23eb88e2ccb0ef9e46ca2f96c2ce7147ceef9d168
F test/strict2.test b22c7a98b5000aef937f1990776497f0e979b1a23bc4f63e2d53b00e59b20070
F test/subjournal.test 8d4e2572c0ee9a15549f0d8e40863161295107e52f07a3e8012a2e1fdd093c49
-F test/subquery.test 3a1a5b600b8d4f504d2a2c61f33db820983dba94a0ef3e4aedca8f0165eaecb8
+F test/subquery.test 312c5d26304b0e93a56ba2cb9d4480b8a1c8217e3b2b8f8be2bfb0b2458ac3a7
F test/subquery2.test 90cf944b9de8204569cf656028391e4af1ccc8c0cc02d4ef38ee3be8de1ffb12
F test/subselect.test 0966aa8e720224dbd6a5e769a3ec2a723e332303
F test/substr.test a673e3763e247e9b5e497a6cacbaf3da2bd8ec8921c0677145c109f2e633f36b
@@ -1991,7 +1991,7 @@ F test/windowfault.test 15094c1529424e62f798bc679e3fe9dfab6e8ba2f7dfe8c923b6248c
F test/windowpushd.test d8895d08870b7226f7693665bd292eb177e62ca06799184957b3ca7dc03067df
F test/with1.test b93833890e5d2a368e78747f124503a0159aa029b98e9ed4795ebf630b2efd3d
F test/with2.test a1df41b987198383b9b70bf5e5fda390582e46398653858dbc6ceb24253b28df
-F test/with3.test fe15975c0b53c9098a757902a908e3f8d6d80ce47c5363ac600f28a79ef8c0ca
+F test/with3.test e30369ea27aa27eb1bda4c5e510c8a9f782c8afd2ab99d1a02b8a7f25a5d3e65
F test/with4.test 257be66c0c67fee1defbbac0f685c3465e2cad037f21ce65f23f86084f198205
F test/with5.test 6248213c41fab36290b5b73aa3f937309dfba337004d9d8434c3fabc8c7d4be8
F test/with6.test e097a03e5c898a8cd8f3a2d6a994ec510ea4376b5d484c2b669a41001e7758c8
@@ -2121,8 +2121,9 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 05107a0ae1456b746d3119af68d39532fad23a7eef88c09a9ef46ab7f8da4b9d
-R fc73d4482ca092115650da8908e20813
-U drh
-Z 760a28483bfb77f8bee5209678f08535
+P c362bde4f4b8489947f080154d7fddcfd6e8e21d742a483c496fb7fbe59969d2 6b6eb38979d68c06e382620c8813d6b67a3de02c4a7a029c84f924b9a2e380c6
+R 41ef20fc783874cad2321b0304c25547
+T +closed 6b6eb38979d68c06e382620c8813d6b67a3de02c4a7a029c84f924b9a2e380c6
+U dan
+Z a54b0e7aba892d4d1c6dff72b9ecc478
# Remove this line to create a well-formed Fossil manifest.
diff --git a/manifest.uuid b/manifest.uuid
index bb06f828c..605b87ee6 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-6b6eb38979d68c06e382620c8813d6b67a3de02c4a7a029c84f924b9a2e380c6 \ No newline at end of file
+37ec43d92bde13efc71fa57ff5e59c4a95b9054c298f844aefeb06d4a91ad0d4 \ No newline at end of file
diff --git a/src/where.c b/src/where.c
index 01ae180ae..3a865d3f1 100644
--- a/src/where.c
+++ b/src/where.c
@@ -3700,9 +3700,6 @@ static int whereLoopAddBtree(
#else
pNew->rRun = rSize + 16;
#endif
- if( IsView(pTab) || (pTab->tabFlags & TF_Ephemeral)!=0 ){
- pNew->wsFlags |= WHERE_VIEWSCAN;
- }
ApplyCostMultiplier(pNew->rRun, pTab->costMult);
whereLoopOutputAdjust(pWC, pNew, rSize);
rc = whereLoopInsert(pBuilder, pNew);
@@ -5121,14 +5118,6 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){
rUnsorted -= 2; /* TUNING: Slight bias in favor of no-sort plans */
}
- /* TUNING: A full-scan of a VIEW or subquery in the outer loop
- ** is not so bad. */
- if( iLoop==0 && (pWLoop->wsFlags & WHERE_VIEWSCAN)!=0 && nLoop>1 ){
- rCost += -10;
- nOut += -30;
- WHERETRACE(0x80,("VIEWSCAN cost reduction for %c\n",pWLoop->cId));
- }
-
/* Check to see if pWLoop should be added to the set of
** mxChoice best-so-far paths.
**
@@ -6143,6 +6132,16 @@ WhereInfo *sqlite3WhereBegin(
wherePathSolver(pWInfo, pWInfo->nRowOut+1);
if( db->mallocFailed ) goto whereBeginError;
}
+
+ /* TUNING: Assume that a DISTINCT clause on a subquery reduces
+ ** the output size by a factor of 8 (LogEst -30).
+ */
+ if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0 ){
+ WHERETRACE(0x0080,("nRowOut reduced from %d to %d due to DISTINCT\n",
+ pWInfo->nRowOut, pWInfo->nRowOut-30));
+ pWInfo->nRowOut -= 30;
+ }
+
}
assert( pWInfo->pTabList!=0 );
if( pWInfo->pOrderBy==0 && (db->flags & SQLITE_ReverseOrder)!=0 ){
diff --git a/src/whereInt.h b/src/whereInt.h
index 759e774e3..343aca9f3 100644
--- a/src/whereInt.h
+++ b/src/whereInt.h
@@ -633,7 +633,7 @@ void sqlite3WhereTabFuncArgs(Parse*, SrcItem*, WhereClause*);
#define WHERE_BLOOMFILTER 0x00400000 /* Consider using a Bloom-filter */
#define WHERE_SELFCULL 0x00800000 /* nOut reduced by extra WHERE terms */
#define WHERE_OMIT_OFFSET 0x01000000 /* Set offset counter to zero */
-#define WHERE_VIEWSCAN 0x02000000 /* A full-scan of a VIEW or subquery */
+ /* 0x02000000 -- available for reuse */
#define WHERE_EXPRIDX 0x04000000 /* Uses an index-on-expressions */
#endif /* !defined(SQLITE_WHEREINT_H) */
diff --git a/test/subquery.test b/test/subquery.test
index a048f9ed4..c51edba04 100644
--- a/test/subquery.test
+++ b/test/subquery.test
@@ -11,8 +11,6 @@
# This file implements regression tests for SQLite library. The
# focus of this script is testing correlated subqueries
#
-# $Id: subquery.test,v 1.17 2009/01/09 01:12:28 drh Exp $
-#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
@@ -613,4 +611,45 @@ do_execsql_test subquery-9.4 {
SELECT (SELECT DISTINCT x FROM t1 ORDER BY +x LIMIT 1 OFFSET 2) FROM t1;
} {{} {} {} {}}
+# 2023-09-15
+# Query planner performance regression reported by private email
+# on 2023-09-14, caused by VIEWSCAN optimization of check-in 609fbb94b8f01d67
+# from 2022-09-01.
+#
+reset_db
+do_execsql_test subquery-10.1 {
+ CREATE TABLE t1(aa TEXT, bb INT, cc TEXT);
+ CREATE INDEX x11 on t1(bb);
+ CREATE INDEX x12 on t1(aa);
+ CREATE TABLE t2(aa TEXT, xx INT);
+ ANALYZE sqlite_master;
+ INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x11', '156789 28');
+ INSERT INTO sqlite_stat1(tbl, idx, stat) VALUES('t1', 'x12', '156789 1');
+ ANALYZE sqlite_master;
+}
+do_eqp_test subquery-10.2 {
+ WITH v1(aa,cc,bb) AS (SELECT aa, cc, bb FROM t1 WHERE bb=12345),
+ v2(aa,mx) AS (SELECT aa, max(xx) FROM t2 GROUP BY aa)
+ SELECT * FROM v1 JOIN v2 ON v1.aa=v2.aa;
+} {
+ QUERY PLAN
+ |--CO-ROUTINE v2
+ | |--SCAN t2
+ | `--USE TEMP B-TREE FOR GROUP BY
+ |--SEARCH t1 USING INDEX x11 (bb=?)
+ `--SEARCH v2 USING AUTOMATIC COVERING INDEX (aa=?)
+}
+# ^^^^^^^^^^^^^
+# Prior to the fix the incorrect (slow) plan caused by the
+# VIEWSCAN optimization was:
+#
+# QUERY PLAN
+# |--CO-ROUTINE v2
+# | |--SCAN t2
+# | `--USE TEMP B-TREE FOR GROUP BY
+# |--SCAN v2
+# `--SEARCH t1 USING INDEX x12 (aa=?)
+#
+
+
finish_test
diff --git a/test/with3.test b/test/with3.test
index 650740dcc..9b110debf 100644
--- a/test/with3.test
+++ b/test/with3.test
@@ -132,8 +132,8 @@ do_eqp_test 3.2.2 {
| | `--SCALAR SUBQUERY xxxxxx
| | `--SCAN w2
| `--RECURSIVE STEP
- | |--SCAN c
- | `--SCAN w1
+ | |--SCAN w1
+ | `--SCAN c
|--SCAN c
|--SEARCH w2 USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH w1 USING INTEGER PRIMARY KEY (rowid=?)
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 05:54:12 +0000