Add experimental implementation of ALTER TABLE DROP COLUMN. Only some cases work so far.

FossilOrigin-Name: f0217937d7306fb595727e61e871e8b03d8c881d339a0865bfd0117d90d42e4e

7 files changed, 332 insertions, 26 deletions

diff --git a/manifest b/manifest
index 3ceb441cf..2350dad5e 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Fix\san\sissue\swith\sthe\sLIKE\soperator\swhen\sit\sincludes\sthe\s"ESCAPE\s'_'"\sclause.\nTicket\s[c0aeea67d58ae0fd].
-D 2021-02-15T17:02:01.949
+C Add\sexperimental\simplementation\sof\sALTER\sTABLE\sDROP\sCOLUMN.\sOnly\ssome\scases\swork\sso\sfar.
+D 2021-02-16T20:43:36.239
 F .fossil-settings/empty-dirs dbb81e8fc0401ac46a1491ab34a7f2c7c0452f2f06b54ebb845d024ca8283ef1
 F .fossil-settings/ignore-glob 35175cdfcf539b2318cb04a9901442804be81cd677d8b889fcc9149c21f239ea
 F LICENSE.md df5091916dbb40e6e9686186587125e1b2ff51f022cc334e886c19a0e9982724
@@ -475,7 +475,7 @@ F spec.template 86a4a43b99ebb3e75e6b9a735d5fd293a24e90ca
 F sqlite.pc.in 42b7bf0d02e08b9e77734a47798d1a55a9e0716b
 F sqlite3.1 fc7ad8990fc8409983309bb80de8c811a7506786
 F sqlite3.pc.in 48fed132e7cb71ab676105d2a4dc77127d8c1f3a
-F src/alter.c c4a973d7fc68f81fbc5d1d028a88ddb2cb212faef8c6f63433175b8d5ecd83dc
+F src/alter.c e912280eed81e2df936cc03af00a59abfcbfc24a27c3e5db8e8bd96022e2bf79
 F src/analyze.c 01c6c6765cb4d40b473b71d85535093730770bb186f2f473abac25f07fcdee5c
 F src/attach.c e80162a47411f296bea550ed8fafd730481f4aa71e89ece23ba9c957eed15d4a
 F src/auth.c 08954fdc4cc2da5264ba5b75cfd90b67a6fc7d1710a02ccf917c38eadec77853
@@ -530,7 +530,7 @@ F src/os_win.c 77d39873836f1831a9b0b91894fec45ab0e9ca8e067dc8c549e1d1eca1566fe9
 F src/os_win.h 7b073010f1451abe501be30d12f6bc599824944a
 F src/pager.c c49952ac5e9cc536778eff528091d79d38b3e45cbeeed4695dc05e207dc6547d
 F src/pager.h 4bf9b3213a4b2bebbced5eaa8b219cf25d4a82f385d093cd64b7e93e5285f66f
-F src/parse.y 67ba503780de64b967ae195b7e14c33531329228e1bc0b83d63324beb733680b
+F src/parse.y 8170885f22f4815c2adba3430ac7c990a39b60e04a0bcc4d02cce54e33475975
 F src/pcache.c 385ff064bca69789d199a98e2169445dc16e4291fa807babd61d4890c3b34177
 F src/pcache.h 4f87acd914cef5016fae3030343540d75f5b85a1877eed1a2a19b9f284248586
 F src/pcache1.c 6596e10baf3d8f84cc1585d226cf1ab26564a5f5caf85a15757a281ff977d51a
@@ -541,12 +541,12 @@ F src/printf.c 30e92b638fac71dcd85cdea1d12ecfae354c9adee2c71e8e1ae4727cde7c91ed
 F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384
 F src/resolve.c 89e4faf6171e179edf279905e8e45c4f9dd108777dc60716396729fbd7cb045e
 F src/rowset.c ba9515a922af32abe1f7d39406b9d35730ed65efab9443dc5702693b60854c92
