TiDB4.0.0-rc版本SQL执行结果不符合预期

🪐 TiDB 技术问题
1
  • 【TiDB 版本】：4.0.0-rc
  • 【问题描述】：SQL查询结果不符合预期,在旧版3.0.12查询结果是正常的。

DDL 信息

CREATE TABLE `admin_roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '角色名称',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `admin_role_has_permissions` (
  `permission_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`permission_id`,`role_id`),
  KEY `admin_role_has_permissions_role_id_foreign` (`role_id`),
  CONSTRAINT `admin_role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `admin_permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `admin_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

填充数据

INSERT INTO `admin_roles` (`id`, `name`, `created_at`, `updated_at`) VALUES
(1, 'admin','2020-04-27 02:40:03', '2020-04-27 02:40:03'),
(2, 'developer','2020-04-27 02:40:03', '2020-04-27 02:40:03'),
(3, 'analyst','2020-04-27 02:40:03', '2020-04-27 02:40:03'),
(4, 'channel_admin','2020-04-27 02:40:03', '2020-04-27 02:40:03'),
(5, 'test','2020-04-27 02:40:08', '2020-04-27 02:40:08');

INSERT INTO `admin_role_has_permissions` (`permission_id`, `role_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(11, 1),
(12, 1),
(13, 1),
(14, 1),
(15, 1),
(16, 1),
(17, 1),
(18, 1),
(19, 1),
(20, 1),
(21, 1),
(22, 1),
(23, 1),
(24, 1),
(25, 1),
(26, 1),
(27, 1),
(28, 1),
(29, 1),
(30, 1),
(31, 1),
(32, 1),
(33, 1),
(34, 1),
(35, 1),
(36, 1),
(37, 1),
(38, 1),
(39, 1),
(40, 1),
(41, 1),
(42, 1),
(43, 1),
(44, 1),
(45, 1),
(46, 1),
(47, 1),
(48, 1),
(49, 1),
(50, 1),
(51, 1),
(52, 1),
(53, 1),
(54, 1),
(55, 1),
(56, 1),
(57, 1),
(58, 1),
(59, 1),
(60, 1),
(61, 1),
(62, 1),
(63, 1),
(64, 1),
(65, 1),
(66, 1),
(67, 1),
(68, 1),
(69, 1),
(70, 1),
(71, 1),
(72, 1),
(73, 1),
(74, 1),
(75, 1),
(76, 1),
(77, 1),
(78, 1),
(79, 1),
(80, 1),
(81, 1),
(82, 1),
(83, 1),
(5, 4),
(6, 4),
(7, 4),
(84, 5),
(85, 5),
(86, 5);

SQL查询

SELECT `admin_roles`.*
FROM `admin_roles` 
INNER JOIN `admin_role_has_permissions` ON `admin_roles`.`id` = `admin_role_has_permissions`.`role_id` 
WHERE `admin_role_has_permissions`.`permission_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67);
2

不符合预期是指什么？ 结果和3.0不一样还是什么

3

3.0.12能查出60多条记录 4.0.0-rc只有8条记录

4

麻烦帮忙确认几个点：

  1. 4.0.0-RC 是完全新安装还是升级上去的，从什么版本升级到 4.0.0-RC
  2. 3.0.12 和 4.0.0-RC 的 执行计划麻烦发下看看

我在我这的 4.0.0-RC 版本似乎没有复现问题，查询结果和 3.0.12 是一样的 70 行记录

image
image776×553 87.4 KB

5

你好，很高兴得到你的帮助。

  1. 我这边是使用Tiup升级的，升级前的版本为 v4.0.0-beta.1
  2. 晚点再提供3.0.12的执行计划
    image
    image1877×945 33.4 KB

    image
6

好的，收到，我们分析一下

7

Hello, 我们这边确认是一个 bug, 您可以通过 sql hint 暂时绕过这个 bug.

SELECT /*+ hash_join(admin_roles) */ `admin_roles`.*
FROM `admin_roles` 
INNER JOIN `admin_role_has_permissions` ON `admin_roles`.`id` = `admin_role_has_permissions`.`role_id` 
WHERE `admin_role_has_permissions`.`permission_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67);

这个问题将在下一个版本修复.

8

感谢你的帮助，该方法确实有效，辛苦大家了。

9

:ok_hand: