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

[1 Jul 2003 13:54] Jonathan Urbach
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.
[3 Jul 2003 6:27] Ramil Kalimullin
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
[21 Nov 2003 14:08] [ name withheld ]
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?
[21 Nov 2003 14:31] Miguel Solorzano
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)