The TK_ALL ("*") expansion explicitly removes duplicate columns from tables
joined using a natural join or those found in a "using" statement. For those
interested in the code, check out selectExpander() +165 through +178.
So while the "*" expansion only shows column "a", it happens to be showing
column t1.a and removing the rest. Igor's comments are correct that there are
three columns and if you specify just "a", the parser doesn't know which one.
-Allan
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Wednesday, December 23, 2009 9:24 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] ambiguous column name
>
> > You have three distinct columns here - t1.a, t2.a and t3.a. With left
> joins, it's possible for some but not all of them to be null, so it
> matters which one you select.
> >
> > Even with inner joins, it may matter which column you pick. E.g., in
> SQLite it's possible that a=b but typeof(a) != typeof(b)
>
> I believe OP's point here (which I can agree with) is when he executes
> "select * ..." he gets only one column (which one is it btw?). So it's
> quite reasonable to assume that if he puts the name of this column
> instead of asterisk he should get the same result. But he doesn't get
> it.
>
> Pavel
>
> On Wed, Dec 23, 2009 at 11:09 AM, Igor Tandetnik <itandet...@mvps.org>
> wrote:
> > Wiktor Adamski
> > <bardzotajneko...@interia.pl> wrote:
> >> There's no reason for following error:
> >>
> >> SQLite version 3.6.21
> >> Enter ".help" for instructions
> >> Enter SQL statements terminated with a ";"
> >> sqlite> .headers ON
> >> sqlite> create table t1(a int);
> >> sqlite> create table t2(a int);
> >> sqlite> create table t3(a int);
> >> sqlite> insert into t1 values(1);
> >> sqlite> select * from t1 left join t2 using(a) left join t3
> using(a);
> >> a
> >> 1
> >> sqlite> select a from t1 left join t2 using(a) left join t3
> using(a);
> >> Error: ambiguous column name: a
> >
> > You have three distinct columns here - t1.a, t2.a and t3.a. With left
> joins, it's possible for some but not all of them to be null, so it
> matters which one you select.
> >
> > Even with inner joins, it may matter which column you pick. E.g., in
> SQLite it's possible that a=b but typeof(a) != typeof(b)
> >
> > Igor Tandetnik
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
