| 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: | |
| 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 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.
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)