Bug #299 Problem with COUNT(DISTINCT xxx) in HAVING clause
Submitted: 16 Apr 2003 14:59 Modified: 16 May 2003 2:23
Reporter: Basilio Vera Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:MySQL 3.23.38 & MySQL 4.1.0 Alpha OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[16 Apr 2003 14:59] Basilio Vera
Description:
#-----------------------------------------------------------
# With the next structure:

CREATE TABLE metges (
  num_met int(11) NOT NULL default '0',
  nom_met char(30) default NULL,
  especialitzat char(30) default NULL,
  adreca char(30) default NULL,
  tel int(11) default NULL,
  sou int(11) default NULL,
  PRIMARY KEY  (num_met)
) TYPE=MyISAM;

CREATE TABLE visites (
  num_met int(11) NOT NULL default '0',
  num_mal int(11) NOT NULL default '0',
  data_visita date NOT NULL default '0000-00-00',
  import int(11) default NULL,
  PRIMARY KEY  (num_met,num_mal,data_visita)
) TYPE=MyISAM;

# And next data:

INSERT INTO metges VALUES (1214, 'DOLORS P.', 'RONYO', 'PARIS', 2240257, 150000);
INSERT INTO metges VALUES (12528, 'JORDI G.', 'ESTOMAC', 'ARAGO', 4252746, 150000);
INSERT INTO metges VALUES (7215, 'MIQUEL L.', 'TRAUMATOLOGIA', 'BALMES', 3276072, 100000);
INSERT INTO metges VALUES (15326, 'CARME M.', 'ENDOCRINOLOGIA', 'AMIGO', 4250667, 100000);
INSERT INTO metges VALUES (8517, 'ANNA R.', 'COR I PULMO', 'MUNTANER', 2093085, 250000);

INSERT INTO visites VALUES (1214, 1, '1995-01-15', 5000);
INSERT INTO visites VALUES (1214, 1, '1995-05-05', 2000);
INSERT INTO visites VALUES (1214, 3, '1995-01-22', 7000);
INSERT INTO visites VALUES (12528, 2, '1995-02-04', 3000);
INSERT INTO visites VALUES (12528, 4, '1995-07-23', 5000);
INSERT INTO visites VALUES (12528, 5, '1995-03-07', 5000);
INSERT INTO visites VALUES (12528, 5, '1995-04-20', 3000);
INSERT INTO visites VALUES (12528, 5, '1995-06-30', 5000);
INSERT INTO visites VALUES (7215, 2, '1995-08-17', 7000);
INSERT INTO visites VALUES (7215, 6, '1995-10-15', 10000);
INSERT INTO visites VALUES (7215, 7, '1995-11-24', 10000);
#------------------------------------------------------------
When I try this QUERY:

SELECT m.nom_met, COUNT(DISTINCT v.import) 
FROM metges m, visites v
WHERE m.num_met = v.num_met
GROUP BY nom_met
HAVING COUNT(DISTINCT v.import)>2

The server says:

Unknown column 'v.import' in 'having clause'.

The problem is with COUNT(DISTINCT ...) <-- Without the DISTINCT clause, it works. But I'want the other query, not this...

This works on PostgreSQL, Informix or Oracle, for example.

How to repeat:

Suggested fix:
Fix the COUNT(DISTINCT xxx) on the HAVING clause.
[17 Apr 2003 4:21] Indrek Siitan
This query will work as:

SELECT m.nom_met, COUNT(DISTINCT v.import) as cnt
FROM metges m, visites v
WHERE m.num_met = v.num_met
GROUP BY nom_met
HAVING cnt>2
[16 May 2003 2:23] Michael Widenius
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Indrek provided a workaround for this problem, but not a solution.

The bug with COUNT(DISTINCT) in HAVING is now fixed and will appear in
4.0.13 and 4.1.1