SQLite Forum

Timeline
Login

3 forum posts by user iffycan

2020-06-30
17:27 Reply: Bug with UPSERT and generated columns? (artifact: 8cbbae4ecd user: iffycan)

Works great! Thank you!

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

2020-06-29
20:16 Edit: Bug with UPSERT and generated columns? (artifact: 73b9a8ccfb user: iffycan)

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
14:01 Post: Bug with UPSERT and generated columns? (artifact: 7ebd2374bc user: iffycan)

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