On 12/27/19, Richard Hipp <d...@sqlite.org> wrote: > > This is the third such false-positive bug like that this month....
And now there is a fourth: https://sqlite.org/src/info/5fbc159eeb092130c6f2 CREATE TABLE t0(c0 NOT NULL DEFAULT 1, c1 AS(c0) UNIQUE); REPLACE INTO t0 VALUES(NULL); If you understood my prior email, then you should clearly see what is going on here. The t0.c0 starts out as NULL. That value is SCopy-ied into t1.c1. Then the REPLACE causes t0.c0 to be overwritten with a numeric 1, which invalidates t1.c1, causing a memIsValid() assert further along in the processing. But the overwrite of t0.c0 didn't really disturb the value in t1.c1 since NULLs are copied by value, not by reference. But notice how this reveals a deeper more subtle problem in the new (unreleased) generated columns feature. The values of generated columns are computed *before* the NOT NULL constraints run during the INSERT. But the NOT NULL constraints might cause (normal) columns to change values due to the REPLACE. If those normal columns where previously used by generated columns, it might cause incorrect generated column values to be stored (for a STORED generated column) or incorrect entries to be written in indexes based on VIRTUAL generated columns (as in the case above). This is a design problem in generated columns. It has never come up before with another database because (as far as I know) SQLite is the only database that supports both generated columns and the NOT NULL ON CONFLICT REPLACE behavior. I suppose the correct solution here is to recompute the values of all generated columns *after* all NOT NULL ON CONFLICT REPLACE constraints have been run, if those constrains caused any changes. The point of this email: The bug report complains about the memIsValid() assertion fault. That assertion fault is not really a problem. It is more like a compiler warning. But in this case, the compiler warning lead me to discover a different, subtle, and unrelated problem in the design. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
