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

[7 Mar 2004 13:17] Thomas Kathmann
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.
[7 Mar 2004 13:26] Thomas Kathmann
Sorry, forgot version and OS.
[8 Mar 2004 8:53] Dean Ellis
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.
[8 Mar 2004 11:10] Thomas Kathmann
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
[8 Mar 2004 11:18] Thomas Kathmann
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.
[8 Mar 2004 11:22] Dean Ellis
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.