Defense against dark arts doc pitfall: ALTER TABLE RENAME COLUMN requires function arg limit > 8

(1) By Warren Young (wyoung) on 2021-06-30 10:33:13 [source]

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?

(2) By Ryan Smith (cuz) on 2021-06-30 11:09:55 in reply to 1 [link] [source]

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?

(3) By Warren Young (wyoung) on 2021-06-30 11:13:52 in reply to 2 [link] [source]

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.

(4) By Ryan Smith (cuz) on 2021-06-30 11:15:58 in reply to 2 [link] [source]

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)

(5) By Ryan Smith (cuz) on 2021-06-30 11:23:06 in reply to 3 [link] [source]

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.

(6) By Warren Young (wyoung) on 2021-06-30 11:31:06 in reply to 4 [link] [source]

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.

(7) By Richard Hipp (drh) on 2021-06-30 11:57:42 in reply to 1 [link] [source]

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.

(8) By Warren Young (wyoung) on 2021-06-30 12:42:28 in reply to 7 [link] [source]

Based on the commit comment, that should do nicely. Thank you!