Re: [Maria-discuss] Bug in MAX() function?

22 Jun 2015

      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
______________________________________________________