Re: [sqlite] When is the decltype recognized?

[Dominique Devienne]

On Thu, Feb 13, 2014 at 11:44 AM, Clemens Ladisch  wrote:
> By design, SQLite uses dynamic typing, and keeps only the actual type of
> the value.  The declared type is a property of the *column*, not of the
> value itself, so it is lost as soon as the value is no longer associated
> directly with the table.

But the fact that SQLite values are dynamically typed is orthogonal to
making a best-effort to carry the column typing through intermediate
result sets (which are logically just like tables). The query parser
obviously knows how to resolve columns to fetch the right values, so
it could carry those columns declared type (if any) along, more often
than it does now.

Whether a query is ordered and must use a temporary table for sorting
shouldn't matter IMHO, for example.

I know the doc is careful to make little promises in this, yet it
feels to me that it's more an implementation issue, than a design
issue. And by that I'm not saying that the implementation is at fault
or buggy, not at all, but when considering this problem logically with
no regard for implementation concerns or how the VDBE VM works, there
are clearly cases SQLite could know the declared type of the outermost
select clause "columns", and does not currently provide it. I
completely understand coding efforts going into other directions, but
this is again does not feel like "by design" to me.

My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> Clemens Ladisch wrote:
>> Peter Otten wrote:
>>> Clemens Ladisch wrote:
 The first query uses a temporary table for sorting.  The column in that
 temporary table does not have a declared type.

 The second query returns the values directly from the underlying table
 column, so the declared type is also from that table.
>>>
>>> Thank you. Is there a way around that limitation?
>>
>> Which of these two cases do you think is the result of a limitation?
>
> Losing type information. I think it should be possible to infer the column
> type in a subquery (but have no idea yet how hard that would be). Given
>
> select * from (select alpha from demo union all select alpha from demo) order 
> by alpha
>
> the alpha column in both parts of the union has the same type ("custom").
> Therefore the resulting column in the outer select could safely be assumed
> to be of type "custom", too.

By design, SQLite uses dynamic typing, and keeps only the actual type of
the value.  The declared type is a property of the *column*, not of the
value itself, so it is lost as soon as the value is no longer associated
directly with the table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is the decltype recognized?

[Peter Otten]

Clemens Ladisch wrote:

> Peter Otten wrote:
>> Clemens Ladisch wrote:
>>> Peter Otten wrote:
 select * from (select alpha from demo union all select alpha from demo)
 order by alpha decltype: (null)

 select * from (select alpha from demo union all select alpha from demo)
 decltype: custom

 Even taking http://sqlite.org/c3ref/column_decltype.html
 """
 If this statement is a SELECT statement and the Nth column of the
 returned result set of that SELECT is a table column (not an expression
 or subquery) then the declared type of the table column is returned. If
 the Nth column of the result set is an expression or subquery, then a
 NULL pointer is returned.
 """
 into account I would have expected either NULL or "custom" for both
 queries.
>>>
>>> The first query uses a temporary table for sorting.  The column in that
>>> temporary table does not have a declared type.
>>>
>>> The second query returns the values directly from the underlying table
>>> column, so the declared type is also from that table.
>>
>> Thank you. Is there a way around that limitation?
> 
> Which of these two cases do you think is the result of a limitation?

Losing type information. I think it should be possible to infer the column 
type in a subquery (but have no idea yet how hard that would be). Given

select * from (select alpha from demo union all select alpha from demo)
order by alpha

the alpha column in both parts of the union has the same type ("custom").
Therefore the resulting column in the outer select could safely be assumed 
to be of type "custom", too.

> According to the documentation, neither query is guaranteed to be able
> to return information about the declared type.  The second does anyway
> because the query optimizer flattened the subquery.

I understand that there are no guarantees.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> Clemens Ladisch wrote:
>> Peter Otten wrote:
>>> select * from (select alpha from demo union all select alpha from demo) 
>>> order by alpha
>>> decltype: (null)
>>>
>>> select * from (select alpha from demo union all select alpha from demo)
>>> decltype: custom
>>>
>>> Even taking http://sqlite.org/c3ref/column_decltype.html
>>> """
>>> If this statement is a SELECT statement and the Nth column of the
>>> returned result set of that SELECT is a table column (not an expression
>>> or subquery) then the declared type of the table column is returned. If
>>> the Nth column of the result set is an expression or subquery, then a
>>> NULL pointer is returned.
>>> """
>>> into account I would have expected either NULL or "custom" for both
>>> queries.
>>
>> The first query uses a temporary table for sorting.  The column in that
>> temporary table does not have a declared type.
>>
>> The second query returns the values directly from the underlying table
>> column, so the declared type is also from that table.
>
> Thank you. Is there a way around that limitation?

