| Bug #40677 | Archive tables joined on primary return no result | ||
|---|---|---|---|
| Submitted: | 12 Nov 2008 21:42 | Modified: | 25 Nov 19:30 |
| Reporter: | Geoffroy Cogniaux | ||
| Status: | Patch queued | ||
| Category: | Server: Archive | Severity: | S1 (Critical) |
| Version: | 5.1/6.0 | OS: | Any |
| Assigned to: | Satya B | Target Version: | 6.0-rc |
| Tags: | archive, join, primary | ||
| Triage: | Triaged: D2 (Serious) | ||
[12 Nov 2008 22:42]
Miguel Solorzano
Thank you for the bug report. Verified as described.
[13 Nov 2008 17: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 11:43]
Satya B
Geoffroy, Your solution seems to be correct, will be submitting patch with testcase
[20 Nov 2008 13: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 16: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 19: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 16: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 19: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 19:30]
Magne Mæhre
Backported to 5.6.0, and pushed to next-mr-runtime 6.0-codebase revid: 2933.1.1
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)