On 04/05/2010, at 3:14 AM, 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?
I've had the same issue. In the end I had to parse my view functions in my own code and look for functions that give a particular type of result. So, for instance, round() gives an integer, round(..., 2) gives a real, concat() gives text. I also look for a cast(... as type) to use that declared type. It's fiddly and I would have hoped SQLite would have at least declared the type if a cast was present, but it seems not. I actually raised this issue on this list back in September 2009: On 22/09/2009, at 10:08 AM, BareFeet wrote: >> 4. Improve the declared_type result of a column to show a declared type for >> an expression in a view. The declared_type C call and the pragma >> table_info() SQL call currently return the type of a column in a view if the >> column is simply referring to a column in a table. But if the column in the >> view is an expression, it returns null. I would like to see it return the >> declared type of the outer function of the expression, since most functions >> have a known result type. For instance, if a column in a view is defined as >> cast(mycomplexfunction() as integer), then the declared type is known to be >> an integer. If the function is abs(), the type is real. If the function is >> length(), the type is integer. Obviously a few functions such as coalesce() >> have varied types so they would still have to show a declared_type of null, >> but the cast() function gives the SQL author the chance to explicitly >> declare the type of such a column. On 06/05/2010, at 7:59 AM, Pavel Ivanov wrote: >> I interpret the silence on the lis that anyone agrees that SQLite has a bug > > Generally silence on this list means that everybody disagrees with you and/or > doesn't see enough arguments in your email to even start any > discussion. When everybody agrees that SQLite has a bug you get a lot of > responses almost immediately. In my case, my silence was "oh good, someone else is mentioning this issue. I won't waste bandwidth by saying "me too" but will watch closely for any replies". >> Why do you want declared data type (which basically doesn't have any meaning >> in SQLite at all) in a view in a first place? Why having value type is not >> enough? I want declared types of a view column for the same reasons I want declared types of a table column. For instance, I can format the output correctly, such as tight aligned for integers, two decimal places for reals, left aligned and wider for text, special handling for a blob. Only having value type means I have to search through the results of a query to find the first non-null result value and examine it's type and hope that it's the same as the column's (un)declared type. That's inefficient. Obviously, when I am displaying individual cells of data I can look at the value type, but the declared type of the column means I can pre-format the column before displaying the data. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
