SQLite Forum

Bug with UPSERT and generated columns?
Login
Here's a transcript of my session demonstrating what I think is a bug:

```
sqlite> SELECT sqlite_version();
sqlite_version()
3.32.1
sqlite> 
sqlite> -- Make a table with generated columns
sqlite> CREATE TEMPORARY TABLE tab (
   ...>     prim DATE PRIMARY KEY
   ...>     ,a INTEGER
   ...>     ,comp INTEGER
   ...>       AS (a)
   ...>     ,b INTEGER
   ...>   );
sqlite> 
sqlite> -- Add some data
sqlite> INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0);
sqlite> 
sqlite> -- Check that each column is 0 like I expect
sqlite> SELECT * FROM tab;
prim|a|comp|b
2001-01-01|0|0|0
sqlite> 
sqlite> -- Do an UPSERT on the b column
sqlite> INSERT INTO tab (prim, b)
   ...>   VALUES('2001-01-01',5)
   ...>   ON CONFLICT(prim)
   ...>     DO UPDATE SET
   ...>       b=excluded.b;
sqlite> 
sqlite> -- Now b is NULL rather than 5
sqlite> SELECT * FROM tab;
prim|a|comp|b
2001-01-01|0|0|
```

I expect `b` to be `5` at the end, but it's `NULL`.

If, when creating the table, I reorder the columns so that `b` comes before `comp`, then everything works as expected:

```
sqlite> CREATE TEMPORARY TABLE tab (
   ...>     prim DATE PRIMARY KEY
   ...>     ,a INTEGER
   ...>     ,b INTEGER
   ...>     ,comp INTEGER
   ...>       AS (a)
   ...>   );
sqlite> 
sqlite> -- Add some data
sqlite> INSERT INTO tab (prim, a, b) VALUES ('2001-01-01', 0, 0);
sqlite> 
sqlite> -- Check that each column is 0 like I expect
sqlite> SELECT * FROM tab;
prim|a|b|comp
2001-01-01|0|0|0
sqlite> 
sqlite> -- Do an UPSERT on the b column
sqlite> INSERT INTO tab (prim, b)
   ...>   VALUES('2001-01-01',5)
   ...>   ON CONFLICT(prim)
   ...>     DO UPDATE SET
   ...>       b=excluded.b;
sqlite> 
sqlite> -- Now b is 5 as expected
sqlite> SELECT * FROM tab;
prim|a|b|comp
2001-01-01|0|5|0
```