# Re: Adding values returned by GREATEST

*Date*: Sat, 14 May 2016 13:57:54 -0500*From*: Peter Brawley <peter.brawley@xxxxxxxxxxxxx>*Subject*: Re: Adding values returned by GREATEST

On 5/14/2016 11:16, shawn l.green wrote:

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, SKI 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?

`He asked this in the Newbie forum last month. The column is Enum, with`

`whose ambiguities Greatest() can produce odd-looking arithmetic ...`

drop table if exists t; create table t(i enum('2','1','3'), j enum('5','2','8') ); insert into t values('1','1'); select greatest(i,j) from t; +---------------+ | greatest(i,j) | +---------------+ | 5 | +---------------+ select greatest(i+0,j+0) from t; +-------------------+ | greatest(i+0,j+0) | +-------------------+ | 2 | +-------------------+ PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql

**Follow-Ups**:**Re: Adding values returned by GREATEST***From:*shawn l.green

**References**:**Adding values returned by GREATEST***From:*Sukhjinder K. Narula

**Re: Adding values returned by GREATEST***From:*shawn l.green

- Prev by Date:
**Re: Adding values returned by GREATEST** - Next by Date:
**Re: Adding values returned by GREATEST** - Previous by thread:
**Re: Adding values returned by GREATEST** - Next by thread:
**Re: Adding values returned by GREATEST** - Index(es):