qualified names in WHERE clause

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

qualified names in WHERE clause

Will Leshner
I guess I never really noticed this before (since I only use SQLite,  
of course :) ). But consider a query like this:

SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name

If the 'name' column happens to be a column in both test2 and test11,  
then SQLite will return an error. You need to qualify 'name' with  
'test2.' to make the query acceptable. Apparently MySQL and  
PostgreSQL are able to recognize that 'name' refers to the 'name' in  
the result set and are able to disambiguate it.
Reply | Threaded
Open this post in threaded view
|

Re: qualified names in WHERE clause

Gregory Letellier
try SELECT Test2.* FROM test2 inner join test11 ON test2.id=test11.id
ORDER By Name;

Will Leshner a ?crit :

> I guess I never really noticed this before (since I only use SQLite,  
> of course :) ). But consider a query like this:
>
> SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name
>
> If the 'name' column happens to be a column in both test2 and test11,  
> then SQLite will return an error. You need to qualify 'name' with  
> 'test2.' to make the query acceptable. Apparently MySQL and  
> PostgreSQL are able to recognize that 'name' refers to the 'name' in  
> the result set and are able to disambiguate it.
>
>
Reply | Threaded
Open this post in threaded view
|

RE: qualified names in WHERE clause

Thomas Briggs
In reply to this post by Will Leshner

   This is a pretty common problem (for us, at least :P) - whether the
columns named in the ORDER BY clause refer to columns in the source
table(s) or columns in the result seems to be something that not all
databases agree on.  I would have thought that the SQL standard laid
this out pretty clearly, but apparently not. :)  It's been our
experience that the only truly reliable way to avoid this problem is to
be explicit.

   -Tom

> -----Original Message-----
> From: Will Leshner [mailto:[hidden email]]
> Sent: Thursday, May 26, 2005 10:18 AM
> To: Forum SQLite
> Subject: [sqlite] qualified names in WHERE clause
>
> I guess I never really noticed this before (since I only use SQLite,  
> of course :) ). But consider a query like this:
>
> SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id
> ORDER BY name
>
> If the 'name' column happens to be a column in both test2 and
> test11,  
> then SQLite will return an error. You need to qualify 'name' with  
> 'test2.' to make the query acceptable. Apparently MySQL and  
> PostgreSQL are able to recognize that 'name' refers to the 'name' in  
> the result set and are able to disambiguate it.
>
Reply | Threaded
Open this post in threaded view
|

Re: qualified names in WHERE clause

Will Leshner
In reply to this post by Gregory Letellier

On May 26, 2005, at 7:23 AM, Gregory Letellier wrote:

> try SELECT Test2.* FROM test2 inner join test11 ON  
> test2.id=test11.id ORDER By Name;


Thanks. I know there are ways to get the query to work. I think the  
problem is when people are migrating over from another database  
engine and they already have thousands of queries written. It's cool  
when those queries can work out of the box, so to speak. On the other  
hand, SQL is implemented slightly differently in all SQL engines, and  
so I imagine that some differences are simply unavoidable.
Reply | Threaded
Open this post in threaded view
|

Re: qualified names in WHERE clause

Will Leshner
In reply to this post by Thomas Briggs

On May 26, 2005, at 7:49 AM, Thomas Briggs wrote:

> It's been our
> experience that the only truly reliable way to avoid this problem  
> is to
> be explicit.
>

I agree, and that's what I've always done up until now because it  
never occurred to me that the SQL engine would be able to figure it  
out. This "problem" was brought to me by somebody else.

--
REALbasic database options: http://sqlabs.net
REALbasic news and tips: http://rbgazette.com
KidzMail & KidzLog: http://haranbanjo.com

Reply | Threaded
Open this post in threaded view
|

RE: qualified names in WHERE clause

Thomas Briggs
In reply to this post by Will Leshner

   You may be the person I've encountered who is able to perceive
Someone Else's Problem.

   :)

   -Tom

> -----Original Message-----
> From: Will Leshner [mailto:[hidden email]]
> Sent: Thursday, May 26, 2005 10:58 AM
> To: [hidden email]
> Subject: Re: [sqlite] qualified names in WHERE clause
>
>
> On May 26, 2005, at 7:49 AM, Thomas Briggs wrote:
>
> > It's been our
> > experience that the only truly reliable way to avoid this problem  
> > is to
> > be explicit.
> >
>
> I agree, and that's what I've always done up until now because it  
> never occurred to me that the SQL engine would be able to figure it  
> out. This "problem" was brought to me by somebody else.
>
> --
> REALbasic database options: http://sqlabs.net
> REALbasic news and tips: http://rbgazette.com
> KidzMail & KidzLog: http://haranbanjo.com
>
>
Reply | Threaded
Open this post in threaded view
|

RE: qualified names in WHERE clause

cronos-2
In reply to this post by Will Leshner
It seems to me that MySQL and PostgreSQL are exhibitting some dubious
guessing behaviour as to which column it refers to, or perhaps they are
making some requirement of the order by to contain a column that is in the
resultset ??? If name were only in test11 then what would MySQL and
PostgreSQL do ?

-----Original Message-----
From: Will Leshner [mailto:[hidden email]]
Sent: 26 May 2005 15:18
To: Forum SQLite
Subject: [sqlite] qualified names in WHERE clause


I guess I never really noticed this before (since I only use SQLite,
of course :) ). But consider a query like this:

SELECT test2.* FROM test2,test11 WHERE test2.id=test11.id ORDER BY name

If the 'name' column happens to be a column in both test2 and test11,
then SQLite will return an error. You need to qualify 'name' with
'test2.' to make the query acceptable. Apparently MySQL and
PostgreSQL are able to recognize that 'name' refers to the 'name' in
the result set and are able to disambiguate it.

Reply | Threaded
Open this post in threaded view
|

Re: qualified names in WHERE clause

Jay Sprenkle
All of the databases I've used required the columns in the order by
clause also be present in the result set. It may not be universally true though

On 5/26/05, Cronos <[hidden email]> wrote:
> It seems to me that MySQL and PostgreSQL are exhibitting some dubious
> guessing behaviour as to which column it refers to, or perhaps they are
> making some requirement of the order by to contain a column that is in the
> resultset ??? If name were only in test11 then what would MySQL and
> PostgreSQL do ?
>