Re: [sqlite] When is the decltype recognized?
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 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
[sqlite] When is the decltype recognized?
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
