On Sat, Nov 07, 2009 at 11:56:28PM +0100, Kristoffer Danielsson scratched on the wall: > > SQLite 3.6.20. > > SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4) NATURAL JOIN > (T2 NATURAL JOIN T3); > > Error: Ambigious column name: Salary
Since you didn't tell us which tables have a 'Salary' column, it is a bit hard to figure out what is going on. You also shouldn't need the triple join, unless there is something else going on here-- A simple t2 nat-join t3 nat-join t4 should produce the same results. But there does seem to be some very odd things going on if you "stack" natural joins. It seems that if the columns do not come directly from a base-table, the joined column elimination doesn't always happen: SQLite version 3.6.20 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .header on sqlite> .mode column sqlite> sqlite> CREATE TABLE t2 ( i, a, b ); sqlite> CREATE TABLE t3 ( a ); sqlite> CREATE TABLE t4 ( b ); sqlite> sqlite> INSERT INTO t2 VALUES ( 1, 10, 20 ); sqlite> INSERT INTO t3 VALUES ( 10 ); sqlite> INSERT INTO t4 VALUES ( 20 ); sqlite> sqlite> select * from t2 natural join t3; i a b ---------- ---------- ---------- 1 10 20 sqlite> select * from t2 natural join t3; i a b ---------- ---------- ---------- 1 10 20 sqlite> sqlite> select * from t2 natural join t3 natural join t4; i a b b ---------- ---------- ---------- ---------- 1 10 20 20 sqlite> select * from (t2 natural join t3) natural join (t2 natural join t4); i a b i b ---------- ---------- ---------- ---------- ---------- 1 10 20 1 20 sqlite> select * from ((t2 natural join t3)) natural join (t2 natural join t4); i a b i b ---------- ---------- ---------- ---------- ---------- 1 10 20 1 20 sqlite> In this case, I was under the impression that all of these should produce the same set of columns. The pattern seems very odd, however... It looks like the system is not getting rid of matched columns if one of those columns is not the result of a JOIN, but if that were true I'd expect the last two queries to have duplicate "a" columns as well. And it isn't that the first (and only the first) column is getting merged, or the third query wouldn't have two "b" columns. I'm not sure what is going on here, but it doesn't look right. I'm just not exactly sure how it is wrong. Thinking it might be related to the t-NJ-t issue that was fixed in .20, I tested this on the slightly older 3.6.12 and got similar results. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
