Bug #90847 Query returns wrong data if order by is present
Submitted: 12 May 16:57 Modified: 14 May 5:12
Reporter: vincenzo antolini Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.7.22, 8.0.11 OS:Microsoft Windows (W10 64bit)
Assigned to: CPU Architecture:x86

[12 May 16:57] vincenzo antolini
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;
[14 May 5:12] Umesh Shastry
Hello Vincenzo,

Thank you for the report.

Thanks,
Umesh
[18 Aug 11:52] Alex Verkuijl
Confirmed on 5.7.21 Windows 7 x64.

Changing type from BIT to INT seems to give correct results, adding extra column with type int gives correct result but flag column keeps giving wrong value.

SELECT a.*,ae.*,acl.*
FROM bug_1 AS a 
JOIN bug_2 AS ae ON a.id=ae.id_fk
JOIN bug_3 AS acl ON a.id=acl.id_fk
ORDER BY a.id ;

Wrong result, flag column with type BIT and order by:

id	id_fk	id_fk2	body	id	id_fk	flag	test
98	98	0		1	98	1	198
98	98	0		2	98	1	298
99	99	0		3	99	1	399

Correct result, flag column type TINYINT:

id	id_fk	id_fk2	body	id	id_fk	flag	test
98	98	0		1	98	0	198
98	98	0		2	98	1	298
99	99	0		3	99	1	399