SQLite

Check-in [bcb0bc6a]
Login

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

Overview
Comment:Fix UPDATE FROM statements on virtual tables that are declared WITHOUT ROWID. See forum post 0fee7951eb for more information.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: bcb0bc6a7b7006f07adb7266b1fecca39bf85a0adea6d78a341623a3546f2c2a
User & Date: dan 2020-10-12 14:29:11
Original Comment: Fix UPDATE FROM statements on virtual tables that are declared WITHOUT ROWID.
Context
2020-10-12
14:56
Add the --nosys option to the .schema command in the CLI. (check-in: b72ee694 user: drh tags: trunk)
14:29
Fix UPDATE FROM statements on virtual tables that are declared WITHOUT ROWID. See forum post 0fee7951eb for more information. (check-in: bcb0bc6a user: dan tags: trunk)
13:24
Fix BEGIN IMMEDIATE and BEGIN EXCLUSIVE so that they work even if one or more of the database files in the connection are read-only. Test cases for this are in TH3. (check-in: 2fa08c39 user: drh tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/update.c.

︙︙
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
      if( pLimit ){
        pGrp = sqlite3ExprListAppend(pParse, pGrp, sqlite3ExprDup(db, pNew, 0));
      }
#endif
      pList = sqlite3ExprListAppend(pParse, pList, pNew);
    }
    eDest = SRT_Upfrom;
  }else if( pTab->pSelect ){
    for(i=0; i<pTab->nCol; i++){
      pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i));
    }
    eDest = SRT_Table;
  }else{
    eDest = IsVirtual(pTab) ? SRT_Table : SRT_Upfrom;








|








230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

#ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
      if( pLimit ){
        pGrp = sqlite3ExprListAppend(pParse, pGrp, sqlite3ExprDup(db, pNew, 0));
      }
#endif
      pList = sqlite3ExprListAppend(pParse, pList, pNew);
    }
    eDest = IsVirtual(pTab) ? SRT_Table : SRT_Upfrom;
  }else if( pTab->pSelect ){
    for(i=0; i<pTab->nCol; i++){
      pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i));
    }
    eDest = SRT_Table;
  }else{
    eDest = IsVirtual(pTab) ? SRT_Table : SRT_Upfrom;
︙︙
1183
1184
1185
1186
1187
1188
1189

1190
1191

1192
1193
1194
1195












1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216

  ** these arguments will be temporarily stored. */
  assert( v );
  ephemTab = pParse->nTab++;
  addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg);
  regArg = pParse->nMem + 1;
  pParse->nMem += nArg;
  if( pSrc->nSrc>1 ){

    Expr *pRow;
    ExprList *pList;

    if( pRowid ){
      pRow = sqlite3ExprDup(db, pRowid, 0);
    }else{
      pRow = sqlite3PExpr(pParse, TK_ROW, 0, 0);












    }
    pList = sqlite3ExprListAppend(pParse, 0, pRow);

    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]>=0 ){
        pList = sqlite3ExprListAppend(pParse, pList,
          sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0)
        );
      }else{
        pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i));
      }
    }

    updateFromSelect(pParse, ephemTab, 0, pList, pSrc, pWhere, 0, 0);
    sqlite3ExprListDelete(db, pList);
    eOnePass = ONEPASS_OFF;
  }else{
    regRec = ++pParse->nMem;
    regRowid = ++pParse->nMem;

    /* Start scanning the virtual table */








>


>
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>













|








1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230

  ** these arguments will be temporarily stored. */
  assert( v );
  ephemTab = pParse->nTab++;
  addr= sqlite3VdbeAddOp2(v, OP_OpenEphemeral, ephemTab, nArg);
  regArg = pParse->nMem + 1;
  pParse->nMem += nArg;
  if( pSrc->nSrc>1 ){
    Index *pPk = 0;
    Expr *pRow;
    ExprList *pList;
    if( HasRowid(pTab) ){
      if( pRowid ){
        pRow = sqlite3ExprDup(db, pRowid, 0);
      }else{
        pRow = sqlite3PExpr(pParse, TK_ROW, 0, 0);
      }
    }else{
      i16 iPk;      /* PRIMARY KEY column */
      pPk = sqlite3PrimaryKeyIndex(pTab);
      assert( pPk!=0 );
      assert( pPk->nKeyCol==1 );
      iPk = pPk->aiColumn[0];
      if( aXRef[iPk]>=0 ){
        pRow = sqlite3ExprDup(db, pChanges->a[aXRef[iPk]].pExpr, 0);
      }else{
        pRow = exprRowColumn(pParse, iPk);
      }
    }
    pList = sqlite3ExprListAppend(pParse, 0, pRow);

    for(i=0; i<pTab->nCol; i++){
      if( aXRef[i]>=0 ){
        pList = sqlite3ExprListAppend(pParse, pList,
          sqlite3ExprDup(db, pChanges->a[aXRef[i]].pExpr, 0)
        );
      }else{
        pList = sqlite3ExprListAppend(pParse, pList, exprRowColumn(pParse, i));
      }
    }

    updateFromSelect(pParse, ephemTab, pPk, pList, pSrc, pWhere, 0, 0);
    sqlite3ExprListDelete(db, pList);
    eOnePass = ONEPASS_OFF;
  }else{
    regRec = ++pParse->nMem;
    regRowid = ++pParse->nMem;

    /* Start scanning the virtual table */
︙︙

Changes to test/vtabJ.test.

︙︙
117
118
119
120
121
122
123




























124
125
126

  SELECT name, value FROM tclvar where name = 'xx';
} {xx att}

do_execsql_test 181 {
  DELETE FROM tclvar WHERE name BETWEEN 'xx' AND 'xx' OR name='xx';
  SELECT name, value FROM tclvar where name = 'xx';
} {}






























finish_test








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

  SELECT name, value FROM tclvar where name = 'xx';
} {xx att}

do_execsql_test 181 {
  DELETE FROM tclvar WHERE name BETWEEN 'xx' AND 'xx' OR name='xx';
  SELECT name, value FROM tclvar where name = 'xx';
} {}

#-------------------------------------------------------------------------

do_execsql_test 200 {
  CREATE TABLE var(k TEXT, v TEXT);
  INSERT INTO var VALUES('testvar1', 10);
  INSERT INTO var VALUES('testvar2', 20);
  INSERT INTO var VALUES('testvar3', 30);
}

do_test 210 {
  foreach {testvar1 testvar2 testvar3} {1 2 3} {}
  execsql {
    UPDATE tclvar SET value = var.v FROM var WHERE name = var.k;
  }
  list $testvar1 $testvar2 $testvar3
} {10 20 30}

do_test 220 {
  execsql {
    CREATE TABLE nam(k TEXT, v TEXT);
    INSERT INTO nam VALUES('testvar1', 'tv1');
    INSERT INTO nam VALUES('testvar2', 'tv2');
    INSERT INTO nam VALUES('testvar3', 'tv3');
    UPDATE tclvar SET fullname = nam.v FROM nam WHERE name = nam.k;
  }
  list $tv1 $tv2 $tv3
} {10 20 30}


finish_test