-F src/select.c 9b4c84fd2703ee3c8b5d4b189387482a84c26acf2c38ca4835db5b48c68a09d4
+F src/select.c 4bd3b7d21d0d58dfdb4134f71d0330915e962a036ae1e1c8cde2f7f8b45de7e9
 F src/shell.c.in 9ebc74e4f05cfbd0f4a36060fdaeff1da4e9af4458358722bc08c5a1ab9a0879
 F src/sqlite.h.in 8855a19f37ade8dad189a9e48233a2ebe1b46faf469c7eb0906a654e252dcc57
 F src/sqlite3.rc 5121c9e10c3964d5755191c80dd1180c122fc3a8
 F src/sqlite3ext.h 61b38c073d5e1e96a3d45271b257aef27d0d13da2bea5347692ae579475cd95e
-F src/sqliteInt.h 4cb469678a0dbf814e4efbde4488a0161a5398e9a63141830d9f676b4e9fb0cc
+F src/sqliteInt.h 514b41619e2c6b49188571df50249db2600ec33e4bfd1a6efb21f7e1c352f400
 F src/sqliteLimit.h d7323ffea5208c6af2734574bae933ca8ed2ab728083caa117c9738581a31657
 F src/status.c 4b8bc2a6905163a38b739854a35b826c737333fab5b1f8e03fa7eb9a4799c4c1
 F src/table.c 0f141b58a16de7e2fbe81c308379e7279f4c6b50eb08efeec5892794a0ba30d1
@@ -647,6 +647,7 @@ F test/alter4.test dfd6086faf461b27ca2d2999848dcd207edf23352fc1592d0005c0844f3f0
 F test/alterauth.test 63442ba61ceb0c1eeb63aac1f4f5cebfa509d352276059d27106ae256bafc959
 F test/alterauth2.test c0a1ddf5b93d93cb0d15ba7acaf0c5c6fb515bbe861ede75b2d3fabad33b6499
 F test/altercol.test 1d6a6fe698b81e626baea4881f5717f9bc53d7d07f1cd23ee7ad1b931f117ddf
+F test/alterdropcol.test 77539861deb0f1a04fb938f33984d7ccac8618337492611ac41dffea4b316863
 F test/alterlegacy.test 82022721ce0de29cedc9a7af63bc9fcc078b0ee000f8283b4b6ea9c3eab2f44b
 F test/altermalloc.test 167a47de41b5c638f5f5c6efb59784002b196fff70f98d9b4ed3cd74a3fb80c9
 F test/altermalloc2.test fa7b1c1139ea39b8dec407cf1feb032ca8e0076bd429574969b619175ad0174b
@@ -1900,7 +1901,10 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93
 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc
 F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e
 F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0
