MySQL Bugs: #47124: Why do I need another rnd_next call to give EOF to the server?

Bug #47124	Why do I need another rnd_next call to give EOF to the server?
Submitted:	4 Sep 2009 7:52	Modified:	21 Nov 2014 8:55
Reporter:	Horst Hunger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Storage Engine API	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, azalea bzr	OS:	Any
Assigned to:	Magnus Blåudd	CPU Architecture:	Any
Tags:	API, plugin, storage engine

Description:
That's only a small thing, but should not be forgotten.
The following shows the method calls made during a nine-row table scan and shows a separate rnd_next call to give EOF (HA_ERR_END_OF_FILE) to the server:

store_lock
external_lock
info
rnd_init
extra - ENUM HA_EXTRA_CACHE   Cache record in HA_rrnd()
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next
rnd_next (delivers only HA_ERR_END_OF_FILE, no values)
extra - ENUM HA_EXTRA_NO_CACHE   End caching of records (def)
external_lock
extra - ENUM HA_EXTRA_RESET   Reset database to after open

Please, check if it is possible to check EOF with the last rnd_next giving values, so that indeed only 9 rnd_next calls will be needed to give 9 rows to the server.

How to repeat:
Look into the TSE code and change it as described below:
int ha_tse::rnd_next(uchar *buf)
{
  int rc= 0;
  DBUG_ENTER("ha_tse::rnd_next");
  int size;
  int i;
  int line_begin;
  int line_end;
...
  for (Field **field=table->field ; *field ; field++)
  {
...
  }
...
  if (next_position >= local_saved_data_file_length)
      DBUG_RETURN(HA_ERR_END_OF_FILE);
  stats.records++;
  next_position= line_begin;
  DBUG_RETURN(rc);
}

instead of 

int ha_tse::rnd_next(uchar *buf)
{
  int rc= 0;
  DBUG_ENTER("ha_tse::rnd_next");
  int size;
  int i;
  int line_begin;
  int line_end;
...
  if (next_position >= local_saved_data_file_length)
      DBUG_RETURN(HA_ERR_END_OF_FILE);
...
  for (Field **field=table->field ; *field ; field++)
  {
...
  }
...
  stats.records++;
  next_position= line_begin;
  DBUG_RETURN(rc);
}

Thank you for the report.

Verified as described using following test:

create table t1(f1 int not null) engine=csv;
insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9);
select * from t1;

Run it as $./mtr bug47124 --debug, then count:

$cat var/log/mysqld.1.trace | grep rnd_next | wc -l
20

As this is exists in version 5.0 as well probably this should be not SE bug, but optimizer?

This is by design and it's totally normally you need to return
HA_ERR_END_OF_FILE when there are no more records. It's not possible to
return a record AND returnh HA_ERR_END_OF_FILE. If there is a record in the
buffer you return 0!

As an example think about the case where there are no records at all in the
table:

rnd_init()
 - returns zero, indicating no error
rnd_next()
 - return HA_ERR_END_OF_FILE, there are no record(s)
rnd_end()

Or where there are one record:
rnd_init()
 - returns zero, indicating no error
rnd_next()
 - filld in the record and return 0 to indicate there is a record.
rnd_next()
 - return HA_ERR_END_OF_FILE, there are no record(s)
rnd_end()

How would you distinguish the case where there is a record and also returned
HA_ERR_END_OF_FILE? I mean even if you are at the end, the HA_ERR_END_OF_FILE
must not be return until next call.