CODEWITHSUNDEEP
mysql
Andreas
Andreas

Reputation: 3

MYSQL - How to cast a field in depending on its value?

I have complex SQL-Statements with group_concat and i need sometimes to convert the value. So i use something like this (Its an example):

SELECT IF(1=1,CAST(TEST AS CHAR),CAST(TEST AS UNSIGNED)) 
FROM(
SELECT '40' as TEST
UNION 
SELECT '5' as TEST
UNION 
SELECT '60' as TEST
) as t1
ORDER BY TEST ASC;

Should return CHAR, returns CHAR.

SELECT IF(1=0,CAST(TEST AS CHAR),CAST(TEST AS UNSIGNED)) 
FROM(
SELECT '40' as TEST
UNION 
SELECT '5' as TEST
UNION 
SELECT '60' as TEST
) as t1
ORDER BY TEST ASC;

Should return UNSIGNED, returns CHAR

So the result of IF condition is always a CHAR and need to be CAST.

How i can resolve the problem?

Upvotes: 0

Views: 957

Answers (1)

Barmar
Barmar

Reputation: 781141

MySQL tries to figure out the column type just by parsing the SQL, not based on runtime conditions like the way an IF() expression goes. While your example allows the result of the IF() to be determined before actually processing any rows, this is not true in general and MySQL doesn't perform this optimization.

It just sees that the IF() can return either UNSIGNED or CHAR, and figures out a common type that both can be converted to safely. This is CHAR.

This is explained in the documentation:

The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

  • If expr2 or expr3 produce a string, the result is a string.
  • If expr2 and expr3 are both strings, the result is case-sensitive if either string is case sensitive.
  • If expr2 or expr3 produce a floating-point value, the result is a floating-point value.
  • If expr2 or expr3 produce an integer, the result is an integer.

Upvotes: 1

Related Questions