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.com@lists.launchpad.net] On Behalf Of Benoit Panizzon Sent: 22 June 2015 15:13 To: Guillaume Lefranc Cc: maria-discuss@lists.launchpad.net 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 : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp