MySQL Bugs: #31833: ORDER BY leads to wrong result when ARCHIVE, BLOB and table cache is full

Bug #31833	ORDER BY leads to wrong result when ARCHIVE, BLOB and table cache is full
Submitted:	25 Oct 2007 7:09	Modified:	10 Jan 2008 17:46
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Archive storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23-bk	OS:	Linux
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
SELECT statement with ORDER BY clause applied to ARCHIVE table with BLOB column can produce a wrong result when table cache is full. See How-to-repeat section for details.

How to repeat:
# This is script for 'mysqltest' tool which reproduces the problem

connect (addconroot1, localhost, root,,test);

# We use 't1' table and huge 'lock tables' statement below to exceed the size of
# table cache (it is 64 by default) 
CREATE TABLE t1 (i int);
LOCK TABLES
t1 AS h1 READ, t1 AS h2 READ, t1 AS h3 READ, t1 AS h4 READ, t1 AS h5 READ, t1 AS h6 READ, t1 AS h7 READ, t1 AS h8 READ,
t1 AS j1 READ, t1 AS j2 READ, t1 AS j3 READ, t1 AS j4 READ, t1 AS j5 READ, t1 AS j6 READ, t1 AS j7 READ, t1 AS j8 READ,
t1 AS k1 READ, t1 AS k2 READ, t1 AS k3 READ, t1 AS k4 READ, t1 AS k5 READ, t1 AS k6 READ, t1 AS k7 READ, t1 AS k8 READ,
t1 AS l1 READ, t1 AS l2 READ, t1 AS l3 READ, t1 AS l4 READ, t1 AS l5 READ, t1 AS l6 READ, t1 AS l7 READ, t1 AS l8 READ,
t1 AS m1 READ, t1 AS m2 READ, t1 AS m3 READ, t1 AS m4 READ, t1 AS m5 READ, t1 AS m6 READ, t1 AS m7 READ, t1 AS m8 READ,
t1 AS n1 READ, t1 AS n2 READ, t1 AS n3 READ, t1 AS n4 READ, t1 AS n5 READ, t1 AS n6 READ, t1 AS n7 READ, t1 AS n8 READ,
t1 AS b1 READ, t1 AS b2 READ, t1 AS b3 READ, t1 AS b4 READ, t1 AS b5 READ, t1 AS b6 READ, t1 AS b7 READ, t1 AS b8 READ,
t1 AS v1 READ, t1 AS v2 READ, t1 AS v3 READ, t1 AS v4 READ, t1 AS v5 READ, t1 AS v6 READ, t1 AS v7 READ, t1 AS v8 READ,
t1 AS c1 READ, t1 AS c2 READ, t1 AS c3 READ, t1 AS c4 READ, t1 AS c5 READ, t1 AS c6 READ, t1 AS c7 READ, t1 AS c8 READ;

connection default;

CREATE TABLE `t2` (
`a` int(11) NOT NULL auto_increment,
b blob(12),
c int,
KEY  (`a`)
) engine=archive;

SELECT * FROM t2;
INSERT INTO t2 VALUES (NULL, "foo", NULL);
INSERT INTO t2 VALUES (NULL, "We the people", 5);
INSERT INTO t2 VALUES (NULL, "in order to form a more pefect union", 9);
INSERT INTO t2 VALUES (NULL, "establish justice", NULL);
INSERT INTO t2 VALUES (NULL, NULL, NULL);
INSERT INTO t2 VALUES (32, "ensure domestic tranquility", NULL);
INSERT INTO t2 VALUES (23, "provide for the common defense", 30);
INSERT INTO t2 VALUES (NULL, "fo fooo", 70);
INSERT INTO t2 VALUES (NULL, NULL, 98);
INSERT INTO t2 VALUES (NULL, "promote the general welfare", 50);
# This produces some rows in result
SELECT * FROM t2;
# And these statements are not... Which is a bug!
SELECT * FROM t2 ORDER BY a;
SELECT * FROM t2 ORDER BY a DESC;

Thank you for the report.

Verified as described.

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

ChangeSet@1.2641, 2007-11-23 17:51:12+04:00, svoj@mysql.com +3 -0
  BUG#31833 - ORDER BY leads to wrong result when ARCHIVE, BLOB and
              table cache is full
  
  After reading last record from freshly opened archive table
  (e.g. after flush table, or if there is no room in table cache),
  the table is reported as crashed.
  
  The problem was that azio wrongly invalidated azio_stream when it
  meets EOF.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

A note has been added tot he 6.0.5 and 5.1.23 changelogs: 

Using ORDER BY leads to the wrong result when using the ARCHIVE on a table with a BLOB when the table cache is full. The table may also be reported as crashed after the query has completed, even though the table data was in tact.