Re: [sqlite] Version 3.1.3 is a headache
On Sat, 28 Feb 2004 11:52:03 +0100, Jakub Adamek <[EMAIL PROTECTED]> wrote: > Is it really so that some database server returns a result set with two > same column names? Seems very strange. And the lovely SQLite 3.0.8 > didn't do such things ... PostgreSQL, which holds closer to the SQL spec than any other DB I'm aware of, refuses this syntax: SELECT * FROM a INNER JOIN b; You are depending on implicit join syntax that I believe is illegal in SQL. The fact that it happens to work doesn't make that a good idea. If you are explicit in the join: petrilli=# SELECT * FROM a, b WHERE a.id = b.id; id | x | id | y +---++--- 1 | 1 | 1 | 2 As you'll notice, it returns both id columns, because you selected ALL columns. This is the correct behavior. In this case, you've simply chosen the wrong behavior. As several other people have commented, you should, in all join cases, explictely call out all columns that you are interested in: petrilli=# SELECT a.id, x, y FROM a, b WHERE a.id = b.id; id | x | y +---+--- 1 | 1 | 2 Good luck. Chris -- | Christopher Petrilli | [EMAIL PROTECTED]
RE: [sqlite] Version 3.1.3 is a headache
> The last one is really annoying and I can't believe the > auto-tests could have missed it ... > >create table a (id, x); >create table b (id, y); >insert into a values (1,1); >insert into b values (1,2); >select * from a inner join b; > > column names returned: id,x,id,y You'll get the same result when you execute that query in SQL Server 2000. I suspect you'll get the same result from a lot of SQL DB systems. > How am I supposed to use such column names? The same way every other programmer does, by using alias on the fields that you are interested in, and leaving the others out of it.
Re: [sqlite] Version 3.1.3 is a headache
Jakub Adamek wrote: select * from a inner join b; Just as a general guide with any database system, this type of query isn't particularly recommended because of the namespace issue. Here's what I do to get around the problem (again, not just with SQLite): 1. Specify the criteria for the join (i.e. "inner join b on a.id = b.id), or the result set gets ridiculously large. 2. Explicitly declare the columns that I'm interested in receiving, since it's pretty rare that I'm going to want all of the columns from both tables. Even when I do want all of the columns, it lets me give alternate names to duplicate columns. I suspect that this is how most other people handle it as well, since I haven't seen an excess of complaints about this issue. Clay
RE: [sqlite] Version 3.1.3 is a headache
The fact is, the pragmas regarding column names now seem completely broken, as they do absolutely nothing. Was this by design, or a new bug ? -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, February 27, 2005 1:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Version 3.1.3 is a headache On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote: > Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. > I have already posted 3 tickets with rather serious problems with > column names. > > The last one is really annoying and I can't believe the auto-tests > could have missed it ... > >create table a (id, x); >create table b (id, y); >insert into a values (1,1); >insert into b values (1,2); >select * from a inner join b; > > column names returned: id,x,id,y How am I supposed to use such > column names? Ouwey. No wonder that my C++ wrapper does not want to > work with such a result set. > Your tickets are unhelpful and will likely be ignored. Rather than complain about the column names, perhaps you can present alternative suggestions. Posting what PostgreSQL, Oracle, and MySQL do with the same queries would be a good start. Explaining why you think the current names are undesirable (instead of just saying "Ouwey") would also be a positive step toward getting the problem addressed. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Version 3.1.3 is a headache
On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote: > Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I > have already posted 3 tickets with rather serious problems with column > names. > > The last one is really annoying and I can't believe the auto-tests could > have missed it ... > >create table a (id, x); >create table b (id, y); >insert into a values (1,1); >insert into b values (1,2); >select * from a inner join b; > > column names returned: id,x,id,y How am I supposed to use such > column names? Ouwey. No wonder that my C++ wrapper does not want to work > with such a result set. > Your tickets are unhelpful and will likely be ignored. Rather than complain about the column names, perhaps you can present alternative suggestions. Posting what PostgreSQL, Oracle, and MySQL do with the same queries would be a good start. Explaining why you think the current names are undesirable (instead of just saying "Ouwey") would also be a positive step toward getting the problem addressed. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Version 3.1.3 is a headache
Isn't ID the field INNER JOIN uses to combine the tables... That means you have to columns named ID but theire content is the same. Bernhard - Original Message - From: "Jakub Adamek" <[EMAIL PROTECTED]> To:Sent: Saturday, February 26, 2005 10:43 PM Subject: [sqlite] Version 3.1.3 is a headache > Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I > have already posted 3 tickets with rather serious problems with column > names. > > The last one is really annoying and I can't believe the auto-tests could > have missed it ... > >create table a (id, x); >create table b (id, y); >insert into a values (1,1); >insert into b values (1,2); >select * from a inner join b; > > column names returned: id,x,id,y How am I supposed to use such > column names? Ouwey. No wonder that my C++ wrapper does not want to work > with such a result set. >