/ Check-in [932a3727]
Login

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

Overview
Comment:Work toward getting generated columns to work with triggers. Still more work to do in this area.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | generated-columns
Files: files | file ages | folders
SHA3-256: 932a37275d7e932f8237d32c8fc6087ed8cd342fe01ef2f7a43c7237ab84c9ac
User & Date: drh 2019-10-19 18:47:27
Context
2019-10-21
01:04
Changes to the INSERT logic to make it simpler and faster and so that it works with generated columns and BEFORE triggers. check-in: bc368cb0 user: drh tags: generated-columns
2019-10-19
18:47
Work toward getting generated columns to work with triggers. Still more work to do in this area. check-in: 932a3727 user: drh tags: generated-columns
15:01
Add testcase macros. check-in: fb9c9bb2 user: drh tags: generated-columns
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/build.c.

   897    897   ** as it appears in the record on disk.  The true column number
   898    898   ** is the index (0,1,2,...) of the column in the CREATE TABLE statement.
   899    899   **
   900    900   ** The storage column number is less than the table column number if
   901    901   ** and only there are VIRTUAL columns to the left.
   902    902   **
   903    903   ** If SQLITE_OMIT_GENERATED_COLUMNS, this routine is a no-op macro.
   904         -**
   905         -** This function is the inverse of sqlite3TableColumnToStorage().
   906    904   */
   907    905   i16 sqlite3StorageColumnToTable(Table *pTab, i16 iCol){
   908    906     if( pTab->tabFlags & TF_HasVirtual ){
   909    907       int i;
   910    908       for(i=0; i<=iCol; i++){
   911    909         if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) iCol++;
   912    910       }
................................................................................
   915    913   }
   916    914   #endif
   917    915   
   918    916   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
   919    917   /* Convert a table column number into a storage column number.
   920    918   **
   921    919   ** The storage column number (0,1,2,....) is the index of the value
   922         -** as it appears in the record on disk.  The true column number
   923         -** is the index (0,1,2,...) of the column in the CREATE TABLE statement.
          920  +** as it appears in the record on disk.  Or, if the input column is
          921  +** the N-th virtual column (zero-based) then the storage number is
          922  +** the number of non-virtual columns in the table plus N.  
   924    923   **
   925         -** If SQLITE_OMIT_GENERATED_COLUMNS, this routine is a no-op macro.
          924  +** The true column number is the index (0,1,2,...) of the column in
          925  +** the CREATE TABLE statement.
   926    926   **
   927         -** This function is the inverse of sqlite3StorageColumnToTable().
          927  +** If the input column is a VIRTUAL column, then it should not appear
          928  +** in storage.  But the value sometimes is cached in registers that
          929  +** follow the range of registers used to construct storage.  This
          930  +** avoids computing the same VIRTUAL column multiple times, and provides
          931  +** values for use by OP_Param opcodes in triggers.  Hence, if the
          932  +** input column is a VIRTUAL table, put it after all the other columns.
          933  +**
          934  +** In the following, N means "normal column", S means STORED, and
          935  +** V means VIRTUAL.  Suppose the CREATE TABLE has columns like this:
          936  +**
          937  +**        CREATE TABLE ex(N,S,V,N,S,V,N,S,V);
          938  +**                     -- 0 1 2 3 4 5 6 7 8
          939  +**
          940  +** Then the mapping from this function is as follows:
          941  +**
          942  +**    INPUTS:     0 1 2 3 4 5 6 7 8
          943  +**    OUTPUTS:    0 1 6 2 3 7 4 5 8
          944  +**
          945  +** So, in other words, this routine shifts all the virtual columns to
          946  +** the end.
          947  +**
          948  +** If SQLITE_OMIT_GENERATED_COLUMNS then there are no virtual columns and
          949  +** this routine is a no-op macro.
   928    950   */
   929    951   i16 sqlite3TableColumnToStorage(Table *pTab, i16 iCol){
   930    952     int i;
   931    953     i16 n;
   932    954     assert( iCol<pTab->nCol );
   933    955     if( (pTab->tabFlags & TF_HasVirtual)==0 ) return iCol;
   934    956     for(i=0, n=0; i<iCol; i++){
   935    957       if( (pTab->aCol[i].colFlags & COLFLAG_VIRTUAL)==0 ) n++;
   936    958     }
   937         -  return n;    
          959  +  if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){
          960  +    /* iCol is a virtual column itself */
          961  +    return pTab->nNVCol + i - n;
          962  +  }else{
          963  +    /* iCol is a normal or stored column */
          964  +    return n;
          965  +  }
   938    966   }
   939    967   #endif
   940    968   
   941    969   /*
   942    970   ** Begin constructing a new table representation in memory.  This is
   943    971   ** the first of several action routines that get called in response
   944    972   ** to a CREATE TABLE statement.  In particular, this routine is called

Changes to src/expr.c.

  3600   3600             if( pCol->colFlags & COLFLAG_GENERATED ){
  3601   3601               if( pCol->colFlags & COLFLAG_BUSY ){
  3602   3602                 sqlite3ErrorMsg(pParse, "generated column loop on \"%s\"",
  3603   3603                                 pCol->zName);
  3604   3604                 return 0;
  3605   3605               }
  3606   3606               pCol->colFlags |= COLFLAG_BUSY;
  3607         -            if( pCol->colFlags & COLFLAG_VIRTUAL ){
  3608         -              target = sqlite3ExprCodeTarget(pParse, pCol->pDflt, target);
  3609         -            }else{
  3610         -              target = iSrc;
  3611         -              if( pCol->colFlags & COLFLAG_NOTAVAIL ){
  3612         -                sqlite3ExprCode(pParse, pCol->pDflt, iSrc);
  3613         -              }
         3607  +            if( pCol->colFlags & COLFLAG_NOTAVAIL ){
         3608  +              sqlite3ExprCode(pParse, pCol->pDflt, iSrc);
  3614   3609               }
  3615   3610               pCol->colFlags &= ~(COLFLAG_BUSY|COLFLAG_NOTAVAIL);
  3616         -            return target;
         3611  +            return iSrc;
  3617   3612             }else
  3618   3613   #endif /* SQLITE_OMIT_GENERATED_COLUMNS */
  3619   3614             if( pCol->affinity==SQLITE_AFF_REAL ){
  3620   3615               sqlite3VdbeAddOp2(v, OP_SCopy, iSrc, target);
  3621   3616               sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
  3622   3617               return target;
  3623   3618             }else{
................................................................................
  4084   4079         ** Then p1 is interpreted as follows:
  4085   4080         **
  4086   4081         **   p1==0   ->    old.rowid     p1==3   ->    new.rowid
  4087   4082         **   p1==1   ->    old.a         p1==4   ->    new.a
  4088   4083         **   p1==2   ->    old.b         p1==5   ->    new.b       
  4089   4084         */
  4090   4085         Table *pTab = pExpr->y.pTab;
  4091         -      int p1 = pExpr->iTable * (pTab->nCol+1) + 1 + pExpr->iColumn;
         4086  +      int iCol = pExpr->iColumn;
         4087  +      int p1 = pExpr->iTable * (pTab->nCol+1) + 1 
         4088  +                     + (iCol>=0 ? sqlite3TableColumnToStorage(pTab, iCol) : -1);
  4092   4089   
  4093   4090         assert( pExpr->iTable==0 || pExpr->iTable==1 );
  4094         -      assert( pExpr->iColumn>=-1 && pExpr->iColumn<pTab->nCol );
  4095         -      assert( pTab->iPKey<0 || pExpr->iColumn!=pTab->iPKey );
         4091  +      assert( iCol>=-1 && iCol<pTab->nCol );
         4092  +      assert( pTab->iPKey<0 || iCol!=pTab->iPKey );
  4096   4093         assert( p1>=0 && p1<(pTab->nCol*2+2) );
  4097   4094   
  4098   4095         sqlite3VdbeAddOp2(v, OP_Param, p1, target);
  4099   4096         VdbeComment((v, "r[%d]=%s.%s", target,
  4100   4097           (pExpr->iTable ? "new" : "old"),
  4101         -        (pExpr->iColumn<0 ? "rowid" : pExpr->y.pTab->aCol[pExpr->iColumn].zName)
         4098  +        (pExpr->iColumn<0 ? "rowid" : pExpr->y.pTab->aCol[iCol].zName)
  4102   4099         ));
  4103   4100   
  4104   4101   #ifndef SQLITE_OMIT_FLOATING_POINT
  4105   4102         /* If the column has REAL affinity, it may currently be stored as an
  4106   4103         ** integer. Use OP_RealAffinity to make sure it is really real.
  4107   4104         **
  4108   4105         ** EVIDENCE-OF: R-60985-57662 SQLite will convert the value back to
  4109   4106         ** floating point when extracting it from the record.  */
  4110         -      if( pExpr->iColumn>=0 
  4111         -       && pTab->aCol[pExpr->iColumn].affinity==SQLITE_AFF_REAL
  4112         -      ){
         4107  +      if( iCol>=0 && pTab->aCol[iCol].affinity==SQLITE_AFF_REAL ){
  4113   4108           sqlite3VdbeAddOp1(v, OP_RealAffinity, target);
  4114   4109         }
  4115   4110   #endif
  4116   4111         break;
  4117   4112       }
  4118   4113   
  4119   4114       case TK_VECTOR: {

Changes to src/insert.c.

   201    201     return 0;
   202    202   }
   203    203   
   204    204   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
   205    205   /*
   206    206   ** All regular columns for table pTab have been puts into registers
   207    207   ** starting with iRegStore.  The registers that correspond to STORED
   208         -** columns have not been initialized.  This routine goes back and computes
   209         -** the values for STORED columns based on the previously computed normal
   210         -** columns.
          208  +** or VIRTUAL columns have not yet been initialized.  This routine goes
          209  +** back and computes the values for those columns based on the previously
          210  +** computed normal columns.
   211    211   */
   212         -void sqlite3ComputeStoredColumns(
          212  +void sqlite3ComputeGeneratedColumns(
   213    213     Parse *pParse,    /* Parsing context */
   214    214     int iRegStore,    /* Register holding the first column */
   215    215     Table *pTab       /* The table */
   216    216   ){
   217    217     int i;
   218         -  /* Because there can be multiple STORED columns that refer to one another,
   219         -  ** either directly or through VIRTUAL columns, this is a two pass
   220         -  ** algorithm.  On the first pass, mark all STORED columns as NOT-AVAILABLE.
          218  +  int nv;
          219  +  /* Because there can be multiple generated columns that refer to one another,
          220  +  ** this is a two-pass algorithm.  On the first pass, mark all generated
          221  +  ** columns as "not available".
   221    222     */
   222    223     for(i=0; i<pTab->nCol; i++){
   223         -    if( pTab->aCol[i].colFlags & COLFLAG_STORED ){
          224  +    if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
   224    225         pTab->aCol[i].colFlags |= COLFLAG_NOTAVAIL;
   225    226       }
   226    227     }
   227    228     /* On the second pass, compute the value of each NOT-AVAILABLE column.
   228    229     ** Companion code in the TK_COLUMN case of sqlite3ExprCodeTarget() will
   229    230     ** compute dependencies and mark remove the COLSPAN_NOTAVAIL mark, as
   230    231     ** they are needed.
   231    232     */
   232    233     pParse->iSelfTab = -iRegStore;
   233         -  for(i=0; i<pTab->nCol; i++, iRegStore++){
          234  +  for(i=nv=0; i<pTab->nCol; i++){
   234    235       u32 colFlags = pTab->aCol[i].colFlags;
   235         -    if( (colFlags & COLFLAG_VIRTUAL)!=0 ){
   236         -      /* Virtual columns are not stored */
   237         -      iRegStore--;
   238         -    }else if( (colFlags & COLFLAG_NOTAVAIL)!=0 ){
   239         -      /* Stored columns are handled on the second pass */
   240         -      sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore);
          236  +    if( (colFlags & COLFLAG_NOTAVAIL)!=0 ){
          237  +      assert( colFlags & COLFLAG_GENERATED );
          238  +      if( colFlags & COLFLAG_VIRTUAL ){
          239  +        /* Virtual columns go at the end */
          240  +        assert( pTab->nNVCol+nv == sqlite3TableColumnToStorage(pTab,i) );
          241  +        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt,
          242  +                        iRegStore+pTab->nNVCol+nv);
          243  +      }else{
          244  +        /* Stored columns go in column order */
          245  +        assert( i-nv == sqlite3TableColumnToStorage(pTab,i) );
          246  +        sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iRegStore+i-nv);
          247  +      }
   241    248         colFlags &= ~COLFLAG_NOTAVAIL;
   242    249       }
          250  +    if( (colFlags & COLFLAG_VIRTUAL)!=0 ) nv++;
   243    251     }
   244    252     pParse->iSelfTab = 0;
   245    253   }
   246    254   #endif /* SQLITE_OMIT_GENERATED_COLUMNS */
   247    255   
   248    256   
   249    257   #ifndef SQLITE_OMIT_AUTOINCREMENT
................................................................................
  1051   1059       */
  1052   1060       nHidden = 0;
  1053   1061       iRegStore = regRowid+1;
  1054   1062       for(i=0; i<pTab->nCol; i++, iRegStore++){
  1055   1063         int k;
  1056   1064         u32 colFlags;
  1057   1065         assert( i>=nHidden );
  1058         -      assert( iRegStore==sqlite3TableColumnToStorage(pTab,i)+regRowid+1 );
  1059   1066         if( i==pTab->iPKey ){
  1060   1067           /* The value of the INTEGER PRIMARY KEY column is always a NULL.
  1061   1068           ** Whenever this column is read, the rowid will be substituted
  1062   1069           ** in its place.  Hence, fill this column with a NULL to avoid
  1063   1070           ** taking up data space with information that will never be used.
  1064   1071           ** As there may be shallow copies of this value, make it a soft-NULL */
  1065   1072           sqlite3VdbeAddOp1(v, OP_SoftNull, iRegStore);
................................................................................
  1104   1111           }
  1105   1112         }else{
  1106   1113           sqlite3ExprCode(pParse, pList->a[k].pExpr, iRegStore);
  1107   1114         }
  1108   1115       }
  1109   1116   
  1110   1117   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
  1111         -    /* Compute the new value for STORED columns after all other
         1118  +    /* Compute the new value for generated columns after all other
  1112   1119       ** columns have already been computed */
  1113         -    if( pTab->tabFlags & TF_HasStored ){
  1114         -      sqlite3ComputeStoredColumns(pParse, regRowid+1, pTab);
         1120  +    if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
         1121  +      sqlite3ComputeGeneratedColumns(pParse, regRowid+1, pTab);
  1115   1122       }
  1116   1123   #endif
  1117   1124   
  1118   1125       /* Generate code to check constraints and generate index keys and
  1119   1126       ** do the insertion.
  1120   1127       */
  1121   1128   #ifndef SQLITE_OMIT_VIRTUALTABLE
