| Bug #97041 | main&sub partition field order different from combined pk, query result lose row | ||
|---|---|---|---|
| Submitted: | 26 Sep 2019 16:20 | Modified: | 28 Sep 2019 14:33 |
| Reporter: | tkaven T | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 8.0+, 8.0.17, 5.7.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | partition select virtual join | ||
[26 Sep 2019 17:11]
MySQL Verification Team
Thank you for the bug report. Please check the known issue of data conversion string->number u.uid = '2653021';:
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 Source distribution BUILT: 2019-SEP-20
Copyright (c) 2000, 2019, 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 8.0 > CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)
mysql 8.0 > USE test
Database changed
mysql 8.0 > CREATE TABLE `project` (
-> `user_id` int(11) NOT NULL,
-> `report_date_minute` datetime NOT NULL,
-> `report_date_hourly` datetime GENERATED ALWAYS AS (date_format(`report_date_minute`,_utf8mb4'%Y-%m-%d %H:00:00')) VIRTUAL,
-> PRIMARY KEY (`user_id`,`report_date_minute`) USING BTREE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
-> /*!50500 PARTITION BY RANGE COLUMNS(report_date_minute)
-> SUBPARTITION BY KEY (user_id)
-> (PARTITION `p_2019-09-24` VALUES LESS THAN ('2019-09-25')
-> (SUBPARTITION `p_2019-09-24_s1` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s2` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s3` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s4` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s5` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s6` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-24_s7` ENGINE = InnoDB),
-> PARTITION `p_2019-09-25` VALUES LESS THAN ('2019-09-26')
-> (SUBPARTITION `p_2019-09-25_s1` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s2` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s3` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s4` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s5` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s6` ENGINE = InnoDB,
-> SUBPARTITION `p_2019-09-25_s7` ENGINE = InnoDB)) */;
Query OK, 0 rows affected, 2 warnings (0.25 sec)
mysql 8.0 > INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (2653021, '2019-09-24 00:00:00');
Query OK, 1 row affected (0.02 sec)
mysql 8.0 > INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (2653021, '2019-09-25 00:00:00');
Query OK, 1 row affected (0.00 sec)
mysql 8.0 >
mysql 8.0 > #user table & data
mysql 8.0 > CREATE TABLE `user` (
-> `uid` int(8) unsigned NOT NULL,
-> PRIMARY KEY (`uid`) USING BTREE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql 8.0 > INSERT INTO `user`(`uid`) VALUES (2653021);
Query OK, 1 row affected (0.01 sec)
mysql 8.0 >
mysql 8.0 > #has BUG
mysql 8.0 > SELECT COUNT(*) AS C
-> FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
-> WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = '2653021';
+---+
| C |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql 8.0 > #has BUG
mysql 8.0 > SELECT COUNT(*) AS C
-> FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
-> WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = 2653021;
+---+
| C |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
[27 Sep 2019 3:28]
tkaven T
# ---- pls try new user table and data, see same type filed join by mysql internal also has incorrect result
#user table & data
CREATE TABLE `user` (
`uid` int(8) unsigned NOT NULL,
`parentid` int(11) DEFAULT NULL,
PRIMARY KEY (`uid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
INSERT INTO `user`(`uid`, `parentid`) VALUES (2653021, 1);
#same type with uid field join by mysql ALSO has BUG!
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.parentid = 1;
#C
#1
#has BUG
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = '2653021';
#C
#1
#no issue
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = 2653021;
#C
#2
#no issue
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND p.user_id = '2653021';
#C
#2
#no issue
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute='2019-09-24' and u.parentid = 1
UNION all
SELECT COUNT(*) AS C
FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid
WHERE p.report_date_minute='2019-09-25' and u.parentid = 1;
#C
#1
#1
[27 Sep 2019 8:00]
tkaven T
mysql> SELECT * FROM `project`; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 2653021 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 2 rows in set (0.10 sec) mysql> SELECT * FROM `project` where user_id=2653021; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 1 row in set (0.10 sec)
[27 Sep 2019 8:09]
tkaven T
mysql> show variables like "%version%" -> ; +--------------------------+---------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------+ | immediate_server_version | 999999 | | innodb_version | 8.0.15-6 | | original_server_version | 999999 | | protocol_version | 10 | | slave_type_conversions | | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | version | 8.0.15-6 | | version_comment | Percona Server (GPL), Release 6, Revision 63abd08 | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | | version_suffix | | +--------------------------+---------------------------------------------------+ 12 rows in set (0.13 sec) mysql> INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (265302, '2019-09-25 00:00:00'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (265302, '2019-09-24 00:00:00'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM `project`; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 2653021 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 265302 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 |
[27 Sep 2019 8:11]
tkaven T
mysql> SELECT * FROM `project`; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 2653021 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 265302 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | | 265302 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 4 rows in set (0.13 sec) mysql> SELECT * FROM `project` where user_id=2653021; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 1 row in set (0.18 sec) mysql> SELECT * FROM `project` where user_id=265302; +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 265302 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 1 row in set (0.17 sec) mysql> SELECT * FROM `project` where user_id IN (265302,2653021); +---------+---------------------+---------------------+ | user_id | report_date_minute | report_date_hourly | +---------+---------------------+---------------------+ | 265302 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 2653021 | 2019-09-24 00:00:00 | 2019-09-24 00:00:00 | | 2653021 | 2019-09-25 00:00:00 | 2019-09-25 00:00:00 | +---------+---------------------+---------------------+ 3 rows in set (0.13 sec) mysql>
[27 Sep 2019 9:12]
MySQL Verification Team
Thank you for the feedback. regards, Umesh
[28 Sep 2019 14:33]
tkaven T
The condition for triggering the bug is that the table has a primary key combined by two fields, and then the child partition is used, and then the partition field order of the primary partition and the child partition is different from the primary key order
Description: A specific partitioned table with virtual fields and a normal table JOIN query. When the query crosses the table partition, the query result is incorrect. How to repeat: #project table & data CREATE TABLE `project` ( `user_id` int(11) NOT NULL, `report_date_minute` datetime NOT NULL, `report_date_hourly` datetime GENERATED ALWAYS AS (date_format(`report_date_minute`,_utf8mb4'%Y-%m-%d %H:00:00')) VIRTUAL, PRIMARY KEY (`user_id`,`report_date_minute`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC /*!50500 PARTITION BY RANGE COLUMNS(report_date_minute) SUBPARTITION BY KEY (user_id) (PARTITION `p_2019-09-24` VALUES LESS THAN ('2019-09-25') (SUBPARTITION `p_2019-09-24_s1` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s2` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s3` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s4` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s5` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s6` ENGINE = InnoDB, SUBPARTITION `p_2019-09-24_s7` ENGINE = InnoDB), PARTITION `p_2019-09-25` VALUES LESS THAN ('2019-09-26') (SUBPARTITION `p_2019-09-25_s1` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s2` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s3` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s4` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s5` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s6` ENGINE = InnoDB, SUBPARTITION `p_2019-09-25_s7` ENGINE = InnoDB)) */; INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (2653021, '2019-09-24 00:00:00'); INSERT INTO `project`(`user_id`, `report_date_minute`) VALUES (2653021, '2019-09-25 00:00:00'); #user table & data CREATE TABLE `user` ( `uid` int(8) unsigned NOT NULL, PRIMARY KEY (`uid`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `user`(`uid`) VALUES (2653021); #has BUG SELECT COUNT(*) AS C FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = '2653021'; #C #1 #no issue SELECT COUNT(*) AS C FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND p.user_id = '2653021'; #C #2 #no issue SELECT COUNT(*) AS C FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid WHERE p.report_date_minute='2019-09-24' UNION all SELECT COUNT(*) AS C FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid WHERE p.report_date_minute='2019-09-25'; #C #1 #1 Suggested fix: #no issue SELECT COUNT(*) AS C FROM user AS u INNER JOIN `project` AS p ON p.user_id = u.uid WHERE p.report_date_minute IN ('2019-09-24','2019-09-25') AND u.uid = '2653021'; #C #2