MySQL Bugs: #16121: creating a table with a specific key statement causes select to fail

Bug #16121	creating a table with a specific key statement causes select to fail
Submitted:	2 Jan 2006 3:45	Modified:	27 Jul 2006 7:26
Reporter:	Timothy Redmond	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.18	OS:	MacOS (Mac OSX)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:

I can create a table in a database where the select statement behaves unexpectedly.  Specifically the select statement returns the empty set where I think that it should return exactly one row.  It appears that the problem occurs because of a single line (the KEY statement) in the definition of the table.  It is not at all clear why because I would not expect the select statement to use the Key.

It seems that I can probably work around this problem by removing the  KEY statements.

How to repeat:

Create a database.   Then define and fill in a table with the commands:

DROP TABLE IF EXISTS `testt`;
CREATE TABLE `testt` (
  `frame` int(11) NOT NULL,
  `frame_type` smallint(6) NOT NULL,
  `slot` int(11) NOT NULL,
  `facet` int(11) NOT NULL,
  `is_template` bit(1) NOT NULL,
  `value_index` int(11) NOT NULL,
  `value_type` smallint(6) NOT NULL,
  `short_value` varchar(255) default NULL,
  `long_value` mediumtext,
  KEY `Testt_I1` (`frame`,`slot`,`facet`,`is_template`,`value_index`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `testt` VALUES 
       (10551,7,2002,0,'\0',1,3,'Testt-db_Slot_0',NULL),
       (10551,7,2006,0,'\0',1,6,'1004',NULL),
       (10551,7,2014,0,'\0',1,3,'String',NULL),
       (10551,7,2017,0,'\0',1,1,'1',NULL),
       (10551,7,2043,0,'\0',1,6,'10478',NULL),
       (10551,7,2002,0,'\0',0,3,'hasJob',NULL);

Now try the select statement:

select * from testt
       where frame = 10551 and 
             slot = 2014 and 
             is_template='\0' and 
             facet = 0;

I think that this should  return the row:

     (10551,7,2014,0,'\0',1,3,'String',NULL)

Instead I get the empty set.  I tried removing individual conjuncts in the where clause one at a time and they all seem to work correctly.  If you remove the KEY clause in the table the select statement works as I expect.

Thanks for the excellent bug report!

As noted by the reporter the problem is with
KEY `Testt_I1` (`frame`,`slot`,`facet`,`is_template`,`value_index`)

Disabling this key produces correct result. Note the Warning below. Sounds like a separate bug with BIT(1):

select * from testt  IGNORE INDEX(Testt_I1)
   where frame = 10551 and
               slot = 2014 and
               is_template='\0' and
               facet = 0\G
*************************** 1. row ***************************
      frame: 10551
 frame_type: 7
       slot: 2014
      facet: 0
is_template:
value_index: 1
 value_type: 3
short_value: String
 long_value: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************   Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: ''
1 row in set (0.00 sec)