mysql左连接where函数调用产生错误结果

vvppvyoh 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(453)

在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 JOIN 世界上为什么要把它作为 a.AgentEmail 与将其作为文字传递不同 NULL ?
[顺便说一句：我相信我过去曾在ms sql server下使用过这种构造，它的工作方式与我预期的一样。而且，我可以逆转 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.... 作为函数的参数，不会导致匹配行…]

mysql left-join mysql-function

来源：https://stackoverflow.com/questions/52535846/mysql-left-join-with-where-function-call-produces-wrong-result

2条答案

按热度按时间

sczxawaw1#

这很可能是优化器将 LEFT JOIN 变成一个 INNER JOIN . 当优化器认为生成的空行的where条件总是false（在本例中不是这样）时，可以这样做。
您可以使用 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 '%@%' )

或将函数 Package 到子查询中：

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

赞(0）回复(0）举报 2021-06-18

au9on6nz2#

更改对中函数的调用 WHERE 要阅读的子句

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

解决了这个问题。
似乎优化器认为它可能错误地猜测函数将返回 NULL 在非比赛中 LEFT JOIN 如果一个简单的引用 a.AgentEmail 作为任何参数传递。但是如果列引用在任何类型的表达式中，优化器都会退出。把它包在一个“假人”里，似乎毫无意义 IFNULL(column, NULL) 这样就足以恢复正确的行为。
我将此标记为可接受的解决方案，因为它是迄今为止最简单的解决方案，需要最少的代码更改/完整的查询重写。
不过，完全归功于@slaakso在本主题中分析问题的帖子。请注意，他声明在MySQL8中已经修复/更改了行为，因此这种解决方法是不必要的，因此可能只有在MySQL5.7或更早版本中才有必要。

赞(0）回复(0）举报 2021-06-18

我来回答

mysql左连接where函数调用产生错误结果

2条答案

相关问题

热门标签

最新问答