Bug #40677 Archive tables joined on primary return no result
Submitted: 12 Nov 2008 20:42 Modified: 8 Mar 2010 11:02
Reporter: Geoffroy Cogniaux Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S1 (Critical)
Version:5.1/6.0 OS:Any
Assigned to: Satya B CPU Architecture:Any
Tags: archive, join, primary

[12 Nov 2008 20:42] Geoffroy Cogniaux
Description:
When archive tables are joined on their primary keys, the query returns no result when the optimizer chooses to use this index.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> create table a
    -> (
    -> id int not null auto_increment,
    -> name varchar(128) not null,
    -> PRIMARY KEY (id)
    -> )engine=archive;
Query OK, 0 rows affected (0.03 sec)

mysql> create table b
    -> (
    -> id int not null auto_increment,
    -> name varchar(128) not null,
    -> PRIMARY KEY (id)
    -> )engine=archive;
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> insert into a values(null,"a");
Query OK, 1 row affected (0.05 sec)

mysql> insert into b values(null,"b");
Query OK, 1 row affected (0.00 sec)

mysql> select a.id,b.id,a.name,b.name from a,b where a.id=b.id;
+----+----+------+------+
| id | id | name | name |
+----+----+------+------+
|  1 |  1 | a    | b    |
+----+----+------+------+
1 row in set (0.02 sec)

mysql>
mysql> insert into a values(null,"a");
Query OK, 1 row affected (0.00 sec)

mysql> insert into b values(null,"b");
Query OK, 1 row affected (0.00 sec)

mysql> select a.id,b.id,a.name,b.name from a,b where a.id=b.id;
Empty set (0.00 sec)
[12 Nov 2008 21:42] MySQL Verification Team
Thank you for the bug report. Verified as described.
[13 Nov 2008 16:44] Geoffroy Cogniaux
Its seems that a simple update of the table->status is missing in the function index_read_idx like it's done in others engines.
I think it should be :

if (found)
{
  table->status= 0;//notify handler that a record has been found
  DBUG_RETURN(0);
}
[20 Nov 2008 10:43] Satya B
Geoffroy,
Your solution seems to be correct, will be submitting patch with testcase
[20 Nov 2008 12:03] Bugs System
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/59360

2934 Satya B	2008-11-20
      Fix for BUG#40677 - Archive tables joined on primary return no result
      
      Problem
      =======
      Select queries on archive tables when joined on their primary keys
      returns no results(empty set)
      
      How it was solved
      =================
      Archive storage doesn't inform the handler about the fetched record 
      status when it is found. Fixed the archive storage engine to update
      the record status when it fetches successfully
      
      TestCase
      ========
      Modified archive.test testcase to test this functionality
[24 Nov 2008 15:54] Bugs System
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/59689

2934 Satya B	2008-11-24
      Fix for BUG#40677 - Archive tables joined on primary return no result
      
      Select queries on archive tables when joined on their primary keys
      returns no results(empty set)
      
      Archive storage doesn't inform the handler about the fetched record 
      status when it is found. Fixed the archive storage engine to update
      the record status when it fetches successfully
[20 Jan 2009 18:58] Bugs System
Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)
[2 Feb 2009 15:58] Tony Bedford
An entry was added to the 6.0.10 changelog:

When archive tables were joined on their primary keys, a query returned no result if the optimizer chose to use this index.
[25 Nov 2009 18:30] Bugs System
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/91689

2942 Magne Mahre	2009-11-25
      Bug#40677 Archive tables joined on primary return no result
      
      Select queries on archive tables when joined on their primary keys
      returns no results(empty set)
            
      Archive storage doesn't inform the handler about the fetched record 
      status when it is found. Fixed the archive storage engine to update
      the record status when it fetches successfully
[25 Nov 2009 18:30] Magne Mæhre
Backported to 5.6.0, and pushed to next-mr-runtime
6.0-codebase revid: 2933.1.1
[6 Mar 2010 11:05] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091210104731-27nl9weemor51ige) (merge vers: 5.6.0-beta) (pib:16)
[8 Mar 2010 11:02] Tony Bedford
Updated entry to be included in 5.5.3 changelog.