Web lists-archives.com

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