List: | General Discussion | « Previous MessageNext Message » | |

From: | shawn l.green | Date: | May 14 2016 4:16pm |

Subject: | Re: Adding values returned by GREATEST | ||

View as plain text |

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

Thread | ||
---|---|---|

• Adding values returned by GREATEST | Sukhjinder K. Narula | 3 May |

• Re: Adding values returned by GREATEST | shawn l.green | 14 May |

• Re: Adding values returned by GREATEST | Peter Brawley | 14 May |

• Re: Adding values returned by GREATEST | shawn l.green | 14 May |

• Re: Adding values returned by GREATEST | Hal.sz S.ndor | 23 May |