SQLite

Check-in [a7db6e45ad]
Login

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

Overview
Comment:Fix ALTER TABLE so that it does not promote the schema version past 3, as that will cause DESC indexes to go corrupt. Ticket [f68bf68513a1c].
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a7db6e45ad45be9b3003f61d4163f543498a7c9d
User & Date: drh 2016-07-14 19:13:11.812
Context
2016-07-15
02:50
Disable the CSV extension when virtual tables are disabled. (check-in: ec7180892a user: drh tags: trunk)
2016-07-14
19:48
Minor tweak the the b-tree balancer. (Closed-Leaf check-in: d2a0af7a37 user: drh tags: btree-tuning)
19:13
Fix ALTER TABLE so that it does not promote the schema version past 3, as that will cause DESC indexes to go corrupt. Ticket [f68bf68513a1c]. (check-in: a7db6e45ad user: drh tags: trunk)
2016-07-13
13:05
Fix harmless compiler warnings in shell.c for NetBSD. (check-in: 824b39e54f user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/alter.c.
597
598
599
600
601
602
603

604
605
606
607
608
609
610
  const char *zDb;          /* Database name */
  const char *zTab;         /* Table name */
  char *zCol;               /* Null-terminated column definition */
  Column *pCol;             /* The new column */
  Expr *pDflt;              /* Default value for the new column */
  sqlite3 *db;              /* The database connection; */
  Vdbe *v = pParse->pVdbe;  /* The prepared statement under construction */


  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ) return;
  assert( v!=0 );
  pNew = pParse->pNewTable;
  assert( pNew );








>







597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
  const char *zDb;          /* Database name */
  const char *zTab;         /* Table name */
  char *zCol;               /* Null-terminated column definition */
  Column *pCol;             /* The new column */
  Expr *pDflt;              /* Default value for the new column */
  sqlite3 *db;              /* The database connection; */
  Vdbe *v = pParse->pVdbe;  /* The prepared statement under construction */
  int r1;                   /* Temporary registers */

  db = pParse->db;
  if( pParse->nErr || db->mallocFailed ) return;
  assert( v!=0 );
  pNew = pParse->pNewTable;
  assert( pNew );

691
692
693
694
695
696
697
698
699
700
701
702
703
704
705






706
707

708
709
710
711
712
713
714
      zDb, SCHEMA_TABLE(iDb), pNew->addColOffset, zCol, pNew->addColOffset+1,
      zTab
    );
    sqlite3DbFree(db, zCol);
    db->flags = savedDbFlags;
  }

  /* If the default value of the new column is NULL, then the file
  ** format to 2. If the default value of the new column is not NULL,
  ** the file format be 3.  Back when this feature was first added
  ** in 2006, we went to the trouble to upgrade the file format to the
  ** minimum support values.  But 10-years on, we can assume that all
  ** extent versions of SQLite support file-format 4, so we always and
  ** unconditionally upgrade to 4.
  */






  sqlite3VdbeAddOp3(v, OP_SetCookie, iDb, BTREE_FILE_FORMAT, 
                    SQLITE_MAX_FILE_FORMAT);


  /* Reload the schema of the modified table. */
  reloadTableSchema(pParse, pTab, pTab->zName);
}

/*
** This function is called by the parser after the table-name in







|
|
<
<
<
<
|

>
>
>
>
>
>
|
<
>







692
693
694
695
696
697
698
699
700




701
702
703
704
705
706
707
708
709

710
711
712
713
714
715
716
717
      zDb, SCHEMA_TABLE(iDb), pNew->addColOffset, zCol, pNew->addColOffset+1,
      zTab
    );
    sqlite3DbFree(db, zCol);
    db->flags = savedDbFlags;
  }

  /* Make sure the schema version is at least 3.  But do not upgrade
  ** from less than 3 to 4, as that will corrupt any preexisting DESC




  ** index.
  */
  r1 = sqlite3GetTempReg(pParse);
  sqlite3VdbeAddOp3(v, OP_ReadCookie, iDb, r1, BTREE_FILE_FORMAT);
  sqlite3VdbeUsesBtree(v, iDb);
  sqlite3VdbeAddOp2(v, OP_AddImm, r1, -2);
  sqlite3VdbeAddOp2(v, OP_IfPos, r1, sqlite3VdbeCurrentAddr(v)+2);
  VdbeCoverage(v);
  sqlite3VdbeAddOp3(v, OP_SetCookie, iDb, BTREE_FILE_FORMAT, 3);

  sqlite3ReleaseTempReg(pParse, r1);

  /* Reload the schema of the modified table. */
  reloadTableSchema(pParse, pTab, pTab->zName);
}

