Re: Adding values returned by GREATEST

[Peter Brawley]

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql