MySQL Bugs: #94723: Incorrect simple query result with func result and FROM table column in where

Bug #94723	Incorrect simple query result with func result and FROM table column in where
Submitted:	20 Mar 2019 18:32	Modified:	31 Aug 2020 12:24
Reporter:	Michal Vrabel	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.20	OS:	Any (7.6.1810/x86_64)
Assigned to:		CPU Architecture:	Any

Description:
Incorrect results for simple query with function result.
MySQL do not filter via function result if filter "O.deleted_utc IS NULL" is present.

INCORRECT RESULT (NOT USING ANY INDEX):
----------------
SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143) AND
                             OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31')
WHERE O.deleted_utc IS NULL;

and also this is incorrect:

SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143)                             
WHERE O.deleted_utc IS NULL AND
      OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31');

You can see in the result, that unit_id and function_unit_id is NOT EQUAL (so no row should be returned):
ownership_id    ownership_unit_id   unit_id   function_unit_id
5911            6143                8053      8052

CORRECT RESULT (USING INDEX PRIMARY):
----------------
SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143) AND
                             OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31')

and also this is correct:

SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143)
WHERE OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31')                            

Correct return is: NO ROW

This bug is not about value in O.deleted_utc value, but about combination of filters (and propably if query use/not use any index).

Thank you for the fix.

How to repeat:
-- SQL code for creat identical tables with values for reproduce
-- use query in bug description
-- Reproduced.

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DELIMITER ;;

DROP FUNCTION IF EXISTS `getOwnershipDefaultUnitId`;;
CREATE FUNCTION `getOwnershipDefaultUnitId`(`pOwnershipId` int, `pDate` date) RETURNS int(11)
    READS SQL DATA
    DETERMINISTIC
BEGIN
 DECLARE RETURN_UNIT_ID INT DEFAULT NULL;

 SELECT OU.unit_id INTO RETURN_UNIT_ID
 FROM ownership_unit AS OU
 WHERE OU.ownership_id = pOwnershipId AND
       (OU.datefrom IS NULL OR OU.datefrom<=pDate) AND
      (OU.dateto IS NULL OR OU.dateto>=pDate)
 ORDER BY OU.default_unit
 LIMIT 1;

 RETURN RETURN_UNIT_ID;
END;;

