Identical MySQL query returning different result i

Question

This query `

delimiter $$

CREATE DEFINER=`root`@`localhost` FUNCTION `calculatePrice`(cheese VARCHAR(50), meat VARCHAR(50), veg VARCHAR(50)) RETURNS decimal(10,0)
    DETERMINISTIC
BEGIN
DECLARE price DECIMAL;
SET price = (SELECT  SUM(x.Price) 
    FROM
    (
        SELECT `priceFactor` AS Price FROM `tblCheese` WHERE `cheeseName` = cheese
        UNION ALL 
        SELECT `priceFactor` AS Price FROM `tblMeat` WHERE `meatName` = meat 
        UNION ALL 
        SELECT `priceFactor` AS Price FROM `tblVeggie` WHERE `veggieName` = veg
    ) x );
RETURN price;
END$$

`

is returning the mathematically-correct answer when called from the MySQL command-line client, but incorrect for any other program or when called from PHP (it's returning 3 there, no matter what parameters are passed.) See below:

The calling statement, in case it's blurry, is SELECT calculatePrice('colby', 'sausage', 'black beans');

I've never seen this weirdness before. It's all going off of the same copy of MySQL, etc.

Edit to add: phpMyAdmin also yields the correct answer to the query.

Answer 1

I can tell you how that went.

MySQL has shipped for the longest time with latin1_swedish_ce as default character set for practically everything. Now, you usually take care of the character set when creating databases, so the danger there is minimal.

However, transferring the data over a line needs an encoding, and interpreting the data on the user end needs interpretation, too. So there are settings for that as well. And the standard character sets for MySQL tools are (as they are from the same company) latin1_swedish.

Whether this will be a problem for a query highly depends on all the data that the query runs over. Also, when using constant strings in your query, they are interpreted much the same way as data coming from the database to your client.

Therefor, character encoding is often the problem, and it was here as well.

Answer 2

@x0cafebabe hit it on the head: all but one of my character_ variables was set to UTF8, what I specified in installing MySQL, but character_set_database was set to a Sweedish encoding. I fixed that and now MySQL function is consistent across mediums. Why that makes a difference, I do not know (I've asked in the comments,) but it did.

I will forever check this on every MySQL installation I ever do ever again... That was WAY too much headache for such a trivial setting!