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