Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[HAUBOURG]

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?

[Stefan Keller]

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?

[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

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[BareFeetWare]

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?

[Ben Harper]

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 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 (* 
>>> 

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Stefan Keller]

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
>>> 

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[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/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?

[Dan Bishop]

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?

[Stefan Keller]

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?

[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

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Dan Bishop]

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?

[BareFeetWare]

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?

[Pavel Ivanov]

> 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?

[Stefan Keller]

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?

[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?

[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

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

[Pavel Ivanov]

> 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?

[Stefan Keller]

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