Bug #46961 archive engine loses rows during join when results approach join_buffer_size

Description:
In 5.1, archive engine loses records during bulk insert.  MyISAM and InnoDB tables both contain 59052 records, whereas archive ends up with 29781.  Output of testcase:

mysql> drop table if exists t2;
Query OK, 0 rows affected (1.97 sec)

mysql> create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam;
Query OK, 0 rows affected (0.95 sec)

mysql> insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.66 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
    -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 59049 rows affected (1 min 15.50 sec)
Records: 59049  Duplicates: 0  Warnings: 0

mysql>
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|    59052 |
+----------+
1 row in set (0.20 sec)

mysql>
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive;
Query OK, 0 rows affected (0.27 sec)

mysql> insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.20 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
    -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 29778 rows affected (51.75 sec)
Records: 29778  Duplicates: 0  Warnings: 0

mysql>
mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|    29781 |
+----------+
1 row in set (0.06 sec)

How to repeat:
execute these two identical sql (except for table engine):

----
drop table if exists t2;
create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam;
insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;

select count(*) from t2;
-----

-----
drop table if exists t2;
create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive;
insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;

select count(*) from t2;
------

now compare the count(*) result. they are wildly different!

Suggested fix:
insert all the rows!

the select is to blame, not the insert.

select t2.a,t2.b,t2.c,t2.d from
t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;

where t2 is archive doesn't return all the rows!

Note, however, that changing the query this way, it selects all the rows:

select t2.a,t2.b,t2.c,t2.d from
t2 j, t2 a, t2 b, t2 c, t2 d, t2 e, t2 f, t2 g, t2 h, t2 ;

Still a bug, but more interesting :)

Thank you for the bug report. Verified as described.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.39-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.1 >create table t2(a char(255),b char(255),c char(255),d char(255)) engine=myisam;
Query OK, 0 rows affected (0.10 sec)

mysql 5.1 >insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1 >insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
    -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 59049 rows affected (0.68 sec)
Records: 59049  Duplicates: 0  Warnings: 0

mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
|    59052 |
+----------+
1 row in set (0.00 sec)

mysql 5.1 >drop table if exists t2;
Query OK, 0 rows affected (0.01 sec)

mysql 5.1 >create table t2(a char(255),b char(255),c char(255),d char(255)) engine=archive;
Query OK, 0 rows affected (0.11 sec)

mysql 5.1 >insert into t2 values ('a','b','c','d'),('a','b','c','d'),('a','b','c','d');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.1 >insert into t2 (a,b,c,d) select t2.a,t2.b,t2.c,t2.d from
    -> t2,t2 a,t2 b,t2 c,t2 d,t2 e,t2 f,t2 g,t2 h,t2 i;
Query OK, 29778 rows affected (0.44 sec)
Records: 29778  Duplicates: 0  Warnings: 0

mysql 5.1 >select count(*) from t2;
+----------+
| count(*) |
+----------+
|    29781 |
+----------+
1 row in set (0.04 sec)

mysql 5.1 >

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

3109 Sergey Vojtovich	2009-09-04
      BUG#46961 - archive engine loses rows during self joining select!
      
      SELECT with join (not only self-join) from archive table may
      return incomplete result set, when result set size exceeds
      join buffer size.
      
      The problem was that archive row counter was initialzed too
      early, when ha_archive::info() method was called. Later,
      when optimizer exceeds join buffer, it attempts to reuse
      handler without calling ha_archive::info() again (which is
      correct).
      
      Fixed by moving row counter initialization from
      ha_archive::info() to ha_archive::rnd_init().
     @ mysql-test/r/archive.result
        A test case for BUG#46961.
     @ mysql-test/t/archive.test
        A test case for BUG#46961.
     @ storage/archive/ha_archive.cc
        Since a cursor may get reused without a call to ::info(),
        move assignment of scan_rows to a proper place, that is
        ::rnd_init().

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)

An entry was added to the 5.4.4 changelog:

The Archive storage engine lost records during a bulk insert.

Moved 5.4 changelog entry from 5.4.4 to 5.4.3.

Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Updated changelog to include versions 5.1.40 and 5.1.41-ndb-7.1.0.

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Updated changelog to include versions 5.1.41-ndb-6.3.31 and 5.1.41-ndb-7.0.11.