DELIMITER ;

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `ownership`;
CREATE TABLE `ownership` (
  `ownership_id` int(11) NOT NULL AUTO_INCREMENT,
  `datefrom` date DEFAULT NULL,
  `dateto` date DEFAULT NULL,
  `deleted_utc` datetime DEFAULT NULL,
  PRIMARY KEY (`ownership_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ownership` (`ownership_id`, `datefrom`, `dateto`, `deleted_utc`) VALUES
(5911,	NULL,	NULL,	NULL);

DROP TABLE IF EXISTS `ownership_unit`;
CREATE TABLE `ownership_unit` (
  `ownership_unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `ownership_id` int(11) NOT NULL,
  `datefrom` date DEFAULT NULL,
  `dateto` int(11) DEFAULT NULL,
  `default_unit` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_id` int(11) NOT NULL,
  PRIMARY KEY (`ownership_unit_id`),
  KEY `ownership_id` (`ownership_id`),
  CONSTRAINT `ownership_unit_ibfk_1` FOREIGN KEY (`ownership_id`) REFERENCES `ownership` (`ownership_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ownership_unit` (`ownership_unit_id`, `ownership_id`, `datefrom`, `dateto`, `default_unit`, `unit_id`) VALUES
(6140,	5911,	NULL,	NULL,	'YES',	8052),
(6143,	5911,	NULL,	NULL,	'NO',	8053);

-- 2019-03-20 18:27:55

Suggested fix:
FIX: Correct filtering

Workaround: use HAVING by "function_unit_id" in my case.

OS/CPU added.

Hi,

Thank you for your bug report.

First of all, totally unrelated with your problem, getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') should be replaced in all JOIN and WHERE clauses by function_unit_id. Also, when you have IN clause with a single value, you should replace it with a single equality clause.

When it comes not using indices, this is not a bug. One table has a single row and the other one has three rows. In 99 % of cases it is faster to use table scanning instead of the index search. Hence, this is expected behaviour. Hence, that issue of using indices is void. It is properly explained in our Reference Manual.

The only important issue here is that you think that one query returns wrong results. Can you please elaborate on that issue only in more detail ??????

Hi,

thank you for information. YES, my bug is ONLY about wrong results. Sorry for my english, I wanna only wrote what I have found about bug. Information about index was only information from explain that it is 'one different think' what I have found in query running. I don't care if index is/isn't used in this case.

Please, what do you mean "more detail"?
I have posted exactly how to reproduce error with all codes (table/data/query);
So you should be able to reproduce it yourself in a minute.
Are you able to reproduce bug?

I think, this bug is serious problem, because MySQL returns invalid result for "simple" query.

Hi,

Let me be very specific. You wrote:

SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143)
WHERE OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31')                            

Correct return is: NO ROW

If correct result is 0 rows, please just answer two questions:

1. What result is this  query returning ???

2. If it returns rows, then why do you think that it is wrong outcome to return rows ???

It is truly confusing. You are writing that correct result means that index is used and incorrect result means that not index is used. 

If that is a case, your premise is wrong. Correctness of result is irrelevant of whether index is used or not. Correctness of result depends only on returned rows.

I explained you why indices can not be used in your above queries. This is explained in much more detail in our Reference Manual.

Hi, everything is upper in my info.

ad 1.)

RETURN THIS (You can see in the result, that unit_id and function_unit_id is NOT EQUAL (so no row should be returned).

ONE ROW:
ownership_id    ownership_unit_id   unit_id   function_unit_id
5911            6143                8053      8052

ad 2.)
Correct result is NO ROW, because  of condition: OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31')
But returned rows have: OU.unit_id = 8053 and function result is 8052. Not equal and returned row is bug. In the WHERE is equal condition. This error is related to second part of where: "O.deleted_utc IS NULL", because 
a) if O.deleted_utc IS NULL as second condition is in the query, you can see the bug
b) if O.deleted_utc IS NULL as second condition missing (leave out), return is correct (return no rows).

That's the crazy behavior and maximum information what can be analyzed (and I have everything already submitted).

I have spent a lot of time finding the shortest and most accurate demonstration of the error.
I have send reproduction steps (copy & paste to create table with data). 
Please, run query and you can see what is crazy with your own eyes.

Hi,

I needed clarification from you, because it was not clear whether you implied indices or number of rows returned.

I will run your test case for that query only .......

Okey, thank you.

SIMPLIFIED:

This query returns incorrect result (1 row)

SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143)                             
WHERE O.deleted_utc IS NULL AND
      OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31');

This query returns correct result (0 row) - only difference is that condition with O.deleted_utc IS NULL missing (but O.deleted_utc is still null and should have no effect to result).

SELECT O.ownership_id,
       OU.ownership_unit_id,
       OU.unit_id, 
       getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31') AS function_unit_id
FROM ownership AS O
JOIN ownership_unit AS OU ON O.ownership_id = OU.ownership_id AND OU.ownership_unit_id IN (6143)                             
WHERE OU.unit_id = getOwnershipDefaultUnitId(OU.ownership_id, '2018-12-31');

Hi,

I am not able to repeat your problems. I have used your test case completely unchanged.

This is what I get as a result from both queries:

ownership_id	ownership_unit_id	unit_id	deleted_utc	function_unit_id
5911	6143	8053	NULL	8052
ownership_id	ownership_unit_id	unit_id	deleted_utc	function_unit_id
5911	6143	8053	NULL	8052

Hence, both queries returned the same result set.

However, 8053 != 8052, which is why this is a bug.

Verified as reported.

Perfect. So the behavior of missing/present second condition O.deleted_utc IS NULL is connected to some other settings, but we do not have to find why and it is propably not so important.

I am "happy" that you are able to reproduce this error.

Thank you for your cooperation.

You are truly welcome.

After a long time I checked this issue and it seems to be solved at least in the 8.0.20 version.

My suggestion is check the issue by your team again and you can propably close this issue.

This is a duplicate of BUG#97553, which was fixed in MySQL 8.0.19.