SQLite Forum

Serious problems with STORED columns
Login
If it's considered a bug, and given that using it in a stored column will certainly cause 'chaos' down the road on some unsuspecting 'idiot', like myself, who years later may try to recover a database from an old dump only to find out **AT THAT TIME** the table definition should have been different.

On the other hand, because this stored column is physically stored in the database (regardless of the low level details, i.e., how it may differ from a normal column), IMO a safer and usable approach would be to use some new `pragma` to make stored columns temporarily appear as normal columns so they can be loaded as-is without computation.

And if you're quick to dismiss this idea because you think datetime() is the only possible 'failure' scenario, consider time-consuming calculations.

Isn't the whole point of having stored (instead of virtual) columns to save repeated (and possibly very expensive) calculations?

Consider a table for each row of which you have to spend N seconds to calculate the stored value.  Now, after years of using the database, and thousands of accumulated rows, you need to reload the dump.  All those calculations will happen at the same time, making the restoration a horror  task.

Now, if you tell me you shouldn't have used a stored column for that case but you should have used a normal ('real') column instead, I will tell you: YES but why on Earth do we then ever need stored columns at all?

Everything that is not significantly time consuming to recalculate can be done with virtual columns that don't need to store anything.