Identical MySQL query returning different result in MySQL Workbench than from command-line

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: Identical query, identical parameters, different results

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.

It looks like it's returning 8 both command line and work bench. What's the error? — Andreas Wederbrand
– Andreas Wederbrand, Commented Oct 19, 2012 at 7:10
Command line mysql and Workbench might very well have different settings, which can result in different result sets. Specifically, the character set settings are used to interpret data from the database. Try comparing the settings. The command is "show variables". — 0xCAFEBABE
– 0xCAFEBABE, Commented Oct 19, 2012 at 7:39
@AndreasWederbrand CL's returning 8, which is correct, or whatever the correct sum is for the query, but WB's returning 3 no matter what. Sorry it's not exactly readable =-S — Janet
– Janet, Commented Oct 19, 2012 at 8:20
@0xCAFEBABE I spoke too soon. One of the character set variables was set to Sweedish o_O (I'm in the midwest USA, so I can assure you NOTHING should've picked up on anything Sweedish!) Fixed that and that cured it. I know it's a bit off-topic, but can someone explain why that made the difference? I'm pretty sure ever letter I used exists in Sweedish....? — Janet
– Janet, Commented Oct 19, 2012 at 9:56

0xCAFEBABE · Accepted Answer · 2012-10-19 10:30:39Z

3

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.

answered Oct 19, 2012 at 10:30

0xCAFEBABE

5,6665 gold badges40 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nakajuice Over a year ago

thank you, had exactly the same problem with same swedish encoding

nakajuice Over a year ago

oh sorry guys, though i had swedish encoding too, it wasn't a reason for different results in MySQL command-line and PHP. The reason was BIT type, in PHP it always returned an empty string. Try to avoid it and use INT instead.

Janet · Accepted Answer · 2012-10-19 10:00:24Z

0

@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!

answered Oct 19, 2012 at 10:00

Janet

3636 silver badges20 bronze badges

3 Comments

Andreas Wederbrand Over a year ago

I don't know where workbench keeps it's settings but for the client you could check my.cnf for anyhing in the [client] or [mysql] blocks. If no character encoding is selected there it means it goes with the system default. Could be that WB gets that from the system it's running on. And Swedish really has just one e :)

Janet Over a year ago

0xCAFEBABE Sorry forgot =-S Still new at posting rather than just reading >_<;;; Done.

Orvar Korvar Over a year ago

How did you notice that the encoding was weird?And how did you fix it? I have a similar problem. Would appreciate if you explained how you fixed it.

Collectives™ on Stack Overflow

Identical MySQL query returning different result in MySQL Workbench than from command-line

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related