MySQL Bugs: #77068: date value add '00:00:00' generate different result

Bug #77068	date value add '00:00:00' generate different result
Submitted:	18 May 2015 10:00	Modified:	18 May 2015 14:12
Reporter:	jacky chen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
when two tables left join, date value '2015-05-16 00:00:00' and '2015-05-16' generate different result.

How to repeat:
CREATE TABLE `t1` (
  `id` INT(11) NOT NULL,
  `business_day` DATE NOT NULL,
  `iid` BIGINT(20) NOT NULL,
  `buyer_num` MEDIUMINT(8) UNSIGNED NOT NULL,
  `seller_num` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`business_day`,`iid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 
CREATE TABLE `t2` (
  `id` INT(11) NOT NULL,
  `business_day` DATE NOT NULL,
  `iid` BIGINT(20) NOT NULL,
  `num` INT(11) NOT NULL DEFAULT '0',
  `item_num` INT(11) NOT NULL DEFAULT '0',
  `amount` DOUBLE NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`business_day`,`iid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8; 
 
 INSERT INTO `t1` (`id`, `business_day`, `iid`, `buyer_num`, `seller_num`)
VALUES
	(16896, '2015-05-16', 9795839182, 1, 44);
INSERT INTO `t2` (`id`, `business_day`, `iid`, `num`, `item_num`, `amount`)
VALUES
	(16896, '2015-05-16', 9795839182, 4, 4, 24.6);

mysql > SELECT t1.*, t2.*
    -> FROM t1 LEFT JOIN t2
    ->   ON t1.business_day = t2.business_day
    ->   AND t1.id = t2.id
    ->   AND t1.iid = t2.iid
    -> WHERE t1.id = 16896
    ->   AND t1.seller_num > 0
    ->   AND t1.business_day IN ('2015-05-16 00:00:00')
    ->   AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id   | business_day | iid  | num  | item_num | amount |
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | NULL | NULL         | NULL | NULL |     NULL |   NULL |
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
1 row in set (0.00 sec)

mysql > SELECT t1.*, t2.*
    -> FROM t1 LEFT JOIN t2
    ->   ON t1.business_day = t2.business_day
    ->   AND t1.id = t2.id
    ->   AND t1.iid = t2.iid
    -> WHERE t1.id = 16896
    ->   AND t1.seller_num > 0
    ->   AND t1.business_day IN ('2015-05-16')
    ->   AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id    | business_day | iid        | num | item_num | amount |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | 16896 | 2015-05-16   | 9795839182 |   4 |        4 |  24.61 |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+

Suggested fix:
don't know.

The diferent chrset setting may make it necessary to execute "SET NAMES latin1" (if the client s designed for latin1/ANSI) in order to make the server return data encoded in such a way that the client understands it.  

char(160) returns a one-byte result in latin1 and a two-byte result in utf8. 
Now if you wrap your query in a HEX() like "SELECT HEX(CONCAT_WS( ...));" you will easier be able to see what happens. They will probably be identical on 5.5 and 5.6 as it will only return characters (0..9,A..F) that have the same encoding in latin1/ANSI and utf8. If it does as I expect, it is all a client-side problem you are facing (client is designed for one specific charset) . 

-- Peter
-- not a MySQL/Oracle person

c:\dbs>5.6\bin\mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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 DATABASE u;
Query OK, 1 row affected (0.00 sec)

mysql> USE u
Database changed
mysql> CREATE TABLE `t1` (
    ->   `id` INT(11) NOT NULL,
    ->   `business_day` DATE NOT NULL,
    ->   `iid` BIGINT(20) NOT NULL,
    ->   `buyer_num` MEDIUMINT(8) UNSIGNED NOT NULL,
    ->   `seller_num` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`,`business_day`,`iid`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

mysql>
mysql> CREATE TABLE `t2` (
    ->   `id` INT(11) NOT NULL,
    ->   `business_day` DATE NOT NULL,
    ->   `iid` BIGINT(20) NOT NULL,
    ->   `num` INT(11) NOT NULL DEFAULT '0',
    ->   `item_num` INT(11) NOT NULL DEFAULT '0',
    ->   `amount` DOUBLE NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`id`,`business_day`,`iid`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)

mysql>
mysql>  INSERT INTO `t1` (`id`, `business_day`, `iid`, `buyer_num`, `seller_num`)
    -> VALUES
    ->  (16896, '2015-05-16', 9795839182, 1, 44);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO `t2` (`id`, `business_day`, `iid`, `num`, `item_num`, `amount`)
    -> VALUES
    ->  (16896, '2015-05-16', 9795839182, 4, 4, 24.6);
Query OK, 1 row affected (0.03 sec)

mysql>  SELECT t1.*, t2.*
    ->  FROM t1 LEFT JOIN t2
    ->  ON t1.business_day = t2.business_day
    ->  AND t1.id = t2.id
    ->  AND t1.iid = t2.iid
    ->  WHERE t1.id = 16896
    ->  AND t1.seller_num > 0
    ->  AND t1.business_day IN ('2015-05-16 00:00:00')
    ->  AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id   | business_day | iid  | num  | item_num | amount |
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | NULL | NULL         | NULL | NULL |     NULL |   NULL |
+-------+--------------+------------+-----------+------------+------+--------------+------+------+----------+--------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT t1.*, t2.*
    -> FROM t1 LEFT JOIN t2
    -> ON t1.business_day = t2.business_day
    -> AND t1.id = t2.id
    -> AND t1.iid = t2.iid
    -> WHERE t1.id = 16896
    -> AND t1.seller_num > 0
    -> AND t1.business_day IN ('2015-05-16')
    -> AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id    | business_day | iid        | num | item_num | amount |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | 16896 | 2015-05-16   | 9795839182 |   4 |        4 |   24.6 |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+-----+----------+--------+
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
Query OK, 1 row affected (0.45 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t2 DROP PRIMARY KEY;
Query OK, 1 row affected (0.50 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>  SELECT t1.*, t2.*
    ->  FROM t1 LEFT JOIN t2
    ->  ON t1.business_day = t2.business_day
    ->  AND t1.id = t2.id
    ->  AND t1.iid = t2.iid
    ->  WHERE t1.id = 16896
    ->  AND t1.seller_num > 0
    ->  AND t1.business_day IN ('2015-05-16 00:00:00')
    ->  AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id    | business_day | iid        | num  | item_num | amount |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | 16896 | 2015-05-16   | 9795839182 |    4 |        4 |   24.6 |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT t1.*, t2.*
    -> FROM t1 LEFT JOIN t2
    -> ON t1.business_day = t2.business_day
    -> AND t1.id = t2.id
    -> AND t1.iid = t2.iid
    -> WHERE t1.id = 16896
    -> AND t1.seller_num > 0
    -> AND t1.business_day IN ('2015-05-16')
    -> AND t1.iid IN (9795839182);
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
| id    | business_day | iid        | buyer_num | seller_num | id    | business_day | iid        | num  | item_num | amount |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
| 16896 | 2015-05-16   | 9795839182 |         1 |         44 | 16896 | 2015-05-16   | 9795839182 |    4 |        4 |   24.6 |
+-------+--------------+------------+-----------+------------+-------+--------------+------------+------+----------+--------+
1 row in set (0.00 sec)

mysql>

It seems I posted my comment the wrong place.  Sorry for that.

I have tested this on 5.6.24 and got the the same results as the previous two testers. This is a bug that is occurring in the executioner,  while searching for the equality of the indexed values.

There could also be a problem in DATETIME -> DATE transformation. It should work properly by discarding the TIME part of the DATETIME.

Fully verified.

Posted by developer:
 
EXPLAIN output shows that it is determined during optimization that the LEFT JOIN gives no matches:

EXPLAIN
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2
ON t1.business_day = t2.business_day AND t1.id = t2.id AND t1.iid = t2.iid
WHERE t1.id = 16896 AND t1.seller_num > 0 AND t1.business_day IN ('2015-05-16 00:00:00') AND t1.iid IN (9795839182);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	const	PRIMARY	PRIMARY	15	const,const,const	1	100.00	NULL
1	SIMPLE	t2	NULL	const	PRIMARY	PRIMARY	15	const,const,const	1	100.00	Impossible ON condition
Warnings:
Note	1003	/* select#1 */ select '16896' AS `id`,'2015-05-16' AS `business_day`,'9795839182' AS `iid`,'1' AS `buyer_num`,'44' AS `seller_num`,NULL AS `id`,NULL AS `business_day`,NULL AS `iid`,NULL AS `num`,NULL AS `item_num`,NULL AS `amount` from `test`.`t1` left join `test`.`t2` on((multiple equal('2015-05-16 00:00:00', NULL) and multiple equal(16896, NULL) and multiple equal(9795839182, NULL))) where (('44' > 0))

From the EXPLAIN warning, it seems to me that constant propagation is going too far, and the DATETIME constant is used for the DATE column in the index look-up.