Hi Benoit, If you provide this as a bunch of CREATE TABLE & INSERT statements it'll be easier for us to look at your problem.
Couple of things... When you do.. MariaDB [maildb]> select mail_out_anon,mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; The first row is... | 0 | 10 | 2015-06-22 16:00:00 | But when you do this... select mail_out_anon+mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; You get ... | 19 | 2015-06-22 16:00:00 | What the discrepancy? Is this being updated in the meantime? Something is going on here. I see nothing wrong with the SQL provided here to indicate why the result would be wrong. Secondly.. select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; This is not correct SQL. Many DBMS will error on this. Here's a good article explaining this... (See ONLY_FULL_GROUP_BY) http://rpbouman.blogspot.co.uk/2007/05/debunking-group-by-myths.html Basically when you do this MySQL does not guarantee that it will return the timeslice associated with the largest value you are computing with MAX. I think using a computed column here would simplify the semantics of your query... https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ As I said provide some table create & insert statements and you'll get some better asnwers. Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen....@lists.launchpad.net] On Behalf Of Benoit Panizzon Sent: 22 June 2015 15:13 To: Guillaume Lefranc Cc: [email protected] Subject: Re: [Maria-discuss] Bug in MAX() function? Hi Guillaume and the others who answered. Thank you. > MAX() is an aggregate function, you have to use GROUP BY for it to > work as expected. > e.g. select MAX(mail_out_anon+mail_out_auth) FROM domaincounters GROUP > BY timefunction; Nope, it's not doing what I want when I GROUP the entries. Ok, here's real data from my table: The counters for the last 24 hours for the domain with ID 19: MariaDB [maildb]> select mail_out_anon,mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; +---------------+---------------+---------------------+ | mail_out_anon | mail_out_auth | timeslice | +---------------+---------------+---------------------+ | 0 | 10 | 2015-06-22 16:00:00 | | 45 | 332 | 2015-06-22 15:00:00 | | 28 | 560 | 2015-06-22 14:00:00 | | 16 | 434 | 2015-06-22 13:00:00 | | 4 | 291 | 2015-06-22 12:00:00 | | 43 | 372 | 2015-06-22 11:00:00 | | 35 | 345 | 2015-06-22 10:00:00 | | 12 | 397 | 2015-06-22 09:00:00 | | 15 | 400 | 2015-06-22 08:00:00 | | 10 | 301 | 2015-06-22 07:00:00 | | 12 | 83 | 2015-06-22 06:00:00 | | 0 | 45 | 2015-06-22 05:00:00 | | 0 | 14 | 2015-06-22 04:00:00 | | 0 | 10 | 2015-06-22 03:00:00 | | 0 | 29 | 2015-06-22 02:00:00 | | 0 | 29 | 2015-06-22 01:00:00 | | 6 | 111 | 2015-06-22 00:00:00 | | 4 | 119 | 2015-06-21 23:00:00 | | 23 | 294 | 2015-06-21 22:00:00 | | 25 | 356 | 2015-06-21 21:00:00 | | 18 | 270 | 2015-06-21 20:00:00 | | 12 | 314 | 2015-06-21 19:00:00 | | 16 | 338 | 2015-06-21 18:00:00 | | 31 | 250 | 2015-06-21 17:00:00 | +---------------+---------------+---------------------+ I want to do one graph 'mail_out' and use the sum of both colums per time: MariaDB [maildb]> select mail_out_anon+mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; +-----------------------------+---------------------+ | mail_out_anon+mail_out_auth | timeslice | +-----------------------------+---------------------+ | 19 | 2015-06-22 16:00:00 | | 377 | 2015-06-22 15:00:00 | | 588 | 2015-06-22 14:00:00 | | 450 | 2015-06-22 13:00:00 | | 295 | 2015-06-22 12:00:00 | | 415 | 2015-06-22 11:00:00 | | 380 | 2015-06-22 10:00:00 | | 409 | 2015-06-22 09:00:00 | | 415 | 2015-06-22 08:00:00 | | 311 | 2015-06-22 07:00:00 | | 95 | 2015-06-22 06:00:00 | | 45 | 2015-06-22 05:00:00 | | 14 | 2015-06-22 04:00:00 | | 10 | 2015-06-22 03:00:00 | | 29 | 2015-06-22 02:00:00 | | 29 | 2015-06-22 01:00:00 | | 117 | 2015-06-22 00:00:00 | | 123 | 2015-06-21 23:00:00 | | 317 | 2015-06-21 22:00:00 | | 381 | 2015-06-21 21:00:00 | | 288 | 2015-06-21 20:00:00 | | 326 | 2015-06-21 19:00:00 | | 354 | 2015-06-21 18:00:00 | | 281 | 2015-06-21 17:00:00 | +-----------------------------+---------------------+ Ok, so that is the sum I want to graph. To correctly scale that graph, I need the largest value of those 24 entries. That would be 588 occuring at 2015-06-22 14:00:00 select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24; +----------------------------------+---------------------+ | max(mail_out_anon+mail_out_auth) | timeslice | +----------------------------------+---------------------+ | 656 | 2015-06-15 13:00:00 | +----------------------------------+---------------------+ Nope, wrong value... select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where domain_id=19 group by timeslice order by timeslice desc limit 24; +----------------------------------+---------------------+ | max(mail_out_anon+mail_out_auth) | timeslice | +----------------------------------+---------------------+ | 31 | 2015-06-22 16:00:00 | | 377 | 2015-06-22 15:00:00 | | 588 | 2015-06-22 14:00:00 | | 450 | 2015-06-22 13:00:00 | [...] Nope, I want just one value, the largest... So where do I make the mistake? Hmm. I think I see the problem now... the MAX() is done before sorting and limiting the result, right? So I can't use limit but I have to use a where clause to select timeslices after a specific time. Mit freundlichen Grüssen Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________ _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
