Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
91d8a8d0b792ea5c4fe68fd9caaf3345 |
| User & Date: | drh 2014-08-25 20:11:52 |
Context
|
2014-08-25
| ||
| 20:21 | Test cases added for using unary "+" and CAST operators on the RHS of range constraints and verifying that STAT3/4 can use those constraints. (check-in: 42505e5a user: drh tags: trunk) | |
| 20:11 | Allow CAST expressions and unary "+" operators to be used in the DEFAULT argument of an ALTER TABLE ADD COLUMN and to be understand on the RHS of range constraints interpreted by STAT3/4. This involves a rewrite of the implementation of the CAST operator. (check-in: 91d8a8d0 user: drh tags: trunk) | |
| 18:29 | In cases where stat4 data is available but cannot be used because the rhs of a range constraint is too complex a expression, fall back to using the default estimates for number of rows scanned. (check-in: e06dc6f0 user: dan tags: trunk) | |
Changes
Changes to src/expr.c.
2591 2591 case TK_AS: { 2592 2592 inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target); 2593 2593 break; 2594 2594 } 2595 2595 #ifndef SQLITE_OMIT_CAST 2596 2596 case TK_CAST: { 2597 2597 /* Expressions of the form: CAST(pLeft AS token) */ 2598 - int aff, to_op; 2599 2598 inReg = sqlite3ExprCodeTarget(pParse, pExpr->pLeft, target); 2600 - assert( !ExprHasProperty(pExpr, EP_IntValue) ); 2601 - aff = sqlite3AffinityType(pExpr->u.zToken, 0); 2602 - to_op = aff - SQLITE_AFF_TEXT + OP_ToText; 2603 - assert( to_op==OP_ToText || aff!=SQLITE_AFF_TEXT ); 2604 - assert( to_op==OP_ToBlob || aff!=SQLITE_AFF_NONE ); 2605 - assert( to_op==OP_ToNumeric || aff!=SQLITE_AFF_NUMERIC ); 2606 - assert( to_op==OP_ToInt || aff!=SQLITE_AFF_INTEGER ); 2607 - assert( to_op==OP_ToReal || aff!=SQLITE_AFF_REAL ); 2608 - testcase( to_op==OP_ToText ); 2609 - testcase( to_op==OP_ToBlob ); 2610 - testcase( to_op==OP_ToNumeric ); 2611 - testcase( to_op==OP_ToInt ); 2612 - testcase( to_op==OP_ToReal ); 2613 2599 if( inReg!=target ){ 2614 2600 sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target); 2615 2601 inReg = target; 2616 2602 } 2617 - sqlite3VdbeAddOp1(v, to_op, inReg); 2603 + sqlite3VdbeAddOp2(v, OP_Cast, target, 2604 + sqlite3AffinityType(pExpr->u.zToken, 0)); 2618 2605 testcase( usedAsColumnCache(pParse, inReg, inReg) ); 2619 2606 sqlite3ExprCacheAffinityChange(pParse, inReg, 1); 2620 2607 break; 2621 2608 } 2622 2609 #endif /* SQLITE_OMIT_CAST */ 2623 2610 case TK_LT: 2624 2611 case TK_LE:
Changes to src/vdbe.c.
1764 1764 sqlite3VdbeMemRealify(pIn1); 1765 1765 } 1766 1766 break; 1767 1767 } 1768 1768 #endif 1769 1769 1770 1770 #ifndef SQLITE_OMIT_CAST 1771 -/* Opcode: ToText P1 * * * * 1771 +/* Opcode: Cast P1 P2 * * * 1772 1772 ** 1773 -** Force the value in register P1 to be text. 1774 -** If the value is numeric, convert it to a string using the 1775 -** equivalent of sprintf(). Blob values are unchanged and 1776 -** are afterwards simply interpreted as text. 1773 +** Force the value in register P1 to be the type defined by P2. 1774 +** 1775 +** <ul> 1776 +** <li value="97"> TEXT 1777 +** <li value="98"> BLOB 1778 +** <li value="99"> NUMERIC 1779 +** <li value="100"> INTEGER 1780 +** <li value="101"> REAL 1781 +** </ul> 1777 1782 ** 1778 1783 ** A NULL value is not changed by this routine. It remains NULL. 1779 1784 */ 1780 -case OP_ToText: { /* same as TK_TO_TEXT, in1 */ 1785 +case OP_Cast: { /* in1 */ 1781 1786 pIn1 = &aMem[pOp->p1]; 1782 1787 memAboutToChange(p, pIn1); 1783 - if( pIn1->flags & MEM_Null ) break; 1784 - assert( MEM_Str==(MEM_Blob>>3) ); 1785 - pIn1->flags |= (pIn1->flags&MEM_Blob)>>3; 1786 - applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding); 1787 1788 rc = ExpandBlob(pIn1); 1788 - assert( pIn1->flags & MEM_Str || db->mallocFailed ); 1789 - pIn1->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero); 1789 + sqlite3VdbeMemCast(pIn1, pOp->p2, encoding); 1790 1790 UPDATE_MAX_BLOBSIZE(pIn1); 1791 1791 break; 1792 1792 } 1793 - 1794 -/* Opcode: ToBlob P1 * * * * 1795 -** 1796 -** Force the value in register P1 to be a BLOB. 1797 -** If the value is numeric, convert it to a string first. 1798 -** Strings are simply reinterpreted as blobs with no change 1799 -** to the underlying data. 1800 -** 1801 -** A NULL value is not changed by this routine. It remains NULL. 1802 -*/ 1803 -case OP_ToBlob: { /* same as TK_TO_BLOB, in1 */ 1804 - pIn1 = &aMem[pOp->p1]; 1805 - if( pIn1->flags & MEM_Null ) break; 1806 - if( (pIn1->flags & MEM_Blob)==0 ){ 1807 - applyAffinity(pIn1, SQLITE_AFF_TEXT, encoding); 1808 - assert( pIn1->flags & MEM_Str || db->mallocFailed ); 1809 - MemSetTypeFlag(pIn1, MEM_Blob); 1810 - }else{ 1811 - pIn1->flags &= ~(MEM_TypeMask&~MEM_Blob); 1812 - } 1813 - UPDATE_MAX_BLOBSIZE(pIn1); 1814 - break; 1815 -} 1816 - 1817 -/* Opcode: ToNumeric P1 * * * * 1818 -** 1819 -** Force the value in register P1 to be numeric (either an 1820 -** integer or a floating-point number.) 1821 -** If the value is text or blob, try to convert it to an using the 1822 -** equivalent of atoi() or atof() and store 0 if no such conversion 1823 -** is possible. 1824 -** 1825 -** A NULL value is not changed by this routine. It remains NULL. 1826 -*/ 1827 -case OP_ToNumeric: { /* same as TK_TO_NUMERIC, in1 */ 1828 - pIn1 = &aMem[pOp->p1]; 1829 - sqlite3VdbeMemNumerify(pIn1); 1830 - break; 1831 -} 1832 1793 #endif /* SQLITE_OMIT_CAST */ 1833 1794 1834 -/* Opcode: ToInt P1 * * * * 1835 -** 1836 -** Force the value in register P1 to be an integer. If 1837 -** The value is currently a real number, drop its fractional part. 1838 -** If the value is text or blob, try to convert it to an integer using the 1839 -** equivalent of atoi() and store 0 if no such conversion is possible. 1840 -** 1841 -** A NULL value is not changed by this routine. It remains NULL. 1842 -*/ 1843 -case OP_ToInt: { /* same as TK_TO_INT, in1 */ 1844 - pIn1 = &aMem[pOp->p1]; 1845 - if( (pIn1->flags & MEM_Null)==0 ){ 1846 - sqlite3VdbeMemIntegerify(pIn1); 1847 - } 1848 - break; 1849 -} 1850 - 1851 -#if !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT) 1852 -/* Opcode: ToReal P1 * * * * 1853 -** 1854 -** Force the value in register P1 to be a floating point number. 1855 -** If The value is currently an integer, convert it. 1856 -** If the value is text or blob, try to convert it to an integer using the 1857 -** equivalent of atoi() and store 0.0 if no such conversion is possible. 1858 -** 1859 -** A NULL value is not changed by this routine. It remains NULL. 1860 -*/ 1861 -case OP_ToReal: { /* same as TK_TO_REAL, in1 */ 1862 - pIn1 = &aMem[pOp->p1]; 1863 - memAboutToChange(p, pIn1); 1864 - if( (pIn1->flags & MEM_Null)==0 ){ 1865 - sqlite3VdbeMemRealify(pIn1); 1866 - } 1867 - break; 1868 -} 1869 -#endif /* !defined(SQLITE_OMIT_CAST) && !defined(SQLITE_OMIT_FLOATING_POINT) */ 1870 - 1871 1795 /* Opcode: Lt P1 P2 P3 P4 P5 1872 1796 ** Synopsis: if r[P1]<r[P3] goto P2 1873 1797 ** 1874 1798 ** Compare the values in register P1 and P3. If reg(P3)<reg(P1) then 1875 1799 ** jump to address P2. 1876 1800 ** 1877 1801 ** If the SQLITE_JUMPIFNULL bit of P5 is set and either reg(P1) or
Changes to src/vdbeInt.h.
421 421 int sqlite3VdbeMemStringify(Mem*, u8, u8); 422 422 i64 sqlite3VdbeIntValue(Mem*); 423 423 int sqlite3VdbeMemIntegerify(Mem*); 424 424 double sqlite3VdbeRealValue(Mem*); 425 425 void sqlite3VdbeIntegerAffinity(Mem*); 426 426 int sqlite3VdbeMemRealify(Mem*); 427 427 int sqlite3VdbeMemNumerify(Mem*); 428 +void sqlite3VdbeMemCast(Mem*,u8,u8); 428 429 int sqlite3VdbeMemFromBtree(BtCursor*,u32,u32,int,Mem*); 429 430 void sqlite3VdbeMemRelease(Mem *p); 430 431 void sqlite3VdbeMemReleaseExternal(Mem *p); 431 432 #define VdbeMemDynamic(X) \ 432 433 (((X)->flags&(MEM_Agg|MEM_Dyn|MEM_RowSet|MEM_Frame))!=0) 433 434 #define VdbeMemReleaseExtern(X) \ 434 435 if( VdbeMemDynamic(X) ) sqlite3VdbeMemReleaseExternal(X);
Changes to src/vdbemem.c.
517 517 sqlite3VdbeIntegerAffinity(pMem); 518 518 } 519 519 } 520 520 assert( (pMem->flags & (MEM_Int|MEM_Real|MEM_Null))!=0 ); 521 521 pMem->flags &= ~(MEM_Str|MEM_Blob); 522 522 return SQLITE_OK; 523 523 } 524 + 525 +/* 526 +** Cast the datatype of the value in pMem according to the affinity 527 +** "aff". Casting is different from applying affinity in that a cast 528 +** is forced. In other words, the value is converted into the desired 529 +** affinity even if that results in loss of data. This routine is 530 +** used (for example) to implement the SQL "cast()" operator. 531 +*/ 532 +void sqlite3VdbeMemCast(Mem *pMem, u8 aff, u8 encoding){ 533 + if( pMem->flags & MEM_Null ) return; 534 + switch( aff ){ 535 + case SQLITE_AFF_NONE: { /* Really a cast to BLOB */ 536 + if( (pMem->flags & MEM_Blob)==0 ){ 537 + sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding); 538 + assert( pMem->flags & MEM_Str || pMem->db->mallocFailed ); 539 + MemSetTypeFlag(pMem, MEM_Blob); 540 + }else{ 541 + pMem->flags &= ~(MEM_TypeMask&~MEM_Blob); 542 + } 543 + break; 544 + } 545 + case SQLITE_AFF_NUMERIC: { 546 + sqlite3VdbeMemNumerify(pMem); 547 + break; 548 + } 549 + case SQLITE_AFF_INTEGER: { 550 + sqlite3VdbeMemIntegerify(pMem); 551 + break; 552 + } 553 + case SQLITE_AFF_REAL: { 554 + sqlite3VdbeMemRealify(pMem); 555 + break; 556 + } 557 + default: { 558 + assert( aff==SQLITE_AFF_TEXT ); 559 + assert( MEM_Str==(MEM_Blob>>3) ); 560 + pMem->flags |= (pMem->flags&MEM_Blob)>>3; 561 + sqlite3ValueApplyAffinity(pMem, SQLITE_AFF_TEXT, encoding); 562 + assert( pMem->flags & MEM_Str || pMem->db->mallocFailed ); 563 + pMem->flags &= ~(MEM_Int|MEM_Real|MEM_Blob|MEM_Zero); 564 + break; 565 + } 566 + } 567 +} 568 + 524 569 525 570 /* 526 571 ** Delete any previous value and set the value stored in *pMem to NULL. 527 572 */ 528 573 void sqlite3VdbeMemSetNull(Mem *pMem){ 529 574 if( pMem->flags & MEM_Frame ){ 530 575 VdbeFrame *pFrame = pMem->u.pFrame; ................................................................................ 1011 1056 const char *zNeg = ""; 1012 1057 int rc = SQLITE_OK; 1013 1058 1014 1059 if( !pExpr ){ 1015 1060 *ppVal = 0; 1016 1061 return SQLITE_OK; 1017 1062 } 1018 - op = pExpr->op; 1063 + while( (op = pExpr->op)==TK_UPLUS ) pExpr = pExpr->pLeft; 1019 1064 if( NEVER(op==TK_REGISTER) ) op = pExpr->op2; 1065 + 1066 + if( op==TK_CAST ){ 1067 + u8 aff = sqlite3AffinityType(pExpr->u.zToken,0); 1068 + rc = valueFromExpr(db, pExpr->pLeft, enc, aff, ppVal, pCtx); 1069 + if( rc==SQLITE_OK && *ppVal ){ 1070 + sqlite3VdbeMemCast(*ppVal, aff, SQLITE_UTF8); 1071 + sqlite3ValueApplyAffinity(*ppVal, affinity, SQLITE_UTF8); 1072 + } 1073 + return rc; 1074 + } 1020 1075 1021 1076 /* Handle negative integers in a single step. This is needed in the 1022 1077 ** case when the value is -9223372036854775808. 1023 1078 */ 1024 1079 if( op==TK_UMINUS 1025 1080 && (pExpr->pLeft->op==TK_INTEGER || pExpr->pLeft->op==TK_FLOAT) ){ 1026 1081 pExpr = pExpr->pLeft;
Changes to test/alter4.test.
141 141 do_test alter4-2.6 { 142 142 catchsql { 143 143 alter table t1 add column d DEFAULT CURRENT_TIME; 144 144 } 145 145 } {1 {Cannot add a column with non-constant default}} 146 146 do_test alter4-2.7 { 147 147 catchsql { 148 - alter table t1 add column d default (-+1); 148 + alter table t1 add column d default (-5+1); 149 149 } 150 150 } {1 {Cannot add a column with non-constant default}} 151 151 do_test alter4-2.99 { 152 152 execsql { 153 153 DROP TABLE t1; 154 154 } 155 155 } {}