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:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18 OS:Mac OS X (Mac OSX)
Assigned to: Alexey Botchkov CPU Architecture:Any

[2 Jan 2006 3:45] Timothy Redmond

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:

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`)


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:


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.
[2 Jan 2006 7:48] Alexander Keremidarski
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
value_index: 1
 value_type: 3
short_value: String
 long_value: NULL
1 row in set, 1 warning (0.00 sec)

*************************** 1. row ***************************   Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: ''
1 row in set (0.00 sec)