Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Performance optimization in the applyAffinity() logic inside the VDBE. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
25f2246be404f38b4f8dd70397cd1454 |
User & Date: | drh 2014-08-23 17:21:37.343 |
References
2024-03-06
| ||
20:54 | When doing a text-affinity comparison between two values where one or both have both a text and a numeric type, make sure the numeric type does not confuse the answer. This is a deeper fix to the problem observed by forum post 3776b48e71. The problem bisects to [25f2246be404f38b] on 2014-08-24, prior to version 3.8.7. (check-in: 6d385ccda8 user: mistachkin tags: branch-3.45) | |
2024-01-20
| ||
15:13 | When doing a text-affinity comparison between two values where one or both have both a text and a numeric type, make sure the numeric type does not confuse the answer. This is a deeper fix to the problem observed by forum pose 3776b48e71. The problem bisects to [25f2246be404f38b] on 2014-08-24, prior to version 3.8.7. (check-in: 709841f88c user: drh tags: trunk) | |
2015-05-19
| ||
19:08 | • New ticket [34cd55d68e] Database corruption following INSERT with a TRIGGER that does an affinity change. (artifact: 208465f372 user: drh) | |
Context
2014-08-23
| ||
17:41 | Improved performance in the type handling of arithmetic operators in the VDBE. (check-in: 0c0a603950 user: drh tags: trunk) | |
17:21 | Performance optimization in the applyAffinity() logic inside the VDBE. (check-in: 25f2246be4 user: drh tags: trunk) | |
2014-08-22
| ||
23:33 | Another performance tweak: Split the sqlite3BtreeCursorHasMoved() routine into two with the second routine named sqlite3BtreeCursorRestore(). The first now only reports whether or not the cursor has moved and the second tries to restore the cursor. This allows the sqlite3VdbeCursorMoveto() routine to be refactored to avoid stack pointer movements, for a noticable performance gain. (check-in: ce123b5c59 user: drh tags: trunk) | |
Changes
Changes to src/vdbe.c.
︙ | ︙ | |||
142 143 144 145 146 147 148 | #endif /* ** Convert the given register into a string if it isn't one ** already. Return non-zero if a malloc() fails. */ #define Stringify(P, enc) \ | | | 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | #endif /* ** Convert the given register into a string if it isn't one ** already. Return non-zero if a malloc() fails. */ #define Stringify(P, enc) \ if(((P)->flags&(MEM_Str|MEM_Blob))==0 && sqlite3VdbeMemStringify(P,enc,0)) \ { goto no_mem; } /* ** An ephemeral string value (signified by the MEM_Ephem flag) contains ** a pointer to a dynamically allocated string where some other entity ** is responsible for deallocating that string. Because the register ** does not control the string, it might be deleted without the register |
︙ | ︙ | |||
224 225 226 227 228 229 230 231 | } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string ** looks like a number, convert it into a number. If it does not ** look like a number, leave it alone. */ | > > > > > > > > > | > < < | 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 | } /* ** Try to convert a value into a numeric representation if we can ** do so without loss of information. In other words, if the string ** looks like a number, convert it into a number. If it does not ** look like a number, leave it alone. ** ** If the bTryForInt flag is true, then extra effort is made to give ** an integer representation. Strings that look like floating point ** values but which have no fractional component (example: '48.00') ** will have a MEM_Int representation when bTryForInt is true. ** ** If bTryForInt is false, then if the input string contains a decimal ** point or exponential notation, the result is only MEM_Real, even ** if there is an exact integer representation of the quantity. */ static void applyNumericAffinity(Mem *pRec, int bTryForInt){ double rValue; i64 iValue; u8 enc = pRec->enc; if( (pRec->flags&MEM_Str)==0 ) return; if( sqlite3AtoF(pRec->z, &rValue, pRec->n, enc)==0 ) return; if( 0==sqlite3Atoi64(pRec->z, &iValue, pRec->n, enc) ){ pRec->u.i = iValue; pRec->flags |= MEM_Int; }else{ pRec->r = rValue; pRec->flags |= MEM_Real; if( bTryForInt ) sqlite3VdbeIntegerAffinity(pRec); } } /* ** Processing is determine by the affinity parameter: ** ** SQLITE_AFF_INTEGER: ** SQLITE_AFF_REAL: ** SQLITE_AFF_NUMERIC: |
︙ | ︙ | |||
271 272 273 274 275 276 277 | ){ if( affinity==SQLITE_AFF_TEXT ){ /* Only attempt the conversion to TEXT if there is an integer or real ** representation (blob and NULL do not get converted) but no string ** representation. */ if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){ | | < | | > > | > | | 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 | ){ if( affinity==SQLITE_AFF_TEXT ){ /* Only attempt the conversion to TEXT if there is an integer or real ** representation (blob and NULL do not get converted) but no string ** representation. */ if( 0==(pRec->flags&MEM_Str) && (pRec->flags&(MEM_Real|MEM_Int)) ){ sqlite3VdbeMemStringify(pRec, enc, 1); } }else if( affinity!=SQLITE_AFF_NONE ){ assert( affinity==SQLITE_AFF_INTEGER || affinity==SQLITE_AFF_REAL || affinity==SQLITE_AFF_NUMERIC ); if( (pRec->flags & MEM_Int)==0 ){ if( (pRec->flags & MEM_Real)==0 ){ applyNumericAffinity(pRec,1); }else{ sqlite3VdbeIntegerAffinity(pRec); } } } } /* ** Try to convert the type of a function argument or a result column ** into a numeric representation. Use either INTEGER or REAL whichever ** is appropriate. But only do the conversion if it is possible without ** loss of information and return the revised type of the argument. */ int sqlite3_value_numeric_type(sqlite3_value *pVal){ int eType = sqlite3_value_type(pVal); if( eType==SQLITE_TEXT ){ Mem *pMem = (Mem*)pVal; applyNumericAffinity(pMem, 0); eType = sqlite3_value_type(pVal); } return eType; } /* ** Exported version of applyAffinity(). This one works on sqlite3_value*, |
︙ | ︙ | |||
3599 3600 3601 3602 3603 3604 3605 | pC->seekOp = pOp->opcode; #endif if( pC->isTable ){ /* The input value in P3 might be of any type: integer, real, string, ** blob, or NULL. But it needs to be an integer before we can do ** the seek, so covert it. */ pIn3 = &aMem[pOp->p3]; | > | > | 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 | pC->seekOp = pOp->opcode; #endif if( pC->isTable ){ /* The input value in P3 might be of any type: integer, real, string, ** blob, or NULL. But it needs to be an integer before we can do ** the seek, so covert it. */ pIn3 = &aMem[pOp->p3]; if( (pIn3->flags & (MEM_Int|MEM_Real))==0 ){ applyNumericAffinity(pIn3, 0); } iKey = sqlite3VdbeIntValue(pIn3); pC->rowidIsValid = 0; /* If the P3 value could not be converted into an integer without ** loss of information, then special processing is required... */ if( (pIn3->flags & MEM_Int)==0 ){ if( (pIn3->flags & MEM_Real)==0 ){ |
︙ | ︙ |
Changes to src/vdbeInt.h.
︙ | ︙ | |||
414 415 416 417 418 419 420 | #else void sqlite3VdbeMemSetDouble(Mem*, double); #endif void sqlite3VdbeMemSetNull(Mem*); void sqlite3VdbeMemSetZeroBlob(Mem*,int); void sqlite3VdbeMemSetRowSet(Mem*); int sqlite3VdbeMemMakeWriteable(Mem*); | | | 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 | #else void sqlite3VdbeMemSetDouble(Mem*, double); #endif void sqlite3VdbeMemSetNull(Mem*); void sqlite3VdbeMemSetZeroBlob(Mem*,int); void sqlite3VdbeMemSetRowSet(Mem*); int sqlite3VdbeMemMakeWriteable(Mem*); int sqlite3VdbeMemStringify(Mem*, u8, u8); i64 sqlite3VdbeIntValue(Mem*); int sqlite3VdbeMemIntegerify(Mem*); double sqlite3VdbeRealValue(Mem*); void sqlite3VdbeIntegerAffinity(Mem*); int sqlite3VdbeMemRealify(Mem*); int sqlite3VdbeMemNumerify(Mem*); int sqlite3VdbeMemFromBtree(BtCursor*,u32,u32,int,Mem*); |
︙ | ︙ |
Changes to src/vdbemem.c.
︙ | ︙ | |||
219 220 221 222 223 224 225 | } /* ** Add MEM_Str to the set of representations for the given Mem. Numbers ** are converted using sqlite3_snprintf(). Converting a BLOB to a string ** is a no-op. ** | | > | < | > | | 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 | } /* ** Add MEM_Str to the set of representations for the given Mem. Numbers ** are converted using sqlite3_snprintf(). Converting a BLOB to a string ** is a no-op. ** ** Existing representations MEM_Int and MEM_Real are invalidated if ** bForce is true but are retained if bForce is false. ** ** A MEM_Null value will never be passed to this function. This function is ** used for converting values to text for returning to the user (i.e. via ** sqlite3_value_text()), or for ensuring that values to be used as btree ** keys are strings. In the former case a NULL pointer is returned the ** user and the later is an internal programming error. */ int sqlite3VdbeMemStringify(Mem *pMem, u8 enc, u8 bForce){ int fg = pMem->flags; const int nByte = 32; assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); assert( !(fg&MEM_Zero) ); assert( !(fg&(MEM_Str|MEM_Blob)) ); assert( fg&(MEM_Int|MEM_Real) ); assert( (pMem->flags&MEM_RowSet)==0 ); assert( EIGHT_BYTE_ALIGNMENT(pMem) ); if( sqlite3VdbeMemGrow(pMem, nByte, 0) ){ return SQLITE_NOMEM; } /* For a Real or Integer, use sqlite3_snprintf() to produce the UTF-8 ** string representation of the value. Then, if the required encoding ** is UTF-16le or UTF-16be do a translation. ** ** FIX ME: It would be better if sqlite3_snprintf() could do UTF-16. */ if( fg & MEM_Int ){ sqlite3_snprintf(nByte, pMem->z, "%lld", pMem->u.i); }else{ assert( fg & MEM_Real ); sqlite3_snprintf(nByte, pMem->z, "%!.15g", pMem->r); } pMem->n = sqlite3Strlen30(pMem->z); pMem->enc = SQLITE_UTF8; pMem->flags |= MEM_Str|MEM_Term; if( bForce ) pMem->flags &= ~(MEM_Int|MEM_Real); sqlite3VdbeChangeEncoding(pMem, enc); return SQLITE_OK; } /* ** Memory cell pMem contains the context of an aggregate function. ** This routine calls the finalize method for that function. The ** result of the aggregate is stored back into pMem. ** |
︙ | ︙ | |||
873 874 875 876 877 878 879 | if( sqlite3VdbeMemMakeWriteable(pVal)!=SQLITE_OK ){ return 0; } } sqlite3VdbeMemNulTerminate(pVal); /* IMP: R-31275-44060 */ }else{ assert( (pVal->flags&MEM_Blob)==0 ); | | | 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 | if( sqlite3VdbeMemMakeWriteable(pVal)!=SQLITE_OK ){ return 0; } } sqlite3VdbeMemNulTerminate(pVal); /* IMP: R-31275-44060 */ }else{ assert( (pVal->flags&MEM_Blob)==0 ); sqlite3VdbeMemStringify(pVal, enc, 0); assert( 0==(1&SQLITE_PTR_TO_INT(pVal->z)) ); } assert(pVal->enc==(enc & ~SQLITE_UTF16_ALIGNED) || pVal->db==0 || pVal->db->mallocFailed ); if( pVal->enc==(enc & ~SQLITE_UTF16_ALIGNED) ){ return pVal->z; }else{ |
︙ | ︙ |