MySQL Bugs: #64002: Aggregate queries with multiple columns and no GROUP BY produce bogus results

Bug #64002	Aggregate queries with multiple columns and no GROUP BY produce bogus results
Submitted:	11 Jan 2012 20:01	Modified:	12 Jan 2012 10:24
Reporter:	Daniel Grace	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.5.19	OS:	Any (Debian, Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	aggregate group by myisam, regression

Description:
Certain queries using fairly simple applications of GROUP BY and multiple aggregate functions report provably-incorrect results.

Using the table structure and data in "How to repeat", the following queries produce incorrect results:

SELECT MIN(start), MAX(end), UNIX_TIMESTAMP(MIN(start)), UNIX_TIMESTAMP(MAX(end)), COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);
-- Reports NULL for all columns (0 for COUNT) *except* for UNIX_TIMESTAMP(MIN(start)), which reports the correct value.  2 rows actually match the WHERE clause.

SELECT MIN(start), COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);
-- Reports NULL, 0.  2 rows actually match the WHERE clause.

However, the following queries produce correct results:
SELECT COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);  -- returns 2

SELECT MIN(start) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200); -- Note that including COUNT(*) and MIN(start) in the same query *fails*

SELECT * FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200); -- returns 2 rows.

Repeating the failing queries with the addition of GROUP BY 'constant_value' causes them to evaluate correctly.  This problem does not occur in MySQL 5.1 up to at least 5.1.49-3-log.  It does still occur with keys disabled (ALTER TABLE calendar_week DISABLE KEYS), and on larger datasets than the sample (408 rows of similar data)

