SQLite Forum

ALTER TABLE DROP COLUMN corrupts data
Login

ALTER TABLE DROP COLUMN corrupts data

(1) By Vladimir Panteleev (CyberShadow) on 2021-04-18 00:15:44 [link] [source]

CREATE TABLE [MyTable] (
[ID] INTEGER PRIMARY KEY NOT NULL,
[Foo] INTEGER,
[Bar] INTEGER);

-- Insert 50000 identical rows
INSERT INTO [MyTable] ([Foo], [Bar])
SELECT 123, 456 FROM generate_series(1, 50000, 1);
-- All rows are: [Foo]==123, [Bar]==456

-- Drop the [Foo] column
ALTER TABLE [MyTable] DROP COLUMN [Foo];
-- All rows should be: [Bar]==456

-- However, mysteriously, some rows now have [Bar]==123 :

SELECT COUNT(*) FROM [MyTable] WHERE [Bar]=123  -- (Prints 17226 for me)

(2) By Larry Brasfield (larrybr) on 2021-04-18 01:47:28 in reply to 1 [link] [source]

I confirm that this bug exists in the current code. An interesting variation: drop table if exists t; create table t(id integer primary key not null, f integer, b integer); insert into t (f,b) select 1,2 from generate_series(1,50000,1); alter table t drop column f; select count(*) from t where b<>2; ; -- emits 49867 drop table if exists t; create table t(id integer primary key not null, f integer, b integer); insert into t (f,b) select 123456789,987654321 from generate_series(1,50000,1); alter table t drop column f; select count(*) from t where b<>987654321; ; -- emits 0

It appears that there is a dependency on the dropped or kept datum size.

(3) By Ryan Smith (cuz) on 2021-04-18 02:15:22 in reply to 1 [link] [source]

To add to this very interesting bug report - I wondered if the generate series had any impact on the problem, or indeed the high record count.

Turns out no impact at all - this is the same test but instead using a CTE and only testing 500 records - small enough to list the entire output. It seems there is a specific switch-over point:

CREATE TABLE t(id integer primary key not null, f integer, b integer);

WITH GS(x) AS (
    SELECT 1 UNION ALL SELECT x+1 FROM GS WHERE x < 500
)
INSERT INTO t(f,b) SELECT 1,2
  FROM GS
;


ALTER TABLE t DROP COLUMN f;


SELECT count(*) FROM t WHERE b<>2;

  --   count(*)  
  -- ------------
  --      366    


