时间戳

首页 > 解决方案 > MySQL LEFT JOIN 与 WHERE 函数调用产生错误结果

mysql - MySQL LEFT JOIN 与 WHERE 函数调用产生错误结果

问题描述

从 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.AgentEmail是NULL（来自不匹配的LEFT JOINed 行），为什么在世界上将它传递给函数的a.AgentEmail行为与将它作为文字传递不同NULL？

[顺便说一句：我相信我过去曾在 MS SQL 服务器下使用过这种结构，它的工作方式与我预期的一样。另外，我可以反转 to 的测试，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....作为函数参数的引用都会导致没有匹配的行...]

标签： mysqlleft-joinmysql-function

解决方案

这很可能是优化器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

推荐阅读