MySQL Bugs: #775: SELECT misses rows in Indexed HEAP table columns

Bug #775	SELECT misses rows in Indexed HEAP table columns
Submitted:	1 Jul 2003 13:54	Modified:	21 Nov 2003 14:31
Reporter:	Jonathan Urbach	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySQL-Max-4.0.11-0 (gamma)	OS:	Linux (Linux 2.4.18-14smp (RedHat 8))
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
When indexed fields of a HEAP table are used in a SELECT WHERE clause with '=', some expected rows are not returned. The same query on a MyISAM table containing the same data, however returns all the expected rows.
I think this relates to a relatively new feature in version 4, that of allowing null values in indexed columns. If I specify in the CREATE TABLE statement that the indexed columns are NOT NULL, then the select works properly. Unfortunately, by default, HEAP tables are created with DEFAULT NULL columns whether or not they are indexed. This leads to weird behavior in selects.

How to repeat:
Here is an example of the problem:

CREATE TABLE tmp_MutantGeneHits (
  MutantID int(11) default NULL,
  InsertGenomeID int(11) default NULL,
  Homolog varchar(255) default NULL,
  GeneName varchar(255) default NULL,
  PlateLocation varchar(5) default NULL,
  BlastScore decimal(6,1) default NULL,
  Description varchar(255) default NULL,
  BitScoreSeparation decimal(6,1) default NULL,
  KEY InsertGenomeID (InsertGenomeID),
  KEY MutantID (MutantID)
) TYPE=HEAP;

INSERT INTO tmp_MutantGeneHits VALUES (10,5,'PA2989','PA2989','1_A10',381.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits VALUES (10,6,'PA2989','PA2989','1_A10',317.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits VALUES (10,7,'PA2989','PA2989','1_A10',103.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits VALUES (10,8,'PA3780','PA3780','1_A10',42.1,'Hypothetical, unclassified, unknown;Membrane proteins',NULL);

CREATE TABLE tmp_MutantGeneHits2 (
  MutantID int(11) default NULL,
  InsertGenomeID int(11) default NULL,
  Homolog varchar(255) default NULL,
  GeneName varchar(255) default NULL,
  PlateLocation varchar(5) default NULL,
  BlastScore decimal(6,1) default NULL,
  Description varchar(255) default NULL,
  BitScoreSeparation decimal(6,1) default NULL,
  KEY InsertGenomeID (InsertGenomeID),
  KEY MutantID (MutantID)
) TYPE=MyISAM;

INSERT INTO tmp_MutantGeneHits2 VALUES (10,5,'PA2989','PA2989','1_A10',381.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits2 VALUES (10,6,'PA2989','PA2989','1_A10',317.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits2 VALUES (10,7,'PA2989','PA2989','1_A10',103.0,'Hypothetical, unclassified, unknown',NULL);
INSERT INTO tmp_MutantGeneHits2 VALUES (10,8,'PA3780','PA3780','1_A10',42.1,'Hypothetical, unclassified, unknown;Membrane proteins',NULL);

SELECT * FROM tmp_MutantGeneHits2 WHERE MutantID=10;
SELECT * FROM tmp_MutantGeneHits WHERE MutantID=10;

The last two select statements should both return 4 rows. However, in the case of the second statement which does a select on a HEAP table, it only returns one row.

Suggested fix:
Fix the indexing of HEAP table columns that may contain NULL values, or don't allow such columns.

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

I am experiencing this problem also. My platform is a windows 2000 professional. 

Why is the status of this bug "Closed" if its still a problem?

The closed status means: already fixed in this case since the developer
reported the changeset in the BK tree.

Below the current behavior:

mysql> select version();
+---------------+
| version()     |
+---------------+
| 4.0.16-max-nt |
+---------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM tmp_MutantGeneHits2 WHERE MutantID=10\G
*************************** 1. row ***************************
          MutantID: 10
    InsertGenomeID: 5
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 381.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
*************************** 2. row ***************************
          MutantID: 10
    InsertGenomeID: 6
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 317.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
*************************** 3. row ***************************
          MutantID: 10
    InsertGenomeID: 7
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 103.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
*************************** 4. row ***************************
          MutantID: 10
    InsertGenomeID: 8
           Homolog: PA3780
          GeneName: PA3780
     PlateLocation: 1_A10
        BlastScore: 42.1
       Description: Hypothetical, unclassified,
unknown;Membrane proteins
BitScoreSeparation: NULL
4 rows in set (0.00 sec)

mysql> SELECT * FROM tmp_MutantGeneHits WHERE MutantID=10\G
*************************** 1. row ***************************
          MutantID: 10
    InsertGenomeID: 8
           Homolog: PA3780
          GeneName: PA3780
     PlateLocation: 1_A10
        BlastScore: 42.1
       Description: Hypothetical, unclassified,
unknown;Membrane proteins
BitScoreSeparation: NULL
*************************** 2. row ***************************
          MutantID: 10
    InsertGenomeID: 7
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 103.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
*************************** 3. row ***************************
          MutantID: 10
    InsertGenomeID: 6
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 317.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
*************************** 4. row ***************************
          MutantID: 10
    InsertGenomeID: 5
           Homolog: PA2989
          GeneName: PA2989
     PlateLocation: 1_A10
        BlastScore: 381.0
       Description: Hypothetical, unclassified,
unknown
BitScoreSeparation: NULL
4 rows in set (0.00 sec)