maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #02708
Re: Bug in MAX() function?
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@xxxxxxxxxxxxxxxxxxx] On Behalf Of Benoit Panizzon
Sent: 22 June 2015 15:13
To: Guillaume Lefranc
Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
Follow ups
References
