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 JOIN Почему в мире передается функция 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.... как параметр функции не вызывает совпадения строки...]

Источник

2 ответа

Скорее всего, это проблема с оптимизатором поворота LEFT JOIN в INNER JOIN, Оптимизатор может сделать это, когда он считает, что WHERE-условие всегда ложно для сгенерированной строки NULL (чего в данном случае нет).

Вы можете взглянуть на план запроса с помощью 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
Источник

Изменение вызова функции в 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 здесь в этой теме для анализа проблемы. Обратите внимание, что он заявляет, что поведение было исправлено / изменено в MySQL 8, так что этот обходной путь не требуется, поэтому он может быть необходим только в MySQL 5.7 или более ранней версии.

Источник
Другие вопросы по тегам