MySQL Bugs: #46227: Wrong result on DISTINCT + aggregate + JOIN + InnoDB

Bug #46227	Wrong result on DISTINCT + aggregate + JOIN + InnoDB
Submitted:	16 Jul 2009 14:43	Modified:	16 Jul 2009 16:38
Reporter:	Philip Stoev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0,5.1,5.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Queries of the following form

SELECT DISTINCT SUM( OUTR .`pk`  )
FROM C OUTR2  JOIN B OUTR
ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`;

returns a wrong result. Note that the DISTINCT is redundant in this query and therefore should not influence the result.

How to repeat:
--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS C, B;
--enable_warnings

CREATE TABLE `C` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `time_key` time NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,'20:53:30','0000-00-00 00:00:00'),(2,'00:00:00','2008-04-19 07:51:37'),(3,'12:37:08','2006-06-03 00:00:00'),(4,'00:00:00','0000-00-00 00:00:00'),(5,'15:59:14','2000-10-03 15:17:43'),(6,'04:43:51','2009-04-25 16:10:46'),(7,'00:00:00','2005-01-11 03:31:23'),(8,'09:51:25','0000-00-00 00:00:00'),(9,'00:00:00','2000-03-07 00:00:00'),(10,'00:00:00','2001-06-14 20:33:16'),(11,'00:00:00','2005-03-06 05:45:38'),(12,'18:50:55','0000-00-00 00:00:00'),(13,'00:00:00','0000-00-00 00:00:00'),(14,'08:22:36','2002-02-13 21:59:10'),(15,'00:00:00','0000-00-00 00:00:00'),(16,'09:37:21','0000-00-00 00:00:00'),(17,'05:15:03','2001-12-23 00:00:00'),(18,'20:11:43','2004-05-16 00:00:00'),(19,'00:00:00','2004-04-02 00:00:00'),(20,'00:00:00','2003-11-26 00:00:00');
CREATE TABLE `B` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `time_key` time NOT NULL,
  `datetime_nokey` datetime NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `time_key` (`time_key`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO `B` VALUES (1,'09:33:50','0000-00-00 00:00:00'),(2,'00:00:00','2007-03-03 01:12:45');

SELECT DISTINCT  SUM( OUTR .`pk`  )
FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`;

Thank you for the bug report. Verified just as described on recent bzr threes:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.85 Source distribution

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

mysql> CREATE TABLE `C` (    ->   `pk` int(11) NOT NULL AUTO_INCREMENT,    ->   `time_key` time NOT NULL,    ->   `datetime_nokey` datetime NOT NULL,
    ->   PRIMARY KEY (`pk`),
    ->   KEY `time_key` (`time_key`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `C` VALUES (1,'20:53:30','0000-00-00 00:00:00'),(2,'00:00:00','2008-04-19 07:51:37'),(3,'12:37:08','2006-06-03 00:00:00'),(4,'00:00:00','0000-00-00 00:00:00'),
    -> (5,'15:59:14','2000-10-03 15:17:43'),(6,'04:43:51','2009-04-25 16:10:46'),
    -> (7,'00:00:00','2005-01-11 03:31:23'),(8,'09:51:25','0000-00-00 00:00:00'),
    -> (9,'00:00:00','2000-03-07 00:00:00'),(10,'00:00:00','2001-06-14 20:33:16'),
    -> (11,'00:00:00','2005-03-06 05:45:38'),(12,'18:50:55','0000-00-00 00:00:00'),
    -> (13,'00:00:00','0000-00-00 00:00:00'),(14,'08:22:36','2002-02-13 21:59:10'),
    -> (15,'00:00:00','0000-00-00 00:00:00'),(16,'09:37:21','0000-00-00 00:00:00'),
    -> (17,'05:15:03','2001-12-23 00:00:00'),(18,'20:11:43','2004-05-16 00:00:00'),
    -> (19,'00:00:00','2004-04-02 00:00:00'),(20,'00:00:00','2003-11-26 00:00:00');
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `B` (
    ->   `pk` int(11) NOT NULL AUTO_INCREMENT,
    ->   `time_key` time NOT NULL,
    ->   `datetime_nokey` datetime NOT NULL,
    ->   PRIMARY KEY (`pk`),
    ->   KEY `time_key` (`time_key`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `B` VALUES (1,'09:33:50','0000-00-00 00:00:00'),
    -> (2,'00:00:00','2007-03-03 01:12:45');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT  SUM( OUTR .`pk`  )
    -> FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`;
+--------------------+
| SUM( OUTR .`pk`  ) |
+--------------------+
|                 50 | 
+--------------------+
1 row in set (0.02 sec)

The above result is wrong! Look:

mysql> SELECT SUM( OUTR .`pk`  ) FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`;
+--------------------+
| SUM( OUTR .`pk`  ) |
+--------------------+
|                 30 | 
+--------------------+
1 row in set (0.00 sec)

mysql> explain SELECT SUM( OUTR .`pk`  ) FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OUTR
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: OUTR2
         type: ALL
possible_keys: time_key
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Range checked for each record (index map: 0x2)
2 rows in set (0.00 sec)

mysql> explain SELECT DISTINCT SUM( OUTR .`pk`  ) FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: OUTR
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: OUTR2
         type: index
possible_keys: time_key
          key: time_key
      key_len: 3
          ref: NULL
         rows: 20
        Extra: Using where; Using index
2 rows in set (0.00 sec)

mysql> SELECT OUTR .`pk` FROM C OUTR2  JOIN B OUTR  ON OUTR2 .`time_key`  <= OUTR .`datetime_nokey`;
+----+
| pk |
+----+
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  1 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
|  2 | 
+----+
20 rows in set (0.00 sec)

Not reproducible with 5.6.12, similar to #46127