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

#1

On Thu, 10 Mar 2005, Tom Lane wrote:

Quote:

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. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely
copying our mistake...)

DB2 (version 8.1) gives 0, 1, 0, 1.

- Heikki

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#2

Tom Lane wrote:

Quote:

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. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely
copying our mistake...)

regards, tom lane

From SQL server 2000 with a service pack, I get:

zero rows from the first query (having 1=0);
one row, col value 1, from second query (having 1=1);
....run inserts...
zero rows from the third query (having 1=0);
one row, col value 1, from forth query (having 1=1);

K.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3

Tom Lane wrote:

Quote:

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. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely
copying our mistake...)

Firebird 1.5.1 FreeBSD 5.3
Database: test
SQL> drop table tab;
SQL> create table tab (col integer);
SQL> select 1 from tab having 1=0;
SQL> select 1 from tab having 1=1;

============

1

SQL> insert into tab values(1);
SQL> insert into tab values(2);
SQL> select 1 from tab having 1=0;
SQL> select 1 from tab having 1=1;

============

1

SQL>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#4

On Fri, 11 Mar 2005 10:37:13 +1300, Mark Kirkwood
<markir (AT) coretech (DOT) co.nz> wrote:

Quote:

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> insert into tab values(1);
SQL> insert into tab values(2);
SQL> select 1 from tab having 1=0;
SQL> select 1 from tab having 1=1;

============

1

SQL>

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP - 03-13-2005 , 08:04 PM

Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP - 03-13-2005 , 08:05 PM

Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP - 03-13-2005 , 08:06 PM

Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP - 03-17-2005 , 11:22 PM