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 [source]

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 [link] [source]

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

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

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] [source]