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 ```
(2) By Richard Hipp (drh) on 2020-06-29 20:32:33 in reply to 1.1 [link]
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
(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.
(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.