Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Thanks Stephan for your feedback. Bad news for me. I see two solutions: - hire a dev for this. I'm not following enough sqlite lists to estimate if this is feasible. Any opinion? - hire a QGIS dev to hack a autodetection of type using content of fields. Ugly, probably slow and prone to errors.. But feasable since I have some devs. Cheers, Régis Cordialement, Régis Haubourg Régis Haubourg Administrateur de données Géographiques Département des Systèmes d'Information (DCSI) Agence de l'eau Adour Garonne 90 rue du Férétra, 31078 Toulouse Cedex4 Tél: 05 61 36 82 58 Mail: regis.haubo...@eau-adour-garonne.fr http://www.eau-adour-garonne.fr Accédez aux données sur l'eau : http://adour-garonne.eaufrance.fr/ > -Message d'origine- > De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] De la part de Stefan Keller > Envoyé : samedi 15 juin 2013 23:09 > À : General Discussion of SQLite Database > Objet : Re: [sqlite] Computed columns in VIEWs return NULL but should be > able to be typed! Any ideas? > > Hi Régis > > I'd wish to give you a solution but I'm sorry to have new news about that > issue. > > I'd be happy if there are any SQlite devs around to give you a solution in > order to make this database more usable (an more SQL compatible). > > Yours, Stefan > > > 2013/6/11 regish : > > Hi all, > > I'm starting to use SQLITE in GIS use cases. I'm facing this view > > typing column issue, which prevent my favourite client from > > interpreting correctly numeric data types. I'm using QGIS, so I won't > > be able to map numeric values (they fall back as text values). > > Is there anything new since 2010? If not, should I suggest Qgis Devs > > to hack the field type detection for views? > > Régis > > > > > > > > -- > > View this message in context: > > http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return- > N > > ULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html > > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Hi Régis I'd wish to give you a solution but I'm sorry to have new news about that issue. I'd be happy if there are any SQlite devs around to give you a solution in order to make this database more usable (an more SQL compatible). Yours, Stefan 2013/6/11 regish : > Hi all, > I'm starting to use SQLITE in GIS use cases. I'm facing this view typing > column issue, which prevent my favourite client from interpreting correctly > numeric data types. I'm using QGIS, so I won't be able to map numeric > values (they fall back as text values). > Is there anything new since 2010? If not, should I suggest Qgis Devs to > hack the field type detection for views? > Régis > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Hi all, I'm starting to use SQLITE in GIS use cases. I'm facing this view typing column issue, which prevent my favourite client from interpreting correctly numeric data types. I'm using QGIS, so I won't be able to map numeric values (they fall back as text values). Is there anything new since 2010? If not, should I suggest Qgis Devs to hack the field type detection for views? Régis -- View this message in context: http://sqlite.1065341.n5.nabble.com/Computed-columns-in-VIEWs-return-NULL-but-should-be-able-to-be-typed-Any-ideas-tp56769p69350.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
On 11/05/2010, at 6:12 PM, Ben Harper wrote: > To determine the type of columns in a view I use > SELECT typeof(column) FROM viewname LIMIT something; > > Unfortunately if most of the column data is NULL then you can end up having > to scan the entire table. Yes, I also do that as a last resort: 1. Check pragma table_info(MyView) to see if SQLite knows the column type. It seems to provide it only if the view's column directly references a table column. 2. If 1 returns null (unknown), then parse the schema of the view to look for the cast(expression, type) or other functions that return a known type. 3. If 2 fails to provide type through a known result type of a function, then go through the actual result data to see what types are returned. If all rows return the same type (ignoring null results) then use that type. Otherwise type is unknown (varies or all null). Of course this doesn't work if there are currently no rows in the view, which makes it impossible to determine the input type for the user if they choose to insert a new row. By the way, your select statement will scan the whole view. Limit just truncates the result, but SQLite is first scanning the whole thing. You'd be better served by something like: select typeof(ColumnName) as "Type" from ViewName where Type not null group by "Type"; If the result gives one row, then you have your type. If zero or multiple rows, then type could not be determined. As already mentioned, though, this won't help if the view currently has no rows, or if the value of MyColumn for the present rows is null, and is of limited value of there are just a few existing rows with 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
To determine the type of columns in a view I use SELECT typeof(column) FROM viewname LIMIT something; Unfortunately if most of the column data is NULL then you can end up having to scan the entire table. I'm not sure how SQlite calculates these types, but this simple workaround has been OK for me so far. On my simple views I always see homogenous column types. Ben -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stefan Keller Sent: 08 May 2010 04:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas? Right, I don't want to lessen scalability of my application. But I also don't want to introcude redundancy just because some columns on the the view lack a type. I assume SQLite wants to adhere to the relational model which states: The result of a select statement is another relation. And "A view is just a relation (a table), but stores a definition, rather than a set of tuples." (from chapter fundamentals of "Database Management Systems" by Ramakrishnan & Gehrke, 2002). So, for the "consumer" a view should behave like a table. This has the following advances: * Decoupling: Rename physical column names without breaking code which reads views. * Security: One can grant read permission on a view without granting any permission to the underlying table. * Simplicity: It's easier to write queries. * Helps to avoud redundancy: Views can have calculated columns, like age (from birthdate) or tax or rebate. So its pretty clear to me that views should have the option to return types. CAST could be a solution. Determintation of the return type of a calculation could be another step. That's from the basics of computer languages. -S. 2010/5/7 Pavel Ivanov : >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). > > So as I see it: you have some universal code for displaying and > inputing data. And you don't want to lessen scalability of your > application by hard-coding the relation between column names and their > data types. So you can create additional table that will contain this > information. And I think this solution is better than just relying on > declared type of columns - more straightforward and more > understandable by somebody coming to your project in the future. > > > Pavel > > On Thu, May 6, 2010 at 7:11 PM, Stefan Keller wrote: >> Thank you, Tom and Dan, for your constructive answers. >> >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). >> >> I understand that it's difficult to implement result-types for >> expressions in general. To me it would be enough if there would be at >> least one way to assign result-types with a CAST "wrapper" as I have >> mentioned before. >> >> Does anybody know whom to present this proposal in order remedy this >> current inconsistency in SQLite? sqlite-dev? >> >> In the meantime I thought of a hack and to assign the result-type by >> hand in the data dictionary after having created the VIEW. >> Would this be a feasible work around? >> >> Yours, S. >> >> 2010/5/6 BareFeetWare : >>> On 06/05/2010, at 2:51 PM, Dan Bishop wrote: >>> >>>> BareFeetWare wrote: >>>>> >>> >>>>> 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. >>>>> >>> >>>> A CAST expression could be assigned a declared type >>> >>> Yes, that should be fairly easy to do in SQLite, simpler than my adding it >>> in my own code (which then requires my own parser - not a trivial exercise). >>> >>>> but it would be difficult to implement it for expressions in general. Not >>>> only would you have to declare a type for every function >>> >>> Yes, probably half the functions don't have
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Right, I don't want to lessen scalability of my application. But I also don't want to introcude redundancy just because some columns on the the view lack a type. I assume SQLite wants to adhere to the relational model which states: The result of a select statement is another relation. And "A view is just a relation (a table), but stores a definition, rather than a set of tuples." (from chapter fundamentals of "Database Management Systems" by Ramakrishnan & Gehrke, 2002). So, for the "consumer" a view should behave like a table. This has the following advances: * Decoupling: Rename physical column names without breaking code which reads views. * Security: One can grant read permission on a view without granting any permission to the underlying table. * Simplicity: It's easier to write queries. * Helps to avoud redundancy: Views can have calculated columns, like age (from birthdate) or tax or rebate. So its pretty clear to me that views should have the option to return types. CAST could be a solution. Determintation of the return type of a calculation could be another step. That's from the basics of computer languages. -S. 2010/5/7 Pavel Ivanov : >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). > > So as I see it: you have some universal code for displaying and > inputing data. And you don't want to lessen scalability of your > application by hard-coding the relation between column names and their > data types. So you can create additional table that will contain this > information. And I think this solution is better than just relying on > declared type of columns - more straightforward and more > understandable by somebody coming to your project in the future. > > > Pavel > > On Thu, May 6, 2010 at 7:11 PM, Stefan Keller wrote: >> Thank you, Tom and Dan, for your constructive answers. >> >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). >> >> I understand that it's difficult to implement result-types for >> expressions in general. To me it would be enough if there would be at >> least one way to assign result-types with a CAST "wrapper" as I have >> mentioned before. >> >> Does anybody know whom to present this proposal in order remedy this >> current inconsistency in SQLite? sqlite-dev? >> >> In the meantime I thought of a hack and to assign the result-type by >> hand in the data dictionary after having created the VIEW. >> Would this be a feasible work around? >> >> Yours, S. >> >> 2010/5/6 BareFeetWare : >>> On 06/05/2010, at 2:51 PM, Dan Bishop wrote: >>> BareFeetWare wrote: > >>> > 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. > >>> A CAST expression could be assigned a declared type >>> >>> Yes, that should be fairly easy to do in SQLite, simpler than my adding it >>> in my own code (which then requires my own parser - not a trivial exercise). >>> but it would be difficult to implement it for expressions in general. Not only would you have to declare a type for every function >>> >>> Yes, probably half the functions don't have a consistently returned type >>> (eg length, concat). The ones that don't (eg coalesce) should just be left >>> to return a null (unknown) type. The schema developer can simply wrap those >>> expressions in a cast if they want a predetermined output type. >>> but you'd have to do it for operators as well, and what type should A * B be if A is "SMALLINT" and B is "UINT"? >>> >>> I hadn't thought of operators. As far as I know, mathematical operators (* >>> / + -) only work with integers and reals, Perhaps the result type could be >>> set to "numeric" or some other superclass. Or, to get real fancy, if the >>> two operands are declared integers, then the result is an integer; if one >>> is real then the result is real. AFAIK SMALLINT and UINT are both treated >>> by SQLite as integer, so the result would be an integer. >>> Should relational operators have a declared type of BOOLEAN? >>> >>> Good idea. >>> >>> 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/
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
> To Pavel: My application reads the column types out in order to pretty > print the values - as mentioned by Tom - but also to generate a dialog > for entering new data (of course combined with INSTEAD OF TRIGGERs). So as I see it: you have some universal code for displaying and inputing data. And you don't want to lessen scalability of your application by hard-coding the relation between column names and their data types. So you can create additional table that will contain this information. And I think this solution is better than just relying on declared type of columns - more straightforward and more understandable by somebody coming to your project in the future. Pavel On Thu, May 6, 2010 at 7:11 PM, Stefan Keller wrote: > Thank you, Tom and Dan, for your constructive answers. > > To Pavel: My application reads the column types out in order to pretty > print the values - as mentioned by Tom - but also to generate a dialog > for entering new data (of course combined with INSTEAD OF TRIGGERs). > > I understand that it's difficult to implement result-types for > expressions in general. To me it would be enough if there would be at > least one way to assign result-types with a CAST "wrapper" as I have > mentioned before. > > Does anybody know whom to present this proposal in order remedy this > current inconsistency in SQLite? sqlite-dev? > > In the meantime I thought of a hack and to assign the result-type by > hand in the data dictionary after having created the VIEW. > Would this be a feasible work around? > > Yours, S. > > 2010/5/6 BareFeetWare : >> On 06/05/2010, at 2:51 PM, Dan Bishop wrote: >> >>> BareFeetWare wrote: >> 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. >> >>> A CAST expression could be assigned a declared type >> >> Yes, that should be fairly easy to do in SQLite, simpler than my adding it >> in my own code (which then requires my own parser - not a trivial exercise). >> >>> but it would be difficult to implement it for expressions in general. Not >>> only would you have to declare a type for every function >> >> Yes, probably half the functions don't have a consistently returned type (eg >> length, concat). The ones that don't (eg coalesce) should just be left to >> return a null (unknown) type. The schema developer can simply wrap those >> expressions in a cast if they want a predetermined output type. >> >>> but you'd have to do it for operators as well, and what type should A * B >>> be if A is "SMALLINT" and B is "UINT"? >> >> I hadn't thought of operators. As far as I know, mathematical operators (* / >> + -) only work with integers and reals, Perhaps the result type could be set >> to "numeric" or some other superclass. Or, to get real fancy, if the two >> operands are declared integers, then the result is an integer; if one is >> real then the result is real. AFAIK SMALLINT and UINT are both treated by >> SQLite as integer, so the result would be an integer. >> >>> Should relational operators have a declared type of BOOLEAN? >> >> Good idea. >> >> 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 >> > ___ > 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Stefan Keller wrote: > Thank you, Tom and Dan, for your constructive answers. > > To Pavel: My application reads the column types out in order to pretty > print the values - as mentioned by Tom - but also to generate a dialog > for entering new data (of course combined with INSTEAD OF TRIGGERs). > > I understand that it's difficult to implement result-types for > expressions in general. To me it would be enough if there would be at > least one way to assign result-types with a CAST "wrapper" as I have > mentioned before. > > Does anybody know whom to present this proposal in order remedy this > current inconsistency in SQLite? sqlite-dev? > Actually, I'd say that the inconsistency is that views have declared types at all. Declared types determine type affinity, which determines what happens when you put data in a column. But SQLite doesn't have updatable views, so typed columns don't *do* anything on them. >>> but you'd have to do it for operators as well, and what type should A * B >>> be if A is "SMALLINT" and B is "UINT"? >>> >> I hadn't thought of operators. As far as I know, mathematical operators (* / >> + -) only work with integers and reals, Perhaps the result type could be set >> to "numeric" or some other superclass. Or, to get real fancy, if the two >> operands are declared integers, then the result is an integer; if one is >> real then the result is real. AFAIK SMALLINT and UINT are both treated by >> SQLite as integer, so the result would be an integer. >> There would be 3 (or 4) cases to deal with: * String concatenation (||) returns TEXT. * Arithmetic operators (+ - * / %) return NUMERIC. * Bit operators (<< >> & | ~) return INTEGER. (BTW, why is there no XOR?) * Relational and logical operators return INTEGER, but you may want to distinguish them with a BOOLEAN type name. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
Thank you, Tom and Dan, for your constructive answers. To Pavel: My application reads the column types out in order to pretty print the values - as mentioned by Tom - but also to generate a dialog for entering new data (of course combined with INSTEAD OF TRIGGERs). I understand that it's difficult to implement result-types for expressions in general. To me it would be enough if there would be at least one way to assign result-types with a CAST "wrapper" as I have mentioned before. Does anybody know whom to present this proposal in order remedy this current inconsistency in SQLite? sqlite-dev? In the meantime I thought of a hack and to assign the result-type by hand in the data dictionary after having created the VIEW. Would this be a feasible work around? Yours, S. 2010/5/6 BareFeetWare : > On 06/05/2010, at 2:51 PM, Dan Bishop wrote: > >> BareFeetWare wrote: >>> > >>> 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. >>> > >> A CAST expression could be assigned a declared type > > Yes, that should be fairly easy to do in SQLite, simpler than my adding it in > my own code (which then requires my own parser - not a trivial exercise). > >> but it would be difficult to implement it for expressions in general. Not >> only would you have to declare a type for every function > > Yes, probably half the functions don't have a consistently returned type (eg > length, concat). The ones that don't (eg coalesce) should just be left to > return a null (unknown) type. The schema developer can simply wrap those > expressions in a cast if they want a predetermined output type. > >> but you'd have to do it for operators as well, and what type should A * B be >> if A is "SMALLINT" and B is "UINT"? > > I hadn't thought of operators. As far as I know, mathematical operators (* / > + -) only work with integers and reals, Perhaps the result type could be set > to "numeric" or some other superclass. Or, to get real fancy, if the two > operands are declared integers, then the result is an integer; if one is real > then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite > as integer, so the result would be an integer. > >> Should relational operators have a declared type of BOOLEAN? > > Good idea. > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
On 06/05/2010, at 2:51 PM, Dan Bishop wrote: > BareFeetWare wrote: >> >> 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. >> > A CAST expression could be assigned a declared type Yes, that should be fairly easy to do in SQLite, simpler than my adding it in my own code (which then requires my own parser - not a trivial exercise). > but it would be difficult to implement it for expressions in general. Not > only would you have to declare a type for every function Yes, probably half the functions don't have a consistently returned type (eg length, concat). The ones that don't (eg coalesce) should just be left to return a null (unknown) type. The schema developer can simply wrap those expressions in a cast if they want a predetermined output type. > but you'd have to do it for operators as well, and what type should A * B be > if A is "SMALLINT" and B is "UINT"? I hadn't thought of operators. As far as I know, mathematical operators (* / + -) only work with integers and reals, Perhaps the result type could be set to "numeric" or some other superclass. Or, to get real fancy, if the two operands are declared integers, then the result is an integer; if one is real then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite as integer, so the result would be an integer. > Should relational operators have a declared type of BOOLEAN? Good idea. 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
BareFeetWare wrote: > 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. > > A CAST expression could be assigned a declared type, but it would be difficult to implement it for expressions in general. Not only would you have to declare a type for every function, but you'd have to do it for operators as well, and what type should A * B be if A is "SMALLINT" and B is "UINT"? Should relational operators have a declared type of BOOLEAN? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
> 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. > because there seems to be no way to get VIEWS returning the column type if the > column is calculated or a function. I've asked you a question and you didn't seem to answer it. 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? Why don't you describe your situation and what you are trying to achieve in more details? > 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? No, it doesn't break compatibility. Queries work the same way as in other databases and return to you the same results. AFAIK, queries don't have to always return to you information about data type of underlying table column or function (which btw can return different data type for each row in SQLite). Datatype system is completely different in SQLite and nobody claimed it should be compatible... Pavel On Wed, May 5, 2010 at 5:32 PM, Stefan Keller wrote: > 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 : >> 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 : >>> >>> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
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 : > 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 : >> >> 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
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 : > > 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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
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
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
> Is there a way to give such columns a type anyway? I guess you aware that columns in SQLite doesn't have types? They only have "declared type" (which is arbitrary string used in CREATE TABLE statement) and affinity. OTOH values in each row have types. And neither "declared type" nor affinity don't prevent column to contain values of any type at all. And I bet values in your view are reported to have appropriate type although columns don't have declared type. Which function return to you NULL type? Pavel On Mon, May 3, 2010 at 1:14 PM, Stefan Keller wrote: > I have a question regarding VIEWs in SQLite: > > It looks like if SQLite simply copies the column type from the > original table into the corresponding view column. And I know SQLite > implements some 'loose column typing'. That's ok so far. > > 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? > > Yours > -S. > ___ > 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
[sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
I have a question regarding VIEWs in SQLite: It looks like if SQLite simply copies the column type from the original table into the corresponding view column. And I know SQLite implements some 'loose column typing'. That's ok so far. 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? Yours -S. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users