Bug #80805 '0' (zero) is returned instead of 'NULL' after LEFT JOIN with NEGATIVE operand

Submitted: 21 Mar 2016 11:36 Modified: 4 May 2016 19:42
Reporter: Michal Vrabel Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[21 Mar 2016 11:36] Michal Vrabel
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'
[21 Mar 2016 13:32] MySQL Verification Team
Hello Micha,

Thank you for the report and test case.

Thanks,
Umesh
[4 May 2016 19:38] Erlend Dahl
Fixed in 5.7.13 as a duplicate of

Bug#80526 LEFT OUTER JOIN returns incorrect results on the outer side

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.