Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8
I just ran into a weird behavior in a rarely-run "upgrade database schema" script here: an
ALTER TABLE RENAME COLUMN call was resulting in
SQLITE_ERROR (SQL logic error) with a rather obscure verbose message:
too many arguments on function sqlite_rename_column in "UPDATE "main".sqlite_master SET sql = sqlite_rename_column(sql, type, name, 'main', 'MYTABLE', 2, 'MYCOLUMN', 0, 0) WHERE name NOT LIKE 'sqliteX_%' ESCAPE
I eventually traced it to the
-DSQLITE_MAX_FUNCTION_ARG=8 option I set for my local build of the SQLite shell, which my upgrade script wraps. I used that value at the docs' behest (§1.1.2) but it seems a value of 9 is needed to allow my particular case to work.
I'm not sure whether this post suffices to document the problem or if the docs should be changed somehow:
- Higher recommendation?
- Is 9 enough, or do more complicated cases require a higher value?
- Or, just warn about the interaction with
ALTER TABLE RENAME COLUMN?
That sqlite_rename_column() function has 9 arguments. Your max was 8, but 9 max is needed to allow that 9-argument function.
I'm not sure what part of that is surprising or needs to be better documented? Are you perhaps suggesting that the max does not apply to internal and UDF functions? (which I'm in support of for at least internals).
As for the question "Is 9 enough?" - Who knows? do you have UDFs that take more? Do you use any internal functions that do? Am I sorely misunderstanding your premise?
I didn't expect my own SQL to result in a call to this internal function, running me up against this limit.
Ultimately, I think the question comes down to this: if someone follows this documents' recommendations unquestioningly, is it reasonable to expect that this breaks
ALTER TABLE RENAME COLUMN?
I don't have any UDFs at all. I'm just using plain old SQL here.
Indeed, I realized after. So I'd like to change and resubmit my original reply to just:
+1 to having the docs mention a sensible lower bound for -DSQLITE_MAX_FUNCTION_ARG.
I think I realize more specific what the complaint it after trying it. You did not know there would be a 9-argument function in the automated scripts and so wish the documentation for "-DSQLITE_MAX_FUNCTION_ARG" to mention care when setting it lower than X (which seemingly could be X=9)
Indeed: there could be a whole table of values, listing what you break with all values from 1..9 or whatever the max recommended upper default limit ends up being.
Command-line shell test case:
CREATE TABLE t1(a,b,c,d); .limit function_arg 8 ALTER TABLE t1 RENAME COLUMN c to x; .schema
Should now be fixed on trunk. Please report back if you disagree.