-P 7cc65ae57183b3c16f1102fca5603a36acda432e5d45e22a2996e5ebe069fc6a
-R 3ed1929e1ca13bbf9ff7ef7c6dc85f4d
-U drh
-Z b9fb1ccb5459387ee6a14bca5dee2b45
+P 27d4117980d125975b5e70eeea58a6ab07bcf066e71b5fcb81b822e05afdbab0
+R af9ecd1afb3809621548f4fa239dc0d6
+T *branch * alter-table-drop-column
+T *sym-alter-table-drop-column *
+T -sym-trunk *
+U dan
+Z e426edd9d98bb43cdac675b225a7f76a
diff --git a/manifest.uuid b/manifest.uuid
index 1137531a2..031aa0ca6 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-27d4117980d125975b5e70eeea58a6ab07bcf066e71b5fcb81b822e05afdbab0
\ No newline at end of file
+f0217937d7306fb595727e61e871e8b03d8c881d339a0865bfd0117d90d42e4e
\ No newline at end of file
diff --git a/src/alter.c b/src/alter.c
index 0f50f195e..4b00d4c38 100644
--- a/src/alter.c
+++ b/src/alter.c
@@ -870,23 +870,33 @@ static void renameTokenFree(sqlite3 *db, RenameToken *pToken){
 
 /*
 ** Search the Parse object passed as the first argument for a RenameToken
-** object associated with parse tree element pPtr. If found, remove it
-** from the Parse object and add it to the list maintained by the
-** RenameCtx object passed as the second argument.
+** object associated with parse tree element pPtr. If found, return a pointer
+** to it. Otherwise, return NULL.
+**
+** If the second argument passed to this function is not NULL and a matching
+** RenameToken object is found, remove it from the Parse object and add it to
+** the list maintained by the RenameCtx object.
 */
-static void renameTokenFind(Parse *pParse, struct RenameCtx *pCtx, void *pPtr){
+static RenameToken *renameTokenFind(
+  Parse *pParse, 
+  struct RenameCtx *pCtx, 
+  void *pPtr
+){
   RenameToken **pp;
   assert( pPtr!=0 );
   for(pp=&pParse->pRename; (*pp); pp=&(*pp)->pNext){
     if( (*pp)->p==pPtr ){
       RenameToken *pToken = *pp;
-      *pp = pToken->pNext;
-      pToken->pNext = pCtx->pList;
-      pCtx->pList = pToken;
-      pCtx->nList++;
-      break;
+      if( pCtx ){
+        *pp = pToken->pNext;
+        pToken->pNext = pCtx->pList;
+        pCtx->pList = pToken;
+        pCtx->nList++;
+      }
+      return pToken;
     }
   }
+  return 0;
 }
 
 /*
@@ -1752,13 +1762,186 @@ static void renameTableTest(
 }
 
 /*
+** Arguments:
+**
+**  argv[0]: An integer - the index of the schema containing the table
+**  argv[1]: CREATE TABLE statement to modify.
+**  argv[2]: An integer - the index of the column to remove.
+**  argv[3]: Byte offset of first byte after last column definition in argv[1]
+*/
+static void dropColumnFunc(
+  sqlite3_context *context,
+  int NotUsed,
+  sqlite3_value **argv
+){
+  sqlite3 *db = sqlite3_context_db_handle(context);
+  int iSchema = sqlite3_value_int(argv[0]);
+  const char *zSql = (const char*)sqlite3_value_text(argv[1]);
+  int iCol = sqlite3_value_int(argv[2]);
+  int iAddColOffset = sqlite3_value_int(argv[3]);
+  const char *zDb = db->aDb[iSchema].zDbSName;
+  int rc;
+  Parse sParse;
+  RenameToken *pCol;
+  Table *pTab;
+  const char *zEnd;
+  char *zNew = 0;
+
+  rc = renameParseSql(&sParse, zDb, db, zSql, iSchema==1);
+  if( rc!=SQLITE_OK ) goto drop_column_done;
+  pTab = sParse.pNewTable;
+
+  pCol = renameTokenFind(&sParse, 0, (void*)pTab->aCol[iCol].zName);
+  if( iCol<pTab->nCol-1 ){
+    RenameToken *pEnd;
+    pEnd = renameTokenFind(&sParse, 0, (void*)pTab->aCol[iCol+1].zName);
+    zEnd = (const char*)pEnd->t.z;
+  }else{
+    zEnd = (const char*)&zSql[iAddColOffset];
+    while( pCol->t.z[0]!=',' && pCol->t.z[1]!='(' ) pCol->t.z--;
+  }
+
+  zNew = sqlite3_mprintf("%.*s%s", pCol->t.z-zSql, zSql, zEnd);
+  sqlite3_result_text(context, zNew, -1, SQLITE_TRANSIENT);
+  sqlite3_free(zNew);
+
+drop_column_done:
+  renameParseCleanup(&sParse);
+}
+
+void sqlite3AlterDropColumn(Parse *pParse, SrcList *pSrc, Token *pName){
+  Table *pTab;
+  int i;
+  int iSchema = 0;
+  const char *zDb = 0;
+  sqlite3 *db = pParse->db;
+  char *zCol = 0;
+  int iCol = 0;
+  Vdbe *v;
+  int iCur;
+  int addr;
+  int reg;
+  int regRec;
+  Index *pIdx;
+  Index *pPk = 0;
+
+  /* Look up the table being altered. */
+  assert( pParse->pNewTable==0 );
+  assert( sqlite3BtreeHoldsAllMutexes(db) );
+  if( db->mallocFailed ) goto exit_drop_column;
+  pTab = sqlite3LocateTableItem(pParse, 0, &pSrc->a[0]);
+  if( !pTab ) goto exit_drop_column;
+
+  /* Which schema holds the table to be altered */  
+  iSchema = sqlite3SchemaToIndex(db, pTab->pSchema);
+  assert( iSchema>=0 );
+  zDb = db->aDb[iSchema].zDbSName;
+
+#ifndef SQLITE_OMIT_VIRTUALTABLE
+  if( IsVirtual(pTab) ){
+    sqlite3ErrorMsg(pParse, "virtual tables may not be altered");
+    goto exit_drop_column;
+  }
+#endif
+
+  /* Make sure this is not an attempt to ALTER a view. */
+  if( pTab->pSelect ){
+    sqlite3ErrorMsg(pParse, "cannot drop a column from a view");
+    goto exit_drop_column;
+  }
+  if( SQLITE_OK!=isAlterableTable(pParse, pTab) ){
+    goto exit_drop_column;
+  }
+
+  /* Find the index of the column being dropped. */
+  zCol = sqlite3NameFromToken(db, pName);
+  if( zCol==0 ){
+    assert( db->mallocFailed );
+    goto exit_drop_column;
+  }
+  iCol = sqlite3ColumnIndex(pTab, zCol);
+  if( iCol<0 ){
+    sqlite3ErrorMsg(pParse, "no such column: \"%s\"", zCol);
+    goto exit_drop_column;
+  }
+
+  /* Do not allow the user to drop a PRIMARY KEY column or a column 
+  ** constrained by a UNIQUE constraint.  */
+  if( pTab->iPKey==iCol ){
+    sqlite3ErrorMsg(pParse, "cannot drop PRIMARY KEY column: \"%s\"", zCol);
+    goto exit_drop_column;
+  }
+  for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
+    int ii;
+    for(ii=0; ii<pIdx->nKeyCol; ii++){
+      if( iCol==pIdx->aiColumn[ii] 
+       && sqlite3_strnicmp("sqlite_", pIdx->zName, 7)==0
+      ){
+        sqlite3ErrorMsg(pParse, "cannot drop %s column: \"%s\"", 
+          pIdx->idxType==2 ? "PRIMARY KEY" : "UNIQUE", zCol
+        );
+        goto exit_drop_column;
+      }
+    }
+  }
+
+  sqlite3NestedParse(pParse, 
+      "UPDATE \"%w\"." DFLT_SCHEMA_TABLE " SET "
+      "sql = sqlite_drop_column(%d, sql, %d, %d) "
+      "WHERE (type=='table' AND tbl_name=%Q COLLATE nocase)"
+      , zDb, iSchema, iCol, pTab->addColOffset, pTab->zName
+  );
+
+  /* Drop and reload the database schema. */
+  renameReloadSchema(pParse, iSchema);
+  renameTestSchema(pParse, zDb, iSchema==1);
+
+  /* Edit rows of table on disk */
+  v = sqlite3GetVdbe(pParse);
+  iCur = pParse->nTab++;
+  sqlite3OpenTable(pParse, iCur, iSchema, pTab, OP_OpenWrite);
+  addr = sqlite3VdbeAddOp1(v, OP_Rewind, iCur);
+  reg = ++pParse->nMem;
+  pParse->nMem += pTab->nCol;
+  if( HasRowid(pTab) ){
+    sqlite3VdbeAddOp2(v, OP_Rowid, iCur, reg);
+  }else{
+    pPk = sqlite3PrimaryKeyIndex(pTab);
+  }
+  for(i=0; i<pTab->nCol; i++){
+    if( i!=iCol ){
+      int iPos = (pPk ? sqlite3TableColumnToIndex(pPk, i) : i);
+      int iColPos = (pPk ? sqlite3TableColumnToIndex(pPk, iCol) : iCol);
+      int regOut = reg+1+iPos-(iPos>iColPos);
+      sqlite3ExprCodeGetColumnOfTable(v, pTab, iCur, i, regOut);
+    }
+  }
+  regRec = reg + pTab->nCol;
+  sqlite3VdbeAddOp3(v, OP_MakeRecord, reg+1, pTab->nCol-1, regRec);
+  if( HasRowid(pTab) ){
+    sqlite3VdbeAddOp3(v, OP_Insert, iCur, regRec, reg);
+  }else{
+    sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iCur, regRec, reg+1, pPk->nKeyCol);
+  }
+
+  sqlite3VdbeAddOp2(v, OP_Next, iCur, addr+1);
+  sqlite3VdbeJumpHere(v, addr);
+
+exit_drop_column:
+  sqlite3DbFree(db, zCol);
+  sqlite3SrcListDelete(db, pSrc);
+  return;
+}
+
+/*
 ** Register built-in functions used to help implement ALTER TABLE
 */
 void sqlite3AlterFunctions(void){
   static FuncDef aAlterTableFuncs[] = {
-    INTERNAL_FUNCTION(sqlite_rename_column, 9, renameColumnFunc),
-    INTERNAL_FUNCTION(sqlite_rename_table,  7, renameTableFunc),
-    INTERNAL_FUNCTION(sqlite_rename_test,   5, renameTableTest),
+    INTERNAL_FUNCTION(sqlite_rename_column,  9, renameColumnFunc),
+    INTERNAL_FUNCTION(sqlite_rename_table,   7, renameTableFunc),
+    INTERNAL_FUNCTION(sqlite_rename_test,    5, renameTableTest),
+    INTERNAL_FUNCTION(sqlite_drop_column,    4, dropColumnFunc),
   };
   sqlite3InsertBuiltinFuncs(aAlterTableFuncs, ArraySize(aAlterTableFuncs));
 }
