planner: Pushing down other conditions error for joins

[fzhedu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

CREATE TABLE `A` (
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_1024_utf8_not_null` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_10_utf8_not_null` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_10_utf8_not_null_key` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_1024_utf8_not_null_key` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_1024_latin1_key` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_10_latin1_not_null` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_1024_latin1_not_null_key` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `col_varchar_10_latin1_not_null_key` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col_varchar_1024_latin1_not_null` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_varchar_10_utf8_not_null_key` (`col_varchar_10_utf8_not_null_key`),
  KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`),
  KEY `col_varchar_1024_utf8_not_null_key` (`col_varchar_1024_utf8_not_null_key`(333)),
  KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(1000)),
  KEY `col_varchar_1024_utf8_key` (`col_varchar_1024_utf8_key`(333)),
  KEY `col_varchar_1024_latin1_not_null_key` (`col_varchar_1024_latin1_not_null_key`(1000)),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`),
  KEY `col_varchar_10_latin1_not_null_key` (`col_varchar_10_latin1_not_null_key`)
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `A` VALUES (7,'f','x','e',NULL,'a','e','q',NULL,NULL,'s','i','c',1,5,'w',5,7,'k','m','l');
INSERT INTO `A` VALUES (NULL,'q','k','o','i','s','p','f','v',NULL,'q','t','n',2,NULL,'s',8,3,'t','o',NULL);
INSERT INTO `A` VALUES (0,'q','d','e','c','y','u','o','i','y','g','t','k',3,NULL,'k',4,0,'b','o','r');
INSERT INTO `A` VALUES (0,'z','v','s','r','d',NULL,'c','x','o','g',NULL,'x',4,3,'q',4,6,'t','m','w');
INSERT INTO `A` VALUES (3,'e','x',NULL,'c','a','h','p',NULL,'f','j','r','e',5,3,'n',2,1,'x','e',NULL);
INSERT INTO `A` VALUES (1,'m','b',NULL,'p','j','l','q','n','p','i','z','p',6,9,'u',8,9,'s','u','j');
INSERT INTO `A` VALUES (NULL,'l','n','i','s','s','n','o','p','j','n','z','h',7,8,NULL,5,4,'u','p',NULL);
INSERT INTO `A` VALUES (7,'k','a',NULL,NULL,'r','p','n','g',NULL,'q','a','w',8,8,'z',2,8,'d','d','v');
INSERT INTO `A` VALUES (4,'x','h','w','g','w',NULL,'c','y','y','l','n','m',9,2,'a',7,4,'n','u','f');
INSERT INTO `A` VALUES (6,'u','k','c','v','s',NULL,'k','b','p','i','b','n',10,8,'i',8,4,'n','e','v');
INSERT INTO `A` VALUES (6,'e','p','h','h','f','b','t','j','p','k','u','z',11,0,'n',5,5,'w','z','t');
INSERT INTO `A` VALUES (5,'j','k','c','k','q','o','s',NULL,NULL,'x','h','c',12,5,NULL,9,3,'x','v',NULL);
INSERT INTO `A` VALUES (4,'y','e','l','c','u',NULL,'y','b',NULL,'x','j','d',13,NULL,'y',8,0,'v','m','y');
INSERT INTO `A` VALUES (7,'n','q','u',NULL,'f','u','z','e',NULL,'q','w','t',14,NULL,'z',7,5,'y','y',NULL);
INSERT INTO `A` VALUES (6,'z','o','v','l','o','q','u','o','c','o','d','y',15,4,'w',2,3,'t','r','b');
INSERT INTO `A` VALUES (7,'b','c',NULL,NULL,'j','b','m','a','u','r',NULL,'u',16,NULL,'h',1,7,'q','j',NULL);
INSERT INTO `A` VALUES (8,'k','e','f','b','c',NULL,'f',NULL,'h','x',NULL,'m',17,8,'w',0,7,'a','s',NULL);
INSERT INTO `A` VALUES (9,'r','i','q','p','e','l','q',NULL,'e','k','c','r',18,NULL,NULL,7,3,'h','k','b');
INSERT INTO `A` VALUES (3,'n','p','t','e','a','g','e','p','s','s','s','v',19,NULL,'u',2,3,'f','k','h');
INSERT INTO `A` VALUES (2,'k','z','r','s','g',NULL,'q','k','l','h',NULL,'m',20,7,'y',8,9,'p','g','c');
INSERT INTO `A` VALUES (NULL,'u','q',NULL,NULL,'j','h','p','t','o','s','u','b',21,8,'m',6,9,'y','q','b');
INSERT INTO `A` VALUES (3,'n','k',NULL,'a','o','i','e','h','k','w','d','r',22,NULL,'p',7,7,'c','c',NULL);
INSERT INTO `A` VALUES (3,'i','t','z','f','d','n','s','d','u','o',NULL,'q',23,NULL,'u',0,5,'s','d','f');
INSERT INTO `A` VALUES (8,'r','a','y','w','w','t','l','a','x','a','g','p',24,0,'x',7,5,'z','o','l');
INSERT INTO `A` VALUES (0,'c','i',NULL,'r','s','j','j','k',NULL,'n','i','d',25,7,'a',1,8,'v','y','p');
INSERT INTO `A` VALUES (7,'c','m','d',NULL,'s','r','v','k','h','t','s','g',26,1,'t',2,0,'z','f','e');
INSERT INTO `A` VALUES (4,'m','o',NULL,'v','i','b','d','j','u','s','g','s',27,3,'x',9,8,'n','b','g');
INSERT INTO `A` VALUES (2,'x','u','y','k','h','e','z','m',NULL,'d','w','f',28,5,'c',0,5,'t','n','k');
INSERT INTO `A` VALUES (1,'r','h','b','k','a','x','q','f','m','d','s','h',29,3,'r',1,0,'u','i','n');
INSERT INTO `A` VALUES (1,'i','k','k','r','x','d','g','u',NULL,'u','w','l',30,9,'b',4,4,'m','t','l');
INSERT INTO `A` VALUES (2,'q','j','b','o','t','t','e','e','w','x','d','p',31,9,'o',0,2,'z','d','o');
INSERT INTO `A` VALUES (7,'h','x','j','o','x','y','r','n','h','m','k','v',32,5,'i',2,3,'u','k','t');
INSERT INTO `A` VALUES (NULL,'x','z','i','i','u','j','d','a','u','u',NULL,'y',33,4,'o',1,2,'t','p',NULL);
INSERT INTO `A` VALUES (3,'b','u','p','m','h','u','u','y','s','u','g','m',34,8,'d',2,0,'p','k','l');
INSERT INTO `A` VALUES (NULL,'z','p',NULL,'h','y','r','b','e','k','l','f','l',35,5,'b',1,2,'s','o','n');
INSERT INTO `A` VALUES (3,'m','v','e','y','h','j','a','v',NULL,'d','a','k',36,7,'j',3,4,'m','j','q');
INSERT INTO `A` VALUES (3,'l','p','r','o','c',NULL,'k','s','o','w','p','b',37,4,'c',7,1,'u','h','x');
INSERT INTO `A` VALUES (NULL,'l','m','d','v','q','u','v','m','o','y',NULL,'q',38,2,'v',5,1,'s','h',NULL);
INSERT INTO `A` VALUES (NULL,'i','v','k','t','n','l','d','w',NULL,'s','g','m',39,1,'y',3,6,'e','e','y');
INSERT INTO `A` VALUES (0,'z','v','h','h','x','c','g','s','a','p','k','y',40,NULL,'v',7,7,'u','p','u');
INSERT INTO `A` VALUES (1,'d','x','r','c','o','c','g','w','a','s','f','u',41,4,NULL,0,8,'x','p','p');
INSERT INTO `A` VALUES (2,'f','s','p','r','t','d','c',NULL,NULL,'x','p','s',42,4,'w',5,2,'e','t',NULL);
INSERT INTO `A` VALUES (NULL,'q','o','b','l','h','y','l','k','v','a','o','s',43,NULL,'x',9,3,'l','b',NULL);
INSERT INTO `A` VALUES (NULL,'g','v','g','c','u','o','z','l','w','l','q','u',44,5,'d',8,5,'o','u','z');
INSERT INTO `A` VALUES (5,'e','w','s','i','q','k','r','f','c','a','d','i',45,1,'o',6,4,'k','e','j');
INSERT INTO `A` VALUES (5,'d','r','l','j','v',NULL,'v','j',NULL,'u',NULL,'z',46,7,'a',8,0,'w','m','k');
INSERT INTO `A` VALUES (2,'d','j',NULL,NULL,'f','w','t','x',NULL,'s','b','e',47,4,'u',3,8,'c','b','l');
INSERT INTO `A` VALUES (2,'g','r',NULL,'y','g',NULL,'x','d',NULL,'r','u','r',48,1,'s',9,1,'i','p','d');
INSERT INTO `A` VALUES (2,'v','i',NULL,'y','w','l','d','x','j','h',NULL,'a',49,4,'b',8,9,'w','y','y');
INSERT INTO `A` VALUES (NULL,'t','x',NULL,'y','y','x','z','h','a','f','v','b',50,NULL,'g',1,8,'p','x','a');
INSERT INTO `A` VALUES (NULL,'r','d','m','u','r',NULL,'r','d','h','x','t','a',51,4,'v',5,4,'j','l','s');
INSERT INTO `A` VALUES (6,'k','o','p','k','f',NULL,'a','p','b','f','a','w',52,7,'f',0,7,'t','w','s');
INSERT INTO `A` VALUES (NULL,'c','f','r','n','x','y','k','m','y','h','u','j',53,NULL,'w',1,7,'m','c','c');
INSERT INTO `A` VALUES (3,'j','m','g',NULL,'p','p','h','c','k','u',NULL,'h',54,7,'q',6,2,'y','g','w');
INSERT INTO `A` VALUES (8,'a','g','l',NULL,'z','v','o','x','x','m','b','b',55,6,'k',2,6,'d','h','e');
INSERT INTO `A` VALUES (1,'v','a','k','o','s',NULL,'b','r','q','o','q','s',56,NULL,'l',9,4,'s','z',NULL);
INSERT INTO `A` VALUES (0,'i','e',NULL,'i','c',NULL,'f','h','j','m','s','n',57,3,'p',7,6,'w','y',NULL);
INSERT INTO `A` VALUES (1,'t','h','b','v','g',NULL,'i',NULL,'n','l','f','d',58,3,NULL,3,4,'i','c','l');
INSERT INTO `A` VALUES (5,'o','i','y','k','h',NULL,'k','c','v','h','e','z',59,8,NULL,5,9,'w','u','p');
INSERT INTO `A` VALUES (0,'f','g','c','u','j','o','u','w',NULL,'r','f','c',60,6,NULL,7,5,'k','q','g');
INSERT INTO `A` VALUES (7,'w','o','x','k','s','g','z',NULL,'b','i','q','a',61,6,'f',8,6,'p','x','c');
INSERT INTO `A` VALUES (NULL,'r','j',NULL,NULL,'v','f','v','o',NULL,'j','k','k',62,4,'p',2,2,'u','g','o');
INSERT INTO `A` VALUES (6,'l','p','g',NULL,'x','y','l','d','g','t',NULL,'h',63,1,'w',9,9,'i','w','b');
INSERT INTO `A` VALUES (6,'d','o','m','w','t','x','i',NULL,NULL,'l','k','v',64,0,'l',5,2,'u','k',NULL);
INSERT INTO `A` VALUES (1,'p','m','k','j','s','i','u','e','y','w','d','f',65,0,'d',4,7,'u','w','k');
INSERT INTO `A` VALUES (0,'q','l','o','i','z','g','q',NULL,NULL,'x','f','y',66,0,'x',2,5,'y','x','z');
INSERT INTO `A` VALUES (2,'k','r','k','i','z','v','w','m','v','b','w','p',67,9,'g',1,2,'h','b','k');
INSERT INTO `A` VALUES (6,'b','n',NULL,'c','a','m','v','t',NULL,'n','p','f',68,0,'p',9,0,'x','c','n');
INSERT INTO `A` VALUES (8,'d','j','b','s','s',NULL,'d','p',NULL,'a','c','t',69,5,NULL,2,5,'z','e','d');
INSERT INTO `A` VALUES (4,'k','o',NULL,NULL,'j',NULL,'f','x','j','j','c','n',70,NULL,NULL,3,1,'a','x','t');
INSERT INTO `A` VALUES (1,'c','r','g',NULL,'t','k','d',NULL,'c','b','l','l',71,NULL,'k',3,1,'z','g','m');
INSERT INTO `A` VALUES (3,'x','z','y','o','j','k','h','k','k','e','u','m',72,8,'o',2,5,'o','o','j');
INSERT INTO `A` VALUES (NULL,'k','e',NULL,'m','h','x','z','d','c','d','k','u',73,2,NULL,9,6,'x','t',NULL);
INSERT INTO `A` VALUES (NULL,'h','c','z','j','p','q','t','t','w','p','k','f',74,1,'d',5,0,'l','v','c');
INSERT INTO `A` VALUES (5,'a','w','n','n','b','i','c','o','a','a',NULL,'l',75,NULL,'o',7,3,'t','q','s');
INSERT INTO `A` VALUES (5,'i','p','c','s','k','c','a','h',NULL,'g','q','g',76,4,'a',3,2,'e','e','j');
INSERT INTO `A` VALUES (NULL,'y','g',NULL,'m','z',NULL,'z','e','g','j','c','h',77,2,'b',1,5,'u','a',NULL);
INSERT INTO `A` VALUES (9,'d','y','k',NULL,'v','r','a','y',NULL,'t','l','a',78,NULL,'p',0,5,'x','z','f');
INSERT INTO `A` VALUES (3,'v','o','b',NULL,'h','a','b','f','g','n','o','i',79,3,'y',8,4,'c','z','f');
INSERT INTO `A` VALUES (NULL,'p','j','y','p','n',NULL,'k','f','j','g','a','r',80,6,'i',7,8,'n','l','i');
INSERT INTO `A` VALUES (1,'j','n','h','x','k','w','s','n','m','r','v','a',81,2,'k',7,8,'i','s','o');
INSERT INTO `A` VALUES (3,'a','h','w','r','j',NULL,'a',NULL,'j','c',NULL,'y',82,1,NULL,7,1,'p','l','c');
INSERT INTO `A` VALUES (9,'y','d',NULL,'s','f','j','t',NULL,'a','h','h','y',83,9,'v',7,2,'p','k','c');
INSERT INTO `A` VALUES (7,'i','d','r','x','f','q','n',NULL,'x','o','n','e',84,3,NULL,1,5,'k','i','e');
INSERT INTO `A` VALUES (3,'x','y','t','r','q','w','b','q','u','j','z','p',85,NULL,'f',6,1,'w','z','s');
INSERT INTO `A` VALUES (5,'p','o',NULL,NULL,'d','p','p',NULL,'m','c','w','y',86,8,'y',3,4,'u','x','y');
INSERT INTO `A` VALUES (6,'b','a','t',NULL,'i','d','h','w',NULL,'i',NULL,'o',87,NULL,'k',3,4,'g','p','p');
INSERT INTO `A` VALUES (6,'e','d','i','b','y','d','c','u','b','v','j','y',88,1,'v',8,1,'o','n','x');
INSERT INTO `A` VALUES (NULL,'h','h','t','v','x','c','a','z','g','t','o','e',89,NULL,NULL,0,0,'l','s','k');
INSERT INTO `A` VALUES (7,'q','z','l','h','p','x','r','e','p','d','x','l',90,4,'p',2,3,'h','i','y');
INSERT INTO `A` VALUES (4,'u','p','v','k','y','l','n',NULL,'p','l','e','m',91,6,'s',5,6,'e','v','y');
INSERT INTO `A` VALUES (3,'b','n','d','r','s','u','g','f','b','e',NULL,'p',92,3,NULL,9,3,'p','z','v');
INSERT INTO `A` VALUES (NULL,'g','z','h','y','c','q','f','k','f','v','u','p',93,5,'i',4,2,'c','p','t');
INSERT INTO `A` VALUES (5,'j','w','l',NULL,'c','g','x','t','z','q','u','w',94,4,'q',2,2,'j','j','f');
INSERT INTO `A` VALUES (5,'i','t','q','a','v','x','e','m','z','r','i','y',95,3,'i',5,0,'q','z','c');
INSERT INTO `A` VALUES (5,'f','c','a','i','r','z','i','w','m','f','k','n',96,NULL,'u',9,7,'d','y','b');
INSERT INTO `A` VALUES (NULL,'b','l','k','w','n','s','x','z','l','q','k','t',97,1,'m',6,2,'u','r','o');
INSERT INTO `A` VALUES (9,'o','n',NULL,'v','a','b','z','o',NULL,'b',NULL,'p',98,NULL,'x',1,1,'p','v','b');
INSERT INTO `A` VALUES (7,'s','v',NULL,NULL,'b',NULL,'m','p',NULL,'d','v','t',99,1,'r',3,8,'z','j','l');
INSERT INTO `A` VALUES (8,'z','k','g','l','h','e','k','z','p','j',NULL,'c',100,6,'o',9,1,'v','m','m');

CREATE TABLE `Q` (
  `col_varchar_1024_latin1_key` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_10_utf8_not_null` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_1024_utf8_not_null` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_1024_latin1_not_null` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col_int` int(11) DEFAULT NULL,
  `col_varchar_10_latin1` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_1024_utf8_not_null_key` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_10_utf8` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `col_int_not_null_key` int(11) NOT NULL,
  `col_varchar_10_latin1_not_null` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_varchar_10_latin1_not_null_key` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `col_int_key` int(11) DEFAULT NULL,
  `col_varchar_10_utf8_not_null_key` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `col_int_not_null` int(11) NOT NULL,
  `col_varchar_1024_latin1_not_null_key` varchar(1024) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `col_varchar_1024_latin1_key` (`col_varchar_1024_latin1_key`(1000)),
  KEY `col_varchar_1024_utf8_not_null_key` (`col_varchar_1024_utf8_not_null_key`(333)),
  KEY `col_varchar_1024_utf8_key` (`col_varchar_1024_utf8_key`(333)),
  KEY `col_varchar_10_latin1_key` (`col_varchar_10_latin1_key`),
  KEY `col_int_not_null_key` (`col_int_not_null_key`),
  KEY `col_varchar_10_latin1_not_null_key` (`col_varchar_10_latin1_not_null_key`),
  KEY `col_int_key` (`col_int_key`),
  KEY `col_varchar_10_utf8_not_null_key` (`col_varchar_10_utf8_not_null_key`),
  KEY `col_varchar_10_utf8_key` (`col_varchar_10_utf8_key`),
  KEY `col_varchar_1024_latin1_not_null_key` (`col_varchar_1024_latin1_not_null_key`(1000))
) ENGINE=MyISAM AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `Q` VALUES ('d','r','x','b',2,'h','l','i','c','q',NULL,6,'n','l','s',1,7,'m','j',9,'b');
INSERT INTO `Q` VALUES ('e','f','a','c',NULL,NULL,'l',NULL,NULL,'g','a',3,'k','g','o',2,6,'q',NULL,8,'a');
INSERT INTO `Q` VALUES ('t','w','d','y',4,'r','s',NULL,'k','j',NULL,9,'q','b','b',3,4,'j','n',8,'o');
INSERT INTO `Q` VALUES ('g','g','f','m',0,NULL,'t','z','b',NULL,NULL,7,'m','p','l',4,6,'l',NULL,3,'h');
INSERT INTO `Q` VALUES ('j','q','p','q',3,NULL,'c',NULL,'g','t','m',1,'z','t','h',5,0,'c',NULL,9,'c');
INSERT INTO `Q` VALUES ('q','l','i','j',0,'z','c',NULL,'s','q','a',8,'m','i','s',6,0,'y',NULL,5,'t');
INSERT INTO `Q` VALUES ('f','t','g','l',NULL,'w','l','e','u','t','b',1,'q','i','u',7,NULL,'k','g',0,'f');
INSERT INTO `Q` VALUES ('y','d','v','c',7,'v','m','e','c',NULL,'g',2,'f','t','l',8,1,'h','k',5,'q');
INSERT INTO `Q` VALUES (NULL,'a','u','l',5,'q','h','h','q','i',NULL,6,'w','p','q',9,NULL,'v','l',4,'y');
INSERT INTO `Q` VALUES ('y','n','k','c',NULL,NULL,'n','y','g','u','c',5,'j','g','f',10,1,'s','c',8,'i');
INSERT INTO `Q` VALUES ('y','o','p','q',NULL,NULL,'f','c','m',NULL,'e',2,'j','g','t',11,3,'a',NULL,8,'a');
INSERT INTO `Q` VALUES ('i','g','i','t',2,'s','b','m','r','d','h',4,'e',NULL,'c',12,6,'z','b',6,'k');
INSERT INTO `Q` VALUES (NULL,'e','j','u',6,NULL,'r',NULL,'j',NULL,'g',7,'h',NULL,'a',13,6,'w','b',8,'e');
INSERT INTO `Q` VALUES ('i','p','n','j',2,'c','v','m',NULL,'p','t',4,'s',NULL,'c',14,2,'u',NULL,9,'l');
INSERT INTO `Q` VALUES (NULL,'d','z','j',3,'x','z','w','s',NULL,'v',8,'l','t','i',15,6,'f','j',9,'z');
INSERT INTO `Q` VALUES ('p','z','d','e',3,'h','m','w','j','v','p',9,'s','p','x',16,8,'i','x',4,'b');
INSERT INTO `Q` VALUES (NULL,'d','k','q',4,'j','x',NULL,'j','c','p',1,'r','s','h',17,NULL,'r','b',9,'u');
INSERT INTO `Q` VALUES ('k','n','q','g',6,'d','p','b','g','x','q',8,'b',NULL,'k',18,1,'x','z',9,'k');
INSERT INTO `Q` VALUES ('i','u','a','s',0,NULL,'i','d',NULL,'m',NULL,7,'j','a','p',19,NULL,'q','k',9,'q');
INSERT INTO `Q` VALUES ('s','s','p','f',2,'j','d','p','x','r','b',0,'y','g','u',20,NULL,'f','y',3,'l');
INSERT INTO `Q` VALUES (NULL,'n','h','y',8,'z','k','x','a','y','h',9,'f','w','i',21,7,'r','a',6,'v');
INSERT INTO `Q` VALUES ('h','q','p','r',NULL,NULL,'i',NULL,NULL,'v','w',4,'b','l','s',22,4,'m','f',5,'u');
INSERT INTO `Q` VALUES ('i','n','u','d',NULL,'x','c','z','p','r','k',2,'c','b','h',23,7,'q','h',9,'p');
INSERT INTO `Q` VALUES ('x','x','u','p',0,'s','n',NULL,'t','y','k',2,'n','x','l',24,3,'a','l',4,'k');
INSERT INTO `Q` VALUES ('x','q','g','j',7,NULL,'p','r','q','t','x',6,'f','c','d',25,NULL,'k','m',6,'u');
INSERT INTO `Q` VALUES (NULL,'d','t','o',5,NULL,'p','k','r','u','k',0,'p','j','f',26,7,'m','v',8,'z');
INSERT INTO `Q` VALUES ('u','f','i','u',7,'z','z','a','k','u','r',2,'v','e','m',27,NULL,'u','y',8,'v');
INSERT INTO `Q` VALUES (NULL,'c','e','s',NULL,'n','p',NULL,'d','z','l',0,'w','i','v',28,5,'c','y',3,'m');
INSERT INTO `Q` VALUES ('u','r','f','m',NULL,'f','t','x','r','k',NULL,7,'v','i','g',29,6,'o','a',7,'u');
INSERT INTO `Q` VALUES ('n','z','w','k',1,'i','l','r','r','k','j',4,'l','i','z',30,4,'b','a',0,'c');
INSERT INTO `Q` VALUES ('w','b','d','o',1,'f','m','w','l','f','f',8,'b','m','j',31,9,'c',NULL,4,'w');
INSERT INTO `Q` VALUES ('a','m','o','s',NULL,'p','a','x',NULL,'v','r',5,'m','r','a',32,8,'w','y',9,'q');
INSERT INTO `Q` VALUES ('o','v','v','d',1,'z','s','k','l','s','h',3,'u','o','w',33,NULL,'x','f',0,'j');
INSERT INTO `Q` VALUES ('x','v','y','r',4,'u','g','r','j','k','l',3,'x','x','u',34,NULL,'f','m',1,'e');
INSERT INTO `Q` VALUES ('f','y','f','a',1,NULL,'w','w',NULL,'e','t',4,'i',NULL,'b',35,NULL,'f','p',0,'n');
INSERT INTO `Q` VALUES (NULL,'x','q','y',6,'o','y','q','s','u','y',6,'g','p','c',36,5,'b','z',8,'n');
INSERT INTO `Q` VALUES ('o','c','x','g',2,'a','p','s','t','d','a',6,'y','p','y',37,2,'z','n',8,'m');
INSERT INTO `Q` VALUES ('g','p','n','q',2,'p','b','k',NULL,'u',NULL,5,'o','g','h',38,8,'l',NULL,1,'y');
INSERT INTO `Q` VALUES ('m','y','o','l',4,'e','i','n','z',NULL,'r',1,'r',NULL,'x',39,0,'e',NULL,7,'q');
INSERT INTO `Q` VALUES (NULL,'h','m','e',8,'n','l',NULL,'j','q',NULL,2,'w',NULL,'t',40,2,'f','w',2,'d');
INSERT INTO `Q` VALUES ('u','q','p','c',8,NULL,'l','w',NULL,'t','o',2,'v',NULL,'s',41,9,'n',NULL,6,'d');
INSERT INTO `Q` VALUES ('v','q','b','f',6,NULL,'c',NULL,'p',NULL,'i',5,'v','y','u',42,9,'x','l',7,'b');
INSERT INTO `Q` VALUES ('f','d','h','o',8,'g','j','x','h','g','s',7,'l','o','o',43,NULL,'d','n',2,'s');
INSERT INTO `Q` VALUES ('j','u','h','j',1,'k','q','b','y',NULL,'z',5,'i','o','t',44,8,'h','h',7,'d');
INSERT INTO `Q` VALUES ('l','u','j','v',1,'x','s','q','z','y',NULL,3,'x','e','p',45,NULL,'u','b',1,'e');
INSERT INTO `Q` VALUES (NULL,'y','s','x',NULL,'o','a','e','z','c',NULL,3,'g','y','e',46,9,'q','o',9,'i');
INSERT INTO `Q` VALUES (NULL,'x','l','r',6,'x','i','g','z','r','b',0,'m','c','e',47,2,'s',NULL,7,'p');
INSERT INTO `Q` VALUES (NULL,'w','a','e',NULL,'o','r','m','k',NULL,'h',5,'j','h','l',48,7,'u','q',7,'h');
INSERT INTO `Q` VALUES ('t','b','s','t',7,'e','g','f','k','m','p',4,'o','i','z',49,8,'o',NULL,1,'g');
INSERT INTO `Q` VALUES (NULL,'d','b','y',0,'y','b','k',NULL,'y','s',3,'f','o','q',50,1,'b','m',2,'f');
INSERT INTO `Q` VALUES ('e','g','l','y',1,'o','u',NULL,'s',NULL,'l',4,'l',NULL,'y',51,NULL,'s',NULL,5,'h');
INSERT INTO `Q` VALUES ('j','v','i','i',0,'b','z',NULL,'p','y','q',1,'s','s','z',52,NULL,'t','b',2,'z');
INSERT INTO `Q` VALUES ('e','l','u','a',5,'l','g','z','t',NULL,'k',6,'c',NULL,'f',53,6,'w','v',9,'l');
INSERT INTO `Q` VALUES ('h','i','z','v',NULL,'m','n','n','p','k','d',6,'c','j','g',54,4,'p','t',7,'u');
INSERT INTO `Q` VALUES (NULL,'w','h','e',4,'p','t','r','p',NULL,'a',0,'e','j','a',55,9,'d','r',9,'i');
INSERT INTO `Q` VALUES ('a','t','f','f',3,'j','p',NULL,NULL,'z','w',3,'e','v','p',56,NULL,'r',NULL,2,'h');
INSERT INTO `Q` VALUES ('i','o','c','a',1,'w','o','f','u','m','j',0,'v','f','m',57,6,'d','o',1,'o');
INSERT INTO `Q` VALUES ('d','g','f','z',6,'q','g','x','t',NULL,'i',5,'c','x','n',58,3,'s','x',4,'b');
INSERT INTO `Q` VALUES ('s','l','b','y',6,'x','z','e','g','a','l',1,'x','f','s',59,3,'o','u',4,'y');
INSERT INTO `Q` VALUES (NULL,'m','m','t',6,'w','v','x','x',NULL,'p',9,'g','u','m',60,5,'v','c',6,'o');
INSERT INTO `Q` VALUES (NULL,'p','z','f',0,'c','c','k','h',NULL,'d',3,'y','m','e',61,1,'u','m',0,'j');
INSERT INTO `Q` VALUES (NULL,'p','p','g',4,'k','l','r','t','c','p',9,'h',NULL,'x',62,9,'z',NULL,3,'o');
INSERT INTO `Q` VALUES ('l','d','m','v',NULL,'v','k','b','h',NULL,'g',6,'p',NULL,'a',63,9,'t','g',9,'m');
INSERT INTO `Q` VALUES ('a','i','j','a',1,'l','h','p',NULL,'m','m',0,'z','q','e',64,3,'w','o',8,'a');
INSERT INTO `Q` VALUES ('z','u','c','p',9,NULL,'c','d','g','v','h',5,'w','m','r',65,NULL,'e','r',2,'x');
INSERT INTO `Q` VALUES ('a','j','a','a',NULL,'o','q','z','s',NULL,NULL,8,'c','d','z',66,3,'k','v',4,'k');
INSERT INTO `Q` VALUES ('e','i','u','m',7,'n','r',NULL,'f',NULL,'q',9,'m',NULL,'m',67,1,'a','g',9,'l');
INSERT INTO `Q` VALUES ('w','y','a','n',NULL,'d','v',NULL,'t','t','j',2,'u','l','b',68,0,'z','t',0,'t');
INSERT INTO `Q` VALUES ('p','s','v','z',3,'f','u','d','b','h','m',4,'c',NULL,'f',69,8,'c','l',8,'j');
INSERT INTO `Q` VALUES ('a','k','i','n',1,'l','i','n','l','p','t',7,'h','s','g',70,7,'q',NULL,6,'d');
INSERT INTO `Q` VALUES (NULL,'t','q','r',6,'c','n','r','v','z','z',5,'x','e','j',71,1,'s','l',3,'h');
INSERT INTO `Q` VALUES (NULL,'p','x','h',2,'u','r','l','m','q',NULL,1,'v',NULL,'m',72,9,'o','q',3,'o');
INSERT INTO `Q` VALUES ('z','z','z','t',NULL,NULL,'p','t','n','z','g',2,'y','i','b',73,1,'r','c',5,'w');
INSERT INTO `Q` VALUES ('f','o','x','p',3,'v','b','s','b','n','g',4,'u','n','n',74,0,'u','j',4,'u');
INSERT INTO `Q` VALUES ('j','z','x','k',0,'k','k','g','q',NULL,'j',8,'e','w','j',75,5,'s','q',3,'b');
INSERT INTO `Q` VALUES ('i','e','f','o',6,'t','o','s','p','c','j',7,'z','x','u',76,3,'o',NULL,1,'k');
INSERT INTO `Q` VALUES ('l','s','j','o',8,'i','q','v','y','j','f',0,'t','m','e',77,6,'a','t',2,'l');
INSERT INTO `Q` VALUES (NULL,'q','s','r',3,'w','s','v','r',NULL,'j',8,'i','z','k',78,2,'r','x',9,'r');
INSERT INTO `Q` VALUES ('i','i','z','j',3,'u','s','k','b','m','o',1,'d',NULL,'x',79,4,'r','o',8,'v');
INSERT INTO `Q` VALUES ('u','b','g','t',9,'z','e','z','k','h','j',9,'g','k','f',80,3,'f','u',4,'s');
INSERT INTO `Q` VALUES ('w','f','d','p',1,'v','t',NULL,'e','f','x',6,'p',NULL,'v',81,NULL,'e','y',0,'n');
INSERT INTO `Q` VALUES (NULL,'u','x','z',6,NULL,'q','l',NULL,'m',NULL,2,'j','y','z',82,6,'b','w',8,'w');
INSERT INTO `Q` VALUES ('w','r','q','h',0,'k','n','a',NULL,'a','k',4,'l','x','f',83,6,'r','e',1,'s');
INSERT INTO `Q` VALUES ('d','p','i','x',NULL,'j','r','v','s','o',NULL,6,'b','p','v',84,0,'l','w',1,'n');
INSERT INTO `Q` VALUES (NULL,'w','v','d',1,NULL,'i','o','a','x','p',4,'d','i','c',85,NULL,'z','n',9,'j');
INSERT INTO `Q` VALUES ('w','n','r','i',7,'e','a',NULL,'f','m','z',0,'b','w','t',86,NULL,'f','b',8,'n');
INSERT INTO `Q` VALUES ('a','d','g','a',9,NULL,'d',NULL,NULL,NULL,'w',6,'e','u','o',87,0,'t','a',0,'m');
INSERT INTO `Q` VALUES (NULL,'e','y','d',4,'e','n','t','h','x','q',3,'f','s','f',88,2,'w','h',7,'i');
INSERT INTO `Q` VALUES ('u','u','w','f',7,NULL,'f','a','g','a','z',0,'t',NULL,'x',89,8,'t','p',4,'k');
INSERT INTO `Q` VALUES ('q','c','x','h',6,NULL,'i',NULL,'x','n','r',2,'b','f','t',90,2,'h','y',5,'x');
INSERT INTO `Q` VALUES (NULL,'b','r','q',3,'d','z',NULL,'m','w','l',9,'c','r','x',91,NULL,'f','u',3,'g');
INSERT INTO `Q` VALUES ('x','j','h','a',8,'t','f','i','y','r','e',2,'p','i','q',92,NULL,'w','q',6,'q');
INSERT INTO `Q` VALUES ('t','b','u','c',7,'q','t',NULL,'k','v','u',5,'g',NULL,'g',93,1,'o','s',1,'c');
INSERT INTO `Q` VALUES ('u','j','f','l',NULL,'t','w','p','b',NULL,'n',8,'l','k','d',94,NULL,'p',NULL,3,'x');
INSERT INTO `Q` VALUES ('v','k','r','m',2,NULL,'u','l','k','j',NULL,9,'s','j','h',95,NULL,'x',NULL,4,'s');
INSERT INTO `Q` VALUES ('o','d','k','g',7,NULL,'t','y','l','u','z',2,'q',NULL,'o',96,3,'j',NULL,5,'v');
INSERT INTO `Q` VALUES (NULL,'u','a','t',5,'x','s','d','s','f','y',2,'w',NULL,'a',97,NULL,'j','z',7,'u');
INSERT INTO `Q` VALUES ('c','q','g','y',0,'w','z','v',NULL,NULL,'f',8,'b','c','s',98,9,'q','i',5,'q');
INSERT INTO `Q` VALUES ('h','y','d','t',5,NULL,'p','h',NULL,'v','b',2,'p','i','p',99,6,'t','a',1,'u');
INSERT INTO `Q` VALUES (NULL,'l','l','p',7,'i','i',NULL,NULL,'u','m',3,'o','s','x',100,7,'a','z',0,'g');

 SELECT    SUM(  table2 . `col_int` ) AS field1 FROM  A AS table1  RIGHT OUTER JOIN Q AS table2 ON  table1 . `col_varchar_10_latin1_key` =  table2 . `col_varchar_1024_latin1_key`  WHERE ( ( ( table2 . `col_int_key`  BETWEEN 2 AND ( 2 + 6 ) AND table1 . `col_int_key` >= table2 . `pk` ) AND table1 . `col_int_key` IS  NULL ) OR table1 . `pk` > table2 . `col_int` )  ;

