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:", &db); 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, &statement, 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, &statement, 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