Jay A. Kreibich wrote: > On Sat, Sep 19, 2009 at 01:14:56PM -0700, Darren Duncan scratched on the wall: >> 3c. I would like to have the option for SQLite to never have duplicate >> unqualified column names; for example, if one said "foo NATURAL INNER >> JOIN bar" then only a single column with the common data would be in >> the result, rather than 2; > > Umm... it does work that way. That's part of the SQL standard. > > NATURAL JOINs and JOIN ... USING( ) will only return one copy of > each column pair used in the JOIN.
My memory must be rusty then, because while I believe that is what should happen, I recalled using SQL DBMSs that behaved differently (which is, NATURAL or USING just controlled what records joined with what records, and that all of the non-distinct input columns were still output); I did not check recently though. >> likewise for inner joins with explicit join conditions of "foo.a = >> bar.a" would just return a single "a" in the result. > > This goes against the SQL standard and, in this case, I think > this would be a *very* Bad Idea. You're taking a command format that > is designed to take an arbitrary expression and changing the output > format based off the particulars of that expression. That strikes me > as extremely dangerous. For example, if someone has the first line > of code and changes it to something like the second line of code, > suddenly their output changes! > > ...t1 JOIN t2 ON t1.a = t2.a... => ..., a, ... > ...t1 JOIN t2 ON toupper(t1.a) = toupper(t2.a)... => ..., t1.a, t2.a, ... > > If you only want one column because you're using a straight equality, > use NATURAL or USING. USING is particularly useful to JOIN across a > sub-set of the commonly named columns. The more general solution here to the duplicate column name problem is to be stricter than the SQL standard and treat attempts to return duplicate column names as a syntax or constraint error. For example, if you had 2 tables 'foo' and 'bar' with columns named (a, b) and (b, c), then a plain "select * from foo inner join bar on ..." should throw an exception because there would be two 'b' in the result. And so, proper NATURAL or USING behavior is one way to say "select * from foo inner join bar ..." with success, and spelling out the result column list rather than using "*" is another way. But you have to deal with it explicitly or the SQL will refuse to run, is what the DBMS should do, or the DBMS should be customizable so it can be thusly strict. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
