
> Allow "col AS name" to use name in WHERE clause?  Is this ANSI? 
> 	Works in GROUP BY
> 
Neighter Informix nor Oracle do it, so it is probably not ansi, but it would

be a very neat feature, especially if you do some arithmetic, 
the statement gets a lot clearer.

But it probably adds some complexity:

create table a (a int, b int, c int);
select a, b as c from a where c=5; 

Which c do you use alias or column ? You prbly need to use the column, 
since this is how all others work, but would this be intuitive ?

Andreas

Wed, 19 May 1999 15:01:11 +0200
ZEUGSWETTER Andreas IZ5 <Andreas.Zeugswetter@telecom.at>
Re: [HACKERS] Open 6.5 items
Thus spake ZEUGSWETTER Andreas IZ5
> > Allow "col AS name" to use name in WHERE clause?  Is this ANSI? 
> > 	Works in GROUP BY

> But it probably adds some complexity:
> 
> create table a (a int, b int, c int);
> select a, b as c from a where c=5; 
> 
> Which c do you use alias or column ? You prbly need to use the column, 
> since this is how all others work, but would this be intuitive ?

Not to me.  What if I don't know that a c exists in the table, or it is
added after creating many scripts?  I think we should use the alias in
that case.  Either that or it should generate an error.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 19 May 1999 09:25:25 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Open 6.5 items
> 
> > Allow "col AS name" to use name in WHERE clause?  Is this ANSI? 
> > 	Works in GROUP BY
> > 
> Neighter Informix nor Oracle do it, so it is probably not ansi, but it would
> 
> be a very neat feature, especially if you do some arithmetic, 
> the statement gets a lot clearer.
> 
> But it probably adds some complexity:
> 
> create table a (a int, b int, c int);
> select a, b as c from a where c=5; 
> 
> Which c do you use alias or column ? You prbly need to use the column, 
> since this is how all others work, but would this be intuitive ?

That is an excellent point.  GROUP BY has to use a column name, and they
have to be unique, while WHERE does not require stuff to be in the
target list, so there is a change of ambiguity.  I am going to remove
the item from the list.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 19 May 1999 12:34:39 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Open 6.5 items
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> That is an excellent point.  GROUP BY has to use a column name, and they
> have to be unique, while WHERE does not require stuff to be in the
> target list, so there is a change of ambiguity.  I am going to remove
> the item from the list.

Good point --- consider this:
	SELECT a, b AS a FROM tt GROUP BY a;
We do get it right: "ERROR:  GROUP BY 'a' is ambiguous".
Whereas in
	SELECT a, b AS a FROM tt WHERE a = 1;
the WHERE clause is taken as referring to the "real" column a.

So, unless there's some violation of spec behavior here, there is a
reason for GROUP BY to behave differently from WHERE.  I think I was
the one who complained that they were different --- I withdraw the
complaint.

BTW, which behavior should ORDER BY exhibit?  I find that
	SELECT a, b AS a FROM tt ORDER BY a;
is accepted and 'a' is taken to be the real column a.  Considering that
ORDER BY is otherwise much like GROUP BY, I wonder whether it shouldn't
complain that 'a' is ambiguous...

			regards, tom lane

Wed, 19 May 1999 16:40:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Open 6.5 items 