It appears that Postgres, DB2 and SQL Server CE have issues with
certain types of alias expresssions in GROUP BY, while MySQL does
not.
Postgres will allow column aliases in GROUP BY - even aliases overriding
table column names - as long as every table column component of that
alias' expression is also explicitly individually specified in the
GROUP BY clause (of course this would change the result of the query).
DB2 and SQL Server CE had similar GROUP BY errors, so I'm speculating
that they may the same GROUP BY resolution behavior as postgres.
I guess the superset behavior that would allow the running of queries
from any of the databases mentioned would be to allow aliases in
GROUP BY, with aliases having precedence over table column names.
create table t1(a INT, b INT, c INT);
insert into t1 values(1, 2, 4);
insert into t1 values(2, -1000, 5);
postgres=> select a, a+b AS c from t1 group by c, a order by c, a;
ERROR: column "t1.b" must appear in the GROUP BY clause or be used in an
aggregate function
"b" added to SELECT list and GROUP BY (changing the meaning of the query):
postgres=> select a, a+b AS c, b from t1 group by c, a, b order by c, a, b;
a | c | b
---+------+-------
2 | -998 | -1000
1 | 3 | 2
Verify that an unambiguous alias "g" returns the same results:
postgres=> select a, a+b AS g, b from t1 group by g, a, b order by g, a, b;
a | g | b
---+------+-------
2 | -998 | -1000
1 | 3 | 2
Remove mention of column b from the SELECT list:
postgres=> select a, a+b AS c from t1 group by c, a, b order by c, a, b;
a | c
---+------
2 | -998
1 | 3
Do not overload c, use unambiguous alias g:
postgres=> select a, a+b AS g from t1 group by g, a, b order by g, a, b;
a | g
---+------
2 | -998
1 | 3
> mysql> select a, a+b AS c from t1 group by c, a order by c, a;
> +------+------+
> | a | c |
> +------+------+
> | 2 | -998 |
> | 1 | 3 |
> +------+------+
DB2:
> select a, a+b AS c from t1 group by c, a order by c, a
> SQL0119N An expression starting with "B" specified in a SELECT clause, HAVING
> clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in
> a SELECT clause, HAVING clause, or ORDER BY clause with a column function and
> no GROUP BY clause is specified. SQLSTATE=42803
SQL Server CE:
> select a, a+b AS c from t1 group by c, a order by c, a;
> SQL Server Compact Edition Version 3.5 returns
> Unhandled Exception: In aggregate and grouping expressions, the SELECT
> clause can contain only aggregates and grouping expressions. [ Select
> clause = ,b ]
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
