[Maria-discuss] Bug in MAX() function?
![](https://secure.gravatar.com/avatar/795029208944cb99d9d409e369caa606.jpg?s=120&d=mm&r=g)
Hi all I stumbled over that problem, while trying to graph mail traffic.. I have two different counters for emails sent with authentication and without. Aggregated per hour. I would like to total the both values per row, to create a graph of outgoing emails per hour. MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters; +---------------+---------------+ | mail_out_anon | mail_out_auth | +---------------+---------------+ | 8 | 58 | | 8 | 48 | | 4 | 63 | | 9 | 53 | | 2 | 36 | | 0 | 12 | | 3 | 2 | | 0 | 2 | | 0 | 6 | | 0 | 2 | | 0 | 9 | | 0 | 44 | | 14 | 63 | | 0 | 96 | | 7 | 43 | | 4 | 61 | | 2 | 43 | | 2 | 66 | | 0 | 86 | | 6 | 77 | | 6 | 55 | | 0 | 63 | | 6 | 48 | | 2 | 52 | +---------------+---------------+
select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 | | 38 | | 12 | | 5 | | 2 | | 6 | | 2 | | 9 | | 44 | | 77 | | 96 | | 50 | | 65 | | 45 | | 68 | | 86 | | 83 | | 61 | | 63 | | 54 | | 54 | +-----------------------------+ Now I need the max value of that addition to auto-scale the graph. MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth) +----------------------------------+ | MAX(mail_out_anon+mail_out_auth) | +----------------------------------+ | 187 | +----------------------------------+ What did MariaDB count here? I was expecting a value 96 which is the largest value of all the additions. Also trying various ways of first using GREATEST() always returns a way to high value. Is it a bug, or did I do something wrong? 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 ______________________________________________________
![](https://secure.gravatar.com/avatar/01d86d32e4a72ce9ecd34dba70b74cc4.jpg?s=120&d=mm&r=g)
Hi Benoit,
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;
Hope this helps
-Guillaume
2015-06-19 10:59 GMT+02:00 Benoit Panizzon
Hi all
I stumbled over that problem, while trying to graph mail traffic..
I have two different counters for emails sent with authentication and without. Aggregated per hour.
I would like to total the both values per row, to create a graph of outgoing emails per hour.
MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters;
+---------------+---------------+ | mail_out_anon | mail_out_auth | +---------------+---------------+ | 8 | 58 | | 8 | 48 | | 4 | 63 | | 9 | 53 | | 2 | 36 | | 0 | 12 | | 3 | 2 | | 0 | 2 | | 0 | 6 | | 0 | 2 | | 0 | 9 | | 0 | 44 | | 14 | 63 | | 0 | 96 | | 7 | 43 | | 4 | 61 | | 2 | 43 | | 2 | 66 | | 0 | 86 | | 6 | 77 | | 6 | 55 | | 0 | 63 | | 6 | 48 | | 2 | 52 | +---------------+---------------+
select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 | | 38 | | 12 | | 5 | | 2 | | 6 | | 2 | | 9 | | 44 | | 77 | | 96 | | 50 | | 65 | | 45 | | 68 | | 86 | | 83 | | 61 | | 63 | | 54 | | 54 | +-----------------------------+
Now I need the max value of that addition to auto-scale the graph.
MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth) +----------------------------------+ | MAX(mail_out_anon+mail_out_auth) | +----------------------------------+ | 187 | +----------------------------------+
What did MariaDB count here? I was expecting a value 96 which is the largest value of all the additions.
Also trying various ways of first using GREATEST() always returns a way to high value.
Is it a bug, or did I do something wrong?
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
![](https://secure.gravatar.com/avatar/391533e9371c184ff01828fd7fe6491c.jpg?s=120&d=mm&r=g)
“you have to use GROUP BY for it to work as expected.”
Not in this case you don’t. There’s no value in the SELECT to group by.
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Guillaume Lefranc
Sent: 19 June 2015 10:03
To: Benoit Panizzon
Cc: maria-discuss@lists.launchpad.net
Subject: Re: [Maria-discuss] Bug in MAX() function?
Hi Benoit,
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;
Hope this helps
-Guillaume
2015-06-19 10:59 GMT+02:00 Benoit Panizzon
select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 | | 38 | | 12 | | 5 | | 2 | | 6 | | 2 | | 9 | | 44 | | 77 | | 96 | | 50 | | 65 | | 45 | | 68 | | 86 | | 83 | | 61 | | 63 | | 54 | | 54 | +-----------------------------+ Now I need the max value of that addition to auto-scale the graph. MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth) +----------------------------------+ | MAX(mail_out_anon+mail_out_auth) | +----------------------------------+ | 187 | +----------------------------------+ What did MariaDB count here? I was expecting a value 96 which is the largest value of all the additions. Also trying various ways of first using GREATEST() always returns a way to high value. Is it a bug, or did I do something wrong? 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.netmailto:maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
![](https://secure.gravatar.com/avatar/795029208944cb99d9d409e369caa606.jpg?s=120&d=mm&r=g)
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 ______________________________________________________
![](https://secure.gravatar.com/avatar/391533e9371c184ff01828fd7fe6491c.jpg?s=120&d=mm&r=g)
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
![](https://secure.gravatar.com/avatar/795029208944cb99d9d409e369caa606.jpg?s=120&d=mm&r=g)
Hi Rhys
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.
Yes, this is a live database with counters being constantly updated for the actual timeslice :-)
I think using a computed column here would simplify the semantics of your query...
Thank you, I'll look into this.
As I said provide some table create & insert statements and you'll get some better asnwers.
CREATE TABLE `domaincounters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `timeslice` datetime DEFAULT NULL, `bad_filename` int(11) DEFAULT '0', `ham` int(11) DEFAULT '0', `mail_in` int(11) DEFAULT '0', `mail_out_anon` int(11) DEFAULT '0', `mail_out_auth` int(11) DEFAULT '0', `out_bad_filename` int(11) DEFAULT '0', `skip` int(11) DEFAULT '0', `spam` int(11) DEFAULT '0', `virus` int(11) DEFAULT '0', `suspicious_chars` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `domain_id` (`domain_id`,`timeslice`) ) ENGINE=InnoDB AUTO_INCREMENT=1557693 DEFAULT CHARSET=utf8 The insert/update query looks like this: $query = "INSERT INTO domaincounters set timeslice = DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'), $counter = $count, domain_id = " . $domainid->{'id'} . " ON DUPLICATE KEY UPDATE $counter = $counter + $count"; $counter is the column to be updated $count is the number to add to the counter 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 ______________________________________________________
![](https://secure.gravatar.com/avatar/391533e9371c184ff01828fd7fe6491c.jpg?s=120&d=mm&r=g)
Hi Benoit, Had a bit of downtime so I did a little test... CREATE TABLE domaincounters ( id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, mail_out_anon SMALLINT NOT NULL DEFAULT 0, mail_out_auth SMALLINT NOT NULL DEFAULT 0, timeslice DATETIME NOT NULL, KEY (timeslice) ); INSERT INTO domaincounters (mail_out_anon, mail_out_auth, timeslice) VALUES (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') ; SELECT mail_out_anon + mail_out_auth, timeslice FROM domaincounters "mail_out_anon + mail_out_auth" "timeslice" "10" "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" # Not valid SQL SELECT MAX(mail_out_anon+mail_out_auth),timeslice FROM domaincounters "max(mail_out_anon+mail_out_auth)" "timeslice" "588" "2015-06-22 16:00:00" <- DATETIME does NOT correspond WITH MAX VALUE # This will do what you want (May not perform well on a big dataset) SELECT mail_out_anon+mail_out_auth,timeslice FROM domaincounters ORDER BY mail_out_anon+mail_out_auth DESC LIMIT 1 As I said a computed column may be useful for you. You can index these so it should be possible to combine that with timeslice for super-fast performance. -----Original Message----- From: Benoit Panizzon [mailto:benoit.panizzon@imp.ch] Sent: 22 June 2015 15:50 To: Rhys Campbell Cc: maria-discuss@lists.launchpad.net Subject: Re: [Maria-discuss] Bug in MAX() function? Hi Rhys
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.
Yes, this is a live database with counters being constantly updated for the actual timeslice :-)
I think using a computed column here would simplify the semantics of your query...
Thank you, I'll look into this.
As I said provide some table create & insert statements and you'll get some better asnwers.
CREATE TABLE `domaincounters` ( `id` int(11) NOT NULL AUTO_INCREMENT, `domain_id` int(11) NOT NULL, `timeslice` datetime DEFAULT NULL, `bad_filename` int(11) DEFAULT '0', `ham` int(11) DEFAULT '0', `mail_in` int(11) DEFAULT '0', `mail_out_anon` int(11) DEFAULT '0', `mail_out_auth` int(11) DEFAULT '0', `out_bad_filename` int(11) DEFAULT '0', `skip` int(11) DEFAULT '0', `spam` int(11) DEFAULT '0', `virus` int(11) DEFAULT '0', `suspicious_chars` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `domain_id` (`domain_id`,`timeslice`) ) ENGINE=InnoDB AUTO_INCREMENT=1557693 DEFAULT CHARSET=utf8 The insert/update query looks like this: $query = "INSERT INTO domaincounters set timeslice = DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00'), $counter = $count, domain_id = " . $domainid->{'id'} . " ON DUPLICATE KEY UPDATE $counter = $counter + $count"; $counter is the column to be updated $count is the number to add to the counter 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 ______________________________________________________
![](https://secure.gravatar.com/avatar/99fde0c1dfd216326aae0aff30d493f1.jpg?s=120&d=mm&r=g)
Benoit Panizzon
MariaDB [maildb]> select mail_out_anon+mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
This takes the first 24 rows by timeslice, and selects them.
select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
This takes a _single_ row containing the max(mail_out_anon+mail_out_auth) over the entire table. Then it sorts the single row, and limits it to 24 rows (neither of which does anything of course).
+----------------------------------+---------------------+ | max(mail_out_anon+mail_out_auth) | timeslice | +----------------------------------+---------------------+ | 656 | 2015-06-15 13:00:00 | +----------------------------------+---------------------+
Nope, wrong value...
Probably just the maximum over the entire table, right? Maybe try something like this (untested): SELECT * FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice FROM domaincounters WHERE domain_id=19 ORDER BY timeslice desc LIMIT 24) tmp ORDER BY ss DESC LIMIT 1;
Hmm. I think I see the problem now... the MAX() is done before sorting and limiting the result, right?
Right. - Kristian.
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
Try select max(total) from (select mail_out_anon + mail_out_auth as total
... ) dependent_subquery;
On Mon, Jun 22, 2015 at 9:05 AM, Kristian Nielsen
Benoit Panizzon
writes: MariaDB [maildb]> select mail_out_anon+mail_out_auth,timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
This takes the first 24 rows by timeslice, and selects them.
select max(mail_out_anon+mail_out_auth),timeslice from domaincounters where domain_id=19 order by timeslice desc limit 24;
This takes a _single_ row containing the max(mail_out_anon+mail_out_auth) over the entire table. Then it sorts the single row, and limits it to 24 rows (neither of which does anything of course).
+----------------------------------+---------------------+ | max(mail_out_anon+mail_out_auth) | timeslice | +----------------------------------+---------------------+ | 656 | 2015-06-15 13:00:00 | +----------------------------------+---------------------+
Nope, wrong value...
Probably just the maximum over the entire table, right?
Maybe try something like this (untested):
SELECT * FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice FROM domaincounters WHERE domain_id=19 ORDER BY timeslice desc LIMIT 24) tmp ORDER BY ss DESC LIMIT 1;
Hmm. I think I see the problem now... the MAX() is done before sorting and limiting the result, right?
Right.
- Kristian.
_______________________________________________ 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
![](https://secure.gravatar.com/avatar/795029208944cb99d9d409e369caa606.jpg?s=120&d=mm&r=g)
Hi Kristian
Maybe try something like this (untested):
SELECT * FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice FROM domaincounters WHERE domain_id=19 ORDER BY timeslice desc LIMIT 24) tmp ORDER BY ss DESC LIMIT 1;
Thank you, that is the solution I was looking for. Justin, you cannot have a subquery on the same table within your query. My previous attempt was to return the ID's of the last 24 timeslices in a subquery and then MAX() them. But MariaDB prevents this kind of query. 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 ______________________________________________________
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
You can't use a temp table more than once in a query.
If you could not use table more than once you could not do self join, or
variety of other things:
select * from a A1 join a A2 on A1.child_id A2.id;
--Justin
On Tue, Jun 23, 2015 at 12:17 AM, Benoit Panizzon
Hi Kristian
Maybe try something like this (untested):
SELECT * FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice FROM domaincounters WHERE domain_id=19 ORDER BY timeslice desc LIMIT 24) tmp ORDER BY ss DESC LIMIT 1;
Thank you, that is the solution I was looking for.
Justin, you cannot have a subquery on the same table within your query. My previous attempt was to return the ID's of the last 24 timeslices in a subquery and then MAX() them. But MariaDB prevents this kind of query.
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
![](https://secure.gravatar.com/avatar/392856b43200ef35060b655747853c7c.jpg?s=120&d=mm&r=g)
Hi,
select max(plus) from (select max(t1.c1) plus from t1 join (Select * from
t1) z) z;
+-----------+
| max(plus) |
+-----------+
| 235 |
+-----------+
1 row in set (0.00 sec)
On Tue, Jun 23, 2015 at 6:21 AM, Justin Swanhart
Hi,
You can't use a temp table more than once in a query.
If you could not use table more than once you could not do self join, or variety of other things: select * from a A1 join a A2 on A1.child_id A2.id;
--Justin
On Tue, Jun 23, 2015 at 12:17 AM, Benoit Panizzon
wrote: Hi Kristian
Maybe try something like this (untested):
SELECT * FROM (SELECT mail_out_anon+mail_out_auth ss, timeslice FROM domaincounters WHERE domain_id=19 ORDER BY timeslice desc LIMIT 24) tmp ORDER BY ss DESC LIMIT 1;
Thank you, that is the solution I was looking for.
Justin, you cannot have a subquery on the same table within your query. My previous attempt was to return the ID's of the last 24 timeslices in a subquery and then MAX() them. But MariaDB prevents this kind of query.
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
![](https://secure.gravatar.com/avatar/391533e9371c184ff01828fd7fe6491c.jpg?s=120&d=mm&r=g)
Hello Benoit, It might be useful to supply your table definitions. I did the following test and I can't find a problem. I am using 10.0.19-MariaDB. CREATE TABLE t1 ( col1 INT, # have also tried defined as VARCHAR. No problem either. col2 INT ); INSERT INTO `t1` (`col1`, `col2`) VALUES('8','58'); INSERT INTO `t1` (`col1`, `col2`) VALUES('8','48'); INSERT INTO `t1` (`col1`, `col2`) VALUES('4','63'); INSERT INTO `t1` (`col1`, `col2`) VALUES('9','53'); INSERT INTO `t1` (`col1`, `col2`) VALUES('2','36'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','12'); INSERT INTO `t1` (`col1`, `col2`) VALUES('3','2'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','2'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','6'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','2'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','9'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','44'); INSERT INTO `t1` (`col1`, `col2`) VALUES('14','63'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','96'); INSERT INTO `t1` (`col1`, `col2`) VALUES('7','43'); INSERT INTO `t1` (`col1`, `col2`) VALUES('4','66'); INSERT INTO `t1` (`col1`, `col2`) VALUES('2','43'); INSERT INTO `t1` (`col1`, `col2`) VALUES('2','66'); INSERT INTO `t1` (`col1`, `col2`) VALUES('0','63'); INSERT INTO `t1` (`col1`, `col2`) VALUES('6','48'); INSERT INTO `t1` (`col1`, `col2`) VALUES('2','52'); SELECT col1, col2, col1 + col2 FROM t1; # Correctly returns 96 SELECT MAX(col1 + col2) FROM t1; Cheers, Rhys -----Original Message----- From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=tradingscreen.com@lists.launchpad.net] On Behalf Of Benoit Panizzon Sent: 19 June 2015 09:59 To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Bug in MAX() function? Hi all I stumbled over that problem, while trying to graph mail traffic.. I have two different counters for emails sent with authentication and without. Aggregated per hour. I would like to total the both values per row, to create a graph of outgoing emails per hour. MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters; +---------------+---------------+ | mail_out_anon | mail_out_auth | +---------------+---------------+ | 8 | 58 | | 8 | 48 | | 4 | 63 | | 9 | 53 | | 2 | 36 | | 0 | 12 | | 3 | 2 | | 0 | 2 | | 0 | 6 | | 0 | 2 | | 0 | 9 | | 0 | 44 | | 14 | 63 | | 0 | 96 | | 7 | 43 | | 4 | 61 | | 2 | 43 | | 2 | 66 | | 0 | 86 | | 6 | 77 | | 6 | 55 | | 0 | 63 | | 6 | 48 | | 2 | 52 | +---------------+---------------+
select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 | | 38 | | 12 | | 5 | | 2 | | 6 | | 2 | | 9 | | 44 | | 77 | | 96 | | 50 | | 65 | | 45 | | 68 | | 86 | | 83 | | 61 | | 63 | | 54 | | 54 | +-----------------------------+ Now I need the max value of that addition to auto-scale the graph. MariaDB [maildb]> select MAX(mail_out_anon+mail_out_auth) +----------------------------------+ | MAX(mail_out_anon+mail_out_auth) | +----------------------------------+ | 187 | +----------------------------------+ What did MariaDB count here? I was expecting a value 96 which is the largest value of all the additions. Also trying various ways of first using GREATEST() always returns a way to high value. Is it a bug, or did I do something wrong? 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
![](https://secure.gravatar.com/avatar/bd7b55b8066c3ff42b93e46df109ae5e.jpg?s=120&d=mm&r=g)
Hi On Fri, 2015-06-19 at 10:59 +0200, Benoit Panizzon wrote:
Hi all
I stumbled over that problem, while trying to graph mail traffic..
I have two different counters for emails sent with authentication and without. Aggregated per hour.
I would like to total the both values per row, to create a graph of outgoing emails per hour.
MariaDB [maildb]> select mail_out_anon,mail_out_auth from domaincounters;
+---------------+---------------+ | mail_out_anon | mail_out_auth | +---------------+---------------+ | 8 | 58 | | 8 | 48 | | 4 | 63 | | 9 | 53 | | 2 | 36 | | 0 | 12 | | 3 | 2 | | 0 | 2 | | 0 | 6 | | 0 | 2 | | 0 | 9 | | 0 | 44 | | 14 | 63 | | 0 | 96 | | 7 | 43 | | 4 | 61 | | 2 | 43 | | 2 | 66 | | 0 | 86 | | 6 | 77 | | 6 | 55 | | 0 | 63 | | 6 | 48 | | 2 | 52 | +---------------+---------------+
select mail_out_anon+mail_out_auth as mail_out_total from domaincounters;
+-----------------------------+ | mail_out_anon+mail_out_auth | +-----------------------------+ | 70 | | 56 | | 67 | | 62 |
This looks wrong: Your query does not match the output you pasted...
From that query, the column heading would be "mail_out_total", and the first row would be 66, not 70... Copypasta error perhaps?
Hope this helps... -- Karl E. Jorgensen
participants (6)
-
Benoit Panizzon
-
Guillaume Lefranc
-
Justin Swanhart
-
Karl E. Jorgensen
-
Kristian Nielsen
-
Rhys Campbell