← Back to team overview

maria-discuss team mailing list archive

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