Pavel Ivanov <paiva...@gmail.com> wrote: >> 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?).
Good point. I bit the bullet and dug through SQL-92. What apparently should happen is there should be a synthesized column named 'a' with the value of coalesce(t1.a, t2.a, t3.a) (see SQL-92 7.5p6). In other words, this statement: select a from t1 left join t2 using(a) left join t3 using(a); should be equivalent to select coalesce(coalesce(t1.a, t2.a), t3.a) as a from t1 left join t2 on (t1.a = t2.a) left join t3 on (coalesce(t1.a, t2.a) = t3.a); So yes, it does appear SQLite is in violation of the standard. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
