MySQL Bugs: #36097: Falcon: searches fail after repeated inserts

Bug #36097	Falcon: searches fail after repeated inserts
Submitted:	15 Apr 2008 17:10	Modified:	5 Oct 2008 15:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
I create a Falcon table t with an indexed VARCHAR(10) column.
I insert 96 rows, using 10-character ASCII values.
I say "INSERT INTO t SELECT * FROM t" 12 times.
I say "SELECT * FROM t WHERE n LIKE 'n%'".
The result is wrong. Rows are missing.

How to repeat:
delimiter //
set @@autocommit=0//
drop table if exists t//
drop procedure if exists p//
create procedure p ()
begin
  declare v int default 32;
  declare ch char(8);
  create table t (s1 int, s2 varchar(10) character set latin1) engine=falcon;
  create index i3 on t (s2);
  while v < 128 do
    begin
      set ch = hex(v);
      if length(ch) < 2 then set ch = concat('0',ch); end if;
      set @x = concat('insert into t values (',v,',repeat(0x',ch,',10))');
      prepare stmt1 from @x;
      execute stmt1;
      end;
    set v = v + 1;
    if v mod 100 = 0 then commit; end if;
    end while;
  end//
delimiter ;
call p();
select count(*) from t where s2 like 'n%';       /* 2 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 4 rows */ 
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 8 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 16 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 32 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 64 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 128 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 256 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 512 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 1024 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 2048 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 4096 rows */
insert into t select * from t;
select count(*) from t where s2 like 'n%';       /* 910 rows */

Thank you for the bug report. Verified as described on Suse 10.3 X64.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46227

ChangeSet@1.2659, 2008-04-30 08:55:32-05:00, klewis@klewis-mysql. +2 -0
  Bug#36097 - Be sure the highWater index is set to zero when a record is thawed.  
  Thawing memsets the field offset vector to zero and only addes the first offset,
  to the beginning of the record.  Record::getEncodedValue must have a zero 
  highWater offset so it will recalculate the offsets of the fields in that record.

This test worked fine until records started to chill in the larger INSERT transactions.  The following SELECT statement caused the records to be thawed again.  And the SELECT statements were looking for fields other than the first field.

Each record contains a data buffer that stores the encoded record. Before the actual record, allocRecordData() allocates an ectra vector of 2-byte offsets that are used to find the actual offset into the encoded record of each field, once they have been found the first time.  Initially this vector is memset to zero and the offset of the first field (the record start) is added into the first vector index.  The rest of the vector is initially memset to zero.

In order to decode a field, Record::getEncodedValue() needs to find it within the record.  In order to find field index 5, for example, it will decode the record field by field and record the offsets of fields 1 through 5 in the vector.  Then it will set the highWater value to 5. The next time, if it needs field 8, it only needs to decode fields 6 thru 8.  

When an record is initially created, the highWater value is zero.  It was not being reset to zero when the record is thawed.  This should happen in Record::setRecordData() just like it does in Record::setEncodedRecord().

Fix is in version 6.0.6

Documented bugfic in the 6.0.6 changelog as follows:

        A SELECT ... LIKE query issued following a number of INSERT statements
        on a Falcon table failed to return all matching records.