MySQL Bugs: #118453: select datetime field returns wrong result after update

Bug #118453	select datetime field returns wrong result after update
Submitted:	16 Jun 8:50	Modified:	16 Jun 10:45
Reporter:	yujie wang	Email Updates:
Status:	Analyzing	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.41	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Description:
select datetime field returns wrong result after update

expected:
SELECT * FROM update_tbtest_no_part WHERE (id=35337 or id=35437 or id=35537 or id=35637 or id=35737 or id=35837 or id=35937 or id=36037) ORDER BY id
--------------
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+
| id    | tinyint_field | smallint_field | bigint_field | float_field | datetime_field      | timestamp_field     | char_field | varchar_field | text_field           |
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+
| 35337 |             1 |              0 |        35337 |       35337 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35337    | aaaaaaaaaaaaaaa35337 |
| 35437 |             1 |              0 |        35437 |       35437 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35437    | aaaaaaaaaaaaaaa35437 |
| 35537 |             1 |              0 |        35537 |       35537 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35537    | aaaaaaaaaaaaaaa35537 |
| 35637 |             1 |              0 |        35637 |       35637 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35637    | aaaaaaaaaaaaaaa35637 |
| 35737 |             1 |              0 |        35737 |       35737 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35737    | aaaaaaaaaaaaaaa35737 |
| 35837 |             1 |              0 |        35837 |       35837 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35837    | aaaaaaaaaaaaaaa35837 |
| 35937 |             1 |              0 |        35937 |       35937 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa35937    | aaaaaaaaaaaaaaa35937 |
| 36037 |             1 |              0 |        36037 |       36037 | 2018-08-09 00:01:00 | 2018-08-08 00:00:00 | 55         | aaaaa36037    | aaaaaaaaaaaaaaa36037 |
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+

results:
SELECT * FROM update_tbtest_no_part WHERE (id=35337 or id=35437 or id=35537 or id=35637 or id=35737 or id=35837 or id=35937 or id=36037) ORDER BY id
--------------
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+
| id    | tinyint_field | smallint_field | bigint_field | float_field | datetime_field      | timestamp_field     | char_field | varchar_field | text_field           |
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+
| 35337 |             1 |              0 |        35337 |       35337 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35337    | aaaaaaaaaaaaaaa35337 |
| 35437 |             1 |              0 |        35437 |       35437 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35437    | aaaaaaaaaaaaaaa35437 |
| 35537 |             1 |              0 |        35537 |       35537 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35537    | aaaaaaaaaaaaaaa35537 |
| 35637 |             1 |              0 |        35637 |       35637 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35637    | aaaaaaaaaaaaaaa35637 |
| 35737 |             1 |              0 |        35737 |       35737 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35737    | aaaaaaaaaaaaaaa35737 |
| 35837 |             1 |              0 |        35837 |       35837 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35837    | aaaaaaaaaaaaaaa35837 |
| 35937 |             1 |              0 |        35937 |       35937 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa35937    | aaaaaaaaaaaaaaa35937 |
| 36037 |             1 |              0 |        36037 |       36037 | 2018-08-09 00:00:00 | 2018-08-08 00:00:00 | 55         | aaaaa36037    | aaaaaaaaaaaaaaa36037 |
+-------+---------------+----------------+--------------+-------------+---------------------+---------------------+------------+---------------+----------------------+

How to repeat:
create database test;
use test
CREATE TABLE IF NOT EXISTS update_tbtest_part (
  id INT,
  tinyint_field TINYINT,
  smallint_field SMALLINT,
  bigint_field BIGINT,
  float_field FLOAT,
  datetime_field DATETIME,
  timestamp_field TIMESTAMP NULL,
  char_field CHAR(8),
  varchar_field VARCHAR(10),
  text_field TEXT(20),
  PRIMARY KEY (id)
  )ENGINE=Innodb ; 

CREATE TABLE IF NOT EXISTS update_tbtest_no_part (
  id INT,
  tinyint_field TINYINT,
  smallint_field SMALLINT,
  bigint_field BIGINT,
  float_field FLOAT,
  datetime_field DATETIME,
  timestamp_field TIMESTAMP NULL,
  char_field CHAR(8),
  varchar_field VARCHAR(10),
  text_field TEXT(20),
  PRIMARY KEY (id)
  )ENGINE=Innodb ;
  
CREATE TABLE IF NOT EXISTS update_tbtest_part_2 (
  id INT,
  tinyint_field TINYINT,
  smallint_field SMALLINT,
  bigint_field BIGINT,
  float_field FLOAT,
  datetime_field DATETIME,
  timestamp_field TIMESTAMP NULL,
  char_field CHAR(8),
  varchar_field VARCHAR(10),
  text_field TEXT(20),
  PRIMARY KEY (id)
  )ENGINE=Innodb ;

