On 2014/09/24 18:48, 麦田观望者 wrote:
if we have to tables:
create table t1(f1 integer,f2 integer);
create table t2(f1 integer,f2 integer);
the fellowing sql generate a result set with a strange field name:
select t1.f1 from t1
union
select t2.f1 from t2
we expect a column named "f1" but we get "t1.f1"
but "select t1.f1 from t1 " get a "f1"
is this a bug or by designed?
Hi there,
It is not a bug and also not by design. It is just whatever the Query producer feels comfortable writing in the header to identify
the column. It does not have to write anything there, nor are there any rules of what must be there - the SQL standard says that if
you need a specific name you must specify it using AS. If you do not say what you want, then the query producer can put whatever it
likes.
SQLite is rather consistent with what it chooses to put there (much like the other engines) and this sometimes give people the idea
that whatever is used might be expected in future - but it really is not set in stone and may not be "expected" to conform to
anything. If you do not use an AS specifier, then you should not count on the header to be any specific name. What you CAN count on
is the column ordering, i.e. the 3rd column you asked for will always be the 3rd column of values in the output - but the name for
that column can be anything weird if you did not ask for something specifically.
This next query will always give what you want:
SELECT t1.f1 AS f1 from t1
UNION
SELECT t2.f1 AS f1 from t2
(The second AS is not needed since the first already defines the column, but
just added here for clarity)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
