Re: Adding values returned by GREATEST

Hello Sukhjinder,

On 5/3/2016 8:55 AM, Sukhjinder K. Narula wrote:
> 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
>
I attempted to reproduce this problem but was unable to do so.
(testing with 5.7.11)

localhost.test>SELECT @@version;
+---------------------------------------+
| @@version                             |
+---------------------------------------+
| 5.7.11-enterprise-commercial-advanced |
+---------------------------------------+

localhost.(none)>select greatest(1,3,0), greatest(0,1,0), 
greatest(1,3,0)+ greatest(0,1,0) as total
     -> ;
+-----------------+-----------------+-------+
| greatest(1,3,0) | greatest(0,1,0) | total |
+-----------------+-----------------+-------+
|               3 |               1 |     4 |
+-----------------+-----------------+-------+
1 row in set (0.00 sec)

localhost.(none)>set @A = 1, @B = 3, @C=0, @D = 0, @E = 1, @F = 0;
Query OK, 0 rows affected (0.00 sec)

localhost.(none)>select greatest(@a,@b,@c), greatest(@d,@e,@f), 
greatest(@a,@b,@c)+ greatest(@d,@e,@f) as total;
+--------------------+--------------------+-------+
| greatest(@a,@b,@c) | greatest(@d,@e,@f) | total |
+--------------------+--------------------+-------+
|                  3 |                  1 |     4 |
+--------------------+--------------------+-------+
1 row in set (0.00 sec)

localhost.(none)>create database test;
Query OK, 1 row affected (0.00 sec)

localhost.(none)>use test
Database changed
localhost.test>create table t1 (a int, b int, c int, d int, e int, f int);
Query OK, 0 rows affected (0.23 sec)

localhost.test>insert t1 values (1,3,0,0,1,0);
Query OK, 1 row affected (0.03 sec)

localhost.test>select greatest(a,b,c), greatest(d,e,f), 
greatest(a,b,c)+ greatest(d,e,f) as total from t1;
+-----------------+-----------------+-------+
| greatest(a,b,c) | greatest(d,e,f) | total |
+-----------------+-----------------+-------+
|               3 |               1 |     4 |
+-----------------+-----------------+-------+
1 row in set (0.00 sec)

localhost.test>select greatest(a,b,c)+ greatest(d,e,f)+ greatest(d,e,f) 
as total from t1;
+-------+
| total |
+-------+
|     5 |
+-------+
1 row in set (0.00 sec)

Can you provide a more complete test case?
Can you tell us which version of MySQL you are using?

-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql