/ Check-in [bb2f7234]
Login

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

Overview
Comment:Fix a problem with renaming a column that occurs as an "excluded.colname" construction in an UPSERT that is part of a trigger program.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | alter-table-rename-column
Files: files | file ages | folders
SHA3-256:bb2f7234968157b605522dea8e115d8772ab4234147894089d6e48fedf5d6a5d
User & Date: dan 2018-08-24 20:10:22
Context
2018-08-24
20:20
Fix a problem by renaming a column when the schema contains an trigger program featuring an UPSERT that uses an "excluded.*" reference to a different table. check-in: 83d5f5ab user: dan tags: alter-table-rename-column
20:10
Fix a problem with renaming a column that occurs as an "excluded.colname" construction in an UPSERT that is part of a trigger program. check-in: bb2f7234 user: dan tags: alter-table-rename-column
17:55
After modifying and reparsing the schema as part of an ALTER TABLE RENAME COLUMN, check that no new schema errors have been introduced (e.g. ambiguous column names in views) before committing the operation. check-in: a0e06d2c user: dan tags: alter-table-rename-column
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

1357
1358
1359
1360
1361
1362
1363





1364
1365
1366
1367
1368
1369
1370
....
1371
1372
1373
1374
1375
1376
1377

1378
1379
1380
1381
1382
1383
1384
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
          }
          assert( !pStep->pUpsert || (!pStep->pWhere && !pStep->pExprList) );
          if( pStep->pUpsert ){
            Upsert *pUpsert = pStep->pUpsert;
            assert( rc==SQLITE_OK );





            rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
            if( rc==SQLITE_OK ){
              ExprList *pUpsertSet = pUpsert->pUpsertSet;
              if( pTarget==pTab ){
                renameColumnElistNames(&sParse, &sCtx, pUpsertSet, zOld);
              }
              rc = sqlite3ResolveExprListNames(&sNC, pUpsertSet);
................................................................................
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
            }

          }

          if( rc==SQLITE_OK && pTarget==pTab ){
            renameColumnIdlistNames(&sParse, &sCtx, pStep->pIdList, zOld);
            renameColumnElistNames(&sParse, &sCtx, pStep->pExprList, zOld);
          }
        }







>
>
>
>
>







 







>







1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
....
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
          if( rc==SQLITE_OK ){
            rc = sqlite3ResolveExprListNames(&sNC, pStep->pExprList);
          }
          assert( !pStep->pUpsert || (!pStep->pWhere && !pStep->pExprList) );
          if( pStep->pUpsert ){
            Upsert *pUpsert = pStep->pUpsert;
            assert( rc==SQLITE_OK );
            if( pTarget==pTab ){
              pUpsert->pUpsertSrc = &sSrc;
              sNC.uNC.pUpsert = pUpsert;
              sNC.ncFlags = NC_UUpsert;
            }
            rc = sqlite3ResolveExprListNames(&sNC, pUpsert->pUpsertTarget);
            if( rc==SQLITE_OK ){
              ExprList *pUpsertSet = pUpsert->pUpsertSet;
              if( pTarget==pTab ){
                renameColumnElistNames(&sParse, &sCtx, pUpsertSet, zOld);
              }
              rc = sqlite3ResolveExprListNames(&sNC, pUpsertSet);
................................................................................
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertWhere);
            }
            if( rc==SQLITE_OK ){
              rc = sqlite3ResolveExprNames(&sNC, pUpsert->pUpsertTargetWhere);
            }
            sNC.ncFlags = 0;
          }

          if( rc==SQLITE_OK && pTarget==pTab ){
            renameColumnIdlistNames(&sParse, &sCtx, pStep->pIdList, zOld);
            renameColumnElistNames(&sParse, &sCtx, pStep->pExprList, zOld);
          }
        }

Changes to src/resolve.c.

345
346
347
348
349
350
351





352
353
354

355
356
357
358
359
360
361
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
#ifndef SQLITE_OMIT_UPSERT
          if( pExpr->iTable==2 ){
            testcase( iCol==(-1) );





            pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
            eNewExprOp = TK_REGISTER;
            ExprSetProperty(pExpr, EP_Alias);

          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
#ifndef SQLITE_OMIT_TRIGGER
            if( iCol<0 ){
              pExpr->affinity = SQLITE_AFF_INTEGER;
            }else if( pExpr->iTable==0 ){







>
>
>
>
>
|
|
|
>







345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
          iCol = -1;
        }
        if( iCol<pTab->nCol ){
          cnt++;
#ifndef SQLITE_OMIT_UPSERT
          if( pExpr->iTable==2 ){
            testcase( iCol==(-1) );
            if( IN_RENAME_COLUMN ){
              pExpr->iColumn = iCol;
              pExpr->pTab = pTab;
              eNewExprOp = TK_COLUMN;
            }else{
              pExpr->iTable = pNC->uNC.pUpsert->regData + iCol;
              eNewExprOp = TK_REGISTER;
              ExprSetProperty(pExpr, EP_Alias);
            }
          }else
#endif /* SQLITE_OMIT_UPSERT */
          {
#ifndef SQLITE_OMIT_TRIGGER
            if( iCol<0 ){
              pExpr->affinity = SQLITE_AFF_INTEGER;
            }else if( pExpr->iTable==0 ){

Changes to test/altercol.test.

665
666
667
668
669
670
671
672





673












674
  ALTER TABLE t2 RENAME d TO a;
} {1 {error in view v4 after rename: ambiguous column name: a}}

do_execsql_test 16.2 {
  SELECT * FROM v4;
} {1 4}

reset_db


















finish_test







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

665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
  ALTER TABLE t2 RENAME d TO a;
} {1 {error in view v4 after rename: ambiguous column name: a}}

do_execsql_test 16.2 {
  SELECT * FROM v4;
} {1 4}

do_execsql_test 16.3 {
  CREATE UNIQUE INDEX t2d ON t2(d);
  CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
    INSERT INTO t2 VALUES(new.a, new.b, new.c)
      ON CONFLICT(d) DO UPDATE SET f = excluded.f;
  END;
}

do_execsql_test 16.4 {
  INSERT INTO t1 VALUES(4, 8, 456);
  SELECT * FROM t2;
} {4 5 456}

do_execsql_test 16.5 {
  ALTER TABLE t2 RENAME COLUMN f TO "big f";
  INSERT INTO t1 VALUES(4, 0, 20456);
  SELECT * FROM t2;
} {4 5 20456}

finish_test