Description:
Hi,
MySQL 5.7 has problem with SELECT ... LEFT JOIN with negative operand
'0' is returned instead of 'null' when row has not been found with "negative" operand (eg !=, <>, NOT IN) in ON clause. With equal (=) works fine.
How to repeat:
How to reproduce:
################# TABLES/ DATA INNODB ##############################
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `bclaim`;
CREATE TABLE `bclaim` (
`bclaim_id` int(11) NOT NULL AUTO_INCREMENT,
`fiscal_year` smallint(6) NOT NULL,
`deleted_utc` date DEFAULT NULL,
PRIMARY KEY (`bclaim_id`),
KEY `table1_id` (`fiscal_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `bclaim` (`bclaim_id`, `fiscal_year`, `deleted_utc`) VALUES
(1, 2015, NULL),
(2, 2016, NULL),
(3, 2017, NULL);
DROP TABLE IF EXISTS `bclaim_prescript`;
CREATE TABLE `bclaim_prescript` (
`bclaim_prescript_id` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(10,2) NOT NULL,
`bclaim_id` int(11) NOT NULL,
`deleted_utc` date DEFAULT NULL,
PRIMARY KEY (`bclaim_prescript_id`),
KEY `table3_id` (`amount`),
KEY `bclaim_id` (`bclaim_id`),
CONSTRAINT `bclaim_prescript_ibfk_1` FOREIGN KEY (`bclaim_id`) REFERENCES `bclaim` (`bclaim_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `bclaim_prescript` (`bclaim_prescript_id`, `amount`, `bclaim_id`, `deleted_utc`) VALUES
(2, 150.00, 1, NULL),
(3, 250.00, 1, NULL),
(4, 360.00, 2, NULL),
(5, 400.00, 2, NULL),
(6, 500.00, 3, NULL),
(7, 600.00, 3, NULL);
DROP TABLE IF EXISTS `bwallet`;
CREATE TABLE `bwallet` (
`bwallet_id` int(11) NOT NULL AUTO_INCREMENT,
`bclaim_prescript_id` int(11) NOT NULL,
`deleted_utc` date DEFAULT NULL,
PRIMARY KEY (`bwallet_id`),
KEY `bclaim_prescript_id` (`bclaim_prescript_id`),
CONSTRAINT `bwallet_ibfk_1` FOREIGN KEY (`bclaim_prescript_id`) REFERENCES `bclaim_prescript` (`bclaim_prescript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `bwallet` (`bwallet_id`, `bclaim_prescript_id`, `deleted_utc`) VALUES
(2, 2, NULL),
(3, 3, NULL),
(4, 4, NULL),
(5, 5, NULL),
(6, 6, NULL),
(7, 7, NULL);
###################### TEST QUERY ############################
###### I. CORRECT result: not equal BC rows have BC.bclaim_id 'NULL'
SELECT BW.*, BC.bclaim_id, BC.fiscal_year
FROM bwallet AS BW
LEFT JOIN bclaim_prescript AS BCP ON BCP.bclaim_prescript_id = BW.bclaim_prescript_id AND BCP.deleted_utc IS NULL
LEFT JOIN bclaim AS BC ON BC.bclaim_id = BCP.bclaim_id AND BC.deleted_utc IS NULL AND BC.fiscal_year = 2015
WHERE BW.deleted_utc IS NULL
###### II. INCORRECT result (BC.fiscal_year changed to NOT EQUAL): not equal BC rows have BC.bclaim_id '0' instead of 'NULL'
SELECT BW.*, BC.bclaim_id, BC.fiscal_year
FROM bwallet AS BW
LEFT JOIN bclaim_prescript AS BCP ON BCP.bclaim_prescript_id = BW.bclaim_prescript_id AND BCP.deleted_utc IS NULL
LEFT JOIN bclaim AS BC ON BC.bclaim_id = BCP.bclaim_id AND BC.deleted_utc IS NULL AND BC.fiscal_year != 2015
WHERE BW.deleted_utc IS NULL
Suggested fix:
MySQL should return NULL instead of '0'