2. What did you expect to see?

+--------+
| field1 |
+--------+
|    650 |
+--------+

3. What did you see instead?

+--------+
| field1 |
+--------+
|    582 |
+--------+

+----------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | operator info                                                                                                                                                                                                                                                                                                                                          |
+----------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_10                       | 1.00    | root      | funcs:sum(Column#44)->Column#43                                                                                                                                                                                                                                                                                                                        |
| └─Projection_89                  | 124.88  | root      | cast(randgen_test.q.col_int, decimal(65,0) BINARY)->Column#44                                                                                                                                                                                                                                                                                          |
|   └─HashLeftJoin_40              | 124.88  | root      | inner join, equal:[eq(randgen_test.a.col_varchar_10_latin1_key, randgen_test.q.col_varchar_1024_latin1_key)], other cond:or(and(and(ge(randgen_test.q.col_int_key, 2), le(randgen_test.q.col_int_key, 8)), and(ge(randgen_test.a.col_int_key, randgen_test.q.pk), isnull(randgen_test.a.col_int_key))), gt(randgen_test.a.pk, randgen_test.q.col_int)) |
|     ├─TableReader_56(Build)      | 99.90   | root      | data:Selection_55                                                                                                                                                                                                                                                                                                                                      |
|     │ └─Selection_55             | 99.90   | cop[tikv] | not(isnull(randgen_test.q.col_varchar_1024_latin1_key))                                                                                                                                                                                                                                                                                                |
|     │   └─TableFullScan_54       | 100.00  | cop[tikv] | table:table2, keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                           |
|     └─TableReader_50(Probe)      | 99.90   | root      | data:Selection_49                                                                                                                                                                                                                                                                                                                                      |
|       └─Selection_49             | 99.90   | cop[tikv] | not(isnull(randgen_test.a.col_varchar_10_latin1_key))                                                                                                                                                                                                                                                                                                  |
|         └─TableFullScan_48       | 100.00  | cop[tikv] | table:table1, keep order:false, stats:pseudo                                                                                                                                                                                                                                                                                                           |
+----------------------------------+---------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.7.25-TiDB-v4.0.0-beta-379-ga999ef698-dirty |
+----------------------------------------------+