Comments - CREATE FUNCTION

9 years, 9 months ago Mikhail Gavrilov

Don't understand in which cases function above will be work incorrect, because I defined it as "DETERMINISTIC", but it really READ SQL DATA.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `bpl_get_product_by_code`(
par_product_code VARCHAR(10)
) RETURNS VARBINARY(36)
    DETERMINISTIC
BEGIN
	DECLARE var_id_product VARBINARY(36);	
	SELECT id_product INTO var_id_product
	FROM bpl_product_types
	WHERE `code_product` = par_product_code;
	RETURN var_id_product;
    END$$

DELIMITER ;

If I defined it as "READ SQL DATA" follow SQL query

SELECT
  bpl_product_prices.id_product code_product
  ,ext_code code_shop
  ,SUM(cnt) cnt
  ,price
FROM bpl_product_prices
JOIN bpl_departments_stores USING (id_depart)
JOIN bpl_product_cnts USING (id_store, id_product)
JOIN `#departments` USING (id_depart)
WHERE
bpl_product_prices.id_product = bpl_get_product_by_code('15693')
AND (bpl_product_prices.id_depart = get_id_depart('') OR ('' = ''))
AND price > 0
AND cnt > 0
GROUP BY id_depart

will executing above 2 min.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.