Adding values returned by GREATEST
Hello,
i have a question regarding the GREATEST function of mysql.
I would like to add the values returned by GREATEST function is mysql, so a query is like below:
For example table t has 6 fields with values as follows: A = 1, B = 3, C=0, D = 0, E = 1 and F = 0 and I run a query:
SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F)
) AS Total
FROM t
The result row I expect is: 3, 1, 4
But I get 3, 1, 6
However when I run the query like below I get correct results as total being 4:
SELECT
(
GREATEST (1, 3, 0) + GREATEST(0,1,0)
) AS Total
So what I noticed is as I add result from GREATEST function, the result is adding 1 for each GREATEST call I have in total. So, if I change my query as below:
SELECT
GREATEST (A, B, C) AS G1,
GREATEST (D, E, F) AS G2,
(
GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F)
) AS Total
FROM t
The results will be 3, 1, 8
GREATEST (A, B, C) + GREATEST(D, E, F) + GREATEST(D, E, F) is calculated as
GREATEST (A, B, C) = 3 + 1
GREATEST(D, E, F) = 1 +1
GREATEST(D, E, F) = 1 +1
So the total is 8.
I have tried online to search for this type of behaviour but no luck. Can anyone please explain this.
Many Thanks,
SK