> On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote:
>
>> The output column "ycis_id" is unabiguously a single value with regards
>> to
>> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used
>> this
>> exact type of query before either on PostgreSQL or another system, maybe
>> Oracle, and it did work.
>
> Doesn't work in Oracle 10g:
>
> SELECT ycis_id, tindex from x where ycis_id = 15;
> YCIS_ID TINDEX
> ======= ======
> 15 10
> 15 20
>
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15;
> ORA-00937: not a single-group group function
>
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP
> BY ycis_id;
> YCIS_ID MIN(TINDEX) AVG(TINDEX)
> ======= =========== ===========
> 15 10 15
That's interesting. I am digging through the SQL99 spec, and am trying to
find a definitive answer.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
