On 2015/01/25 15:16, Marcus Bergner wrote:
Hi,
Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the
following inconsistent behaviour:
$ ./sqlite3
sqlite> .headers on
sqlite> pragma short_column_names;
1
sqlite> pragma full_column_names;
0
sqlite> create table tbl1 (id1 integer, s1 text);
sqlite> create table tbl2 (id2 integer, s2 text);
sqlite> insert into tbl1 values (1, 'v1');
sqlite> insert into tbl2 values (1, 'v2');
sqlite> select x.id1, x.s1, y.s2 from tbl1 x inner join tbl2 y on
x.id1=y.id2;
id1|s1|s2
1|v1|v2
So far so good, everything as expected. If I rewrite the above select
statement to do a join with a subquery instead the resulting output changes
in an unexpected way.
sqlite> select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|subq.s2
1|v1|v2
Here we get unexpected column prefixes on all fetched columns. If I rewrite
the query again to a subq.* query the behaviour is different again where
only the first two columns have prefixes.
sqlite> select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|id2|s2
1|v1|1|v2
Expected behaviour: returned columns should not contain prefixes in any of
the above scenarios.
May I ask what exactly makes you "expect" your expected behavior? There is no documentation in either SQLite or the SQL standard
that would lead anyone to believe that behavior is expected - in fact it is very clear about the returned column names being
non-deterministic if not explicitly requested by aliasing.
In short, if you need the column names to be something specific, you need to ask for it exactly so, else the SQL engine (any of
them, not just SQLite) may return whatever they like by virtue of what might seem the most unconvoluted but also non-ambiguous (this
latter simply being a minor paradigm, not a rule of any kind).
My guess is you have come to expect the behavior by simply observing it in the past and not through consulting the standards
(because that's how I made the mistake initially).
Always use aliasing when you need the results to conform.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