................................................................................
  1450   1457       }
  1451   1458       if( onError==OE_Replace && pTab->aCol[i].pDflt==0 ){
  1452   1459         onError = OE_Abort;
  1453   1460       }
  1454   1461       assert( onError==OE_Rollback || onError==OE_Abort || onError==OE_Fail
  1455   1462           || onError==OE_Ignore || onError==OE_Replace );
  1456   1463       addr1 = 0;
  1457         -    if( (pTab->tabFlags & TF_HasVirtual)==0 ){
  1458         -      iReg = regNewData+1+i;
  1459         -    }else if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ){
  1460         -      iReg = ++pParse->nMem;
  1461         -      assert( pParse->iSelfTab==0 );
  1462         -      pParse->iSelfTab = -regNewData;
  1463         -      sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, iReg);
  1464         -      pParse->iSelfTab = 0;
  1465         -      if( onError==OE_Replace ) onError = OE_Abort;
  1466         -    }else{
  1467         -      testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
  1468         -      iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
  1469         -    }
         1464  +    testcase( i!=sqlite3TableColumnToStorage(pTab, i) );
         1465  +    testcase( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL );
         1466  +    testcase( pTab->aCol[i].colFlags & COLFLAG_STORED );
         1467  +    testcase( pTab->aCol[i].colFlags & COLFLAG_GENERATED );
         1468  +    iReg = sqlite3TableColumnToStorage(pTab, i) + regNewData + 1;
  1470   1469       switch( onError ){
  1471   1470         case OE_Replace: {
  1472   1471           assert( onError==OE_Replace );
  1473   1472           addr1 = sqlite3VdbeMakeLabel(pParse);
  1474   1473           sqlite3VdbeAddOp2(v, OP_NotNull, iReg, addr1);
  1475   1474             VdbeCoverage(v);
  1476   1475           sqlite3ExprCode(pParse, pTab->aCol[i].pDflt, regNewData+1+i);

Changes to src/sqliteInt.h.

  4040   4040     void sqlite3AutoincrementEnd(Parse *pParse);
  4041   4041   #else
  4042   4042   # define sqlite3AutoincrementBegin(X)
  4043   4043   # define sqlite3AutoincrementEnd(X)
  4044   4044   #endif
  4045   4045   void sqlite3Insert(Parse*, SrcList*, Select*, IdList*, int, Upsert*);
  4046   4046   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
  4047         -  void sqlite3ComputeStoredColumns(Parse*, int, Table*);
         4047  +  void sqlite3ComputeGeneratedColumns(Parse*, int, Table*);
  4048   4048   #endif
  4049   4049   void *sqlite3ArrayAllocate(sqlite3*,void*,int,int*,int*);
  4050   4050   IdList *sqlite3IdListAppend(Parse*, IdList*, Token*);
  4051   4051   int sqlite3IdListIndex(IdList*,const char*);
  4052   4052   SrcList *sqlite3SrcListEnlarge(Parse*, SrcList*, int, int);
  4053   4053   SrcList *sqlite3SrcListAppend(Parse*, SrcList*, Token*, Token*);
  4054   4054   SrcList *sqlite3SrcListAppendFromTerm(Parse*, SrcList*, Token*, Token*,

Changes to src/update.c.

   689    689           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
   690    690         }else{
   691    691           sqlite3VdbeAddOp2(v, OP_Null, 0, k);
   692    692         }
   693    693       }
   694    694     }
   695    695   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
   696         -  if( pTab->tabFlags & TF_HasStored ){
   697         -    sqlite3ComputeStoredColumns(pParse, regNew, pTab);
          696  +  if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
          697  +    sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
   698    698     }
   699    699   #endif
   700    700   
   701    701     /* Fire any BEFORE UPDATE triggers. This happens before constraints are
   702    702     ** verified. One could argue that this is wrong.
   703    703     */
   704    704     if( tmask&TRIGGER_BEFORE ){
................................................................................
   733    733         if( pTab->aCol[i].colFlags & COLFLAG_GENERATED ){
   734    734           if( pTab->aCol[i].colFlags & COLFLAG_VIRTUAL ) k--;
   735    735         }else if( aXRef[i]<0 && i!=pTab->iPKey ){
   736    736           sqlite3ExprCodeGetColumnOfTable(v, pTab, iDataCur, i, k);
   737    737         }
   738    738       }
   739    739   #ifndef SQLITE_OMIT_GENERATED_COLUMNS
   740         -    if( pTab->tabFlags & TF_HasStored ){
   741         -      sqlite3ComputeStoredColumns(pParse, regNew, pTab);
          740  +    if( pTab->tabFlags & (TF_HasStored|TF_HasVirtual) ){
          741  +      sqlite3ComputeGeneratedColumns(pParse, regNew, pTab);
   742    742       }
   743    743   #endif 
   744    744     }
   745    745   
   746    746     if( !isView ){
   747    747       /* Do constraint checks. */
   748    748       assert( regOldRowid>0 );