MySQL Stored Functions — DETERMINISTIC Causes Replica Drift

-- ============================================================
-- SCENARIO: An e-commerce platform needs to show each customer
-- their loyalty tier (Bronze / Silver / Gold) based on their
-- total lifetime spend. This logic appears in dashboards, emails,
-- and reports — so it must live in one place.
-- ============================================================

-- Step 1: Switch to your target database
USE ecommerce_db;

-- Step 2: Tell MySQL what delimiter to use while defining the function.
-- Without this, MySQL would interpret the first semicolon inside the
-- function body as the END of the CREATE statement — which breaks everything.
DELIMITER $$

CREATE FUNCTION get_loyalty_tier(
    total_spend DECIMAL(10, 2)   -- lifetime spend in dollars
)
RETURNS VARCHAR(10)              -- will return 'Bronze', 'Silver', or 'Gold'
DETERMINISTIC                   -- same input always gives same output (no randomness, no NOW())
COMMENT 'Returns customer loyalty tier based on lifetime spend thresholds'
BEGIN
    -- Declare a local variable to hold our result
    DECLARE tier_label VARCHAR(10);

    -- Apply the business rule: thresholds defined by the product team
    IF total_spend >= 1000.00 THEN
        SET tier_label = 'Gold';
    ELSEIF total_spend >= 250.00 THEN
        SET tier_label = 'Silver';
    ELSE
        SET tier_label = 'Bronze';   -- default for new or low-spend customers
    END IF;

    RETURN tier_label;   -- every code path MUST hit a RETURN
END$$

-- Step 3: Restore the normal semicolon delimiter
DELIMITER ;

-- ============================================================
-- Assume this customers table exists:
-- CREATE TABLE customers (
--     customer_id   INT PRIMARY KEY AUTO_INCREMENT,
--     full_name     VARCHAR(100),
--     email         VARCHAR(150),
--     lifetime_spend DECIMAL(10,2) DEFAULT 0.00
-- );
--
-- With sample data:
-- (1, 'Alice Hartman',  'alice@example.com',  1250.75)
-- (2, 'Ben Okafor',     'ben@example.com',     310.00)
-- (3, 'Cleo Ramirez',   'cleo@example.com',     89.50)
-- ============================================================

-- USE CASE 1: Show every customer with their computed loyalty tier
SELECT
    customer_id,
    full_name,
    lifetime_spend,
    get_loyalty_tier(lifetime_spend) AS loyalty_tier   -- function called inline
FROM customers
ORDER BY lifetime_spend DESC;

-- USE CASE 2: Filter — show only Gold-tier customers for a VIP email campaign
SELECT
    full_name,
    email
FROM customers
WHERE get_loyalty_tier(lifetime_spend) = 'Gold';

-- USE CASE 3: Quick sanity check — test the function directly
SELECT
    get_loyalty_tier(1500.00) AS result_gold,
    get_loyalty_tier(500.00)  AS result_silver,
    get_loyalty_tier(10.00)   AS result_bronze;

-- ============================================================
-- EXAMPLE 1: Correctly declared DETERMINISTIC function
-- Converts a raw numeric discount rate (e.g. 0.15) to a
-- human-readable label. Same input = same output. Always.
-- ============================================================
DELIMITER $$

CREATE FUNCTION format_discount_label(
    discount_rate DECIMAL(5, 4)   -- e.g. 0.1500 for 15%
)
RETURNS VARCHAR(20)
DETERMINISTIC
NO SQL                            -- no database reads or writes
BEGIN
    RETURN CONCAT(FORMAT(discount_rate * 100, 0), '% OFF');
END$$

DELIMITER ;

-- Test it:
SELECT format_discount_label(0.1500) AS label;
-- Output: '15% OFF'


-- ============================================================
-- EXAMPLE 2: Correctly declared NOT DETERMINISTIC function
-- Calculates how many days ago an order was placed.
-- The result changes every day — it reads the current date.
-- ============================================================
DELIMITER $$

CREATE FUNCTION days_since_order(
    order_placed_at DATE
)
RETURNS INT
NOT DETERMINISTIC                 -- result changes with time; optimizer must NOT cache it
NO SQL
BEGIN
    RETURN DATEDIFF(CURDATE(), order_placed_at);
END$$

DELIMITER ;

