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
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, 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
Re: Adding values returned by GREATEST
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