A stored function returns one value and goes wherever an expression may: a select list, WHERE, ORDER BY or another routine. It cannot return a result set, commit, or call itself. Binary logging is on by default, and there the first attempt at an order total fails:
CREATE FUNCTION order_total(p_order INT UNSIGNED) RETURNS DECIMAL(10,2)
RETURN (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order);
CREATE FUNCTION order_total(p_order INT UNSIGNED) RETURNS DECIMAL(10,2) READS SQL DATA
RETURN (SELECT SUM(quantity * unit_price) FROM order_items WHERE order_id = p_order);
CREATE FUNCTION add_vat(p DECIMAL(10,2)) RETURNS DECIMAL(10,2) NO SQL DETERMINISTIC
RETURN ROUND(p * 1.08, 2);
CREATE FUNCTION stamp() RETURNS DATETIME DETERMINISTIC RETURN NOW();
SELECT id, order_total(id) AS total, add_vat(order_total(id)) AS with_vat,
stamp() = NOW() AS lie_accepted FROM orders WHERE order_total(id) > 88;ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in ... +----+--------+----------+--------------+ | id | total | with_vat | lie_accepted | +----+--------+----------+--------------+ | 2 | 88.47 | 95.55 | 1 | | 6 | 128.80 | 139.10 | 1 | +----+--------+----------+--------------+
With the binary log on, a function must declare itself DETERMINISTIC (same arguments, same result) or free of writes (NO SQL, READS SQL DATA), because statement-based replication reruns the call. MySQL 524 takes your word: the nondeterministic stamp() was accepted, and the manual warns that such a lie can mislead the optimizer. SET GLOBAL log_bin_trust_function_creators = 1 lifts the check, but on a test server 9.7 answers with warning 1287, '@@log_bin_trust_function_creators' is deprecated and will be removed in a future release. The default ROW binlog format logs rows, not calls; label honestly and leave the variable alone.