On Fri, 11 Mar 2005 10:37:13 +1300, Mark Kirkwood
[EMAIL PROTECTED] wrote:
Firebird 1.5.1 FreeBSD 5.3
[correct results]
Interbase 6.0:
SQL create table tab (col integer);
SQL select 1 from tab having 1=0;
SQL select 1 from tab having 1=1;
0---:-)
SQL
Just a quick test on the DBMS I have available at work.
IBM Informix Dynamic Server Version 10.00.TC1TL
Error: The column (id) must be in the GROUP BY list.
Oracle 9.2.0.11
Returns the same records as if where would be used.
MSSQL Express 2005 Beta February TP
Error: Column 'tab.id' is invalid in
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote:
Would those of you with access to other DBMSes try this:
create table tab (col integer);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
insert into tab values(1);
insert into tab values(2);
select 1 from tab having
Here is your Sql run in a DB2 database.
connect to phoenix
Database Connection Information
Database server= DB2/LINUX 8.1.5
SQL authorization ID = GILL
Local database alias = PHOENIX
create table tab (col integer)
DB2I The SQL command completed successfully.
select 1
Results from Oracle 9.2.0.3 (9.2 patch 2)
===
Connected to Oracle9i Release 9.2.0.3.0
Connected as system
SQL
SQL DROP TABLE TESTTAB;
DROP TABLE TESTTAB
ORA-00942: table or view does not exist
SQL create table TESTtab (col integer);
Table created
SQL select 1 as col from TESTtab
On Thu, 10 Mar 2005, Tom Lane wrote:
Would those of you with access to other DBMSes try this:
create table tab (col integer);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
insert into tab values(1);
insert into tab values(2);
select 1 from tab having 1=0;
select 1 from tab having
What that means is that neither the HAVING clause nor the targetlist
can use any ungrouped columns except within aggregate calls; that is,
select col from tab having 21
Informix:
select tabname from systables having 2 1;
294: The column (tabname) must be in the GROUP BY list.
I wrote:
This is quite clear that the output of a HAVING clause is a grouped
table no matter whether the query uses GROUP BY or aggregates or not.
What that means is that neither the HAVING clause nor the targetlist
can use any ungrouped columns except within aggregate calls; that is,
Tom Lane wrote:
Would those of you with access to other DBMSes try this:
create table tab (col integer);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
insert into tab values(1);
insert into tab values(2);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
I claim that a
On Oracle 9.2 you get 0, 0, 0, and 2 rows.
--Barry
SQL create table tab (col integer);
Table created.
SQL select 1 from tab having 1=0;
no rows selected
SQL select 1 from tab having 1=1;
no rows selected
SQL insert into tab values (1);
1 row created.
SQL insert into tab values (2);
1
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote:
Would those of you with access to other DBMSes try this:
DB2/LINUX 8.1.6
create table tab (col integer);
select 1 from tab having 1=0;
1
---
0 record(s) selected.
select 1 from tab having 1=1;
1
On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Would those of you with access to other DBMSes try this:
On informix 9.21.UC4
create table tab (col integer);
select 1 from tab having 1=0;
returns no rows
select 1 from tab having 1=1;
returns no rows
insert into
Tom Lane wrote:
Would those of you with access to other DBMSes try this:
create table tab (col integer);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
insert into tab values(1);
insert into tab values(2);
select 1 from tab having 1=0;
select 1 from tab having 1=1;
I claim that a
Barry Lind [EMAIL PROTECTED] writes:
On Oracle 9.2 you get 0, 0, 0, and 2 rows.
Really!? Well, we always knew they were a bit standards-challenged ;-).
I have more faith in DB2 being an accurate implementation of the spec.
regards, tom lane
I wrote in reference to bug#1528:
What the spec actually says, or at least implies, is that a HAVING
clause is to be evaluated only once per group --- where the group
is the whole table if there's no GROUP BY clause.
In fact, reading the spec more closely, it is clear that the presence
of
Tom Lane wrote:
What that means is that neither the HAVING clause nor the targetlist
can use any ungrouped columns except within aggregate calls; that is,
select col from tab having 21
is in fact illegal per SQL spec, because col isn't a grouping column
(there are no grouping
Tom Lane [EMAIL PROTECTED] writes:
In particular this whole business of moving HAVING into WHERE is
wrong and should go away.
It sort of seems like select aggregate(col) from tab with no GROUP BY clause
is a bit of a special case. The consistent thing to do would be to return no
records. It's
Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?
Oracle does not allow such references. It issues ORA-00979: not a
GROUP BY expression when you try to hand it such a reference.
MS SQL Server does not allow such references either, yielding
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
Comments? Can anyone confirm whether DB2 or other databases allow
ungrouped column references with HAVING?
MySQL allows it:
A slightly tighter experiment shows that they treat HAVING like WHERE
in this case:
mysql create table tab(col int);
Greg Stark [EMAIL PROTECTED] writes:
It sort of seems like select aggregate(col) from tab with no GROUP BY clause
is a bit of a special case. The consistent thing to do would be to return no
records.
I don't think so. SQL99 defines this stuff in a way that might make you
feel better: it says
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Wednesday, March 09, 2005 8:45 PM
To: Christopher Kings-Lynne
Cc: Kevin Brown; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] We are not following the spec for HAVING without
GROUP
21 matches
Mail list logo
