/ Check-in [3f34f4f5]
Login

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

Overview
Comment:Fix the JSON table-valued functions to make use of SQLITE_CONSTRAINT.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 3f34f4f561c77f8ec88906818f2984dcf8f17d1645bac175e9027595517560bc
User & Date: drh 2018-11-16 16:04:50
Context
2018-11-16
19:19
Update the explain virtual table to make use of SQLITE_CONSTRAINT. check-in: b2d41ff7 user: drh tags: trunk
16:04
Fix the JSON table-valued functions to make use of SQLITE_CONSTRAINT. check-in: 3f34f4f5 user: drh tags: trunk
15:41
Add an assert() to the generate_series virtual table to verify assumptions about the design. check-in: cd13b499 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/json1.c.

  1990   1990   #define JEACH_VALUE   1
  1991   1991   #define JEACH_TYPE    2
  1992   1992   #define JEACH_ATOM    3
  1993   1993   #define JEACH_ID      4
  1994   1994   #define JEACH_PARENT  5
  1995   1995   #define JEACH_FULLKEY 6
  1996   1996   #define JEACH_PATH    7
         1997  +/* The xBestIndex method assumes that the JSON and ROOT columns are
         1998  +** the last two columns in the table.  Should this ever changes, be
         1999  +** sure to update the xBestIndex method. */
  1997   2000   #define JEACH_JSON    8
  1998   2001   #define JEACH_ROOT    9
  1999   2002   
  2000   2003     UNUSED_PARAM(pzErr);
  2001   2004     UNUSED_PARAM(argv);
  2002   2005     UNUSED_PARAM(argc);
  2003   2006     UNUSED_PARAM(pAux);
................................................................................
  2247   2250   ** 1 if the constraint is found, 3 if the constraint and zRoot are found,
  2248   2251   ** and 0 otherwise.
  2249   2252   */
  2250   2253   static int jsonEachBestIndex(
  2251   2254     sqlite3_vtab *tab,
  2252   2255     sqlite3_index_info *pIdxInfo
  2253   2256   ){
  2254         -  int i;
  2255         -  int jsonIdx = -1;
  2256         -  int rootIdx = -1;
         2257  +  int i;                     /* Loop counter or computed array index */
         2258  +  int aIdx[2];               /* Index of constraints for JSON and ROOT */
         2259  +  int unusableMask = 0;      /* Mask of unusable JSON and ROOT constraints */
         2260  +  int idxMask = 0;           /* Mask of usable == constraints JSON and ROOT */
  2257   2261     const struct sqlite3_index_constraint *pConstraint;
  2258   2262   
         2263  +  /* This implementation assumes that JSON and ROOT are the last two
         2264  +  ** columns in the table */
         2265  +  assert( JEACH_ROOT == JEACH_JSON+1 );
  2259   2266     UNUSED_PARAM(tab);
         2267  +  aIdx[0] = aIdx[1] = -1;
  2260   2268     pConstraint = pIdxInfo->aConstraint;
  2261   2269     for(i=0; i<pIdxInfo->nConstraint; i++, pConstraint++){
  2262         -    if( pConstraint->usable==0 ) continue;
  2263         -    if( pConstraint->op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
  2264         -    switch( pConstraint->iColumn ){
  2265         -      case JEACH_JSON:   jsonIdx = i;    break;
  2266         -      case JEACH_ROOT:   rootIdx = i;    break;
  2267         -      default:           /* no-op */     break;
         2270  +    int iCol;
         2271  +    int iMask;
         2272  +    if( pConstraint->iColumn < JEACH_JSON ) continue;
         2273  +    iCol = pConstraint->iColumn - JEACH_JSON;
         2274  +    assert( iCol==0 || iCol==1 );
         2275  +    iMask = 1 << iCol;
         2276  +    if( pConstraint->usable==0 ){
         2277  +      unusableMask |= iMask;
         2278  +    }else if( pConstraint->op==SQLITE_INDEX_CONSTRAINT_EQ ){
         2279  +      aIdx[iCol] = i;
         2280  +      idxMask |= iMask;
  2268   2281       }
  2269   2282     }
  2270         -  if( jsonIdx<0 ){
         2283  +  if( (unusableMask & ~idxMask)!=0 ){
         2284  +    /* If there are any unusable constraints on JSON or ROOT, then reject
         2285  +    ** this entire plan */
         2286  +    return SQLITE_CONSTRAINT;
         2287  +  }
         2288  +  if( aIdx[0]<0 ){
         2289  +    /* No JSON input.  Leave estimatedCost at the huge value that it was
         2290  +    ** initialized to to discourage the query planner from selecting this
         2291  +    ** plan. */
  2271   2292       pIdxInfo->idxNum = 0;
  2272         -    pIdxInfo->estimatedCost = 1e99;
  2273   2293     }else{
  2274   2294       pIdxInfo->estimatedCost = 1.0;
  2275         -    pIdxInfo->aConstraintUsage[jsonIdx].argvIndex = 1;
  2276         -    pIdxInfo->aConstraintUsage[jsonIdx].omit = 1;
  2277         -    if( rootIdx<0 ){
  2278         -      pIdxInfo->idxNum = 1;
         2295  +    i = aIdx[0];
         2296  +    pIdxInfo->aConstraintUsage[i].argvIndex = 1;
         2297  +    pIdxInfo->aConstraintUsage[i].omit = 1;
         2298  +    if( aIdx[1]<0 ){
         2299  +      pIdxInfo->idxNum = 1;  /* Only JSON supplied.  Plan 1 */
  2279   2300       }else{
  2280         -      pIdxInfo->aConstraintUsage[rootIdx].argvIndex = 2;
  2281         -      pIdxInfo->aConstraintUsage[rootIdx].omit = 1;
  2282         -      pIdxInfo->idxNum = 3;
         2301  +      i = aIdx[1];
         2302  +      pIdxInfo->aConstraintUsage[i].argvIndex = 2;
         2303  +      pIdxInfo->aConstraintUsage[i].omit = 1;
         2304  +      pIdxInfo->idxNum = 3;  /* Both JSON and ROOT are supplied.  Plan 3 */
  2283   2305       }
  2284   2306     }
  2285   2307     return SQLITE_OK;
  2286   2308   }
  2287   2309   
  2288   2310   /* Start a search on a new JSON string */
  2289   2311   static int jsonEachFilter(