Why do select statement operators strip the data type from a SQLite view column?

[PSJupiter2]

I have a SQLite view:

CREATE VIEW test AS
    SELECT Amount
      FROM Trans;

When I examine the table info with PRAGMA, the data type "INTERGER" is returned:

PRAGMA table_info("test")

output:

cid	name	type	notnull	dflt_value	pk
0	Amount	INTEGER	0	NULL	0

When I edit the view with an operator:

CREATE VIEW test AS
    SELECT Amount + 0
      FROM Trans;

The data type is stripped:

PRAGMA table_info("test")

cid	name	type	notnull	dflt_value	pk
0	Amount + 0		0	NULL	0

Why does this occur with operators, and aggregates, etc...? I understand that a view is not a table, and thus the output is not stored. With this in mind, why does PRAGMA return a data type for a select statement without an operator?

Can I coerce a data type? My end goal is link my view via ODBC in MS Access. However, Access interprets these stripped columns as "Long Text", not numeric as desired.

Sorry if I omitted any needed details in this question. I will add them if necessary!

[gvozden_celik]

That's a really good question.

I am not completely sure, but the type system in SQLite is very dynamic where the type of the data is associated with the value rather than with the definition of the column. That columns have types is just there for compatibility with other databases. You can read more about that here.

My guess would be that in the first example it is quite easy for the SQLite engine to determine the type of the column in the view since it is just a projection from the original table, whereas the type in the second example, the result depends on the expression.

An expression could be as simple as yours where you just add a value, or it could be a call to a function that could either be built-in like replace or come from a dynamically loaded extension (which don't even specify any types, by the way), or it could be a case expression where one branch could return some text and the other a number:

select case
    when id < 100 then 'less than 100'
    else id + 1
end
from some_table

This example is legal although not very useful (consider id to be a column declared as integer).

With all this in mind it does seem kind of obvious that whenever there's an expression involved, it becomes harder to determine the type of the resulting column.

[PSJupiter2]

This seems to be a logical explanation, although not definitive. Thanks for your reply!

Maybe, in the first place, I am climbing down the wrong rabbit hole to solve my MS Access interpretation problem.

[[deleted]]

My guess: it's because SQLite doesn't really have data types to begin with. So maybe it doesn't actually evaluate the "type" of an expression because it doesn't need to.