diff --git a/src/parse.y b/src/parse.y
index 591cde3b9..92626f2c1 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -1621,6 +1621,10 @@ cmd ::= ALTER TABLE add_column_fullname
   Y.n = (int)(pParse->sLastToken.z-Y.z) + pParse->sLastToken.n;
   sqlite3AlterFinishAddColumn(pParse, &Y);
 }
+cmd ::= ALTER TABLE fullname(X) DROP COLUMNKW nm(Y). {
+  sqlite3AlterDropColumn(pParse, X, &Y);
+}
+
 add_column_fullname ::= fullname(X). {
   disableLookaside(pParse);
   sqlite3AlterBeginAddColumn(pParse, X);
diff --git a/src/select.c b/src/select.c
index 1ab2ec367..04c29e6d2 100644
--- a/src/select.c
+++ b/src/select.c
@@ -262,7 +262,7 @@ int sqlite3JoinType(Parse *pParse, Token *pA, Token *pB, Token *pC){
 ** Return the index of a column in a table.  Return -1 if the column
 ** is not contained in the table.
 */
-static int columnIndex(Table *pTab, const char *zCol){
+int sqlite3ColumnIndex(Table *pTab, const char *zCol){
   int i;
   u8 h = sqlite3StrIHash(zCol);
   Column *pCol;
@@ -294,7 +294,7 @@ static int tableAndColumnIndex(
 
   assert( (piTab==0)==(piCol==0) );  /* Both or neither are NULL */
   for(i=0; i<N; i++){
-    iCol = columnIndex(pSrc->a[i].pTab, zCol);
+    iCol = sqlite3ColumnIndex(pSrc->a[i].pTab, zCol);
     if( iCol>=0 
      && (bIgnoreHidden==0 || IsHiddenColumn(&pSrc->a[i].pTab->aCol[iCol])==0)
     ){
@@ -504,7 +504,7 @@ static int sqliteProcessJoin(Parse *pParse, Select *p){
         int iRightCol;   /* Column number of matching column on the right */
 
         zName = pList->a[j].zName;
-        iRightCol = columnIndex(pRightTab, zName);
+        iRightCol = sqlite3ColumnIndex(pRightTab, zName);
         if( iRightCol<0
          || !tableAndColumnIndex(pSrc, i+1, zName, &iLeft, &iLeftCol, 0)
         ){
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index 17b1cb415..9c09b24ac 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -4531,6 +4531,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, ExprList*,Expr*,int);
 #endif
 
 int sqlite3JoinType(Parse*, Token*, Token*, Token*);
+int sqlite3ColumnIndex(Table *pTab, const char *zCol);
 void sqlite3SetJoinExpr(Expr*,int);
 void sqlite3CreateForeignKey(Parse*, ExprList*, Token*, ExprList*, int);
 void sqlite3DeferForeignKey(Parse*, int);
@@ -4715,6 +4716,7 @@ int sqlite3ResolveOrderGroupBy(Parse*, Select*, ExprList*, const char*);
 void sqlite3ColumnDefault(Vdbe *, Table *, int, int);
 void sqlite3AlterFinishAddColumn(Parse *, Token *);
 void sqlite3AlterBeginAddColumn(Parse *, SrcList *);
+void sqlite3AlterDropColumn(Parse*, SrcList*, Token*);
 void *sqlite3RenameTokenMap(Parse*, void*, Token*);
 void sqlite3RenameTokenRemap(Parse*, void *pTo, void *pFrom);
 void sqlite3RenameExprUnmap(Parse*, Expr*);
diff --git a/test/alterdropcol.test b/test/alterdropcol.test
new file mode 100644
index 000000000..7bb597705
--- /dev/null
+++ b/test/alterdropcol.test
@@ -0,0 +1,113 @@
+# 2021 February 16
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#*************************************************************************
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+set testprefix alterdropcol
+
+# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
+ifcapable !altertable {
+  finish_test
+  return
+}
+
+do_execsql_test 1.0 {
+  CREATE TABLE t1(a, b, c);
+  CREATE VIEW v1 AS SELECT * FROM t1;
+
+  CREATE TABLE t2(x INTEGER PRIMARY KEY, y, z UNIQUE);
+  CREATE INDEX t2y ON t2(y);
+}
+
+do_catchsql_test 1.1 {
+  ALTER TABLE nosuch DROP COLUMN z;
+} {1 {no such table: nosuch}}
+
+do_catchsql_test 1.2 {
+  ALTER TABLE v1 DROP COLUMN c;
+} {1 {cannot drop a column from a view}}
+
+ifcapable fts5 {
+  do_execsql_test 1.3.1 {
+    CREATE VIRTUAL TABLE ft1 USING fts5(one, two);
+  }
+  do_catchsql_test 1.3.2 {
+    ALTER TABLE ft1 DROP COLUMN two;
+  } {1 {virtual tables may not be altered}}
+}
+
+do_catchsql_test 1.4 {
+  ALTER TABLE sqlite_schema DROP COLUMN sql;
+} {1 {table sqlite_master may not be altered}}
+
+do_catchsql_test 1.5 {
+  ALTER TABLE t1 DROP COLUMN d;
+} {1 {no such column: "d"}}
+
+do_execsql_test 1.6.1 {
+  ALTER TABLE t1 DROP COLUMN b;
+}
+do_execsql_test 1.6.2 {
+  SELECT sql FROM sqlite_schema WHERE name = 't1'
+} {{CREATE TABLE t1(a, c)}}
+
+do_execsql_test 1.7.1 {
+  ALTER TABLE t1 DROP COLUMN c;
+}
+do_execsql_test 1.7.2 {
+  SELECT sql FROM sqlite_schema WHERE name = 't1'
+} {{CREATE TABLE t1(a)}}
+
+
+do_catchsql_test 1.8 {
+  ALTER TABLE t2 DROP COLUMN z
+} {1 {cannot drop UNIQUE column: "z"}}
+
+do_catchsql_test 1.9 {
+  ALTER TABLE t2 DROP COLUMN x
+} {1 {cannot drop PRIMARY KEY column: "x"}}
+
+do_catchsql_test 1.10 {
+  ALTER TABLE t2 DROP COLUMN y
+} {1 {cannot drop indexed column: "y"}}
+
+#-------------------------------------------------------------------------
+
+foreach {tn wo} {
+  1 {}
+  2 {WITHOUT ROWID}
+} { eval [string map [list %TN% $tn %WO% $wo] {
+
+  reset_db
+  do_execsql_test 2.%TN%.0 {
+    CREATE TABLE t1(x, y INTEGER PRIMARY KEY, z) %WO% ;
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t1 VALUES(4, 5, 6);
+    INSERT INTO t1 VALUES(7, 8, 9);
+  }
+  
+  do_execsql_test 2.%TN%.1 {
+    ALTER TABLE t1 DROP COLUMN x;
+    SELECT * FROM t1;
+  } {
+    2 3  5 6  8 9
+  }
+  do_execsql_test 2.%TN%.2 {
+    ALTER TABLE t1 DROP COLUMN z;
+    SELECT * FROM t1;
+  } {
+    2 5 8
+  }
+}]}
+
+finish_test
+