I respectfully disagree, as the existing wording:

Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed.

already emphasises the central point--the second sentence simply restates the first from an internal implementation POV. Adding a third statement that "this also applies to generated columns" invites the question "since you felt the need to call out a certain type of column, are there any column definitions that don't fall under this restriction?", which rather weakens the universal coverage ("every...must...") of the leading statement.

rather weakens the universal coverage

You ignored the second suggestion where this critique does not apply.

Expressions have type in SQLite, where "create table t (a as 42) strict;" does in fact specify a type for the column.

Strictly speaking, no it does not. That said, why would you want a special case for generated columns with constant values? That doesn't seem likely to come up in any practical use

why would you want a special case for generated columns with constant values

I don't. I did not request a new feature. I requested better documentation.

Strictly speaking, no it does not.

Yes it does, because SQLite has typeof(X). I would appreciate if you would provide an example where SQLite is unable to determine the type of an expression where the expression is valid for a generated column. In particular, that means the expression must follow limitation #3 as listed at https://sqlite.org/gencol.html. If I have overlooked something, which is very possible, then an example would both clarify things and help me learn SQLite.

But typeof applies to a value, not an expression.
It's gets the value of evaluating the expression.
You're asking for evaluating the result type of an un-evaluated expression.
Sure, in the case of a STRICT table, the expression's inputs are of known types,
unless there's an ANY typed one, in which case it's back to the general undeterminate case, IMHO.

Sure, in the case of a STRICT table, the expression's inputs are of known types...

Only known by the application, not by the library.

Suppose column_B is generated as frobnicate(column_A) where frobnicate is an application defined function with SQLITE_DETERMINISTIC set.

There is no way to know what the datatype of column_B is by inspection alone. That's why it must be specified in a STRICT table.

Suppose column_B is generated as frobnicate(column_A) where frobnicate is an application defined function with SQLITE_DETERMINISTIC set.

Does SQLite provide a way for application-defined functions to specify return type? If not (which appears to be the case), then they have return type ANY, and that is the type of column_B.

Again, however, I am NOT requesting a new feature. I am requesting better documentation.

Does SQLite provide a way for application-defined functions to specify return type?

Of course. There are many different sqlite3_result_* functions. But which one is called for any given execution cannot be determined by inspection alone.

I am requesting better documentation.

The docs for STRICT tables are very clear that:

Every column definition must specify a datatype for that column.

What is unclear about that? The six acceptable datatypes are listed immediately after that statement.

If there's still any confusion about the phrase "column definition", then the railroad diagrams on the CREATE TABLE page tell you exactly what that means.

For STRICT tables, the "column-def" route is modified so you must specfify a "type-name". The siding that bypasses it has been closed off. And "type-name" must be one of the six mentioned. The "column-constraint" diagram is unchanged.

The docs for STRICT tables are very clear...

As I said originally, I held the viewpoint that generated columns did specify a type by virtue of having an expression. This is how they differ from other columns. The modified column-def rule you mention is not shown anywhere that I can find. Taking the strongest view of your statement, I still cannot but believe you expect users to mentally modify formal grammar rules to accommodate implications of a general statement written in English. You are reaching across four different locations (strict table page, create table page, column-def rule, column-constraint rule) to put together this implication, and that's not going to be obvious to users learning from the documentation. I can only reply the same as you did, "what is unclear about that?"

As I said originally, I held the viewpoint that generated columns did specify a type by virtue of having an expression

And the docs are clear that by a "datatype" they mean one of the six listed words. Others have explained that an expression is not a datatype, and that it is not always possible to infer a datatype from an expression without evaluating it. Regardless of your viewpoint, that's not the syntax accepted by SQLite, which is that in every column definition of a STRICT table, the column name is followed by one of the six listed datatypes.

GENERATED AS is just one of many column constraints that can be applied to a column definition. NOT NULL is another. The railroad diagram for column constraints lists them.

If I understand correctly, you wish to emphasise the inclusion of one of those column constraints in case some readers do not understand the phrase "Every column definition". But why just that one? Why not emphasise that "Every column definition" also includes those with COLLATE clauses, or FOREIGN KEY constraints as well? It soon gets absurd, in my view.

That's why the railroad diagrams exist: to show what the syntax is. All available at the top of the relevant language page - in this example, the CREATE TABLE page. That is the anchor page for that SQL statement. It links to the STRICT page for more detail about that particular table option. There's possibly a valid argument for the STRICT page to show the specific restriction to the "column-def" railroad diagram that it changes from:

