И есть в MySQL такое дело, как возможность расширения встроенного набора функций своими самописными функциями. Самописные функции пишутся на С/С++ и в именуются UDF: Used-Defined Function.
Так вот если мы пишем UDF, то в простом случае (то есть не рассматривая агрегатные функции) мы пишем на Си(++) три функции: собственно саму функцию, да ещё пару обработчиков, а именно обработчик-инициализации и обработчик деинициализации.
Например, если мы делаем MySQL-функцию XXX(), то мы делаем три сишных функци: xxx, xxx_init, xxx_deinit
Смысл оставшихся двух — провести инициализацию/деинициализацию, выделение освобождение памяти и т.п.
Если например в вас есть таблица something, и в ней 10 записей, и вы выполняете такой запрос:
- Код: Выделить всё
SELECT xxx(field1) FROM something
То сперва один раз будет вызвана xxx_init, затем 10 раз будет вызвана xxx, и затем один раз xxx_deinit
Как функция xxx_init, так и сама xxx — обе получают информацию об аргументах. Причём здесь есть интересный момент. Фраза «информация об аргументах» предполагает под собой информацию во-первых о типах (и природе) аргументов, а во-вторых, информацию о значениях аргументов.
Естественно, что раз init-хендлер вызывается единожды для целой группы последующих вызовов основной функции, то в init-хендер значения аргументов не могут быть переданы, поскольку для каждого вызова основной функции эти аргументы будут разными. На самом деле, это немного не так. Если конкретный аргумент функции в конкретном запросе меняется от записи к записи, то значение такого аргумента считается переменным и в init-хендлер, естественно, не передаётся. Если же от строчки к строчке значение аргумента не меняется, то оно считается константным, и в init-хендлер передаётся (равно как и в основную функцию тоже).
Возьмём для примера функцию CONCAT. Это встроенная функция MySQL, которая просто занимается конкатенацией переданных ей строк (у неё переменное число аргументов). Хоть она и встроенная, в рамках примера можно думать, что она не встроенная, а наша, UDF-шная.
SELECT CONCAT('aaa', 123, 'bbb') вернёт просто строчку «aaa123bbb».
Теперь представьте себе такой запрос:
- Код: Выделить всё
SELECT p.id, CONCAT('Hello ', p.name, ' from ', p.location) as `abc`
FROM persons p
WHERE p.age >= 18
Такой запрос выберет из таблицы peoples все строчки, у которых age >= 18, то есть все строчк, которые олицетворяют совершеннолетних людей. Результат будет, к примеру, такой
- Код: Выделить всё
p.id | abc
707 Hello John from USA
921 Hello Ivan from Russia
954 Hello Ebru from Turkey
987 Hello Olaf from Germany
Так вот если бы CONCAT была нашей собственной UDF-функцией, то сперва был бы вызван обработчик concat_init. И он бы получил информацию о том, что функция будет получать 4 аргумента. Причём первый и третий — константные, а второй и четвёртый - нет. Первый и третий ('Hello ' и ' from ') будут переданы и единожды в concat_init и каждый раз в concat. Меняющиеся от строки к строке значения p.name и p.location будут передаваться только concat — всякий раз, когда она (функция concat) будет вызвана для соответствующей строки (записи).
Но в любом случае, сперва будет вызвана concat_init, и вот она-то получит помимо самих значений двух (из четырёх) аргументов информацию о природе всех 4-ых аргументах. Эта информация, помимо всего прочего, включает сведения о типе аргумента и о том, может ли этот значение этого аргумента быть NULL.
Помимо того, что это даёт нам возможность подготовиться к пачке вызовов основной функции, выделив, например, буфер заведомо большого размера, но не больше, чем требуется для обработчик пачки вызовов, это даёт нам возможность сразу же проверить типы аргументов и если типы не те, какие нежны (или количество аргументов не то, какое ожидается) — выкинуть ошибку.
Так вот, если вы хотите написать функцию, которая принимает только строки, вы можете сделать именно так, и тогда запрос:
- Код: Выделить всё
SELECT XXX(p.id, p.age) FROM persons p
обломается ещё до того, как начнётся выполняться, потому что MySQL видит, что в роли аргументов выступают поля id и age таблицы persons, и MySQL знает, что эти поля числовые, и она знает, что тип этих полей не поменяется в рамках запроса (для всех записей из таблицы persons эти поля всегда будут иметь одни и те же типы значений) и она передаст информациб об этих полях init-хендлеру функции xxx и тот вернёт сбойный код в знак того, что его не устраивают такие аргументы (в дополнение он вернёт ещё и текстовое описание ошибки, любое, какое ему угодно).
Естественно, что помимо ограничения того, каких типов будут значения, вы можете ограничить передачу по признаку «может ли там быть NULL».
Понятно, что если поля id и age помечены как not null, то какие бы там ни были записи в таблице, ваша UDF никогда не получит NULL в качестве аргумента. И наоборот.
Так вот помимо того, что ваша получает информацию о том, может ли конкретный аргумент когда-нибудь оказаться содержащим NULL, она так же может вернуть информацию о том, может ли возвращаемое значение вашей функции быть NULL.
Кроме того, что вы можете это делать, встроенные функции MySQL тоже это делают.
Пусть вы написали UDF-функцию, которая ругается, если MySQL информирует её о том, что аргумент может быть NULL. Ругается с формулировкой «Argument must not be NULL» к примеру. Если аргумент не NULL, она просто возвращает его.
И вы выполняете: SELECT my_function(1) и получаете 1.
Окей.
Тогда вы полняете SELECT my_function(NULL) и закономерно получаете «Argument must not be NULL».
Если в качестве аргумента при вызове вашей UDF стоит выражение, то никаких проблем — MySQL хранит для любого выражения информацию о том, что оно собой представляет, и в частности, может ли значение этого выаржения быть NULL. Вызов какой-либо функции — это, естественно, тоже выражение.
Напримре, если говорить о встроенной фунции CONCAT, то хоть CONCAT и возвращает склейку всех аргументов в единую строку, окажись вдруг хоть один из её аргументов — NULL, то и возвращаемое значение тоже будет NULL.
CONCAT('food', 0, 'bar', 7) => 'food0bar7'
CONCAT('food', 0, NULL, 7) => NULL
Такая логика, кстати, для NULL актуальна почти для любых операций с NULL, кроме некоторых специально.
Так вот функция CONCAT тоже может предугадать, может ли её возвращаемое значение быть NULL, просто проанализоровав информацию о своих потенциальных аргументах. Если хоть один из её аргументов may be NULL, значит и возвращаемое значение тоже may be NULL, а уж что конкретно будет — определяется при обработке каждой отдельной строчке.
Поэтому ваша функция, которая отвергает аргументы, которые теоретически могут оказаться содержащими NULL, при таком вызове:
- Код: Выделить всё
SELECT my_function(CONCAT('Hello', p.name))
FROM persons p
WHERE p.age > 20
Успешно отработает, но только в том случае, если в таблице persons поле name имеет атрибут NOT NULL. Потому что если поле имеет такой атрибут, то функция функция CONCAT (concat_init, как бы) проанализировав информацию о типе будущих аргументов, поймёт, что NULL не может быть нигде и никогда (ни в одном из аргументов) и вернёт MySQL информацию о том, что и возвращаемое значение никогда не может быть NULL, и тогда MySQL передаст в my_function_init информацию о том, что единственный аргумент тоже никогда не может быть NULL, и всё будет хорошо.
Но если колонка name может содержать NULL, тогда concat_init вернёт информацию о том, что возврат CONCAT может быть NULL, и тогда my_function получит информацию о том, что аргумент может быть NULL, и тогда my_function_init просто выкинет ошибку, и запрос даже не начнёт выполняться.
Звучит хорошо, но дурь начинается вот в чём:
- В хранимых процедурах отслеживание того, может ли выражение содержать NULL или гарантировано не может — не работает. Вернее, оно для аргументов хранимых процедур.
Это значит, что если у вас UDF сделана так, что устроена так, что отказываетсяработатьинициализироваться, если какой-то аргумент может оказаться NULL, и вы такую функци вызываете в хранимой процедуре, и в качестве аргумента функции служит аргумент хранимой процедуры или переменная хранимой процедуры (или любое выражение с участием аргумента хранимой процедуры или переменной хранимой процедуры), то вы обречены — вы никогда не сможете вызвать функцию.
Иными словами:
select my_function(1);
гарантированно сработает: my_function_init получит информацию о том, что аргумент будет всегда один, всегда будет иметь тип int, всегда будет константой и никогда не будет NULL. И не взбрыкнет.
Но если вы обёрнёте это в фиктивную прозрачную хранимую процедуру:- Код: Выделить всё
CREATE PROCEDURE my_function_wrapper(IN param INT)
BEGIN
SELECT my_function(param);
END
и потом
CALL my_function_wrapper(1);
то всё, вы обречены, облом. Хотя MySQL имеет те же шансы проследить всю цепочку исчисления выражения и определить, что параметр хранимой процедуры — константен и гарантированно не может быть NULL, она этого не делает. - Вторая тупость касается того, как устроена другая встроенная функция MySQL — функция COALESCE.
Эта функция принимает переменное число аргументов и возвращает первый попавший аргумент, который не NULL.
Это значит, что
COALESCE(1,2,3) => 1
COALESCE(NULL, NULL, NULL) => NULL
COALESCE(NULL, 'qwe', NULL) => 'qwe'
Вот у функции CONCAT возвращаемое значение может быть NULL, если хотя бы один из аргументов может быть NULL Иными словами, ещё до того, как начнутся обрабатываться конкретные записи, можно просчитать, может ли там в возврате быть NULL или не может.
Функция COALESCE тоже может на основе информации о NotNull-ности своих аргументов дать ответ о NotNull-ности своего результирующего значения. Но проблема в том, что она этого не далет.
Помните нашу my_function, которая ругается, если аргумент может быть NULL?
SELECT my_function(123); — ошибки не будет гарантированно.
SELECT my_function(NULL); — ошибка будет гарантированно.
SELECT my_function(p.abc) FROM p; — ошибки не будет гарантированно, если колонка p имеет атрибут not null.
SELECT my_function(p.abc) FROM p; — ошибка будет гарантировано, если колонка p не имеет атрибута not null, даже если в таблице 1000 записей и реально поле p ни в одной из записей не принимает значение NULL. (Возможно я не прав тут в прогнозе)
SELECT my_function(CONCAT('foo', 123)); — ошибки не будет, гарантированно.
SELECT my_function(CONCAT('foo', NULL)); — ошибка будет, гарантированно.
SELECT my_function(CONCAT('foo', p.abc)) FROM p; — будет ли ошибка, зависит от того, имеет ли колонка p атрибут not null.
SELECT my_function(COALESCE(NULL, NULL, NULL)); — ошибка будет, гарантированно, что логично.
SELECT my_function(COALESCE('Jajaja', NULL)); — ошибка будет! Идиотизм!
SELECT my_function(COALESCE('foo', 'bar')); — ошибка будет! Идиотизм!
Итак, каждая функция, ещё перед тем как запрос начнёт выполняться, ещё перед тем, как будет обработана хоть одна реальная запись из БД, имеет возможность оценить все свои аргументы на предмет того, могут или не могут они быть NULL в рамках текущего запроса и дать ответ о том, может ли она сама когда-либо вернуть NULL в рамках этого же запроса, естественно.
Тем не менее, COALESCE опасливо клянётся, что может вернуть NULL, даже в тех случаях, когда известно, что хотя бы один из аргументов — никогда не будет NULL, а значит даже в худшем случае она (COALESCE) сможет вернуть именно его, а не NULL.
В итоге: UDF, которая преднамеренно написана таким образом, чтобы не допускать передачу NULL в качестве аргументов, вызвать из хранимой процедуры у вас нет никаких шансов, если при вызове аргументы UDF из SP не являются константами.
