MySQLQuery on second iterations causes a skipped record

[adrhumphreys]

Affected Version

4.4.1

Description

Rewind is defined in SilverStripe\ORM\Connect\Query as the following:

public function rewind()
{
    if ($this->queryHasBegun && $this->numRecords() > 0) {
        $this->queryHasBegun = false;
        $this->currentRecord = null;
        return $this->seek(0);
    }
    return null;
}

Which calls seek defined in SilverStripe\ORM\Connect\MySQLQuery:

public function seek($row)
{
    if (is_object($this->handle)) {
        $this->handle->data_seek($row);
        return $this->nextRecord();
    }
    return null;
}

Seek calls data_seek which adjusts the result pointer to an arbitrary row in the result, in this case zero (the first record). We then return the next record, this changes the pointer to now be pointing to 1 (the second record).

This means that each time we call rewind we are effectively rewinding to the second record and not the first.

Steps to Reproduce

Create your example object:

class ExampleDataObject extends DataObject
{
    private static $db = ['Title' => 'Varchar'];

    private static $table_name = 'ye_ol_example';

    private static $default_records = [
        ['Title' => 'Example1'],
        ['Title' => 'Example2'],
        ['Title' => 'Example3'],
    ];
}

Run some code:

$query = DB::query("SELECT ID, TITLE FROM ye_ol_example");
$first = $query->map();
$second = $query->map();
Debug::show($first);
Debug::show($second);

Output:

Debug (_config.php:22) 1 = Example1 2 = Example2 3 = Example3
Debug (_config.php:23) 2 = Example2 3 = Example3

[sminnee]

Underlying issue here is that PDO pre-fetches the entire query result in to an array (which I think caused memory issues on big projects) and MySQL iterates the query as you go, and its rewind() behaviour seems to be broekn.

[robbieaverill]

Fixed in silverstripe/silverstripe-postgresql#99 and #9098

@sminnee there are some merge conflicts with these changes when merging 4 -> master. Would you mind taking a look at them when you have a minute? I'm not clear on the context enough to merge them since master has been updated to use generators