Hello, On 2016-04-06 09:43, Darren Duncan wrote: > On 2016-04-05 10:19 AM, Richard Hipp wrote:
>> 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. Non--consistency: sqlite> CREATE TABLE a (a DECIMAL(10)); sqlite> CREATE TABLE b AS SELECT * FROM a; sqlite> CREATE VIEW bv AS SELECT * FROM a; sqlite> PRAGMA table_info(b); 0|a|NUM|0||0 sqlite> PRAGMA table_info(bv); 0|a||0||0 Info on column's affinity (besides if it is a declared one --- DECIMAL, FLOAT --- or a real one --- NUMERIC, REAL) is still useful, when comparison is made. Column's affinity is still propagated: sqlite> CREATE TABLE b2 AS SELECT * FROM bv; sqlite> PRAGMA table_info(b2); 0|a|NUM|0||0 so the point is that ``PRAGMA table_info'' stopped displaying column's affinity in case of views. On the other side, views are not tables so a consistency does not require to preserve that info. In 3.11.x ``PRAGMA table_info'' was not displaying an affinity in case of expressions, too: === SHELL 3.11.x === sqlite> CREATE VIEW av2 AS SELECT CAST(+a AS NUMERIC) FROM a; sqlite> PRAGMA table_info(av2); 0|CAST(+a AS NUMERIC)||0||0 so the decision was to remove that view's info at all. Now, we know that this behavior was changed by an arbitrary, intentional decision. We can debate about correctitude of that decision (there are pros and cons), however more important thing is to describe the fact in the documentation: http://sqlite.org/pragma.html#pragma_table_info: ``The table named in the table_info pragma can also be a view.'' --- in case of views only column's id and name are displayed. > 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; Syntax error. Did you mean: CREATE VIEW v3 (foo) AS SELECT CAST(w+x+y+z AS NUMERIC) FROM t2; -- best regards Cezary H. Noweta
