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
