Bug with UPSERT and generated columns?
(1) By iffy (iffycan) on 2020-06-29 14:01:50 updated by 1.1
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 ```
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 ```