DROP PROCEDURE IF EXISTS load_tbtest_update;
DELIMITER $$
CREATE PROCEDURE load_tbtest_update( id_count INT)
BEGIN
SET @id = 1;
SET @tinyint_f = 1;
SET @smallint_f = 1;
SET @bigint_f = 1;
SET @float_f = 1;
SET @lsql = '';
WHILE @id <= id_count DO
SET @datetime_f = '2018-08-08';
SET @timestamp_f = '2018-08-08';
SET @char_f = @id ;
SET @varchar_f = concat(repeat('a',10-length(@id)),@id);
SET @text_f = concat(repeat('a',20-length(@id)),@id);
SET @lsql = CONCAT('(',@id,',', @tinyint_f,',', @smallint_f,',', @bigint_f,',', @float_f,',\'', @datetime_f,'\',\'', @timestamp_f,'\',\'', @char_f,'\',\'', @varchar_f,'\',\'', @text_f,'\'),',@lsql);
IF @id % 10000 = 0 THEN
SET @lsql=left(@lsql,CHAR_LENGTH(@lsql)-1);
SET @lsql = CONCAT('INSERT INTO update_tbtest_part VALUES',@lsql,';');
PREPARE st_insert FROM @lsql;
EXECUTE st_insert ;
COMMIT;
SET @lsql = '';
DEALLOCATE PREPARE st_insert;
END IF;
SET @id = @id + 1;
SET @float_f = @float_f + 1;
SET @bigint_f = @bigint_f + 1;
END WHILE;
COMMIT;
END ;
$$
DELIMITER ;

call load_tbtest_update(400000);

INSERT INTO update_tbtest_part_2 SELECT * from update_tbtest_part ;
INSERT INTO update_tbtest_no_part SELECT * from update_tbtest_part ;

CREATE OR REPLACE VIEW update_v_tbtest as SELECT * FROM update_tbtest_part ;

CREATE UNIQUE INDEX idx_uni ON update_tbtest_no_part(id,tinyint_field asc, smallint_field asc, bigint_field asc);
CREATE INDEX idx_f ON update_tbtest_no_part(char_field asc, varchar_field desc);  
CREATE INDEX idx_n ON update_tbtest_no_part(float_field desc);
CREATE INDEX idx_d ON update_tbtest_no_part(datetime_field asc, timestamp_field desc); 
CREATE UNIQUE INDEX idx_uni ON update_tbtest_part(id,tinyint_field asc, smallint_field asc, bigint_field asc);
CREATE INDEX idx_f ON update_tbtest_part(char_field asc, varchar_field desc);  
CREATE INDEX idx_n ON update_tbtest_part(float_field desc);
CREATE INDEX idx_d ON update_tbtest_part(datetime_field asc, timestamp_field desc);
CREATE UNIQUE INDEX idx_uni ON update_tbtest_part_2(id,tinyint_field asc, smallint_field asc, bigint_field asc);
CREATE INDEX idx_f ON update_tbtest_part_2(char_field asc, varchar_field desc);  
CREATE INDEX idx_n ON update_tbtest_part_2(float_field desc);
CREATE INDEX idx_d ON update_tbtest_part_2(datetime_field asc, timestamp_field desc);

UPDATE IGNORE test.update_v_tbtest AS table_UPDATE_MultipleTable_Join_045 ,update_tbtest_no_part FORCE INDEX FOR ORDER BY (idx_n) FORCE KEY FOR JOIN (idx_d) SET update_tbtest_no_part.smallint_field =( (ABS(MOD(1,table_UPDATE_MultipleTable_Join_045.tinyint_field))) ^ (0.1) ) , update_tbtest_no_part.char_field =( ASCII(LENGTH('abcdefg')) ) , update_tbtest_no_part.datetime_field = ((update_tbtest_no_part.datetime_field + INTERVAL 1 DAY) + INTERVAL (update_tbtest_no_part.smallint_field IN (select 1 from update_tbtest_part_2 where id = 1)) MINUTE ) WHERE (table_UPDATE_MultipleTable_Join_045.id=35337 or table_UPDATE_MultipleTable_Join_045.id=35437 or table_UPDATE_MultipleTable_Join_045.id=35537 or table_UPDATE_MultipleTable_Join_045.id=35637 or table_UPDATE_MultipleTable_Join_045.id=35737 or table_UPDATE_MultipleTable_Join_045.id=35837 or table_UPDATE_MultipleTable_Join_045.id=35937 or table_UPDATE_MultipleTable_Join_045.id=36037) AND (update_tbtest_no_part.id=35337 or update_tbtest_no_part.id=35437 or update_tbtest_no_part.id=35537 or update_tbtest_no_part.id=35637 or update_tbtest_no_part.id=35737 or update_tbtest_no_part.id=35837 or update_tbtest_no_part.id=35937 or update_tbtest_no_part.id=36037) ;

SELECT * FROM update_tbtest_no_part WHERE (id=35337 or id=35437 or id=35537 or id=35637 or id=35737 or id=35837 or id=35937 or id=36037) ORDER BY id;