MySQL Bugs: #3097: MAX on integers does not work on Berkley Tables

Bug #3097	MAX on integers does not work on Berkley Tables
Submitted:	7 Mar 2004 13:17	Modified:	8 Mar 2004 11:22
Reporter:	Thomas Kathmann	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	UNIX
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
The MAX function returns the minimum value on Berkley DB tables on a UNIX machine (since the machine running the db belongs to my provider I don't have the specs)

How to repeat:
Create a table containing a primary key of the type int. Insert several records. Select the maximum. You will get the minimum.

Sorry, forgot version and OS.

I cannot repeat this.  Please supply a test case with the SQL to create, populate and query the table which demonstrates this behavior, as there may be something particular to your case which I did not test.

Well it's true - it's a little bt more complicated. The following SQL should repeat the bug:

CREATE TABLE PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID INT NOT NULL,PLY30_SENDING_PLAYER_ID INT NOT NULL,PLY30_MESSAGE_ID INT AUTO_INCREMENT NOT NULL,PLY30_SEND_DATE DATETIME NOT NULL,PLY30_DEFAULT_MESSAGE_ID CHAR(12) NOT NULL,PLY30_SUBJECT VARCHAR(255),PLY30_CHANGE_USER CHAR(12) NOT NULL,PLY30_CHANGE_DT TIMESTAMP NOT NULL , PRIMARY KEY (PLY30_PLAYER_ID,PLY30_SENDING_PLAYER_ID,PLY30_MESSAGE_ID));

ALTER TABLE PLY30M_PLAYER_MESSAGE TYPE=BDB;

INSERT INTO PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID,  PLY30_SEND_DATE,PLY30_DEFAULT_MESSAGE_ID, PLY30_SUBJECT, PLY30_CHANGE_USER) VALUES(1000,1000, '2004-03-03 12:00:00', 'CUSTOM', 'xyz', 'TEST');

INSERT INTO PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID,  PLY30_SEND_DATE,PLY30_DEFAULT_MESSAGE_ID, PLY30_SUBJECT, PLY30_CHANGE_USER) VALUES(1000,1000, '2004-03-03 12:00:00', 'CUSTOM', 'xyz', 'TEST');

INSERT INTO PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID,  PLY30_SEND_DATE,PLY30_DEFAULT_MESSAGE_ID, PLY30_SUBJECT, PLY30_CHANGE_USER) VALUES(1000,1000, '2004-03-03 12:00:00', 'CUSTOM', 'xyz', 'TEST');

INSERT INTO PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID,  PLY30_SEND_DATE,PLY30_DEFAULT_MESSAGE_ID, PLY30_SUBJECT, PLY30_CHANGE_USER) VALUES(1000,1000, '2004-03-03 12:00:00', 'CUSTOM', 'xyz', 'TEST');

INSERT INTO PLY30M_PLAYER_MESSAGE (PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID,  PLY30_SEND_DATE,PLY30_DEFAULT_MESSAGE_ID, PLY30_SUBJECT, PLY30_CHANGE_USER) VALUES(1000,1000, '2004-03-03 12:00:00', 'CUSTOM', 'xyz', 'TEST');

SELECT MAX(PLY30_MESSAGE_ID) FROM PLY30M_PLAYER_MESSAGE WHERE PLY30_PLAYER_ID=1000 AND PLY30_SENDING_PLAYER_ID=1000;

The select returns '1' while the correct answer would be '5'. The problem disappears if the restriction 'AND PLY30_SENDING_PLAYER_ID=1000' is removed from
the where clause. Hope this helps

Found out an other thing: A 'GROUP BY PLY30_PLAYER_ID, PLY30_SENDING_PLAYER_ID' solves the problem (i.e. MAX returns the correct number). As I undestand it, the GROUP BY should not be necessary. However, this reduces the severity of the problem.

I cannot repeat it with 4.0.18 or 4.0.19 on Linux (it reports the expected result), so we need to know more specific information about the host if at all possible.