MySQL Bugs: #40130: Falcon date / time indexes broken

Bug #40130	Falcon date / time indexes broken
Submitted:	18 Oct 2008 17:19	Modified:	13 Dec 2008 10:03
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0-falcon-team	OS:	Any
Assigned to:	Lars-Erik BjÃ¸rk	CPU Architecture:	Any
Tags:	F_INDEX, pb2, pushbuild, test failure

Description:
When executing a simple workload of inserts and updates transactions (COMMIT only) against an indexed date or time field, Falcon returns different results than Innodb or MyISAM. At the end of the test, the databases have diverged.

No concurrency, no chill/thaw, no rollbacks and no LIMIT are involved. Server reports no errors when executing those queries. All data is valid and ISO-style strings.

How to repeat:
A test case will be uploaded shortly. The test case uses time columns and < and > comparisons on purpose in order to avoid any semblance to the recent YEAR bug (bug #39342).

Grammar file for bug 40130

Attachment: bug40130.yy (application/octet-stream, text), 304 bytes.

Data generation file for bug 40130

Attachment: bug40130.zz (text/plain), 169 bytes.

To reproduce with the Random Query Generator:

$ perl runall.pl \
  --basedir=/build/bzr/6.0-falcon-team \
  --vardir1=/build/vardir1 \
  --vardir2=/build/vardir2 \
  --mysqld1=--default-storage-engine=Falcon \
  --mysqld2=--default-storage-engine=Innodb \
  --threads=1 \
  --queries=50 \
  --gendata=conf/falcon_timestamp.zz \
  --grammar=conf/falcon_timestamp.yy \
  --validators=ResultsetComparator \
  --reporters=

This will run 50 non-concurrent COMMIT transactions against both Falcon and Innodb and will dump the differences between the databases afterwards.

Test case for bug 40130

Attachment: bug40130.test (application/octet-stream, text), 9.02 KiB.

To reproduce this bug, first record the expected output with innodb:

$ perl mysql-test-run.pl --record --mysqld=--default-storage-engine=Innodb bug40130.test

and then compare this to Falcon:

$ perl mysql-test-run.pl --mysqld=--default-storage-engine=Falcon bug40130.test

The tables diverge after the last transaction, whereas they were identical before it. The Falcon UPDATE does not appear to update all records that should have been updated.

This bug causes the test falcon_compare_innodb to fail in Pushbuild 2, so I am  tagging this as a test failure. Quoting Philip Stoev:

"so this test is unlikely to produce any meaningful output until this is fixed."

First symptom of this bug in the test log is currently somehing like:

# 00:53:49 Query: UPDATE `table1000` SET `year` = '14:15:41' WHERE `timestamp_key` > '2001-07-12 22:41:29' failed: affected_rows mismatch between servers (474 vs. 476)

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/57786

2899 lars-erik.bjork@sun.com	2008-11-04
      This is a commit for bug#40130 (Falcon date / time indexes broken)
      
      The bug was actually fixed by the patch to bug 40112 (Thanks
      Kevin:) ). This commit only includes a regression test for the bug.
      
      Two files are added, these are:
      
      mysql-test/suite/falcon/t/falcon_bug_40130.test
      mysql-test/suite/falcon/r/falcon_bug_40130.result

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/57797

2900 lars-erik.bjork@sun.com	2008-11-04
      This is a commit for bug#40130 (Falcon date / time indexes broken)
      
      The bug was actually fixed by the patch to bug 40112 (Thanks
      Kevin:) ). This commit only includes a regression test for the bug.
      
      Two files are added, these are:
      
      mysql-test/suite/falcon/t/falcon_bug_40130.test
      mysql-test/suite/falcon/r/falcon_bug_40130.result

The way the bug used to show itself was that the second last value returned by the last 'select *' statement would not be changed by the previous update statement although the second last 'select *' statement clearly shows that it should have been.

A commit (and push) lacking the bug number in the message is available at

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

This commit just removes the IGNORE keyword from the INSERT statements in the test

Pushed into 6.0.8-alpha  (revid:lars-erik.bjork@sun.com-20081104145159-2y8ny03960o21fhp) (version source revid:lars-erik.bjork@sun.com-20081104145159-2y8ny03960o21fhp) (pib:5)

A note has been added to the 6.0.8 changelog: 

The indexes and record contents of a FALCON table could get out of synchronization during a lrge number of updates. Because FALCON returns data only if it matches both the index and record data the result sets returned could be invalid when comparing the results of an index and non-index based SELECT.