Here is an example which I used to investigate the bug:
----------------------------------------------------------------
drop table if exists test_table;
create table test_table (
keyval float default null,
unique key keyval (`keyval`)
) engine=myisam;
insert into test_table values (-1), (2);
delimiter //
drop procedure if exists test_proc;
create procedure test_proc()
begin
loop
select round(rand(),6) into @r;
select @r;
insert into test_table values (@r);
select max(keyval) into @t from test_table where keyval <= @r;
select @t;
end loop;
end
//
delimiter ;
call test_proc();
----------------------------------------------------------------
Then I run:
$ while true; do mysql MYSQL_ARGS < testmax.sql | grep -i null -3; done |& grep -v
entry
There shouldn't be any output, but there is. However, if I get rid of
'unique' or change 'float' to 'double', the problem goes away. Perhaps
it is a problem where the engine incorrectly tries to optimize the
'unique' case (possibly, in a way that doesn't work for 'float'
because of conversions back and forth to 'double'). If that's true, it
should be pretty easy to fix.
If I change the engine to InnoDB or BDB, the problem persists.
However, if I change it to 'memory', the problem goes away.
It might be wise to add these tests to the test suite, seeing as the
bug appears to have been introduced in version 4.1, to prevent that
from happening again.
Frederik
On Wed, Jul 19, 2006 at 08:41:32PM +0100, Frederik Eaton wrote:
> Thanks for the responses. Is there a fix or a workaround? Is a fix
> planned? I'm a bit new to MySQL. Should I use version 4.0, or are
> there more bugs in that version?
>
> I am in the middle of a large project, and I was hoping to depend on
> max() working for numbers. I'm sorry to say it, but I'm a bit
> surprised that MySQL has been around so long, and such basic
> functionality is still broken.
>
> Should I use another database engine? Another database server?
>
> Thanks,
>
> Frederik
>
> On Mon, Jul 10, 2006 at 07:40:23PM -0700, Rick James wrote:
> > Ditto for 4.1.14; ok for 4.0.26, but note that it is important that you said
> > <= an existing value:
> > ==============
> > 4.1.14
> > mysql> select max(keyval) from nodes where keyval <= 0.6158;
> > Empty set (0.00 sec)
> >
> > mysql> select max(keyval) from nodes where keyval <= 0.615;
> > +------------------+
> > | max(keyval) |
> > +------------------+
> > | 0.38449999690056 |
> > +------------------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select max(keyval) from nodes where keyval <= 0.6159;
> > +------------------+
> > | max(keyval) |
> > +------------------+
> > | 0.61580002307892 |
> > +------------------+
> > 1 row in set (0.00 sec)
> >
> > ==============
> > 4.0.26:
> > mysql> select max(keyval) from nodes where keyval <= 0.6158;
> > +------------------+
> > | max(keyval) |
> > +------------------+
> > | 0.38449999690056 |
> > +------------------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select max(keyval) from nodes where keyval <= 0.61580002307892;
> > +------------------+
> > | max(keyval) |
> > +------------------+
> > | 0.61580002307892 |
> > +------------------+
> > 1 row in set (0.00 sec)
> >
> > > -----Original Message-----
> > > From: Konstantin Osipov [mailto:konstantin@stripped]
> > > Sent: Monday, July 10, 2006 3:58 PM
> > > To: frederik@stripped
> > > Cc: internals@stripped
> > > Subject: Re: bug with max()
> > >
> > > * Frederik Eaton <frederik@stripped> [06/07/11 00:43]:
> > >
> > >
> > > > mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486)
> > > using readline 5.1
> > > >
> > > > mysql> select avg(keyval) from nodes where keyval <= 0.6158;
> > > > +------------------+
> > > > | avg(keyval) |
> > > > +------------------+
> > > > | 0.38034999370575 |
> > > > +------------------+
> > > > 1 row in set (0.00 sec)
> > > >
> > > > mysql> select max(keyval) from nodes where keyval <= 0.6158;
> > > > Empty set (0.00 sec)
> > >
> > > This is repeatable in 5.0 as well.
> > >
> > > Reported as Bug#20954 "avg(keyval) retuns 0.38 but max(keyval)
> > > returns an empty set"
> > >
> > > --
> > > Konstantin Osipov, Software Developer
> > > MySQL AB, www.mysql.com
> > >
> > > --
> > > MySQL Internals Mailing List
> > > For list archives: http://lists.mysql.com/internals
> > > To unsubscribe:
> > > http://lists.mysql.com/internals?unsub=1
> > >
> > >
> >
> >
> > --
> > MySQL Internals Mailing List
> > For list archives: http://lists.mysql.com/internals
> > To unsubscribe: http://lists.mysql.com/internals?unsub=1
> >
>
> --
> http://ofb.net/~frederik/
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
--
http://ofb.net/~frederik/
