Problems with HAVING expressions

[P.B.van.den.Berg]

From: p.b.van.den.berg@stripped
To: bugs@stripped
Subject: Problems with HAVING expressions

>Description:
	The following select-query produces 403 rows:
	 select p.patid, gebdat, 
	  count(distinct atc) as natc, min(afldat) as refdat
	 from paty p inner join recy r using (patid)
	 group by p.patid, gebdat
	 having refdat>=gebdat+interval 75 year
	If you leave out the 'distinct', the number is 406:
	 select p.patid, gebdat, 
	  count(atc) as natc, min(afldat) as refdat
	 from paty p inner join recy r using (patid)
	 group by p.patid, gebdat
	 having refdat>=gebdat+interval 75 year
	It looks like the HAVING expression is not properly evaluated in the
	first query.
	If I substitute 80 for 75, the result with distinct is 219 rows, and
	without it 216 rows. In the distinct-result 97 out of 219 rows have
	not been evaluated properly. Another 94 rows have not been evaluated
	properly because they are not included in the result.
>How-To-Repeat:
	Run the queries on the tables that I have uploaded to your
	ftp-site support.mysql.com/pub/mysql/secret/havexprb.tgz.
	tar zxf yourpath/havexprb.tgz will create directory havexprb relative
	to your current directory.
	It contains a file README, identical to the email I sent to
	bugs@stripped
	From the havexprb directory load the test-tables:
	 mysql test < paty.sql  # created with mysqldump -a -e
	 mysql -e 'select count(*) from paty' test  # 406
	 mysql test < recy.sql
	 mysql -e 'select count(*) from recy' test  # 3289
	Run queries to show the first bug:
	 mysql test < bughunt.sql
	Run queries to show the bug with 75 replaced by 80:
	 mysql test < bug80.sql
	I found some workarounds, but also more problems:
	 mysql test < war_mpr.sql
	The output from my queries is collected in
	 debout.txt (Debian woody with mysql 3.23.49)
	 winout.txt (Windows 98 SE with mysql 4.0.11)
>Fix:
	Workaround1: use expression instead of alias in HAVING.
	Workaround2: move the date-expression in HAVING to the SELECT-list
	 with an alias and use the alias in HAVING.
	Non-fix: I have reduced the dataset as much as possible. With
	 a further reduction in size the problem disapears.

>Submitter-Id:	<submitter ID>
>Originator:	paul b van den berg
>Organization: 	InterAction Database,
		Department of Social Pharmacy, Pharmacoepidemiology and
		Pharmacotherapeutics
		University of Groningen, Netherlands

>MySQL support: [none* | licence | email support | extended email support ]
>Synopsis:	Problems with HAVING expressions
>Severity:	[ non-critical | serious* | critical ] 
>Priority:	[ low | medium | high ] 
>Category:	mysql-server
>Class:		[ sw-bug* | doc-bug | change-request | support ] 
>Release:	mysql-3.23.49 (Source distribution)
>Server: /usr/bin/mysqladmin  Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free 
software,
and you are welcome to modify and redistribute it under the GPL 
license

Server version		3.23.49-log
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			11 min 59 sec

Threads: 1  Questions: 16  Slow queries: 0  Opens: 8  Flush tables: 1 
 
Open tables: 2 Queries per second avg: 0.022
>Environment:
	<machine, os, target, libraries (multiple lines)>
System: Linux noteb3 2.4.18 #1 Mon Mar 25 22:29:33 CET 2002 i686 
unknown
Architecture: i686

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc'  CFLAGS=''  CXX='c++'  CXXFLAGS='' 
 LDFLAGS=''
LIBC: 
lrwxrwxrwx    1 root     root           13 Nov 21 21:10 /lib/libc.so.6 
-> libc-2.2.5.so
-rwxr-xr-x    1 root     root      1153784 Sep 18 11:40 
/lib/libc-2.2.5.so
-rw-r--r--    1 root     root      2390970 Sep 18 11:41 
/usr/lib/libc.a
-rw-r--r--    1 root     root          178 Sep 18 11:41 
/usr/lib/libc.so
Configure command: ./configure  --prefix=/usr --exec-prefix=/usr \
  --libexecdir=/usr/sbin --datadir=/usr/share --sysconfdir=/etc/mysql 
\
  --localstatedir=/var/lib/mysql --includedir=/usr/include \
  --infodir=/usr/share/info --mandir=/usr/share/man --enable-shared \
  --with-libwrap --enable-assembler --with-berkeley-db --with-innodb \
  --enable-static --enable-shared --enable-local-infile --with-raid \
  --enable-thread-safe-client --without-readline \
  --with-unix-socket-path=/var/run/mysqld/mysqld.sock \
  --with-mysqld-user=mysql --without-bench 
--with-client-ldflags=-lstdc++ \
  --with-extra-charsets=all

The bug was discovered on a linux box running Debian Woody (3.0.1) 
with a 
Debian-patched version of 3.23.49 (patched by the Debian security 
team?). 
This bug-report was written on a notebook with the same Debian setup 
(details
above), and also running Windows 98 SE with mysql-4.0.11-gamma-win.zip 
binaries
from www.mysql.com

Paul B van den Berg p.b.van.den.berg@stripped
InterAction Database
Department of Social Pharmacy, Pharmacoepidemiology & 
Pharmacotherapeutics, University of Groningen
tel [phone] fax [phone]