Avoiding "Ambigious column"?

[Kristoffer Danielsson]

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

 

However, if I add two parentheses around "T2 NATURAL JOIN T4", the error goes away:

 

SELECT SUM(Salary - TotalSpent) / 100 FROM ((T2 NATURAL JOIN T4)) NATURAL JOIN (T2 NATURAL JOIN T3);

 

 

Why is this so?
     
_________________________________________________________________
Nya Windows 7 - Hitta en dator som passar dig! Mer information.
http://windows.microsoft.com/shop
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin-3]

On 7 Nov 2009, at 10:56pm, Kristoffer Danielsson wrote:

> SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4)  
> NATURAL JOIN (T2 NATURAL JOIN T3);
>
> Error: Ambigious column name: Salary
>
> However, if I add two parentheses around "T2 NATURAL JOIN T4", the  
> error goes away:
>
> SELECT SUM(Salary - TotalSpent) / 100 FROM ((T2 NATURAL JOIN T4))  
> NATURAL JOIN (T2 NATURAL JOIN T3);
>
> Why is this so?

Because there's no longer any reason to wonder if the Salary column  
could come from T2 NATURAL JOIN T4: you haven't referred to either of  
them directly.

Another way to get rid of the error message would be to specify  
T2.Salary or T3.Salary.

Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Kristoffer Danielsson]

Ok, so parentheses "hide" the columns in the present clause? There must be a common rule for this...

 

And yes, I know you can explicitly use "table.column", but in this case the code comes from the user and conflicts with my own columns.

Hence, I have to think of a way to avoid column conflicts.

 

Thanks for your reply.
 

> From: slavins@...
> Date: Sat, 7 Nov 2009 23:55:52 +0000
> To: sqlite-users@...
> Subject: Re: [sqlite] Avoiding "Ambigious column"?
>
>
> On 7 Nov 2009, at 10:56pm, Kristoffer Danielsson wrote:
>
> > SELECT SUM(Salary - TotalSpent) / 100 FROM (T2 NATURAL JOIN T4)
> > NATURAL JOIN (T2 NATURAL JOIN T3);
> >
> > Error: Ambigious column name: Salary
> >
> > However, if I add two parentheses around "T2 NATURAL JOIN T4", the
> > error goes away:
> >
> > SELECT SUM(Salary - TotalSpent) / 100 FROM ((T2 NATURAL JOIN T4))
> > NATURAL JOIN (T2 NATURAL JOIN T3);
> >
> > Why is this so?
>
> Because there's no longer any reason to wonder if the Salary column
> could come from T2 NATURAL JOIN T4: you haven't referred to either of
> them directly.
>
> Another way to get rid of the error message would be to specify
> T2.Salary or T3.Salary.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

     
_________________________________________________________________
Nya Windows 7 gör allt lite enklare. Hitta en dator som passar dig!
http://windows.microsoft.com/shop
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Jay A. Kreibich-2]

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@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users