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
