Problems with HAVING expressions
am 08.03.2003 21:55:53 von Paul B van den BergFrom: p.b.van.den.berg@farm.rug.nl
To: bugs@lists.mysql.com
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@lists.mysql.com
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:
>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:
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@farm.rug.nl
InterAction Database
Department of Social Pharmacy, Pharmacoepidemiology &
Pharmacotherapeutics, University of Groningen
tel +31 50 3633331 fax +31 50 3632772
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13928@lists.mysql.com
To unsubscribe, e-mail