linerad = 10px
     linewid *= 0.5

     circle radius 10%
     arrow 2*arrowht
CN:  oval "column-name" fit
A1:  arrow 2*arrowht
     arrow
TN:  box "type-name" fit
     arrow 125%
CC:  box "column-constraint" fit with .w at TN.e+(2*linewid,-CN.ht)
     arrow from last arrow.end right even with CC.e
     arrow right even with 1.5*linewid east of CC.e
     circle same

     # type-name bypass
     arrow from A1.end right linerad then down 0.85*CN.ht \
        then right even with TN
     line right even with linerad east of TN.e then up even with TN \
        then right linerad

     # column-constraint loop back
     arrow from (CC.e,TN) right 2*arrowht then down even with CC then to CC.e
     arrow from CC.w left 2*arrowht then up even with TN then right

to the following:

linerad = 10px
     linewid *= 0.5

     circle radius 10%
     arrow 2*arrowht
CN:  oval "column-name" fit
A1:  arrow 2*arrowht
     arrow
TN:  box "type-name" fit
     arrow 125%
CC:  box "column-constraint" fit with .w at TN.e+(2*linewid,-CN.ht)
     arrow from last arrow.end right even with CC.e
     arrow right even with 1.5*linewid east of CC.e
     circle same

     # column-constraint loop back
     arrow from (CC.e,TN) right 2*arrowht then down even with CC then to CC.e
     arrow from CC.w left 2*arrowht then up even with TN then right

Personally, I think that is sufficiently covered by

Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed.

But feel free to keep putting your case to the devs.

But feel free to keep putting your case to the devs.

Is it really the devs that manage the documentation? The two are completely different skill sets, and I have rarely found someone skilled in both.

Regardless, that was in the first post, and they can act on it or not.

But why just that one?

Because this is the only constraint where a datatype is involved in the same statement as the constraint definition itself, and in fact is implied in the vast majority of actual code using it, even if posters here pull out application-defined deterministic functions (those must be very commonly used in generated columns!) to illustrate how dare I request a documentation change.

Generated columns are already special in the very way that strict tables differ from non-strict tables. Treating them specially is appropriate in that context.

Including the railroad diagram you posted would be an improvement to the current documentation, and would have saved me frustration. Is that included on some other page, and I missed it, or did you generate it just to illustrate a point that I already understood from the first time you said it? I made suggestions in the original post because it is rude to complain about a problem without also suggesting a fix, if one is capable of doing so, which can then be evaluated, discussed, changed, and improved. I regret posting at all now and, with a rapidly evaporating sense of obligation to follow-through on discussion that I started, will bow out. You win!

Is it really the devs that manage the documentation? The two are completely different skill sets, and I have rarely found someone skilled in both.

I believe so. SQLite has only a handful of devs. The website is a dry, technical reference manual - or at least that's how I use it. That obviosuly implies close linkage with the code. It isn't an education manual (there are plenty of other good places for that). The site's minimal styling is clearly designed to reinforce that usage.

That, combined with a 21-year track record of backward-compatibility at the same major version (3.x.x), means that SQLite is very conservatively managed. Factual errors and bugs are fixed very quickly. Subjective interpretations of reference documentation, which I believe this thread is about, would require significant evidence to convince the devs. After all, there are over a trillion SQLite databases in active use, with billions of end-users and many (millions?) who will write SQL to be executed on those databases. We all have opinions; we often believe ours is the best opinion; and we view the world from our own perspective. Satisfying the contradictions between those is impossible.

Your best bet (in my view as an enthusiastic user of SQLite) is to convince the devs of a factual inaccuracy in the docs.

I made suggestions in the original post because it is rude to complain about a problem without also suggesting a fix,

I think this gets at the heart of your dissatisfaction here. You started this thread with the implicit assumption that there must be a problem with the docs, and then suggested how it can be "fixed". I think some of the rest of us have yet to be convinced of that first step. The fact that you encountered a syntax error having read the docs does not automatically imply that the docs are inadequate. There might be other posssibilities.

I regret posting at all now

I can see that. SQLite's "open source but not open contribution" approach can be frustrating if you feel you want to contribute.

Is it really the devs that manage the documentation? The two are completely different skill sets, and I have rarely found someone skilled in both.

I believe so. SQLite has only a handful of devs.

