MySQL 左连接与 WHERE 函数调用产生错误的结果

Question

从MySQL 5.7我正在执行一个LEFT JOIN，WHERE子句调用我的用户定义函数。 它找不到它应该找到的匹配行。

[最初，为了这篇文章的目的，我稍微简化了我的实际代码。 但是，鉴于用户提出的响应，我发布了可能相关的实际代码。

我的用户功能是：

CREATE FUNCTION `jfn_rent_valid_email`(
rent_mail_to varchar(1),
agent_email varchar(45),
contact_email varchar(60)
)
RETURNS varchar(60)
BEGIN
IF rent_mail_to = 'A' AND agent_email LIKE '%@%' THEN
RETURN agent_email;
ELSEIF contact_email LIKE '%@%' THEN
RETURN contact_email;
ELSE
RETURN NULL;
END IF
END

我的查询是：

SELECT r.RentCode, r.MailTo, a.AgentEmail, co.Email,
jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) 
AS ValidEmail
FROM rents r
LEFT JOIN contacts co ON r.RentCode = co.RentCode -- this produces one match
LEFT JOIN link l ON r.RentCode = l.RentCode -- there will be no match in `link` on this
LEFT JOIN agents a ON l.AgentCode = a.AgentCode -- there will be no match in `agents` on this
WHERE  r.RentCode = 'ZAKC17' -- this produces one match
AND (jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) IS NOT NULL)

这不会产生任何行。

然而。 当我a.AgentEmail IS NULL如果我只改变

AND (jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) IS NOT NULL)

自

AND (jfn_rent_valid_email(r.MailTo, NULL, co.Email) IS NOT NULL)

它确实会正确生成匹配的行：

RentCode, MailTo, AgentEmail, Email,      ValidEmail
ZAKC17,   N,      <NULL>,     name@email, name@email

那么，当a.AgentEmailNULL时(来自不匹配的LEFT JOINed 行(，为什么将其传递给函数a.AgentEmail与将其作为文字NULL传递不同？

[顺便说一句：我相信我过去曾在MS SQL服务器下使用过这种结构，并且它的工作符合我的预期。 此外，我可以将AND (jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) IS NOT NULL)的测试逆转为AND (jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) IS NULL)但我仍然没有匹配。 就好像任何对a....作为函数参数的引用都会导致没有匹配的行......]

Answer 1

这很可能是优化器将LEFT JOIN变成INNER JOIN的问题。当优化程序认为生成的 NULL 行的 WHERE 条件始终为假时，它可以执行此操作(在本例中不是(。

您可以使用EXPLAIN命令查看查询计划，您可能会看到不同的表顺序，具体取决于查询变体。

如果该函数的实际逻辑是用一个函数调用检查所有电子邮件，那么使用仅将一个电子邮件地址作为参数并将其用于每个电子邮件列的函数可能会更好。

您可以尝试不使用该功能：

SELECT r.RentCode, r.MailTo, a.AgentEmail, co.Email,
jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) 
AS ValidEmail
FROM rents r
LEFT JOIN contacts co ON r.RentCode = co.RentCode -- this produces one match
LEFT JOIN link l ON r.RentCode = l.RentCode -- there will be no match in `link` on this
LEFT JOIN agents a ON l.AgentCode = a.AgentCode -- there will be no match in `agents` on this
WHERE  r.RentCode = 'ZAKC17' -- this produces one match
AND ((r.MailTo='A' AND a.AgentEmail LIKE '%@%') OR co.Email LIKE '%@%' )

或者将函数包装在子查询中：

SELECT q.RentCode, q.MailTo, q.AgentEmail, q.Email, q.ValidEmail
FROM (
SELECT r.RentCode, r.MailTo, a.AgentEmail, co.Email,
jfn_rent_valid_email(r.MailTo, a.AgentEmail, co.Email) AS ValidEmail
FROM rents r
LEFT JOIN contacts co ON r.RentCode = co.RentCode -- this produces one match
LEFT JOIN link l ON r.RentCode = l.RentCode -- there will be no match in `link` on this
LEFT JOIN agents a ON l.AgentCode = a.AgentCode -- there will be no match in `agents` on this
WHERE  r.RentCode = 'ZAKC17' -- this produces one match
) as q
WHERE q.ValidEmail IS NOT NULL

Answer 2

更改对WHERE子句中函数的调用以读取

jfn_rent_valid_email(r.MailTo, IFNULL(a.AgentEmail, NULL), IFNULL(co.Email, NULL)) IS NOT NULL

解决了问题。

优化器似乎认为如果将对a.AgentEmail的纯引用作为任何参数传递，则它可以错误地猜测该函数将在不匹配的情况下返回NULLLEFT JOIN。 但是，如果列引用在任何类型的表达式中，优化程序就会躲开。 因此，将其包裹在一个看似毫无意义的IFNULL(column, NULL)"假人"中，足以恢复正确的行为。

我将其标记为可接受的解决方案，因为它是迄今为止最简单的解决方法，需要最少的代码更改/完整的查询重写。

但是，完全归功于@slaakso在本主题中分析问题的帖子。 请注意，他指出该行为已在MySQL 8中修复/更改，因此不需要此解决方法，因此可能仅在MySQL 5.7或更早版本中才需要。