Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY

2005-03-13 Thread Mark Shewmaker 
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 1=0;
 select 1 from tab having 1=1;
 
 I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows
 from the 4 selects --- that is, the contents of tab make no difference
 at all.

Sybase ASE version 12.5.2 returns 0, 0, 0, and 1 rows.

A plain select 1 from tab returns zero rows when tab is empty.

-- 
Mark Shewmaker
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] [BUGS] We are not following the spec for HAVING without GROUP

2005-03-13 Thread Mark Shewmaker 
On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote: 
 Comments?  Can anyone confirm whether DB2 or other databases allow
 ungrouped column references with HAVING?

In Sybase:

1 select 2 as id, max(myfield) from mytable where 2=1
2 go
 id
 --- --
   2 NULL

(1 row affected)
1 select 2 as id, max(myfield) from mytable having 2=1
2 go
 id
 --- --

(0 rows affected)

-- 
Mark Shewmaker
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq