# Re: Adding values returned by GREATEST

*Date*: Sat, 14 May 2016 16:23:35 -0400*From*: "shawn l.green" <shawn.l.green@xxxxxxxxxx>*Subject*: Re: Adding values returned by GREATEST

On 5/14/2016 2:57 PM, Peter Brawley wrote:

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

Thanks Peter!

`Yes, using ENUMS instead of actual numeric values can easily make`

`everything act weird. Sometimes you see the position within the ENUM of`

`the matching value, sometimes you see the value. It all depends on how`

`you reference the column:`

`For everyone else, remember: ENUM is a way to store only a specific set`

`of string values into a column. We even document how confusing it can be`

`if you attempt to work with it as a set of numeric constants.`

Quoting from http://dev.mysql.com/doc/refman/5.7/en/enum.html > If you store a number into an ENUM column, the number is treated as > the index into the possible values, and the value stored is the > enumeration member with that index. (However, this does not work > with LOAD DATA, which treats all input as strings.) If the numeric > value is quoted, it is still interpreted as an index if there is no > matching string in the list of enumeration values. For these > reasons, it is not advisable to define an ENUM column with > enumeration values that look like numbers, because this can easily > become confusing. For example, the following column has enumeration > members with string values of '0', '1', and '2', but numeric index > values of 1, 2, and 3: (remainder of example snipped for brevity) > -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN

`Become certified in MySQL! Visit https://www.mysql.com/certification/`

`for details.`

-- 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:*Hal.sz S.ndor

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

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

**Re: Adding values returned by GREATEST***From:*Peter Brawley

- Prev by Date:
**Re: Adding values returned by GREATEST** - Next by Date:
**ANN: Database Designer for MySQL 2.1.7 released!** - Previous by thread:
**Re: Adding values returned by GREATEST** - Next by thread:
**Re: Adding values returned by GREATEST** - Index(es):