-- Test it (assuming today is 2025-07-15):
SELECT days_since_order('2025-07-01') AS days_ago;
-- Output: 14


-- ============================================================
-- EXAMPLE 3: Function that READS from a table
-- Gets a product's current category name by product_id.
-- Requires READS SQL DATA because it runs a SELECT.
-- ============================================================
DELIMITER $$

CREATE FUNCTION get_product_category(
    product_id_input INT
)
RETURNS VARCHAR(100)
NOT DETERMINISTIC                 -- data in the table can change between calls
READS SQL DATA                    -- required: this function runs a SELECT statement
BEGIN
    DECLARE category_name VARCHAR(100);

    SELECT c.category_name
    INTO category_name             -- stores query result into the local variable
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    WHERE p.product_id = product_id_input
    LIMIT 1;                       -- LIMIT 1 prevents 'Result consisted of more than one row' error

    RETURN IFNULL(category_name, 'Uncategorized');  -- graceful fallback if no match
END$$

DELIMITER ;

-- ============================================================
-- INSPECTING FUNCTIONS
-- ============================================================

-- List all stored functions in the current database
SHOW FUNCTION STATUS WHERE Db = 'ecommerce_db';

-- View the exact CREATE statement for an existing function
-- (useful for code review, documentation, or migrating servers)
SHOW CREATE FUNCTION get_loyalty_tier;

-- Query the information schema for richer metadata
SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DATA_TYPE         AS return_type,
    IS_DETERMINISTIC,
    ROUTINE_COMMENT   AS description,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'ecommerce_db'
  AND ROUTINE_TYPE   = 'FUNCTION'
ORDER BY ROUTINE_NAME;


-- ============================================================
-- UPDATING A FUNCTION (drop-and-recreate pattern)
-- The product team changed the Gold threshold from $1000 to $750
-- ============================================================

-- Always use IF EXISTS to avoid an error if it somehow doesn't exist
DROP FUNCTION IF EXISTS get_loyalty_tier;

DELIMITER $$

CREATE FUNCTION get_loyalty_tier(
    total_spend DECIMAL(10, 2)
)
RETURNS VARCHAR(10)
DETERMINISTIC
COMMENT 'v2: Gold threshold lowered to $750 per 2025 loyalty program update'
BEGIN
    DECLARE tier_label VARCHAR(10);

    IF total_spend >= 750.00 THEN      -- updated threshold
        SET tier_label = 'Gold';
    ELSEIF total_spend >= 250.00 THEN
        SET tier_label = 'Silver';
    ELSE
        SET tier_label = 'Bronze';
    END IF;

    RETURN tier_label;
END$$

DELIMITER ;


-- ============================================================
-- CONTROLLING PERMISSIONS
-- ============================================================

-- Grant the app's read-only user permission to CALL functions
-- but NOT to create or modify them
GRANT EXECUTE ON FUNCTION ecommerce_db.get_loyalty_tier TO 'app_readonly'@'%';

-- Grant a developer account full routine management rights
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE
    ON ecommerce_db.*
    TO 'dev_user'@'localhost';

-- Revoke all routine privileges from a deprecated service account
REVOKE EXECUTE ON ecommerce_db.* FROM 'legacy_service'@'%';

-- Verify current grants on the function
SHOW GRANTS FOR 'app_readonly'@'%';

-- ============================================================
-- BAD: Function that reads a table called in WHERE
-- Runs the SELECT inside get_product_category for every row of the outer query
-- ============================================================
SELECT p.product_name, p.price
FROM products p
WHERE get_product_category(p.product_id) = 'Electronics';

-- EXPLAIN shows: "Using where" with a note that a function is applied per row

-- ============================================================
-- GOOD: Replace with JOIN
-- ============================================================
SELECT p.product_name, p.price
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.category_name = 'Electronics';

-- EXPLAIN shows index usage on categories.name


-- ============================================================
-- BETTER: Use a generated column for frequently filtered computed values
-- ============================================================
ALTER TABLE products
ADD COLUMN category_name VARCHAR(100)
GENERATED ALWAYS AS (get_category_name(category_id)) VIRTUAL;

-- Now you can create an index on it (MySQL 8.0+):
CREATE INDEX idx_category_name ON products(category_name);

SELECT product_name, price
FROM products
WHERE category_name = 'Electronics';   -- uses index

