I interpret the silence on the lis that anyone agrees that SQLite has a bug because there seems to be no way to get VIEWS returning the column type if the column is calculated or a function. This also breaks compatibility as mentioned in http://www.sqlite.org/datatype3.html ("SQL statement that work on statically typed databases should work the same way in SQLite."). => Time for a ticket?
-S. 2010/5/3 Stefan Keller <sfkel...@gmail.com>: > Unfortunately the application which reads from this view needs that > all columns are typed - even if the values types deviate from it - and > I think this is a logical assumption. So, I fear I do have only one > chance and SQLite doesn't let me do it: > > CREATE VIEW myview AS > SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; > > To me it seems like an inconsistency (or bug) when TABLES allow > declaration of types and VIEWS only eventually. > > -S. > > 2010/5/3 Simon Slavin <slav...@bigfraud.org>: >> >> On 3 May 2010, at 6:14pm, Stefan Keller wrote: >> >>> But in SQLite if a view column comes from a function result or some >>> computation, then the column type is NULL...!? It's not taking the >>> result-type as mentioned in the manual >>> (http://www.sqlite.org/lang_select.html) - even when I try to do a >>> CAST in the projection clause, like this: >>> >>> SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; >>> >>> I mean that a VIEW should behave like a (read only) TABLE in any case. >>> => Is there a way to give such columns a type anyway? >> >> You get two chances to CAST, one when you define the VIEW, and another when >> you SELECT from the VIEW. If one of them doesn't enforce the type of >> evaluation you want, the other probably will. As you say, VIEW columns >> don't have types at all. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
