/ Check-in [0e05679d]
Login

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

Overview
Comment:Improved comments on the OP_Column changes. Optimize out loading of overflow pages for content with zero length. Add test cases for the latter.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | OP_Column-refactor
Files: files | file ages | folders
SHA1: 0e05679db7aa302a49e087a81f85203844b98cbe
User & Date: drh 2013-11-20 20:58:00
Context
2013-11-20
21:51
Reduce the size of the VdbeCursor object from 144 to 120 bytes. check-in: 5f9d5068 user: drh tags: OP_Column-refactor
20:58
Improved comments on the OP_Column changes. Optimize out loading of overflow pages for content with zero length. Add test cases for the latter. check-in: 0e05679d user: drh tags: OP_Column-refactor
19:28
Further performance tweaks to OP_Column. check-in: 0e3f5df6 user: drh tags: OP_Column-refactor
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

  2280   2280     assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  2281   2281     pC = p->apCsr[pOp->p1];
  2282   2282     assert( pC!=0 );
  2283   2283     assert( p2<pC->nField );
  2284   2284     aType = pC->aType;
  2285   2285     aOffset = pC->aOffset;
  2286   2286   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2287         -  assert( pC->pVtabCursor==0 );
         2287  +  assert( pC->pVtabCursor==0 ); /* OP_Column never called on virtual table */
  2288   2288   #endif
  2289   2289     pCrsr = pC->pCursor;
  2290         -  assert( pCrsr!=0 || pC->pseudoTableReg>0 );
  2291         -  assert( pC->pseudoTableReg==0 || pC->nullRow );
         2290  +  assert( pCrsr!=0 || pC->pseudoTableReg>0 ); /* pCrsr NULL on PseudoTables */
         2291  +  assert( pCrsr!=0 || pC->nullRow );          /* pC->nullRow on PseudoTables */
  2292   2292   
  2293   2293     /* If the cursor cache is stale, bring it up-to-date */
  2294   2294     rc = sqlite3VdbeCursorMoveto(pC);
  2295   2295     if( rc ) goto abort_due_to_error;
  2296   2296     if( pC->cacheStatus!=p->cacheCtr || (pOp->p5&OPFLAG_CLEARCACHE)!=0 ){
  2297   2297       if( pC->nullRow ){
  2298   2298         if( pCrsr==0 ){
................................................................................
  2335   2335         }else{
  2336   2336           pC->szRow = avail;
  2337   2337         }
  2338   2338         if( pC->payloadSize > (u32)db->aLimit[SQLITE_LIMIT_LENGTH] ){
  2339   2339           goto too_big;
  2340   2340         }
  2341   2341       }
  2342         -      
  2343   2342       pC->cacheStatus = p->cacheCtr;
  2344   2343       pC->iHdrOffset = getVarint32(pC->aRow, offset);
  2345   2344       pC->nHdrParsed = 0;
  2346   2345       aOffset[0] = offset;
  2347   2346       if( avail<offset ){
         2347  +      /* pC->aRow does not have to hold the entire row, but it does at least
         2348  +      ** need to cover the header of the record.  If pC->aRow does not contain
         2349  +      ** the complete header, then set it to zero, forcing the header to be
         2350  +      ** dynamically allocated. */
  2348   2351         pC->aRow = 0;
  2349   2352         pC->szRow = 0;
  2350   2353       }
  2351   2354   
  2352   2355       /* Make sure a corrupt database has not given us an oversize header.
  2353   2356       ** Do this now to avoid an oversize memory allocation.
  2354   2357       **
................................................................................
  2364   2367       }
  2365   2368     }
  2366   2369   
  2367   2370     /* Make sure at least the first p2+1 entries of the header have been
  2368   2371     ** parsed and valid information is in aOffset[] and aType[].
  2369   2372     */
  2370   2373     if( pC->nHdrParsed<=p2 ){
  2371         -    /* If there is more header available for parsing, try to extract 
  2372         -    ** additional fields up through the p2-th field 
         2374  +    /* If there is more header available for parsing in the record, try
         2375  +    ** to extract additional fields up through the p2+1-th field 
  2373   2376       */
  2374   2377       if( pC->iHdrOffset<aOffset[0] ){
  2375   2378         /* Make sure zData points to enough of the record to cover the header. */
  2376   2379         if( pC->aRow==0 ){
  2377   2380           memset(&sMem, 0, sizeof(sMem));
  2378   2381           rc = sqlite3VdbeMemFromBtree(pCrsr, 0, pC->aOffset[0], pC->isIndex,
  2379   2382                                        &sMem);
................................................................................
  2426   2429          || (zHdr==zEndHdr && offset!=pC->payloadSize)
  2427   2430         ){
  2428   2431           rc = SQLITE_CORRUPT_BKPT;
  2429   2432           goto op_column_error;
  2430   2433         }
  2431   2434       }
  2432   2435   
  2433         -    /* If after nHdrParsed is still not up to p2, that means that the record
  2434         -    ** has fewer than p2 columns.  So the result will be either the default
  2435         -    ** value or a NULL. */
         2436  +    /* If after trying to extra new entries from the header, nHdrParsed is
         2437  +    ** still not up to p2, that means that the record has fewer than p2
         2438  +    ** columns.  So the result will be either the default value or a NULL.
         2439  +    */
  2436   2440       if( pC->nHdrParsed<=p2 ){
  2437   2441         if( pOp->p4type==P4_MEM ){
  2438   2442           sqlite3VdbeMemShallowCopy(pDest, pOp->p4.pMem, MEM_Static);
  2439   2443         }else{
  2440   2444           MemSetTypeFlag(pDest, MEM_Null);
  2441   2445         }
  2442   2446         goto op_column_out;
  2443   2447       }
  2444   2448     }
  2445   2449   
  2446         -  /* Get the column information. If aOffset[p2] is non-zero, then 
  2447         -  ** deserialize the value from the record. If aOffset[p2] is zero,
  2448         -  ** then there are not enough fields in the record to satisfy the
  2449         -  ** request.  In this case, set the value NULL or to P4 if P4 is
  2450         -  ** a pointer to a Mem object.
         2450  +  /* Extract the content for the p2+1-th column.  Control can only
         2451  +  ** reach this point if aOffset[p2], aOffset[p2+1], and aType[p2] are
         2452  +  ** all valid.
  2451   2453     */
  2452   2454     assert( p2<pC->nHdrParsed );
  2453   2455     assert( rc==SQLITE_OK );
  2454   2456     if( pC->szRow>=aOffset[p2+1] ){
  2455         -    /* This is the common case where the whole row fits on a single page */
         2457  +    /* This is the common case where the desired content fits on the original
         2458  +    ** page - where the content is not on an overflow page */
  2456   2459       VdbeMemRelease(pDest);
  2457   2460       sqlite3VdbeSerialGet(pC->aRow+aOffset[p2], aType[p2], pDest);
  2458   2461     }else{
  2459         -    /* This branch happens only when the row overflows onto multiple pages */
         2462  +    /* This branch happens only when content is on overflow pages */	
  2460   2463       t = aType[p2];
  2461         -    if( (pOp->p5 & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG))!=0
  2462         -     && ((t>=12 && (t&1)==0) || (pOp->p5 & OPFLAG_TYPEOFARG)!=0)
         2464  +    if( ((pOp->p5 & (OPFLAG_LENGTHARG|OPFLAG_TYPEOFARG))!=0
         2465  +          && ((t>=12 && (t&1)==0) || (pOp->p5 & OPFLAG_TYPEOFARG)!=0))
         2466  +     || (len = sqlite3VdbeSerialTypeLen(t))==0
  2463   2467       ){
  2464   2468         /* Content is irrelevant for the typeof() function and for
  2465   2469         ** the length(X) function if X is a blob.  So we might as well use
  2466   2470         ** bogus content rather than reading content from disk.  NULL works
  2467   2471         ** for text and blob and whatever is in the payloadSize64 variable
  2468         -      ** will work for everything else. */
  2469         -      zData = t<12 ? (u8*)&payloadSize64 : 0;
         2472  +      ** will work for everything else.  Content is also irrelevant if
         2473  +      ** the content length is 0. */
         2474  +      zData = t<=13 ? (u8*)&payloadSize64 : 0;
  2470   2475         sMem.zMalloc = 0;
  2471   2476       }else{
  2472         -      len = sqlite3VdbeSerialTypeLen(t);
  2473   2477         memset(&sMem, 0, sizeof(sMem));
  2474   2478         sqlite3VdbeMemMove(&sMem, pDest);
  2475   2479         rc = sqlite3VdbeMemFromBtree(pCrsr, aOffset[p2], len,  pC->isIndex,
  2476   2480                                      &sMem);
  2477   2481         if( rc!=SQLITE_OK ){
  2478   2482           goto op_column_error;
  2479   2483         }

Changes to test/func.test.

  1314   1314     db eval {SELECT sum(length(x)) FROM t29}
  1315   1315   } {1000009}
  1316   1316   do_test func-29.6 {
  1317   1317     set x [lindex [sqlite3_db_status db CACHE_MISS 1] 1]
  1318   1318     if {$x<5} {set x 1}
  1319   1319     set x
  1320   1320   } {1}
         1321  +
         1322  +# The OP_Column opcode has an optimization that avoids loading content
         1323  +# for fields with content-length=0 when the content offset is on an overflow
         1324  +# page.  Make sure the optimization works.
         1325  +#
         1326  +do_execsql_test func-29.10 {
         1327  +  CREATE TABLE t29b(a,b,c,d,e,f,g,h,i);
         1328  +  INSERT INTO t29b 
         1329  +   VALUES(1, hex(randomblob(2000)), null, 0, 1, '', zeroblob(0),'x',x'01');
         1330  +  SELECT typeof(c), typeof(d), typeof(e), typeof(f),
         1331  +         typeof(g), typeof(h), typeof(i) FROM t29b;
         1332  +} {null integer integer text blob text blob}
         1333  +do_execsql_test func-29.11 {
         1334  +  SELECT length(f), length(g), length(h), length(i) FROM t29b;
         1335  +} {0 0 1 1}
         1336  +do_execsql_test func-29.12 {
         1337  +  SELECT quote(f), quote(g), quote(h), quote(i) FROM t29b;
         1338  +} {'' X'' 'x' X'01'}
  1321   1339   
  1322   1340   # EVIDENCE-OF: R-29701-50711 The unicode(X) function returns the numeric
  1323   1341   # unicode code point corresponding to the first character of the string
  1324   1342   # X.
  1325   1343   #
  1326   1344   # EVIDENCE-OF: R-55469-62130 The char(X1,X2,...,XN) function returns a
  1327   1345   # string composed of characters having the unicode code point values of