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

Bug #36097	Falcon: searches fail after repeated inserts
Submitted:	15 Apr 2008 19:10	Modified:	5 Oct 2008 17:11
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server: Falcon	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Kevin Lewis	Target Version:	6.0.6
Triage:	D2 (Serious) / R2 (Low) / E3 (Medium)

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.