-- ============================================================
-- Quick check: How many times does your function get called?
-- Run this to see if the function appears in the execution plan
-- ============================================================
EXPLAIN FORMAT=JSON
SELECT ... FROM ... WHERE my_function(col) = X;
-- Look for "materialized_from_subquery" or "dependent" in the JSON plan

// io.thecodeforge — database tutorial

DELIMITER $$

CREATE PROCEDURE ApplyBulkDiscount(
    IN order_id INT,
    IN customer_tier VARCHAR(10)
)
BEGIN
    DECLARE discount_rate DECIMAL(5,2);
    
    -- Call function ONCE, store result
    SET discount_rate = CalculateTierDiscount(customer_tier);
    
    UPDATE order_line_items
    SET unit_price = base_price * (1 - discount_rate)
    WHERE fk_order_id = order_id;
    
    INSERT INTO order_audit_log(order_id, discount_applied, applied_at)
    VALUES(order_id, discount_rate, NOW());
END$$

DELIMISTER ;

// io.thecodeforge — database tutorial

DELIMITER $$

CREATE FUNCTION SafeCommissionPct(sales_amount DECIMAL(12,2), target DECIMAL(12,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC
BEGIN
    DECLARE result DECIMAL(5,2) DEFAULT 0.00;
    
    -- Defensive: guard against NULL and zero division
    IF sales_amount IS NOT NULL AND target IS NOT NULL AND target > 0 THEN
        SET result = (sales_amount / target) * 0.15;
        
        -- Cap commission at 25%
        IF result > 25.00 THEN
            SET result = 25.00;
        END IF;
    END IF;
    
    RETURN result;
END$$

DELIMITER ;

-- Stored Function: returns a single value, used in SQL expressions
CREATE FUNCTION calculate_discount(price DECIMAL(10,2), discount_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * (1 - discount_rate / 100);
END;

-- Usage in SELECT
SELECT product_name, calculate_discount(price, 10) AS discounted_price FROM products;

-- Stored Procedure: can have OUT parameters and multiple statements
CREATE PROCEDURE apply_discount(IN product_id INT, IN discount_rate DECIMAL(5,2), OUT new_price DECIMAL(10,2))
BEGIN
    DECLARE current_price DECIMAL(10,2);
    SELECT price INTO current_price FROM products WHERE id = product_id;
    SET new_price = current_price * (1 - discount_rate / 100);
    UPDATE products SET price = new_price WHERE id = product_id;
END;

-- Calling procedure
CALL apply_discount(1, 10, @new_price);
SELECT @new_price;

-- Window function inside a stored function (MySQL 8.4+)
CREATE FUNCTION get_ranked_price(product_category INT)
RETURNS JSON
DETERMINISTIC
BEGIN
    DECLARE result JSON;
    SELECT JSON_ARRAYAGG(
        JSON_OBJECT('name', name, 'price', price, 'rank', rnk)
    ) INTO result
    FROM (
        SELECT name, price,
               RANK() OVER (ORDER BY price DESC) AS rnk
        FROM products
        WHERE category_id = product_category
    ) ranked;
    RETURN result;
END;

-- Function with default parameter (MySQL 8.4+)
CREATE FUNCTION calculate_tax(amount DECIMAL(10,2), tax_rate DECIMAL(5,2) DEFAULT 0.08)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN amount * tax_rate;
END;

-- Calling with default
SELECT calculate_tax(100); -- uses default 0.08
SELECT calculate_tax(100, 0.10); -- overrides default

-- Deterministic function: same input always same output
CREATE FUNCTION add_tax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN amount * 1.08;
END;

-- Non-deterministic function: uses current timestamp
CREATE FUNCTION get_current_timestamp()
RETURNS DATETIME
NOT DETERMINISTIC
BEGIN
    RETURN NOW();
END;

-- This UPDATE may cause replica drift if binary logging is STATEMENT-based
UPDATE orders SET processed_at = get_current_timestamp() WHERE id = 1;

-- Safe alternative: use ROW-based logging or compute timestamp in application
-- Or use a deterministic function with a parameter:
CREATE FUNCTION set_timestamp(ts DATETIME)
RETURNS DATETIME
DETERMINISTIC
BEGIN
    RETURN ts;
END;

UPDATE orders SET processed_at = set_timestamp(NOW()) WHERE id = 1; -- still non-deterministic because NOW() is passed