德尔福7+Zeos : how to prevent sqlite 3 converting date and integer/float to string?

Question

测试数据库:

create table a ( d date not null primary key);
create table b ( d date not null primary key);
insert into a values ('2013-01-01');
insert into b values ('2014-01-01');

在 Delphi 7 中使用 Zeos lib，这些查询都返回 TDateTimeField:

select d from a order by 1;

select d from b order by 1;

select d from a union all select d from b;

select * from (select d from a union all select d from b) s;

但是，此查询返回一个 TStringField:

select * from (select d from a union all select d from b) s order by 1;

问题:

• 这是为什么？
• 如何防止这种情况发生？
• 这是一个错误吗？对结果集进行排序到底是如何改变列的类型的？？？
• 这是一个严重的问题，因为我无法从程序生成 SQL 查询并在设计时创建的 TZReadOnlyQuery 中打开它们

更新:它也不适用于整数。

create table c ( id integer not null primary key);
create table d ( id integer not null primary key);
insert into c values(1);
insert into d values(2);

这些结果产生 TLargeIntField:

select * from c order by 1

select * from d order by 1

select * from ( select * from c union all select * from d) s

但是，这会产生 TStringField:

select * from ( select * from c union all select * from d) s order by 1

Answer 1

如下所述:http://www.sqlite.org/datatype3.html

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

那么，您可以尝试下面的语句并发布结果吗？!

select date(*) as test from (select date(d) from a union all select date(d) from b) s order by 1;