| Bug #42114 | aggregation function with a null criterion and indexing returns wrong data | ||
|---|---|---|---|
| Submitted: | 14 Jan 2009 15:06 | Modified: | 17 Aug 2011 13:50 |
| Reporter: | nadav wexler | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.1.30 | OS: | Linux (FC6) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | INDEX, MAX, null | ||
[14 Jan 2009 19:09]
Valeriy Kravchuk
Verified just as described with 5.1.30 on Windows:
C:\...Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3307 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.30-enterprise-gpl-advanced MySQL Enterprise Server - Advance
d Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table a;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE a (b int not null) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO a (b) values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT MAX(b) FROM a where b > 2;
+--------+
| MAX(b) |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b <2;
+--------+
| MAX(b) |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b < NULL;
+--------+
| MAX(b) |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b > NULL;
+--------+
| MAX(b) |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> ALTER TABLE a ADD INDEX (b);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT MAX(b) FROM a where b > 2;
+--------+
| MAX(b) |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b <2;
+--------+
| MAX(b) |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b > NULL
-> ;
+--------+
| MAX(b) |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b <NULL;
+--------+
| MAX(b) |
+--------+
| 3 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b > NULL;
+--------+
| MAX(b) |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> SELECT MAX(b) FROM a where b < NULL;
+--------+
| MAX(b) |
+--------+
| 2 |
+--------+
1 row in set (0.00 sec)
mysql> explain select MAX(b) FROM a where b < NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)
[17 Aug 2011 13:50]
Jon Stephens
Duplicate of BUG#47762.
Description: preforming an aggregate function over data, with a where condition evaluating to NULL and index on the relevant column returns wrong and inconsistent data. How to repeat: -------------- DROP TABLE IF EXISTS a -------------- -------------- CREATE TABLE a (b int not null) -------------- -------------- INSERT INTO a (b) values (1),(2),(3),(4) -------------- -------------- SELECT MAX(b) FROM a where b > 2 -------------- +--------+ | MAX(b) | +--------+ | 4 | +--------+ -------------- SELECT MAX(b) FROM a where b < 2 -------------- +--------+ | MAX(b) | +--------+ | 1 | +--------+ -------------- SELECT MAX(b) FROM a where b > NULL -------------- +--------+ | MAX(b) | +--------+ | NULL | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | NULL | +--------+ -------------- ALTER TABLE a ADD INDEX (b) -------------- -------------- SHOW CREATE TABLE a -------------- *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `b` int(11) NOT NULL, KEY `b` (`b`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -------------- SELECT MAX(b) FROM a where b > 2 -------------- +--------+ | MAX(b) | +--------+ | 4 | +--------+ -------------- SELECT MAX(b) FROM a where b < 2 -------------- +--------+ | MAX(b) | +--------+ | 1 | +--------+ -------------- SELECT MAX(b) FROM a where b > NULL -------------- +--------+ | MAX(b) | +--------+ | NULL | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | 3 | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | 2 | +--------+ -------------- SELECT MAX(b) FROM a where b > NULL -------------- +--------+ | MAX(b) | +--------+ | NULL | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | 3 | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | 2 | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | 1 | +--------+ -------------- SELECT MAX(b) FROM a where b < NULL -------------- +--------+ | MAX(b) | +--------+ | NULL | +--------+ -------------- SELECT VERSION() -------------- +-----------+ | VERSION() | +-----------+ | 5.1.30 | +-----------+