having1.err

application/octet-stream

Download

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name		:	Jose' Soares
Your email address	:	sferac@bo.nettuno.it 


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: Intel Pentium

  Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.0.34 Elf

  PostgreSQL version (example: PostgreSQL-6.1)  : PostgreSQL-v6.4

  Compiler used (example:  gcc 2.7.2)		: gcc 2.7.2.1


Please enter a FULL description of your problem:
------------------------------------------------
Seems that I found a bug on HAVING clause


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
I have a table like this:

CREATE TABLE comuni (
	istat			CHAR(06) PRIMARY KEY NOT NULL,
	nome 			CHAR(50) NOT NULL,
	provincia		CHAR(02),
	codice_fiscale		CHAR(04),
	cap 			CHAR(05),
	regione			CHAR(03),
	distretto		CHAR(04)
	);
CREATE INDEX nome_comune_idx ON comuni (nome);

I tried the following query :

select * from comuni where nome in (
     select nome from comuni group by nome having 1 < count(nome)
     );

on the above table with 8342 rows, it begins search
but after 2m34 seconds it aborts backend.
If I try the same query with 242 rows it works fine.

EXAMPLES:

$ time psql marche -f 242.sql

select count(nome) from comuni;
count
-----
  242
(1 row)

select * from comuni where nome in (
     select nome from comuni group by nome having 1 < count(nome)
     );
istat|nome|provincia|codice_fiscale|cap|regione|distretto
-----+----+---------+--------------+---+-------+---------
(0 rows)

EOF

real    0m16.516s
user    0m0.030s
sys     0m0.020s


a$ time psql hygea -f 8342.sql
select count(nome) from comuni;
count
-----
 8342
(1 row)

select * from comuni where nome in (
     select nome from comuni group by nome having 1 < count(nome)
     );
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally before or while pr
ocessing the request.
We have lost the connection to the backend, so further processing is impossible.
  Terminating.

real    2m34.069s
user    0m0.030s
sys     0m0.030s


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I don't know.