Bad interaction between STRICT and generated column in 3.37.2
(1) By Steve M (steve_m) on 2022-01-17 21:36:59 [source]
The script below causes a confusing error message:
Error: near line 15: stepping, cannot store REAL value in REAL column transactions.credit (19)
CREATE TABLE csv_import_table (
"debit" TEXT,
"credit" TEXT
);
INSERT INTO csv_import_table
VALUES ('', '250.00');
CREATE TABLE IF NOT EXISTS transactions (
debit REAL,
credit REAL,
amount REAL GENERATED ALWAYS AS (ifnull(credit, 0.0) - ifnull(debit, 0.0))
) STRICT;
INSERT INTO transactions
SELECT
nullif(debit, '') AS debit,
nullif(credit, '') AS credit
FROM csv_import_table;
I'm using sqlite 3.37.2 installed via homebrew on MacOS 10.15.7:
/tmp % sqlite3 --version
3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5d17a0
(2) By Steve M (steve_m) on 2022-01-17 22:59:11 in reply to 1 [link] [source]
I had this problem occur in another case just now, which has led me to refine my hypothesis.
It also seems to matter that the value being inserted ('250.00') is equivalent to an integer. If you replace it by '250.01', for example, it runs fine.
(3) By Richard Hipp (drh) on 2022-01-17 23:13:00 in reply to 1 [link] [source]
Simplified test case:
CREATE TABLE t1(x REAL, y REAL AS (x)) STRICT; INSERT INTO t1 VALUES(5);
(4) By Steve M (steve_m) on 2022-01-17 23:16:28 in reply to 3 [link] [source]
Confirmed, that reproduces the issue: /tmp % sqlite3 test4.sqlite3 SQLite version 3.37.2 2022-01-06 13:25:41 Enter ".help" for usage hints. sqlite> CREATE TABLE t1(x REAL, y REAL AS (x)) STRICT; sqlite> INSERT INTO t1 VALUES(5); Error: stepping, cannot store REAL value in REAL column t1.x (19) sqlite>