/ Check-in [f898d04c]
Login

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

Changes to ext/rtree/rtree.c.

321
322
323
324
325
326
327






328
329
330
331
332
333
334
....
1240
1241
1242
1243
1244
1245
1246
1247

1248
1249


1250
1251
1252
1253
1254
1255
1256
....
1280
1281
1282
1283
1284
1285
1286
1287

1288
1289
1290
1291


1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
....
1831
1832
1833
1834
1835
1836
1837

1838
1839
1840
1841
1842
1843
1844
....
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857









1858
1859
1860
1861
1862
1863
1864
#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 */
................................................................................

  /* 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 );

  assert( ((((char*)pCellData) - (char*)0)&3)==0 );  /* 4-byte aligned */
  switch( p->op ){


    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 */
................................................................................
  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 );

  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_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.
................................................................................
      }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;
          }else{
#ifdef SQLITE_RTREE_INT_ONLY
            p->u.rValue = sqlite3_value_int64(argv[ii]);
#else
            p->u.rValue = sqlite3_value_double(argv[ii]);
#endif









          }
        }
      }
    }
    if( rc==SQLITE_OK ){
      RtreeSearchPoint *pNew;
      pNew = rtreeSearchPointNew(pCsr, RTREE_ZERO, (u8)(pRtree->iDepth+1));







>
>
>
>
>
>







 







|
>


>
>







 







|
>




>
>
|
|
|
|
|







 







>







 







|





>
>
>
>
>
>
>
>
>







321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
....
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
....
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
....
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
#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 */
................................................................................

  /* 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 */
................................................................................
  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.
................................................................................
      }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
377
378
379
380
381
382









383














384
385
386
387
388
389
390
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<3 } } {}
do_test rtree-8.1.7 { execsql { SELECT ii FROM t6 WHERE x1<4 } } {1}









do_test rtree-8.1.8 { execsql { SELECT ii FROM t6 WHERE x1<5 } } {1 2}















#----------------------------------------------------------------------------
# Test cases rtree-9.*
#
# Test that ticket #3549 is fixed.
do_test rtree-9.1 {
  execsql {







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







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 {