SQLite Forum

Bug with UPSERT and generated columns?
Login

Bug with UPSERT and generated columns?

(1.1) By iffy (iffycan) on 2020-06-29 20:16:58 edited from 1.0 [link]

I update a value to `5` within the `ON CONFLICT DO UPDATE` part of an UPSERT, but instead the value is set to `NULL`.  This has something to do with the order in which generated columns are defined on the table.  Here's a transcript of my session demonstrating the 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
```

(2) By Richard Hipp (drh) on 2020-06-29 20:32:33 in reply to 1.1

Please try this with the latest [prerelease snapshot][1] and report back if
you continue to have problems.  Thanks.

[1]: https://sqlite.org/download.html

(4) By iffy (iffycan) on 2020-06-30 17:27:24 in reply to 2 [link]

Works great!  Thank you!

And as an aside, it makes me happy to see TCL tests.

(3) By ddevienne on 2020-06-29 20:50:31 in reply to 1.1 [link]

See [](https://www.sqlite.org/src/info/fa9d93cf32fac4b8) for Richard's fix.