Forgot to mention, compare the previous query to: select * from t1 left join t2 on t1.a = t2.a left join t3 on t1.a = t3.a; a|a|a 1||
-Allan > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Schrum, Allan > Sent: Wednesday, December 23, 2009 9:45 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] ambiguous column name > > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
