SQLite Forum

CAST type-name is optional

CAST type-name is optional

(1) By ET (EricTsau) on 2021-08-04 07:32:48 [link] [source]

select typeof(cast('42' as)); integer

It appears that the type name passed to cast() is optional, however the docs don't reflect this, so can the cast railroad at https://www.sqlite.org/lang_expr.html be amended with an arrow from AS to ) bypassing type-name to show that it is optional?

(2) By Harald Hanche-Olsen (hanche) on 2021-08-05 08:57:42 in reply to 1 [source]

Poking around in the grammar at src/parse.y, I find the entry for CAST as follows:

expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. {
  A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1);
  sqlite3ExprAttachSubtrees(pParse->db, A, E, 0);

and looking for typetoken in the same file, there is this illuminating comment:

// A typetoken is really zero or more tokens that form a type name such
// as can be found after the column name in a CREATE TABLE statement.
// Multiple tokens are concatenated to form the value of the typetoken.

To emphasise, zero or more tokens. This makes good sense in the context of CREATE TABLE, less so in the case of a CAST expression.

I conjecture that this inadvertently legalised a CAST expression with no type designation. It certainly appears to be undocumented.

Perhaps the grammar should be tightened to disallow this; but if that cannot (or should not) be done for the sake of backward compatibility, I think it ought to remain undocumented, so as not to trick users into relying on it. Or, it could be documented, with a caveat that the resulting behaviour is undefined. (It seems to cast to numeric at the moment.) In either case, I think it had better be kept out of the syntax diagram.

Just my three cents' worth – inflation, y´know.

(3) By Larry Brasfield (larrybr) on 2021-08-05 13:08:20 in reply to 2 [link] [source]

For the sake of a virtue I call orthogonality, I think the parser should accept this construction. The rule is simple: However a type affinity could be specified in a column definition, that is how it can be specified in the cast.

However, as you advocate, I see no good reason to document it. The purpose of a cast is better served by use of an explicit type name rather than by how such defaults when not stated. The grammar is sensible but the semantics less so, in this context.

I was surprised to find that the said "default" is not BLOB as would be the case in a column definition. Instead it appears to be numeric (as you say.) To my way of thinking, this defeats the orthogonality virtue.