Which of these two cases do you think is the result of a limitation?

According to the documentation, neither query is guaranteed to be able
to return information about the declared type.  The second does anyway
because the query optimizer flattened the subquery.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is the decltype recognized?

[Peter Otten]

Clemens Ladisch wrote:

> Peter Otten wrote:
>> select * from (select alpha from demo union all select alpha from demo)
>> order by alpha decltype: (null)
>>
>> select * from (select alpha from demo union all select alpha from demo)
>> decltype: custom
>>
>> Even taking http://sqlite.org/c3ref/column_decltype.html
>> """
>> If this statement is a SELECT statement and the Nth column of the
>> returned result set of that SELECT is a table column (not an expression
>> or subquery) then the declared type of the table column is returned. If
>> the Nth column of the result set is an expression or subquery, then a
>> NULL pointer is returned.
>> """
>> into account I would have expected either NULL or "custom" for both
>> queries.
> 
> The first query uses a temporary table for sorting.  The column in that
> temporary table does not have a declared type.
> 
> The second query returns the values directly from the underlying table
> column, so the declared type is also from that table.

Thank you. Is there a way around that limitation?

PS: The bug reporter has since confirmed that her code used to work with 
sqlite 3.3.4. Where can I download historical versions of sqlite to look 
into that myself?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is the decltype recognized?

[Clemens Ladisch]

Peter Otten wrote:
> select * from (select alpha from demo union all select alpha from demo) order 
> by alpha
> decltype: (null)
>
> select * from (select alpha from demo union all select alpha from demo)
> decltype: custom
>
> Even taking http://sqlite.org/c3ref/column_decltype.html
> """
> If this statement is a SELECT statement and the Nth column of the returned
> result set of that SELECT is a table column (not an expression or subquery)
> then the declared type of the table column is returned. If the Nth column of
> the result set is an expression or subquery, then a NULL pointer is
> returned.
> """
> into account I would have expected either NULL or "custom" for both queries.

The first query uses a temporary table for sorting.  The column in that
temporary table does not have a declared type.

The second query returns the values directly from the underlying table
column, so the declared type is also from that table.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When is the decltype recognized?

[Peter Otten]

Hello!

Over at python.org there is a bug report that its sqlite3 module sometimes 
doesn't correctly deserialize a typed column. From my limited understanding 
it boils down to the following once you go down to sqlite's C API:

#include 
#include "sqlite3.h"

int main(int argc, char **argv)
{
sqlite3 * db;
sqlite3_stmt * statement;
const char * type;

printf("%s\n", sqlite3_libversion());

sqlite3_open(":memory:", );
sqlite3_exec(db, "create table demo (alpha custom);", 0, 0, 0);

sqlite3_prepare_v2(db, "select * from (select alpha from demo union all 
select alpha from demo) order by alpha", -1, , NULL);
type = sqlite3_column_decltype(statement, 0);
printf("decltype: %s\n", type);

sqlite3_prepare_v2(db, "select * from (select alpha from demo union all 
select alpha from demo)", -1, , NULL);
type = sqlite3_column_decltype(statement, 0);
printf("decltype: %s\n", type);

return 0;
}

Long time no write C, I hope it doesn't show ;)

$ gcc demo.c sqlite3.c -lpthread -ldl
$ ./a.out 
3.8.3.1
decltype: (null)
decltype: custom

A minor change (the order by clause) defeats type detection.

Even taking http://sqlite.org/c3ref/column_decltype.html
"""
If this statement is a SELECT statement and the Nth column of the returned 
result set of that SELECT is a table column (not an expression or subquery) 
then the declared type of the table column is returned. If the Nth column of 
the result set is an expression or subquery, then a NULL pointer is 
returned. 
"""
into account I would have expected either NULL or "custom" for both queries.

PS: the original bug report is http://bugs.python.org/issue20587

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users