Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the RTREE extension so that it give correct query results even if the query uses non-numeric constraints. Ticket [a55ab6d97d01ecbc] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
f898d04cf272ef0130dcae146cb86d86 |
| User & Date: | drh 2019-12-05 00:44:47 |
References
|
2019-12-06
| ||
| 01:38 | Further enhancements to RTREE, following up to check-in [f898d04cf272ef01] so that constraints are allowed to be compared to strings that look like numbers. Ticket [aa573a787b8f1fdf] (check-in: 32772dfd user: drh tags: trunk) | |
Context
|
2019-12-05
| ||
| 13:34 | Be strict about type enforcement on rowid look-ups in the RTREE extension. Ticket [30e2c183b6b356e4] (check-in: d43e0efb user: drh tags: trunk) | |
| 00:44 | Enhance the RTREE extension so that it give correct query results even if the query uses non-numeric constraints. Ticket [a55ab6d97d01ecbc] (check-in: f898d04c user: drh tags: trunk) | |
|
2019-12-04
| ||
| 19:45 | Window functions are never constant. (check-in: 35f0b5a8 user: drh tags: trunk) | |
Changes
Changes to ext/rtree/rtree.c.
| ︙ | ︙ | |||
321 322 323 324 325 326 327 328 329 330 331 332 333 334 |
#define RTREE_LE 0x42 /* B */
#define RTREE_LT 0x43 /* C */
#define RTREE_GE 0x44 /* D */
#define RTREE_GT 0x45 /* E */
#define RTREE_MATCH 0x46 /* F: Old-style sqlite3_rtree_geometry_callback() */
#define RTREE_QUERY 0x47 /* G: New-style sqlite3_rtree_query_callback() */
/*
** An rtree structure node.
*/
struct RtreeNode {
RtreeNode *pParent; /* Parent node */
i64 iNode; /* The node number */
| > > > > > > | 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 |
#define RTREE_LE 0x42 /* B */
#define RTREE_LT 0x43 /* C */
#define RTREE_GE 0x44 /* D */
#define RTREE_GT 0x45 /* E */
#define RTREE_MATCH 0x46 /* F: Old-style sqlite3_rtree_geometry_callback() */
#define RTREE_QUERY 0x47 /* G: New-style sqlite3_rtree_query_callback() */
/* Special operators available only on cursors. Needs to be consecutive
** with the normal values above, but must be less than RTREE_MATCH. These
** are used in the cursor for contraints such as x=NULL (RTREE_FALSE) or
** x<'xyz' (RTREE_TRUE) */
#define RTREE_TRUE 0x3f /* ? */
#define RTREE_FALSE 0x40 /* @ */
/*
** An rtree structure node.
*/
struct RtreeNode {
RtreeNode *pParent; /* Parent node */
i64 iNode; /* The node number */
|
| ︙ | ︙ | |||
1240 1241 1242 1243 1244 1245 1246 | /* p->iCoord might point to either a lower or upper bound coordinate ** in a coordinate pair. But make pCellData point to the lower bound. */ pCellData += 8 + 4*(p->iCoord&0xfe); assert(p->op==RTREE_LE || p->op==RTREE_LT || p->op==RTREE_GE | | > > > | 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 |
/* p->iCoord might point to either a lower or upper bound coordinate
** in a coordinate pair. But make pCellData point to the lower bound.
*/
pCellData += 8 + 4*(p->iCoord&0xfe);
assert(p->op==RTREE_LE || p->op==RTREE_LT || p->op==RTREE_GE
|| p->op==RTREE_GT || p->op==RTREE_EQ || p->op==RTREE_TRUE
|| p->op==RTREE_FALSE );
assert( ((((char*)pCellData) - (char*)0)&3)==0 ); /* 4-byte aligned */
switch( p->op ){
case RTREE_TRUE: return; /* Always satisfied */
case RTREE_FALSE: break; /* Never satisfied */
case RTREE_LE:
case RTREE_LT:
case RTREE_EQ:
RTREE_DECODE_COORD(eInt, pCellData, val);
/* val now holds the lower bound of the coordinate pair */
if( p->u.rValue>=val ) return;
if( p->op!=RTREE_EQ ) break; /* RTREE_LE and RTREE_LT end here */
|
| ︙ | ︙ | |||
1280 1281 1282 1283 1284 1285 1286 |
int eInt, /* True if RTree holds integer coordinates */
u8 *pCellData, /* Raw cell content as appears on disk */
int *peWithin /* Adjust downward, as appropriate */
){
RtreeDValue xN; /* Coordinate value converted to a double */
assert(p->op==RTREE_LE || p->op==RTREE_LT || p->op==RTREE_GE
| | > > > | | | | | | 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 |
int eInt, /* True if RTree holds integer coordinates */
u8 *pCellData, /* Raw cell content as appears on disk */
int *peWithin /* Adjust downward, as appropriate */
){
RtreeDValue xN; /* Coordinate value converted to a double */
assert(p->op==RTREE_LE || p->op==RTREE_LT || p->op==RTREE_GE
|| p->op==RTREE_GT || p->op==RTREE_EQ || p->op==RTREE_TRUE
|| p->op==RTREE_FALSE );
pCellData += 8 + p->iCoord*4;
assert( ((((char*)pCellData) - (char*)0)&3)==0 ); /* 4-byte aligned */
RTREE_DECODE_COORD(eInt, pCellData, xN);
switch( p->op ){
case RTREE_TRUE: return; /* Always satisfied */
case RTREE_FALSE: break; /* Never satisfied */
case RTREE_LE: if( xN <= p->u.rValue ) return; break;
case RTREE_LT: if( xN < p->u.rValue ) return; break;
case RTREE_GE: if( xN >= p->u.rValue ) return; break;
case RTREE_GT: if( xN > p->u.rValue ) return; break;
default: if( xN == p->u.rValue ) return; break;
}
*peWithin = NOT_WITHIN;
}
/*
** One of the cells in node pNode is guaranteed to have a 64-bit
** integer value equal to iRowid. Return the index of this cell.
|
| ︙ | ︙ | |||
1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 |
}else{
memset(pCsr->aConstraint, 0, sizeof(RtreeConstraint)*argc);
memset(pCsr->anQueue, 0, sizeof(u32)*(pRtree->iDepth + 1));
assert( (idxStr==0 && argc==0)
|| (idxStr && (int)strlen(idxStr)==argc*2) );
for(ii=0; ii<argc; ii++){
RtreeConstraint *p = &pCsr->aConstraint[ii];
p->op = idxStr[ii*2];
p->iCoord = idxStr[ii*2+1]-'0';
if( p->op>=RTREE_MATCH ){
/* A MATCH operator. The right-hand-side must be a blob that
** can be cast into an RtreeMatchArg object. One created using
** an sqlite3_rtree_geometry_callback() SQL user function.
*/
rc = deserializeGeometry(argv[ii], p);
if( rc!=SQLITE_OK ){
break;
}
p->pInfo->nCoord = pRtree->nDim2;
p->pInfo->anQueue = pCsr->anQueue;
p->pInfo->mxLevel = pRtree->iDepth + 1;
| > | > > > > > > > > > | 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 |
}else{
memset(pCsr->aConstraint, 0, sizeof(RtreeConstraint)*argc);
memset(pCsr->anQueue, 0, sizeof(u32)*(pRtree->iDepth + 1));
assert( (idxStr==0 && argc==0)
|| (idxStr && (int)strlen(idxStr)==argc*2) );
for(ii=0; ii<argc; ii++){
RtreeConstraint *p = &pCsr->aConstraint[ii];
int eType = sqlite3_value_type(argv[ii]);
p->op = idxStr[ii*2];
p->iCoord = idxStr[ii*2+1]-'0';
if( p->op>=RTREE_MATCH ){
/* A MATCH operator. The right-hand-side must be a blob that
** can be cast into an RtreeMatchArg object. One created using
** an sqlite3_rtree_geometry_callback() SQL user function.
*/
rc = deserializeGeometry(argv[ii], p);
if( rc!=SQLITE_OK ){
break;
}
p->pInfo->nCoord = pRtree->nDim2;
p->pInfo->anQueue = pCsr->anQueue;
p->pInfo->mxLevel = pRtree->iDepth + 1;
}else if( eType==SQLITE_INTEGER || eType==SQLITE_FLOAT ){
#ifdef SQLITE_RTREE_INT_ONLY
p->u.rValue = sqlite3_value_int64(argv[ii]);
#else
p->u.rValue = sqlite3_value_double(argv[ii]);
#endif
}else{
p->u.rValue = RTREE_ZERO;
if( eType==SQLITE_NULL ){
p->op = RTREE_FALSE;
}else if( p->op==RTREE_LT || p->op==RTREE_LE ){
p->op = RTREE_TRUE;
}else{
p->op = RTREE_FALSE;
}
}
}
}
}
if( rc==SQLITE_OK ){
RtreeSearchPoint *pNew;
pNew = rtreeSearchPointNew(pCsr, RTREE_ZERO, (u8)(pRtree->iDepth+1));
|
| ︙ | ︙ |
Changes to ext/rtree/rtree1.test.
| ︙ | ︙ | |||
370 371 372 373 374 375 376 |
do_test rtree-8.1.1 {
execsql {
CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2);
INSERT INTO t6 VALUES(1, 3, 7);
INSERT INTO t6 VALUES(2, 4, 6);
}
} {}
| | | | | | | > > > > > > > > > | > > > > > > > > > > > > > > | 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 |
do_test rtree-8.1.1 {
execsql {
CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2);
INSERT INTO t6 VALUES(1, 3, 7);
INSERT INTO t6 VALUES(2, 4, 6);
}
} {}
do_test rtree-8.1.2 { execsql { SELECT ii FROM t6 WHERE x1>2 } } {1 2}
do_test rtree-8.1.3 { execsql { SELECT ii FROM t6 WHERE x1>3 } } {2}
do_test rtree-8.1.4 { execsql { SELECT ii FROM t6 WHERE x1>4 } } {}
do_test rtree-8.1.5 { execsql { SELECT ii FROM t6 WHERE x1>5 } } {}
do_test rtree-8.1.6 { execsql { SELECT ii FROM t6 WHERE x1>''} } {}
do_test rtree-8.1.7 { execsql { SELECT ii FROM t6 WHERE x1>null}} {}
do_test rtree-8.2.2 { execsql { SELECT ii FROM t6 WHERE x1>=2 } } {1 2}
do_test rtree-8.2.3 { execsql { SELECT ii FROM t6 WHERE x1>=3 } } {1 2}
do_test rtree-8.2.4 { execsql { SELECT ii FROM t6 WHERE x1>=4 } } {2}
do_test rtree-8.2.5 { execsql { SELECT ii FROM t6 WHERE x1>=5 } } {}
do_test rtree-8.2.6 { execsql { SELECT ii FROM t6 WHERE x1>=''} } {}
do_test rtree-8.2.7 { execsql { SELECT ii FROM t6 WHERE x1>=null}} {}
do_test rtree-8.3.2 { execsql { SELECT ii FROM t6 WHERE x1<2 } } {}
do_test rtree-8.3.3 { execsql { SELECT ii FROM t6 WHERE x1<3 } } {}
do_test rtree-8.3.4 { execsql { SELECT ii FROM t6 WHERE x1<4 } } {1}
do_test rtree-8.3.5 { execsql { SELECT ii FROM t6 WHERE x1<5 } } {1 2}
do_test rtree-8.3.6 { execsql { SELECT ii FROM t6 WHERE x1<''} } {1 2}
do_test rtree-8.3.7 { execsql { SELECT ii FROM t6 WHERE x1<null}} {}
do_test rtree-8.4.2 { execsql { SELECT ii FROM t6 WHERE x1<=2 } } {}
do_test rtree-8.4.3 { execsql { SELECT ii FROM t6 WHERE x1<=3 } } {1}
do_test rtree-8.4.4 { execsql { SELECT ii FROM t6 WHERE x1<=4 } } {1 2}
do_test rtree-8.4.5 { execsql { SELECT ii FROM t6 WHERE x1<=5 } } {1 2}
do_test rtree-8.4.6 { execsql { SELECT ii FROM t6 WHERE x1<=''} } {1 2}
do_test rtree-8.4.7 { execsql { SELECT ii FROM t6 WHERE x1<=null}} {}
do_test rtree-8.5.2 { execsql { SELECT ii FROM t6 WHERE x1=2 } } {}
do_test rtree-8.5.3 { execsql { SELECT ii FROM t6 WHERE x1=3 } } {1}
do_test rtree-8.5.4 { execsql { SELECT ii FROM t6 WHERE x1=4 } } {2}
do_test rtree-8.5.5 { execsql { SELECT ii FROM t6 WHERE x1=5 } } {}
do_test rtree-8.5.6 { execsql { SELECT ii FROM t6 WHERE x1=''} } {}
do_test rtree-8.5.7 { execsql { SELECT ii FROM t6 WHERE x1=null}} {}
#----------------------------------------------------------------------------
# Test cases rtree-9.*
#
# Test that ticket #3549 is fixed.
do_test rtree-9.1 {
execsql {
|
| ︙ | ︙ |