SELECT * FROM t;

  --      id     | b 
  -- ------------|---
  --       1     | 2 
  --       2     | 2 
  --       3     | 2 
  --       4     | 2 
  --       5     | 2 
  --       6     | 2 
  --       7     | 2 
  --       8     | 2 
  --       9     | 2 
  --      10     | 2 
  --      11     | 2 
  --      12     | 2 
  --      13     | 2 
  --      14     | 2 
  --      15     | 2 
  --      16     | 2 
  --      17     | 2 
  --      18     | 2 
  --      19     | 2 
  --      20     | 2 
  --      21     | 2 
  --      22     | 2 
  --      23     | 2 
  --      24     | 2 
  --      25     | 2 
  --      26     | 2 
  --      27     | 2 
  --      28     | 2 
  --      29     | 2 
  --      30     | 2 
  --      31     | 2 
  --      32     | 2 
  --      33     | 2 
  --      34     | 2 
  --      35     | 2 
  --      36     | 2 
  --      37     | 2 
  --      38     | 2 
  --      39     | 2 
  --      40     | 2 
  --      41     | 2 
  --      42     | 2 
  --      43     | 2 
  --      44     | 2 
  --      45     | 2 
  --      46     | 2 
  --      47     | 2 
  --      48     | 2 
  --      49     | 2 
  --      50     | 2 
  --      51     | 2 
  --      52     | 2 
  --      53     | 2 
  --      54     | 2 
  --      55     | 2 
  --      56     | 2 
  --      57     | 2 
  --      58     | 2 
  --      59     | 2 
  --      60     | 2 
  --      61     | 2 
  --      62     | 2 
  --      63     | 2 
  --      64     | 2 
  --      65     | 2 
  --      66     | 2 
  --      67     | 2 
  --      68     | 2 
  --      69     | 2 
  --      70     | 2 
  --      71     | 2 
  --      72     | 2 
  --      73     | 2 
  --      74     | 2 
  --      75     | 2 
  --      76     | 2 
  --      77     | 2 
  --      78     | 2 
  --      79     | 2 
  --      80     | 2 
  --      81     | 2 
  --      82     | 2 
  --      83     | 2 
  --      84     | 2 
  --      85     | 2 
  --      86     | 2 
  --      87     | 2 
  --      88     | 2 
  --      89     | 2 
  --      90     | 2 
  --      91     | 2 
  --      92     | 2 
  --      93     | 2 
  --      94     | 2 
  --      95     | 2 
  --      96     | 2 
  --      97     | 2 
  --      98     | 2 
  --      99     | 2 
  --      100    | 2 
  --      101    | 2 
  --      102    | 2 
  --      103    | 2 
  --      104    | 2 
  --      105    | 2 
  --      106    | 2 
  --      107    | 2 
  --      108    | 2 
  --      109    | 2 
  --      110    | 2 
  --      111    | 2 
  --      112    | 2 
  --      113    | 2 
  --      114    | 2 
  --      115    | 2 
  --      116    | 2 
  --      117    | 2 
  --      118    | 2 
  --      119    | 2 
  --      120    | 2 
  --      121    | 2 
  --      122    | 2 
  --      123    | 2 
  --      124    | 2 
  --      125    | 2 
  --      126    | 2 
  --      127    | 2 
  --      128    | 2 
  --      129    | 2 
  --      130    | 2 
  --      131    | 2 
  --      132    | 2 
  --      133    | 2 
  --      134    | 2 
  --      135    | 1 
  --      136    | 1 
  --      137    | 1 
  --      138    | 1 
  --      139    | 1 
  --      140    | 1 
  --      141    | 1 
  --      142    | 1 
  --      143    | 1 
  --      144    | 1 
  --      145    | 1 
  --      146    | 1 
  --      147    | 1 
  --      148    | 1 
  --      149    | 1 
  --      150    | 1 
  --      151    | 1 
  --      152    | 1 
  --      153    | 1 
  --      154    | 1 
  --      155    | 1 
  --      156    | 1 
  --      157    | 1 
  --      158    | 1 
  --      159    | 1 
  --      160    | 1 
  --      161    | 1 
  --      162    | 1 
  --      163    | 1 
  --      164    | 1 
  --      165    | 1 
  --      166    | 1 
  --      167    | 1 
  --      168    | 1 
  --      169    | 1 
  --      170    | 1 
  --      171    | 1 
  --      172    | 1 
  --      173    | 1 
  --      174    | 1 
  --      175    | 1 
  --      176    | 1 
  --      177    | 1 
  --      178    | 1 
  --      179    | 1 
  --      180    | 1 
  --      181    | 1 
  --      182    | 1 
  --      183    | 1 
  --      184    | 1 
  --      185    | 1 
  --      186    | 1 
  --      187    | 1 
  --      188    | 1 
  --      189    | 1 
  --      190    | 1 
  --      191    | 1 
  --      192    | 1 
  --      193    | 1 
  --      194    | 1 
  --      195    | 1 
  --      196    | 1 
  --      197    | 1 
  --      198    | 1 
  --      199    | 1 
  --      200    | 1 
  --      201    | 1 
  --      202    | 1 
  --      203    | 1 
  --      204    | 1 
  --      205    | 1 
  --      206    | 1 
  --      207    | 1 
  --      208    | 1 
  --      209    | 1 
  --      210    | 1 
  --      211    | 1 
  --      212    | 1 
  --      213    | 1 
  --      214    | 1 
  --      215    | 1 
  --      216    | 1 
  --      217    | 1 
  --      218    | 1 
  --      219    | 1 
  --      220    | 1 
  --      221    | 1 
  --      222    | 1 
  --      223    | 1 
  --      224    | 1 
  --      225    | 1 
  --      226    | 1 
  --      227    | 1 
  --      228    | 1 
  --      229    | 1 
  --      230    | 1 
  --      231    | 1 
  --      232    | 1 
  --      233    | 1 
  --      234    | 1 
  --      235    | 1 
  --      236    | 1 
  --      237    | 1 
  --      238    | 1 
  --      239    | 1 
  --      240    | 1 
  --      241    | 1 
  --      242    | 1 
  --      243    | 1 
  --      244    | 1 
  --      245    | 1 
  --      246    | 1 
  --      247    | 1 
  --      248    | 1 
  --      249    | 1 
  --      250    | 1 
  --      251    | 1 
  --      252    | 1 
  --      253    | 1 
  --      254    | 1 
  --      255    | 1 
  --      256    | 1 
  --      257    | 1 
  --      258    | 1 
  --      259    | 1 
  --      260    | 1 
  --      261    | 1 
  --      262    | 1 
  --      263    | 1 
  --      264    | 1 
  --      265    | 1 
  --      266    | 1 
  --      267    | 1 
  --      268    | 1 
  --      269    | 1 
  --      270    | 1 
  --      271    | 1 
  --      272    | 1 
  --      273    | 1 
  --      274    | 1 
  --      275    | 1 
  --      276    | 1 
  --      277    | 1 
  --      278    | 1 
  --      279    | 1 
  --      280    | 1 
  --      281    | 1 
  --      282    | 1 
  --      283    | 1 
  --      284    | 1 
  --      285    | 1 
  --      286    | 1 
  --      287    | 1 
  --      288    | 1 
  --      289    | 1 
  --      290    | 1 
  --      291    | 1 
  --      292    | 1 
  --      293    | 1 
  --      294    | 1 
  --      295    | 1 
  --      296    | 1 
  --      297    | 1 
  --      298    | 1 
  --      299    | 1 
  --      300    | 1 
  --      301    | 1 
  --      302    | 1 
  --      303    | 1 
  --      304    | 1 
  --      305    | 1 
  --      306    | 1 
  --      307    | 1 
  --      308    | 1 
  --      309    | 1 
  --      310    | 1 
  --      311    | 1 
  --      312    | 1 
  --      313    | 1 
  --      314    | 1 
  --      315    | 1 
  --      316    | 1 
  --      317    | 1 
  --      318    | 1 
  --      319    | 1 
  --      320    | 1 
  --      321    | 1 
  --      322    | 1 
  --      323    | 1 
  --      324    | 1 
  --      325    | 1 
  --      326    | 1 
  --      327    | 1 
  --      328    | 1 
  --      329    | 1 
  --      330    | 1 
  --      331    | 1 
  --      332    | 1 
  --      333    | 1 
  --      334    | 1 
  --      335    | 1 
  --      336    | 1 
  --      337    | 1 
  --      338    | 1 
  --      339    | 1 
  --      340    | 1 
  --      341    | 1 
  --      342    | 1 
  --      343    | 1 
  --      344    | 1 
  --      345    | 1 
  --      346    | 1 
  --      347    | 1 
  --      348    | 1 
  --      349    | 1 
  --      350    | 1 
  --      351    | 1 
  --      352    | 1 
  --      353    | 1 
  --      354    | 1 
  --      355    | 1 
  --      356    | 1 
  --      357    | 1 
  --      358    | 1 
  --      359    | 1 
  --      360    | 1 
  --      361    | 1 
  --      362    | 1 
  --      363    | 1 
  --      364    | 1 
  --      365    | 1 
  --      366    | 1 
  --      367    | 1 
  --      368    | 1 
  --      369    | 1 
  --      370    | 1 
  --      371    | 1 
  --      372    | 1 
  --      373    | 1 
  --      374    | 1 
  --      375    | 1 
  --      376    | 1 
  --      377    | 1 
  --      378    | 1 
  --      379    | 1 
  --      380    | 1 
  --      381    | 1 
  --      382    | 1 
  --      383    | 1 
  --      384    | 1 
  --      385    | 1 
  --      386    | 1 
  --      387    | 1 
  --      388    | 1 
  --      389    | 1 
  --      390    | 1 
  --      391    | 1 
  --      392    | 1 
  --      393    | 1 
  --      394    | 1 
  --      395    | 1 
  --      396    | 1 
  --      397    | 1 
  --      398    | 1 
  --      399    | 1 
  --      400    | 1 
  --      401    | 1 
  --      402    | 1 
  --      403    | 1 
  --      404    | 1 
  --      405    | 1 
  --      406    | 1 
  --      407    | 1 
  --      408    | 1 
  --      409    | 1 
  --      410    | 1 
  --      411    | 1 
  --      412    | 1 
  --      413    | 1 
  --      414    | 1 
  --      415    | 1 
  --      416    | 1 
  --      417    | 1 
  --      418    | 1 
  --      419    | 1 
  --      420    | 1 
  --      421    | 1 
  --      422    | 1 
  --      423    | 1 
  --      424    | 1 
  --      425    | 1 
  --      426    | 1 
  --      427    | 1 
  --      428    | 1 
  --      429    | 1 
  --      430    | 1 
  --      431    | 1 
  --      432    | 1 
  --      433    | 1 
  --      434    | 1 
  --      435    | 1 
  --      436    | 1 
  --      437    | 1 
  --      438    | 1 
  --      439    | 1 
  --      440    | 1 
  --      441    | 1 
  --      442    | 1 
  --      443    | 1 
  --      444    | 1 
  --      445    | 1 
  --      446    | 1 
  --      447    | 1 
  --      448    | 1 
  --      449    | 1 
  --      450    | 1 
  --      451    | 1 
  --      452    | 1 
  --      453    | 1 
  --      454    | 1 
  --      455    | 1 
  --      456    | 1 
  --      457    | 1 
  --      458    | 1 
  --      459    | 1 
  --      460    | 1 
  --      461    | 1 
  --      462    | 1 
  --      463    | 1 
  --      464    | 1 
  --      465    | 1 
  --      466    | 1 
  --      467    | 1 
  --      468    | 1 
  --      469    | 1 
  --      470    | 1 
  --      471    | 1 
  --      472    | 1 
  --      473    | 1 
  --      474    | 1 
  --      475    | 1 
  --      476    | 1 
  --      477    | 1 
  --      478    | 1 
  --      479    | 1 
  --      480    | 1 
  --      481    | 1 
  --      482    | 1 
  --      483    | 1 
  --      484    | 1 
  --      485    | 1 
  --      486    | 1 
  --      487    | 1 
  --      488    | 1 
  --      489    | 1 
  --      490    | 1 
  --      491    | 1 
  --      492    | 1 
  --      493    | 1 
  --      494    | 1 
  --      495    | 1 
  --      496    | 1 
  --      497    | 1 
  --      498    | 1 
  --      499    | 1 
  --      500    | 1 

