Bug #596 select max() sometimes returns NULL
Submitted: 5 Jun 2003 8:18 Modified: 12 Aug 2003 10:33
Reporter: Riccardo Ghiglianovich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: ISAM storage engine Severity:S1 (Critical)
Version:3.23.56 OS:Linux (Linux R.H. 8.0 s.e.)
Assigned to: Bugs System CPU Architecture:Any

[5 Jun 2003 8:18] Riccardo Ghiglianovich
Description:
in some cases the query:
select max(a) from b where c = n 
returns NULL instead of the correct value;
I do not understand why and when, so I include the table description and a lot of rows:

How to repeat:
Insert into your database this table, then use this queries

drop TABLE if exists rik ;
CREATE TABLE rik (
  idpian int(11) NOT NULL default '0',
  idcomm int(11) NOT NULL default '0',
  idriga int(11) NOT NULL default '0',
  posfas smallint(6) NOT NULL default '0',
  idpadr int(11) NOT NULL default '0',
  dainco date NOT NULL default '0000-00-00',
  dafico date NOT NULL default '0000-00-00',
  priori smallint(6) NOT NULL default '0',
  tempof double(16,4) NOT NULL default '0.0000',
  tmpdaf double(16,4) NOT NULL default '0.0000',
  rescod int(11) NOT NULL default '0',
  datain date NOT NULL default '0000-00-00',
  oraini smallint(6) NOT NULL default '0',
  datafi date NOT NULL default '0000-00-00',
  orafin smallint(6) NOT NULL default '0',
  codfas smallint(6) NOT NULL default '0',
  profrz smallint(6) NOT NULL default '0',
  codrep smallint(6) NOT NULL default '0',
  codiso smallint(6) NOT NULL default '0',
  stafas smallint(6) NOT NULL default '0',
  tiprig char(2) NOT NULL default '',
  unmtmp char(2) NOT NULL default '',
  unmdaf char(2) NOT NULL default '',
  datfix date default NULL,
  dativi date NOT NULL default '0000-00-00',
  critic smallint(6) NOT NULL default '0',
  reslto int(11) NOT NULL default '0',
  seqslz smallint(6) NOT NULL default '0',
  PRIMARY KEY  (idpian,idcomm,idriga,profrz),
  KEY idcomm (idcomm,idpian,idriga)
) TYPE=ISAM PACK_KEYS=1;

INSERT INTO rik VALUES (936625751,3,0,0,0,'0000-00-00','0000-00-
00',0,43.0000,43.0000,0,'2001-12-06',0,'2001-12-06',0,0,1,0,0,0,'R','h','h','0000-00-00','0000-
00-00',3,0,0);
INSERT INTO rik VALUES (936625751,3,2,5,0,'0000-00-00','0000-00-
00',0,0.0000,0.0000,0,'1999-09-06',0,'1999-09-06',0,1,1,0,0,0,'R','h','h','0000-00-00','0000-
00-00',3,0,0);
INSERT INTO rik VALUES (936625751,3,3,10,0,'0000-00-00','0000-00-
00',0,8.0000,8.0000,2,'2001-12-06',0,'2001-12-06',0,1,1,1,101,0,'F','h','h','0000-00-00','0000-
00-00',3,0,0);
INSERT INTO rik VALUES (936625751,3,4,15,0,'0000-00-00','0000-00-
00',0,12.0000,12.0000,2,'2001-12-06',0,'2001-12-06',0,1,1,1,101,0,'F','h','h','0000-00-
00','0000-00-00',3,0,0);
INSERT INTO rik VALUES (936625751,3,5,20,0,'0000-00-00','0000-00-
00',0,5.0000,5.0000,2,'2001-12-06',0,'2001-12-06',0,1,1,1,101,0,'F','h','h','0000-00-00','0000-
00-00',3,0,0);

mysql> select max(idpian) from rik  where idcomm =3;
+-------------+
| max(idpian) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select max(idpian) from rik  ;               
+-------------+
| max(idpian) |
+-------------+
|   936625751 |
+-------------+
1 row in set (0.00 sec)

mysql> select max(idpian) from rik  where idcomm =3 and idpian >0;
+-------------+
| max(idpian) |
+-------------+
|   936625751 |
+-------------+
1 row in set (0.00 sec)
// note that idpian IS always >0 ; it is part of the primary key!
[6 Jun 2003 17:19] Alexander Keremidarski
The 'minimalized' structure which exposes bug is:

CREATE TABLE `rik` (
  `idpian` int(11) NOT NULL default '0',
  `idcomm` int(11) NOT NULL default '0',
  KEY `idcomm` (`idcomm`,`idpian`)
) TYPE=ISAM;

It happens when there is 
WHERE idcomm =,!=,<,>,IS NULL

MAX() returns NULL when Index Prefix column is compared to anything except for idcomm IS NOT NULL.

This is ISAM specific bug. It does not happen with any other table type. I tested it with MyISAM, InnoDB, HEAP and BDB types.
[12 Aug 2003 10:33] Sergei Golubchik
fixed in 3.23.58
[12 Aug 2003 10:33] Sergei Golubchik
it also means it's fixed in 4.0.15