Bug #1769 Problems with MAX-function

• Submitted: 6 Nov 2003 9:16
• Modified: 13 Nov 2003 6:20
• Reporter: Timo Leppänen
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.0.15-standard
• OS: Linux (Linux)
• CPU Architecture: Any

[6 Nov 2003 9:16] Timo Leppänen

Description:
MAX-function does not correctly return maximum value, if there are NULL files inside the table.

How to repeat:
Reproduce:

1. Create a table with the following structure:
CREATE TABLE test (
  number1 int(2) unsigned default NULL,
  number2 int(2) unsigned default NULL,
  type int(11) NOT NULL default '0'
) TYPE=MyISAM;

2. Then insert these values
INSERT INTO test VALUES (5, 3, 1);
INSERT INTO test VALUES (8, 6, 1);
INSERT INTO test VALUES (14, 1, 2);
INSERT INTO test VALUES (NULL, NULL, 2);
INSERT INTO test VALUES (8, 2, 0);

3. Now run the following query
SELECT MAX( number1 - number2 ) AS maxwin
FROM test
WHERE number1 IS NOT NULL AND type
LIKE '%'

4. This should return max value correctly, in this case it is 13.

5. Next add this row (note that number2 > number1)
INSERT INTO test VALUES (2, 4, 0);

6. Now execute the query explained in step 3
-> max value is 18446744073709551614

Suggested fix:
Max value should be calculated correctly where there are null files inside the table.

[12 Nov 2003 13:46] Dean Ellis

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The return value appears to be what one would expect when subtracting unsigned integers in such a manner that the result would be negative.

[13 Nov 2003 3:54] Timo Leppänen

So why there isn't this kind of 'feature' in 3.23?

[13 Nov 2003 6:20] Sergei Golubchik

Because 3.23 uses signed arithmetics when subtracting unsigned numbers. You can turn this mode on in 4.0 by using --sql-mode=NO_UNSIGNED_SUBTRACTION (see the manual)

without NO_UNSIGNED_SUBTRACTION:

mysql> select 18446744073709551615-5, 18446744073709551610-18446744073709551615;
+------------------------+-------------------------------------------+
| 18446744073709551615-5 | 18446744073709551610-18446744073709551615 |
+------------------------+-------------------------------------------+
|   18446744073709551610 |                      18446744073709551611 |
+------------------------+-------------------------------------------+
1 row in set (0.00 sec)

with NO_UNSIGNED_SUBTRACTION:

mysql> select 18446744073709551615-5, 18446744073709551610-18446744073709551615; 
+------------------------+-------------------------------------------+
| 18446744073709551615-5 | 18446744073709551610-18446744073709551615 |
+------------------------+-------------------------------------------+
|                     -6 |                                        -5 |
+------------------------+-------------------------------------------+
1 row in set (0.00 sec)