Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | New testcase() macros. Fix a problem with INSERT when the IPK is to the right of generated columns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | generated-columns |
| Files: | files | file ages | folders |
| SHA3-256: |
412799fc5527aaca987e4e04b8a4f774 |
| User & Date: | drh 2019-10-22 13:01:24 |
|
2019-10-22
| ||
| 13:59 | In UPDATE processing, include generated columns in the set of columns being updated if and only if their generator expressions reference some other column that is being updated. (check-in: d38176e9 user: drh tags: generated-columns) | |
| 13:01 | New testcase() macros. Fix a problem with INSERT when the IPK is to the right of generated columns. (check-in: 412799fc user: drh tags: generated-columns) | |
| 12:02 | Merge changes on trunk into the generated-columns branch. (check-in: ba123b8c user: drh tags: generated-columns) | |
Changes to src/build.c.
2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 |
/* Resolve names in all CHECK constraint expressions.
*/
if( p->pCheck ){
sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( p->tabFlags & (TF_HasVirtual|TF_HasStored) ){
int ii;
for(ii=0; ii<p->nCol; ii++){
u32 colFlags = p->aCol[ii].colFlags;
if( (colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL))!=0 ){
sqlite3ResolveSelfReference(pParse, p, NC_GenCol,
p->aCol[ii].pDflt, 0);
}
}
}
#endif
|
| > > < > > > |
2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224 2225 |
/* Resolve names in all CHECK constraint expressions.
*/
if( p->pCheck ){
sqlite3ResolveSelfReference(pParse, p, NC_IsCheck, 0, p->pCheck);
}
#endif /* !defined(SQLITE_OMIT_CHECK) */
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( p->tabFlags & TF_HasGenerated ){
int ii;
testcase( p->tabFlags & TF_HasVirtual );
testcase( p->tabFlags & TF_HasStored );
for(ii=0; ii<p->nCol; ii++){
u32 colFlags = p->aCol[ii].colFlags;
if( (colFlags & COLFLAG_GENERATED)!=0 ){
testcase( colFlags & COLFLAG_VIRTUAL );
testcase( colFlags & COLFLAG_STORED );
sqlite3ResolveSelfReference(pParse, p, NC_GenCol,
p->aCol[ii].pDflt, 0);
}
}
}
#endif
|
Changes to src/insert.c.
218 219 220 221 222 223 224 225 226 227 228 229 230 231 ... 850 851 852 853 854 855 856 857 858 859 860 861 862 863 .... 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 .... 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 .... 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 .... 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 |
int nv;
/* Because there can be multiple generated columns that refer to one another,
** this is a two-pass algorithm. On the first pass, mark all generated
** columns as "not available".
*/
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;
}
}
/* On the second pass, compute the value of each NOT-AVAILABLE column.
** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
** they are needed.
................................................................................
/* If there is no IDLIST term but the table has an integer primary
** key, the set the ipkColumn variable to the integer primary key
** column index in the original table definition.
*/
if( pColumn==0 && nColumn>0 ){
ipkColumn = pTab->iPKey;
}
/* Make sure the number of columns in the source data matches the number
** of columns to be inserted into the table.
*/
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++;
................................................................................
sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* Compute the new value for generated columns after all other
** columns have already been computed. This must be done after
** computing the ROWID in case one of the generated columns
** refers to the ROWID. */
if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
sqlite3ComputeGeneratedColumns(pParse, regCols+1, pTab);
}
#endif
/* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
** do not attempt any conversions before assembling the record.
** If this is a real table, attempt conversions as required by the
................................................................................
autoIncStep(pParse, regAutoinc, regRowid);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* Compute the new value for generated columns after all other
** columns have already been computed. This must be done after
** computing the ROWID in case one of the generated columns
** refers to the ROWID. */
if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab);
}
#endif
/* Generate code to check constraints and generate index keys and
** do the insertion.
*/
................................................................................
}
assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
|| onError==OE_Ignore || onError==OE_Replace );
addr1 = 0;
testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
testcase( pTab->aCol[i].colFlags & COLFLAG_GENERATED );
iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
switch( onError ){
case OE_Replace: {
assert( onError==OE_Replace );
addr1 = sqlite3VdbeMakeLabel(pParse);
sqlite3VdbeAddOp2(v, OP_NotNull, iReg, addr1);
VdbeCoverage(v);
................................................................................
){
return 0; /* Default values must be the same for all columns */
}
}
/* Generator expressions for generated columns must match */
if( (pDestCol->colFlags & COLFLAG_GENERATED)!=0 ){
if( sqlite3ExprCompare(0, pSrcCol->pDflt, pDestCol->pDflt, -1)!=0 ){
return 0; /* Different generator expressions */
}
}
}
for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
if( IsUniqueIndex(pDestIdx) ){
destHasUniqueIdx = 1;
}
|
> > > > > > > > > > > > > > > | > > | > > < > > | |
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 ... 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 .... 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 .... 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 .... 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 .... 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 |
int nv;
/* Because there can be multiple generated columns that refer to one another,
** this is a two-pass algorithm. On the first pass, mark all generated
** columns as "not available".
*/
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
testcase( pTab->aCol[i].colflags & COLFLAG_VIRTUAL );
testcase( pTab->aCol[i].colflags & COLFLAG_STORED );
pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;
}
}
/* On the second pass, compute the value of each NOT-AVAILABLE column.
** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
** they are needed.
................................................................................
/* If there is no IDLIST term but the table has an integer primary
** key, the set the ipkColumn variable to the integer primary key
** column index in the original table definition.
*/
if( pColumn==0 && nColumn>0 ){
ipkColumn = pTab->iPKey;
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->tabFlags & TF_HasGenerated ){
testcase( pTab->tabFlags & TF_HasVirtual );
testcase( pTab->tabFlags & TF_HasGenerated );
for(i=ipkColumn-1; i>=0; i--){
if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
testcase( pTab->aCol[i].colFlags & COLFLAG_GENERATED );
ipkColumn--;
}
}
}
#endif
}
/* Make sure the number of columns in the source data matches the number
** of columns to be inserted into the table.
*/
for(i=0; i<pTab->nCol; i++){
if( pTab->aCol[i].colFlags & COLFLAG_NOINSERT ) nHidden++;
................................................................................
sqlite3VdbeAddOp3(v, OP_Copy, regRowid+1, regCols+1, pTab->nNVCol-1);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* Compute the new value for generated columns after all other
** columns have already been computed. This must be done after
** computing the ROWID in case one of the generated columns
** refers to the ROWID. */
if( pTab->tabFlags & TF_HasGenerated ){
testcase( pTab->tabFlags & TF_HasVirtual );
testcase( pTab->tabFlags & TF_HasStored );
sqlite3ComputeGeneratedColumns(pParse, regCols+1, pTab);
}
#endif
/* If this is an INSERT on a view with an INSTEAD OF INSERT trigger,
** do not attempt any conversions before assembling the record.
** If this is a real table, attempt conversions as required by the
................................................................................
autoIncStep(pParse, regAutoinc, regRowid);
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
/* Compute the new value for generated columns after all other
** columns have already been computed. This must be done after
** computing the ROWID in case one of the generated columns
** refers to the ROWID. */
if( pTab->tabFlags & TF_HasGenerated ){
testcase( pTab->tabFlags & TF_HasVirtual );
testcase( pTab->tabFlags & TF_HasStored );
sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab);
}
#endif
/* Generate code to check constraints and generate index keys and
** do the insertion.
*/
................................................................................
}
assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
|| onError==OE_Ignore || onError==OE_Replace );
addr1 = 0;
testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
switch( onError ){
case OE_Replace: {
assert( onError==OE_Replace );
addr1 = sqlite3VdbeMakeLabel(pParse);
sqlite3VdbeAddOp2(v, OP_NotNull, iReg, addr1);
VdbeCoverage(v);
................................................................................
){
return 0; /* Default values must be the same for all columns */
}
}
/* Generator expressions for generated columns must match */
if( (pDestCol->colFlags & COLFLAG_GENERATED)!=0 ){
if( sqlite3ExprCompare(0, pSrcCol->pDflt, pDestCol->pDflt, -1)!=0 ){
testcase( pDestCol->colflags & COLFLAG_VIRTUAL );
testcase( pDestCol->colflags & COLFLAG_STORED );
return 0; /* Different generator expressions */
}
}
}
for(pDestIdx=pDest->pIndex; pDestIdx; pDestIdx=pDestIdx->pNext){
if( IsUniqueIndex(pDestIdx) ){
destHasUniqueIdx = 1;
}
|
Changes to src/sqliteInt.h.
2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 |
#define TF_Readonly 0x0001 /* Read-only system table */
#define TF_Ephemeral 0x0002 /* An ephemeral table */
#define TF_HasPrimaryKey 0x0004 /* Table has a primary key */
#define TF_Autoincrement 0x0008 /* Integer primary key is autoincrement */
#define TF_HasStat1 0x0010 /* nRowLogEst set from sqlite_stat1 */
#define TF_HasVirtual 0x0020 /* Has one or more VIRTUAL columns */
#define TF_HasStored 0x0040 /* Has one or more STORED columns */
#define TF_WithoutRowid 0x0080 /* No rowid. PRIMARY KEY is the key */
#define TF_StatsUsed 0x0100 /* Query planner decisions affected by
** Index.aiRowLogEst[] values */
#define TF_NoVisibleRowid 0x0200 /* No user-visible "rowid" column */
#define TF_OOOHidden 0x0400 /* Out-of-Order hidden columns */
#define TF_HasNotNull 0x0800 /* Contains NOT NULL constraints */
#define TF_Shadow 0x1000 /* True for a shadow table */
|
> |
2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 |
#define TF_Readonly 0x0001 /* Read-only system table */
#define TF_Ephemeral 0x0002 /* An ephemeral table */
#define TF_HasPrimaryKey 0x0004 /* Table has a primary key */
#define TF_Autoincrement 0x0008 /* Integer primary key is autoincrement */
#define TF_HasStat1 0x0010 /* nRowLogEst set from sqlite_stat1 */
#define TF_HasVirtual 0x0020 /* Has one or more VIRTUAL columns */
#define TF_HasStored 0x0040 /* Has one or more STORED columns */
#define TF_HasGenerated 0x0060 /* Combo: HasVirtual + HasStored */
#define TF_WithoutRowid 0x0080 /* No rowid. PRIMARY KEY is the key */
#define TF_StatsUsed 0x0100 /* Query planner decisions affected by
** Index.aiRowLogEst[] values */
#define TF_NoVisibleRowid 0x0200 /* No user-visible "rowid" column */
#define TF_OOOHidden 0x0400 /* Out-of-Order hidden columns */
#define TF_HasNotNull 0x0800 /* Contains NOT NULL constraints */
#define TF_Shadow 0x1000 /* True for a shadow table */
|
Changes to src/update.c.
309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 ... 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 ... 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 |
if( j==pTab->iPKey ){
chngRowid = 1;
pRowidExpr = pChanges->a[i].pExpr;
}else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){
chngPk = 1;
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
else if( pTab->aCol[j].colFlags & (COLFLAG_STORED|COLFLAG_VIRTUAL) ){
sqlite3ErrorMsg(pParse,
"cannot UPDATE generated column \"%s\"",
pTab->aCol[j].zName);
goto update_cleanup;
}
#endif
aXRef[j] = i;
................................................................................
sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
}else{
sqlite3VdbeAddOp2(v, OP_Null, 0, k);
}
}
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
}
#endif
/* Fire any BEFORE UPDATE triggers. This happens before constraints are
** verified. One could argue that this is wrong.
*/
................................................................................
if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--;
}else if( aXRef[i]<0 && i!=pTab->iPKey ){
sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
}
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
}
#endif
}
if( !isView ){
/* Do constraint checks. */
|
| > > | > > | > > |
309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 ... 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 ... 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 |
if( j==pTab->iPKey ){
chngRowid = 1;
pRowidExpr = pChanges->a[i].pExpr;
}else if( pPk && (pTab->aCol[j].colFlags & COLFLAG_PRIMKEY)!=0 ){
chngPk = 1;
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
else if( pTab->aCol[j].colFlags & COLFLAG_GENERATED ){
testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
sqlite3ErrorMsg(pParse,
"cannot UPDATE generated column \"%s\"",
pTab->aCol[j].zName);
goto update_cleanup;
}
#endif
aXRef[j] = i;
................................................................................
sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
}else{
sqlite3VdbeAddOp2(v, OP_Null, 0, k);
}
}
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->tabFlags & TF_HasGenerated ){
testcase( pTab->tabFlags & TF_HasVirtual );
testcase( pTab->tabFlags & TF_HasStored );
sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
}
#endif
/* Fire any BEFORE UPDATE triggers. This happens before constraints are
** verified. One could argue that this is wrong.
*/
................................................................................
if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--;
}else if( aXRef[i]<0 && i!=pTab->iPKey ){
sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
}
}
#ifndef SQLITE_OMIT_GENERATED_COLUMNS
if( pTab->tabFlags & TF_HasGenerated ){
testcase( pTab->tabFlags & TF_HasVirtual );
testcase( pTab->tabFlags & TF_HasStored );
sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
}
#endif
}
if( !isView ){
/* Do constraint checks. */
|