It's really true. With the exception of doc patches which we take from the forum, every line of the SQLite website and API docs are written by members of the dev team. The API docs are extracted from the source code to generate those parts of the website, as Alan alludes to with the docs' "close linkage with the code".

The two are completely different skill sets, and I have rarely found someone skilled in both.

FWIW, that's also been my experience. To have several on one team is a statistical oddity.

SQLite has only a handful of devs.

Currently four active: two core topic experts, one generic (🙋) who sticks to the peripheral pieces, and one dedicated to the C#/DotNet components.

I made suggestions in the original post because it is rude to complain about a problem without also suggesting a fix

:-D Thank you!

SQLite's "open source but not open contribution" approach can be frustrating if you feel you want to contribute.

We recognize and sympathize with that, but sqlite has a unique position in the ecosystem which requires a steady and thoughtful guiding hand¹ to ensure that (A) it doesn't break any existing clients and (B) it's maintainable through the target goal of 2050 (while still supporting clients written yesterday). Both of those focuses strongly influence decisions regarding what goes in and what does not.

1. ^{^} To be clear, that's drh's, not mine.

...but sqlite has a unique position in the ecosystem which requires a steady and thoughtful guiding hand to ensure [certain focuses]. Both of those focuses strongly influence decisions regarding what goes in and what does not.

I did not request a new feature, which could affect those focuses. I requested better documentation, which would not impact those goals.

I made suggestions in the original post because it is rude to complain about a problem without also suggesting a fix,

this gets at the heart of your dissatisfaction here

No, not at all, not even close. As I repeated elsewhere in this thread, the suggestions were secondary to the only question in my original post:

Could the documentation emphasize that [generated columns need an explicit datatype and an implicit one is insufficient]?

I am dissatisfied because:

• A strawman was used to misinterpret: why would you want a special case for generated columns with constant values?
• That illustrative code with the constant value was nit-picked to ... I don't know why, actually, other than to disregard the actual request and quote documentation that they a priori judged to be "clearly stated"? That code was wrong, yes, but in what way does that affect anything else? They apparently didn't read the page where the change was requested, because they said the suggested edit was to stand alone... in the existing list of 12 items? No, it's a 13th item.
• Another strawman was used to explain to me that "You're asking for [a new feature]" (again, did not happen) while misunderstanding that ANY is just another type. ANY is listed on the documentation page in question, did they read it before replying?
• I was told that users should be expected to mentally modify grammar rules based on a general English statement to reach implied conclusions. Is this a serious proposition? Every difference from other systems does not need elaborately illustrated, but a single sentence or a single modified column-def diagram is neither elaborate, nor out of touch with how the documentation explains SQLite's differences in other places.
• That was followed up by actually repeating themself and modifying the grammar rule diagram in order to state, "That's why the railroad diagrams exist: to show what the syntax is." I asked if that diagram did in fact actually exist in the documentation, because I can't find it, and it's rather hard for a non-existent diagram to show anything, or for a diagram that exists on another page entirely and isn't mentioned on the page in question to be at the forefront of how you evaluate what is on that page.

Any user led astray by the documentation is an inadequacy of that documentation for that user. That is a tautology, what you called an implicit assumption, but you must accept it as a prerequisite to have the necessary context before jumping to "no" below, as you have done, in what should be evaluated, or else you risk ad hominem:

• Would a significant portion of the documentation's intended audience be similarly affected?
  • A subpart of this is easy to answer: Is this user part of the intended audience? Someone learning about SQLite's differences from other systems appears to be in that intended audience. Someone who took the time to find and report their negative experience might even deserve the courtesy of avoiding strawman replies. But even if this is "no", that doesn't preclude that the intended audience would be similarly affected.
  • A definitive answer here can be hard, and I can't answer any better than another who also hasn't spent time gathering feedback from SQLite users, such as through surveys or by conducting training. However, I can relate my experience and what caused it. Yet I find it likely that those replies listed above are not evaluating this question at all.
  • Even for someone who has widespread user feedback, we're talking a niche feature -- myself, I've used SQLite for almost two decades without having used strict tables. My prior use of generated columns -- both in SQLite and elsewhere -- reinforced the view that they have what SQLite calls column affinity by nature of being defined by an expression within the very same statement that only has typed inputs within the context of a strict table. (And before someone else points out that's an incorrect view: yes, I know, and I said so originally. Before someone attacks my character for how could I have so many years experience and make such a "rookie mistake": this is a niche feature and not every user is a power user, despite most of those participating on forums falling into that category.)
• If no, then this inadequacy is out of scope for the documentation.
• If yes, then what change should be made?

And yet, THANK YOU to the devs who did read and consider my original request, which I know happened because of the separate thread discussing a bug thereby found. THANK YOU for the near-infinite value I have gotten from SQLite compared to what I have paid back in prompting that bug investigation or that I will attempt to pay back through future participation in this forum.

Any user led astray by the documentation is an inadequacy of that documentation for that user. That is a tautology, what you called an implicit assumption, but you must accept it as a prerequisite to have the necessary context before jumping to "no" below, as you have done, in what should be evaluated, or else you risk ad hominem:

I think that it depends on the purpose and goal of the documentation, doesn't it? If people are coming to the SQLite website to learn from scratch, then the documentation will surely fail them. Is that an inadequacy? Should every document meet every possible requirement?

I don't use the SQLite website for anything other than technical reference data. I continually make (simple) errors in my SQL (I always have to look up the exact syntax for CREATE INDEX beacuse I so rarely create them). So I pop along, see the railroad diagrams, and am enlightened. Others might prefer a formal grammar spec (eg as given in the FTS docs), but I like picutres.

You and I probably have different expectations of the documentation. And that's fine - I'm quite chilled by that. We're only two of the very, very many users of the website, and entitled to express our opinions. But ultimately the pen/keyboard is held by the devs. I'm fine with that too. They wrote it, they support it, so they can say what goes into it - both code and docs.

I got very confused in this thread about whether a new syntax was being proposed for STRICT tables (the example you gave of create table t (a as 42) strict; and the reference to performing static analysis on expressions). Your point for raising this was no doubt clear in your own mind, but sadly not in mine.

A strawman was used to misinterpret: why would you want a special case for generated columns with constant values?

That wasn't a strawman, that was me being zoinked at the time of writing and forgetting the second half of your post when I went to reply. I'm not out to get you, I just have a terrible sleep schedule.

As for your last point in the same section, the railroad diagram was modified for use in the post. If you re-read his post, he was actually saying there's a case to be made for that modified diagram to be added to the strict tables page. I think this would actually be a good change, on the grounds that more railroad diagrams is more gooder (still wish the FTS5 docs had diagrams for that syntax...). I kind of wonder if adding the nested diagrams from column constraint would be beneficial, or if it would just be clutter.

That said, the double arrows after column-name have me tweaking, so for my own sake I've edited his version to fix that.

linerad = 10px
     linewid *= 0.5

     circle radius 10%
     arrow 2*arrowht
CN:  oval "column-name" fit
     arrow 2*arrowht
TN:  oval "datatype-name" fit
     arrow 2*arrowht
CC:  box "column-constraint" fit with .w at TN.e+(1.5*linewid,-CN.ht)
     arrow from last arrow.end right even with CC.e
     arrow right even with 1.5*linewid east of CC.e
     circle same

     # column-constraint loop back
     arrow from (CC.e,TN) right 2*arrowht then down even with CC then to CC.e
     arrow from CC.w left 2*arrowht then up even with TN then right

Generated columns are already special in the very way that strict tables differ from non-strict tables. Treating them specially is appropriate in that context.

That's a very good argument. In that light, I support your request for a doc clarification. But I'm also just a (long time) user and forum member (and its ML predecessor), FWIW.

the expression's inputs are of known types, unless there's an ANY typed one

This is not an exception. ANY is one of the known types. For a generated column, all of the expression's inputs are of known types, without exception. ANY is a valid type to specify on a generated column in a strict table.

Again, however, I am NOT requesting a new feature. I am requesting better documentation of the existing feature.

I requested better documentation.

I looked at the STRICT table documentation to see how I might improve it. It is not clear to me why you thought you could omit the datatype on a computed column. The documentation clearly says:

"Every column definition must specify a datatype..." (emphasis added).

Please help me to understand what I wrote in the documentation that lead you to believe that you could omit the datatype from a computed column on a STRICT table? I am truly perplexed.

I did realize the emphasis you added here to "Every" is the piece I was missing, and that's why the question I asked was, "could the documentation emphasize this?" In the context of this thread, literal emphasis on that one word is enough, but again, I knew that already once I was here -- I doubt it would have been enough beforehand. I spent a lot of time trying to figure out why the expression was insufficient or if the error was elsewhere in the statement, and I was most led astray by these factors, in no particular order:

• From that page: "SQLite attempts to coerce the data into the appropriate type using the usual affinity rules, as PostgreSQL, MySQL, SQL Server, and Oracle all do."
• "A STRICT table in SQLite does rigid type enforcement, in order to more closely mimic the behavior of other SQL database engines." https://www.sqlite.org/docs.html
• I've used all 4 of those listed at various times. Those other systems do not require a type on generated columns. The one I've been using the most recently at work, SQL Server, does not even allow a type.
• The detail gone into column affinity and how it is determined at this page: https://sqlite.org/datatype3.html#affinity_of_expressions
• That I was in one place using CAST (listed in the above page).
• That where I wasn't using CAST, I couldn't figure why the expression clearly didn't imply the type.
• That expressions are used to determine column declared types elsewhere: https://sqlite.org/lang_createtable.html#create_table_as_select_statements
• That I started off with a non-trivial demonstration for co-workers who are not ready to embrace flexible typing, while my experience with SQLite is not with strict tables. It was in re-starting from scratch to build up to that practical demonstration that I found my gap in understanding. For a similar reason (bridging these co-workers), I wanted primary keys to imply not null without extra work, which is the default in strict tables.
• That I wanted to attempt the following retrospective: "In retrospect, perhaps it would have been less confusing if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default." https://sqlite.org/quirks.html#flexible_typing

I don't know if that's the level of detail you want in an answer (yours is the first reply to ask), and I recognize the last three go beyond the documentation. I did not foresee that level of detail was warranted.

Edit:

I recognize the last three two go beyond the documentation.

And this can be added:

• "Generated columns work the same." ... "Only the "AS" keyword and the parenthesized expression are required." (https://sqlite.org/gencol.html#syntax)

It was the two pages about strict tables and generated columns I was most flipping between. I closely scrutinized the generated column sections on Capabilities and Limitations, believing the fault was there. The error only says "missing datatype".

You ignored the second suggestion where this critique does not apply.

It absolutely applies. This:

Generated columns work the same. Like other columns, a datatype must be explicitly specified.

can't stand by itself. It needs the "Every column definition..." statement for the necessary context, so it's basically your first option, but worded slightly differently.

As for:

create table t (a as 42) strict;

not throwing an error, that's definitely a bug, as it's clearly stated:

Only the "AS" keyword and the parenthesized expression are required.

and, further down:

Every table must have at least one non-generated column.

Sure enough, when those rules are respected:

% sqlite3
SQLite version 3.50.1 2025-06-06 14:52:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a INT, b AS 42) strict;
Parse error: near "42": syntax error
  create table t(a INT, b AS 42) strict;
               error here ---^
sqlite> create table t(a INT, b AS (42)) strict;
Parse error: missing datatype for t.b
sqlite> create table t(a INT, b INT AS (42)) strict;
sqlite>

I respectfully disagree. Your critique states an avoidance of repeating what should be covered by the general statement:

[inviting] the question "since you felt the need to call out a certain type of column...", which rather weakens the universal coverage ("every...must...") of the leading statement.

If the above critique is applied to the list where I made my second suggestion, then the entire list should be removed. After all, why did someone feel the need to call out those 12 items, and doesn't that weaken the "universal coverage of the leading statement"? I believe the documentation would be worse if this list were to be removed, and therefore I must say this critique does not apply in this location. (It does apply to my first suggestion. That makes sense.)

My actual question is in my first post -- and it's so very frustrating when everyone but you has ignored it to tell me I'm asking something different:

Could the documentation emphasize [that generated columns need an explicit datatype and an implicit one is insufficient]?

Yet you also ignored this question to shoot down the suggested edits from a theoretical approach of "one general statement is enough for users to grasp all the implications" which comes off very heavy-handedly. Do you truly believe no documentation improvement is possible, are you willing to step forward with an alternative suggestion, or are you going to continue to nit-pick syntax errors that were in fact made, but which have no bearing on the actual topic from the original post?

My experience would have been much improved by this small documentation change. I am but one user, but I believed this was general enough that I could contribute it back to SQLite, which I have very much enjoyed. I have learned two things from this experience: (1) the static analysis which is so routine in every other popular database system really is problematic in SQLite even in strict tables (and that was learned from an entirely separate thread, not this one! https://sqlite.org/forum/forumpost/2a17c35e10) and (2) I never want to participate in this forum again.