How to repeat:
CREATE TABLE `calendar_week` (
  `year` smallint(6) NOT NULL,
  `startdate` date NOT NULL,
  `enddate` date NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime NOT NULL,
  `startmonth` date NOT NULL,
  `endmonth` date NOT NULL,
  `in_session` tinyint(4) NOT NULL,
  PRIMARY KEY (`year`,`startdate`),
  UNIQUE KEY `year_start` (`year`,`start`),
  UNIQUE KEY `year_end` (`year`,`end`),
  UNIQUE KEY `year_enddate` (`year`,`enddate`),
  KEY `year_startmonth` (`year`,`startmonth`),
  KEY `year_endmonth` (`year`,`endmonth`),
  KEY `start` (`start`),
  KEY `startdate` (`startdate`),
  KEY `startmonth` (`startmonth`),
  KEY `end` (`end`),
  KEY `enddate` (`enddate`),
  KEY `endmonth` (`endmonth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `calendar_week`
--

LOCK TABLES `calendar_week` WRITE;
/*!40000 ALTER TABLE `calendar_week` DISABLE KEYS */;
INSERT INTO `calendar_week` VALUES (2012,'2012-04-22','2012-04-28','2012-04-22 00:00:00','2012-04-28 23:59:59','2012-04-01','2012-04-01',1),(2012,'2012-04-29','2012-05-05','2012-04-29 00:00:00','2012-05-05 23:59:59','2012-04-01','2012-05-01',1),(2012,'2012-05-06','2012-05-12','2012-05-06 00:00:00','2012-05-12 23:59:59','2012-05-01','2012-05-01',1),(2012,'2012-05-13','2012-05-19','2012-05-13 00:00:00','2012-05-19 23:59:59','2012-05-01','2012-05-01',1),(2012,'2012-05-20','2012-05-26','2012-05-20 00:00:00','2012-05-26 23:59:59','2012-05-01','2012-05-01',1),(2012,'2012-05-27','2012-06-02','2012-05-27 00:00:00','2012-06-02 23:59:59','2012-05-01','2012-06-01',1),(2012,'2012-06-03','2012-06-09','2012-06-03 00:00:00','2012-06-09 23:59:59','2012-06-01','2012-06-01',1),(2012,'2012-06-10','2012-06-16','2012-06-10 00:00:00','2012-06-16 23:59:59','2012-06-01','2012-06-01',1),(2012,'2012-06-17','2012-06-23','2012-06-17 00:00:00','2012-06-23 23:59:59','2012-06-01','2012-06-01',1);
/*!40000 ALTER TABLE `calendar_week` ENABLE KEYS */;
UNLOCK TABLES;

SELECT startdate, enddate, start, end, UNIX_TIMESTAMP(startdate), UNIX_TIMESTAMP(enddate) FROM calendar_week;

SELECT COUNT(*), MIN(start), MAX(end), UNIX_TIMESTAMP(MIN(start)), UNIX_TIMESTAMP(MAX(end)), COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);

SELECT MIN(start) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);

SELECT COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);

SELECT * FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);

Thank you for the bug report. Verified just as described on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.5.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `calendar_week` (
    ->   `year` smallint(6) NOT NULL,
    ->   `startdate` date NOT NULL,
    ->   `enddate` date NOT NULL,
    ->   `start` datetime NOT NULL,
    ->   `end` datetime NOT NULL,
    ->   `startmonth` date NOT NULL,
    ->   `endmonth` date NOT NULL,
    ->   `in_session` tinyint(4) NOT NULL,
    ->   PRIMARY KEY (`year`,`startdate`),
    ->   UNIQUE KEY `year_start` (`year`,`start`),
    ->   UNIQUE KEY `year_end` (`year`,`end`),
    ->   UNIQUE KEY `year_enddate` (`year`,`enddate`),
    ->   KEY `year_startmonth` (`year`,`startmonth`),
    ->   KEY `year_endmonth` (`year`,`endmonth`),
    ->   KEY `start` (`start`),
    ->   KEY `startdate` (`startdate`),
    ->   KEY `startmonth` (`startmonth`),
    ->   KEY `end` (`end`),
    ->   KEY `enddate` (`enddate`),
    ->   KEY `endmonth` (`endmonth`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO `calendar_week` VALUES
    -> (2012,'2012-04-22','2012-04-28','2012-04-22 00:00:00','2012-04-28 23:59:5
9','2012-04-01','2012-04-01',1),
    -> (2012,'2012-04-29','2012-05-05','2012-04-29 00:00:00','2012-05-05 23:59:5
9','2012-04-01','2012-05-01',1),
    -> (2012,'2012-05-06','2012-05-12','2012-05-06 00:00:00','2012-05-12 23:59:5
9','2012-05-01','2012-05-01',1),
    -> (2012,'2012-05-13','2012-05-19','2012-05-13 00:00:00','2012-05-19 23:59:5
9','2012-05-01','2012-05-01',1),
    -> (2012,'2012-05-20','2012-05-26','2012-05-20 00:00:00','2012-05-26 23:59:5
9','2012-05-01','2012-05-01',1),
    -> (2012,'2012-05-27','2012-06-02','2012-05-27 00:00:00','2012-06-02 23:59:5
9','2012-05-01','2012-06-01',1),
    -> (2012,'2012-06-03','2012-06-09','2012-06-03 00:00:00','2012-06-09 23:59:5
9','2012-06-01','2012-06-01',1),
    -> (2012,'2012-06-10','2012-06-16','2012-06-10 00:00:00','2012-06-16 23:59:5
9','2012-06-01','2012-06-01',1),
    -> (2012,'2012-06-17','2012-06-23','2012-06-17 00:00:00','2012-06-23 23:59:5
9','2012-06-01','2012-06-01',1);
Query OK, 9 rows affected (0.09 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SELECT startdate, enddate, start, end, UNIX_TIMESTAMP(startdate), UNIX_TI
MESTAMP(enddate)
    -> FROM calendar_week;
+------------+------------+---------------------+---------------------+---------
------------------+-------------------------+
| startdate  | enddate    | start               | end                 | UNIX_TIM
ESTAMP(startdate) | UNIX_TIMESTAMP(enddate) |
+------------+------------+---------------------+---------------------+---------
------------------+-------------------------+
| 2012-04-22 | 2012-04-28 | 2012-04-22 00:00:00 | 2012-04-28 23:59:59 |
       1335042000 |              1335560400 |
| 2012-04-29 | 2012-05-05 | 2012-04-29 00:00:00 | 2012-05-05 23:59:59 |
       1335646800 |              1336165200 |
| 2012-05-06 | 2012-05-12 | 2012-05-06 00:00:00 | 2012-05-12 23:59:59 |
       1336251600 |              1336770000 |
| 2012-05-13 | 2012-05-19 | 2012-05-13 00:00:00 | 2012-05-19 23:59:59 |
       1336856400 |              1337374800 |
| 2012-05-20 | 2012-05-26 | 2012-05-20 00:00:00 | 2012-05-26 23:59:59 |
       1337461200 |              1337979600 |
| 2012-05-27 | 2012-06-02 | 2012-05-27 00:00:00 | 2012-06-02 23:59:59 |
       1338066000 |              1338584400 |
| 2012-06-03 | 2012-06-09 | 2012-06-03 00:00:00 | 2012-06-09 23:59:59 |
       1338670800 |              1339189200 |
| 2012-06-10 | 2012-06-16 | 2012-06-10 00:00:00 | 2012-06-16 23:59:59 |
       1339275600 |              1339794000 |
| 2012-06-17 | 2012-06-23 | 2012-06-17 00:00:00 | 2012-06-23 23:59:59 |
       1339880400 |              1340398800 |
+------------+------------+---------------------+---------------------+---------
------------------+-------------------------+
9 rows in set (0.03 sec)

mysql> SELECT COUNT(*), MIN(start), MAX(end), UNIX_TIMESTAMP(MIN(start)),
    -> UNIX_TIMESTAMP(MAX(end)), COUNT(*) FROM calendar_week WHERE year=2012 AND
 start BETWEEN
    -> FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);
+----------+------------+----------+----------------------------+---------------
-----------+----------+
| COUNT(*) | MIN(start) | MAX(end) | UNIX_TIMESTAMP(MIN(start)) | UNIX_TIMESTAMP
(MAX(end)) | COUNT(*) |
+----------+------------+----------+----------------------------+---------------
-----------+----------+
|        0 | NULL       | NULL     |                 1335646800 |
      NULL |        0 |
+----------+------------+----------+----------------------------+---------------
-----------+----------+
1 row in set (0.06 sec)

mysql> SELECT MIN(start) FROM calendar_week WHERE year=2012 AND start BETWEEN
    -> FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);
+---------------------+
| MIN(start)          |
+---------------------+
| 2012-04-29 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM calendar_week WHERE year=2012 AND start BETWEEN
    -> FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM calendar_week WHERE year=2012 AND start BETWEEN FROM_UNIXTI
ME(1335078000)
    -> AND FROM_UNIXTIME(1336201200);
+------+------------+------------+---------------------+---------------------+--
----------+------------+------------+
| year | startdate  | enddate    | start               | end                 | s
tartmonth | endmonth   | in_session |
+------+------------+------------+---------------------+---------------------+--
----------+------------+------------+
| 2012 | 2012-04-29 | 2012-05-05 | 2012-04-29 00:00:00 | 2012-05-05 23:59:59 | 2
012-04-01 | 2012-05-01 |          1 |
+------+------------+------------+---------------------+---------------------+--
----------+------------+------------+
1 row in set (0.00 sec)

Workaround really helps:

mysql> SELECT MIN(start) FROM calendar_week WHERE year=2012 AND start BETWEEN
    -> FROM_UNIXTIME(1335078000) AND FROM_UNIXTIME(1336201200) group by 'nothing
';
+---------------------+
| MIN(start)          |
+---------------------+
| 2012-04-29 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

Test case for copy/paste

Attachment: bug64002.sql (text/x-sql), 2.44 KiB.