10.3.28-MariaDB Case Statement not working

[Book]

SERVER INFORMATION

• Server: Localhost via UNIX socket
• Server type: MariaDB
• Server version: 10.3.28-MariaDB - MariaDB Server
• Protocol version: 10
• Apache
• Database client version: libmysql - 5.5.45-37.4
• PHP extension: mysqli

Our Host updated our server overnite from MariaDB 10.2 to 10.3 and now all of our case statements with multiple when are not working–example

select isbn,( Greatest(COALESCE(Fprice, 0),COALESCE(Aprice, 0),
COALESCE(NSprice, 0), COALESCE(Nprice, 0), COALESCE(BWprice, 0), COALESCE(Sprice, 0),
COALESCE(BBprice, 0), COALESCE(MKGprice, 0))) as bestprice,
case Greatest(COALESCE(Fprice, 0),COALESCE(Aprice, 0),COALESCE(NSprice, 0),
COALESCE(Nprice, 0), COALESCE(BWprice, 0), COALESCE(BBprice, 0),
COALESCE(MKGprice, 0),
COALESCE(Sprice, 0))
WHEN Sprice THEN ‘T’
WHEN Fprice THEN ‘a’
WHEN Aprice THEN ‘at’
WHEN Nprice THEN ‘na’
WHEN MKGprice THEN ‘Me’
WHEN BWPrice THEN ‘Br’
WHEN NSprice THEN ‘Ns’
WHEN BBPrice THEN ‘be’
END as bestguide from masterguide25Final

the only When that returns a result is the WHEN Sprice THEN ‘T’ so if the max is Sprice it returns T but if any other value is max it returns null–our entire system is down so any help would be really appreciated

[phdr]

If this data was normalized, rather than being laid out as a spreadsheet, all you would have to do is get the group-wise maximum price row per item, which would contain the corresponding guide abbreviation.

After the database server software was updated, was the following done -

Run mysql_upgrade.
mysql_upgrade does two things:
Ensures that the system tables in the mysql database are fully compatible with the new version.
Does a very quick check of all tables and marks them as compatible with the new version of MariaDB .

The only apparent MariaDB change that might have an affect on how this query works (yes I took the time to read the change documentation) is the addition of an EMPTY_STRING_IS_NULL mode setting, but this would only matter if the data type of any of this are strings with empty values. Note: because the WHEN … values are the non-COALESCE() raw value, you are not really comparing apples to apples.

If you post the table definition (so that the data types are known) and post a row of data where Sprice is the maximum and ‘works’ and a row of data where one of the other columns is the maximum, it would help in finding the cause of the problem. Also, is the bestprice value correct in both cases or is it perhaps zero or a non-displayed value when one of the other columns is the maximum?

[Book]

first thank you for you responce and reading through that mess ( i did to) but i missed the Empty String change) i have changed the query and it now works (WHEN Fprice = ( Greatest(COALESCE(Fprice, 0),COALESCE(Aprice, 0),
COALESCE(NSprice, 0), COALESCE(Nprice, 0), COALESCE(BWprice, 0), COALESCE(Sprice, 0),
COALESCE(BBprice, 0), COALESCE(MKGprice, 0))) THEN '‘a’ so i added the Greatest statment to each when statment and that makes it where it can never return null. The problem is i have these kind of case statements everywhere as they are very usefull and some of them i will not be able to edit this way an example is

select case when isbn13=’$isbn’ and (Status != ‘cancelled’ and Status!=‘archived’)and po<>’$po’ and NEBWANTGuideRef=’$NEBWANTGuideRef’ then sum(NEBWANTQty) END as NEBWANTQtyCheck,
from bctext where po<>’$po’

so any thoughts on how to fix this so it will work when null results are returned