[debian-mysql] Bug#505181: mysql-server-5.0: SELECT COUNT() with a WHERE clause, where no conditions found, returns an empty set.

[Hans Schou]

Package: mysql-server-5.0
Version: 5.0.67-1
Severity: normal


The package should not be checked out to testing or stable.

When using a WHERE claused with COUNT() and no criteria is met, the result should be 0, not an empty set.

mysql> SELECT COUNT(*) FROM user WHERE User='joe' GROUP BY User;
Empty set (0.00 sec)

Using COUNT()+WHERE and having directive spl-mode="ONLY_FULL_GROUP_BY" should not require a GROUP BY:

mysql> SELECT COUNT(*) FROM user WHERE User='joe';
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

External bug report:
http://bugs.mysql.com/bug.php?id=39656

-- System Information:
Debian Release: lenny/sid
  APT prefers unstable
  APT policy: (500, 'unstable')
Architecture: i386 (i686)

Kernel: Linux 2.6.26-1-686 (SMP w/2 CPU cores)
Locale: LANG=en_DK.UTF-8, LC_CTYPE=en_DK.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/bash

Versions of packages mysql-server-5.0 depends on:
ii  adduser                3.110             add and remove users and groups
ii  debconf [debconf-2.0]  1.5.24            Debian configuration management sy
ii  libc6                  2.7-15            GNU C Library: Shared libraries
ii  libdbi-perl            1.607-1           Perl5 database interface by Tim Bu
ii  libgcc1                1:4.3.2-1         GCC support library
ii  libmysqlclient15off    5.0.67-1          MySQL database client library
ii  libncurses5            5.6+20081025-1    shared libraries for terminal hand
ii  libreadline5           5.2-3             GNU readline and history libraries
ii  libstdc++6             4.3.2-1           The GNU Standard C++ Library v3
ii  libwrap0               7.6.q-16          Wietse Venema's TCP wrappers libra
ii  lsb-base               3.2-20            Linux Standard Base 3.2 init scrip
ii  mysql-client-5.0       5.0.67-1          MySQL database client binaries
ii  mysql-common           5.0.67-1          MySQL database common files
ii  passwd                 1:4.1.1-5         change and administer password and
ii  perl                   5.10.0-17         Larry Wall's Practical Extraction 
ii  psmisc                 22.6-1            Utilities that use the proc filesy
ii  zlib1g                 1:1.2.3.3.dfsg-12 compression library - runtime

Versions of packages mysql-server-5.0 recommends:
ii  bsd-mailx [mailx]  8.1.2-0.20071201cvs-3 A simple mail user agent
ii  heirloom-mailx [ma 12.3+cvs20080629-1    feature-rich BSD mail(1)
ii  libhtml-template-p 2.9-1                 HTML::Template : A module for usin
ii  mailx              1:20071201-3          Transitional package for mailx ren

Versions of packages mysql-server-5.0 suggests:
pn  tinyca                        <none>     (no description available)

-- debconf information excluded