Description:
I find incorrect result when executing the following query.
SELECT ae.body, acl.flag
FROM bug_1 AS a
inner JOIN bug_2 AS ae ON a.id=ae.id_fk AND ae.id_fk2 = 0
INNER JOIN bug_3 AS acl ON a.id=acl.id_fk
ORDER BY a.id ASC;
Instead, the following query returns correct result
SELECT ae.body, acl.flag
FROM bug_1 AS a
inner JOIN bug_2 AS ae ON a.id=ae.id_fk AND ae.id_fk2 = 0
INNER JOIN bug_3 AS acl ON a.id=acl.id_fk;
The suspicius elements are:
- ae.body (text field)
- order by a.id ASC
If I remove "ae.body" from the query the result is correct.
If I remove "order by a.id ASC" from the query the result is correct.
How to repeat:
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50722
Source Host : localhost:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 50722
File Encoding : 65001
Date: 12/05/2018 18:56:02
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for bug_1
-- ----------------------------
DROP TABLE IF EXISTS `bug_1`;
CREATE TABLE `bug_1` (
`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 27720 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bug_1
-- ----------------------------
INSERT INTO `bug_1` VALUES (98);
INSERT INTO `bug_1` VALUES (99);
-- ----------------------------
-- Table structure for bug_2
-- ----------------------------
DROP TABLE IF EXISTS `bug_2`;
CREATE TABLE `bug_2` (
`id_fk` mediumint(8) UNSIGNED NOT NULL,
`id_fk2` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
`body` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id_fk`, `id_fk2`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bug_2
-- ----------------------------
INSERT INTO `bug_2` VALUES (98, 0, '');
INSERT INTO `bug_2` VALUES (99, 0, '');
-- ----------------------------
-- Table structure for bug_3
-- ----------------------------
DROP TABLE IF EXISTS `bug_3`;
CREATE TABLE `bug_3` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`id_fk` mediumint(8) UNSIGNED NULL DEFAULT NULL,
`flag` bit(1) NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of bug_3
-- ----------------------------
INSERT INTO `bug_3` VALUES (1, 98, b'0');
INSERT INTO `bug_3` VALUES (2, 98, b'1');
INSERT INTO `bug_3` VALUES (3, 99, b'1');
SET FOREIGN_KEY_CHECKS = 1;