/*
** This function is called by the parser after the table-name in
Changes to test/alter3.test.
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
    ALTER TABLE t1 ADD c;
    SELECT * FROM t1;
  }
} {1 100 {} 2 300 {}}
if {!$has_codec} {
  do_test alter3-3.3 {
    get_file_format
  } {4}
}
ifcapable schema_version {
  do_test alter3-3.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {11}







|







180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
    ALTER TABLE t1 ADD c;
    SELECT * FROM t1;
  }
} {1 100 {} 2 300 {}}
if {!$has_codec} {
  do_test alter3-3.3 {
    get_file_format
  } {3}
}
ifcapable schema_version {
  do_test alter3-3.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {11}
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
    ALTER TABLE t1 ADD c DEFAULT 'hello world';
    SELECT * FROM t1;
  }
} {1 100 {hello world} 2 300 {hello world}}
if {!$has_codec} {
  do_test alter3-4.3 {
    get_file_format
  } {4}
}
ifcapable schema_version {
  do_test alter3-4.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {21}







|







216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
    ALTER TABLE t1 ADD c DEFAULT 'hello world';
    SELECT * FROM t1;
  }
} {1 100 {hello world} 2 300 {hello world}}
if {!$has_codec} {
  do_test alter3-4.3 {
    get_file_format
  } {3}
}
ifcapable schema_version {
  do_test alter3-4.4 {
    execsql {
      PRAGMA schema_version;
    }
  } {21}
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
        PRAGMA aux.schema_version;
      }
    } {31}
  }
  if {!$has_codec} {
    do_test alter3-5.5 {
      list [get_file_format test2.db] [get_file_format]
    } {4 4}
  }
  do_test alter3-5.6 {
    execsql {
      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
      SELECT sql FROM aux.sqlite_master;
    }
  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}







|







266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
        PRAGMA aux.schema_version;
      }
    } {31}
  }
  if {!$has_codec} {
    do_test alter3-5.5 {
      list [get_file_format test2.db] [get_file_format]
    } {3 3}
  }
  do_test alter3-5.6 {
    execsql {
      ALTER TABLE aux.t1 ADD COLUMN d DEFAULT 1000;
      SELECT sql FROM aux.sqlite_master;
    }
  } {{CREATE TABLE t1(a,b, c VARCHAR(128), d DEFAULT 1000)}}
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
    } {1}
    do_test alter3-7.2 {
      execsql {
        CREATE TABLE abc(a, b, c);
        ALTER TABLE abc ADD d DEFAULT NULL;
      }
      get_file_format
    } {4}
    do_test alter3-7.3 {
      execsql {
        ALTER TABLE abc ADD e DEFAULT 10;
      }
      get_file_format
    } {4}
    do_test alter3-7.4 {
      execsql {
        ALTER TABLE abc ADD f DEFAULT NULL;
      }
      get_file_format
    } {4}
    do_test alter3-7.5 {
      execsql {
        VACUUM;
      }
      get_file_format
    } {1}
  }







|





|





|







343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
    } {1}
    do_test alter3-7.2 {
      execsql {
        CREATE TABLE abc(a, b, c);
        ALTER TABLE abc ADD d DEFAULT NULL;
      }
      get_file_format
    } {3}
    do_test alter3-7.3 {
      execsql {
        ALTER TABLE abc ADD e DEFAULT 10;
      }
      get_file_format
    } {3}
    do_test alter3-7.4 {
      execsql {
        ALTER TABLE abc ADD f DEFAULT NULL;
      }
      get_file_format
    } {3}
    do_test alter3-7.5 {
      execsql {
        VACUUM;
      }
      get_file_format
    } {1}
  }
Changes to test/alter4.test.
350
351
352
353
354
355
356



















357
358
  real     9.22337203685478e+18
}

do_execsql_test alter4-9.3 { 
  ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
  SELECT typeof(c), c FROM t5;
} {real 9.22337203685478e+18}




















finish_test







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


350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
  real     9.22337203685478e+18
}

do_execsql_test alter4-9.3 { 
  ALTER TABLE t5 ADD COLUMN c INTEGER DEFAULT (-(-9223372036854775808));
  SELECT typeof(c), c FROM t5;
} {real 9.22337203685478e+18}

# Confirm that doing an ALTER TABLE on a legacy format database
# does not corrupt DESC indexes.
#
# Ticket https://www.sqlite.org/src/tktview/f68bf68513a1c
#
do_test alter4-10.1 {
  db close
  sqlite3 db :memory:
  db eval {
    PRAGMA legacy_file_format=on;
    CREATE TABLE t1(a,b,c);
    CREATE INDEX t1a ON t1(a DESC);
    INSERT INTO t1 VALUES(1,2,3);
    INSERT INTO t1 VALUES(2,3,4);
    ALTER TABLE t1 ADD COLUMN d;
    PRAGMA integrity_check;
  }
} {ok}

finish_test