“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 <benoit.panizzon@imp.ch<mailto:benoit.panizzon@imp.ch>>: 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<mailto:maria-discuss@lists.launchpad.net> Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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...

https://mariadb.com/kb/en/mariadb/virtual-computed-columns/

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 ______________________________________________________

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 <knielsen@knielsen-hq.org> wrote:

Benoit Panizzon <benoit.panizzon@imp.ch> 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

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 <benoit.panizzon@imp.ch> 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