On 2016-04-05 10:19 AM, Richard Hipp wrote:
> This could easily be considered a bug fix rather than a regression.
> Please explain why you think it is important to know the "type" of a
> column in a view?
One should be able to treat a view the same as a base table, not even having to
know whether a table is one type or the other, when it doesn't matter. Type
information should be as readily available, or not, for a view as a base table.
> Or, consider this situation:
>
> CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL);
> CREATE VIEW v3 AS SELECT w+x+y+z FROM t2;
>
> What should "PRAGMA table_info('v3')" report as the column type?
The answer to that question is, the same type as the type that "+" results in
given those arguments. Either the result of that summation is of some
particular data type, eg NUMERIC, in which case use that, or summing different
numeric types is illegal, in which case trying to use the view would fail.
> It seems to me that the most consistent answer is that the "type" of
> columns in a VIEW should always be an empty string.
That's only consistent if you do the same thing with base tables.
Alternately, you can exploit the explicit column list that is optional in a
CREATE VIEW:
CREATE VIEW v3 (foo NUMERIC) AS SELECT w+x+y+z AS foo FROM t2;
So if a VIEW definition uses that syntax, that's what the returned column type
is; otherwise the returned column type is the empty string.
Personally, I think using the expression value type is the best though, and
works without any schema changes.
-- Darren Duncan