(4) By Chris Brody (chrisbrody) on 2021-07-23 22:31:33 in reply to 2 [link] [source]

Hello is there anything we can do to help get this resolved?

I would also be grateful if there could be a flag such as SQLITE_OMIT_DROP_COLUMN.

Thanks!

(5) By Richard Hipp (drh) on 2021-07-23 23:17:18 in reply to 4 [link] [source]

The problem was resolved by check-in 354a4db5cb769c6a (2021-04-18). The fix appears in release 3.36.0.

(6) By Chris Brody (chrisbrody) on 2021-07-23 23:36:21 in reply to 5 [link] [source]

Thanks. I did not see this info in the SQLite 3.36.0 release notes, and the ticket in https://www.sqlite.org/src/tktview?name=c88f3036a2 seems to be still open. Am I missing anything?

(7) By Richard Hipp (drh) on 2021-07-23 23:54:48 in reply to 6 [link] [source]

I just failed to close the ticket.

(8) By Chris Brody (chrisbrody) on 2021-07-24 00:10:38 in reply to 7 [source]

Thanks. I am sorry to say that I (still) do not see this info in the 3.36.0 release notes, think it could help someone else know that they should consider using 3.36.0 to get the bug fix.

(9) By Chris Brody (chrisbrody) on 2021-08-02 17:05:54 in reply to 5 [link] [source]

FYI it looks to me like this fix was in 3.35.5 as well: https://sqlite.org/releaselog/3_35_5.html