batch()/each() break without exception when set with()

[paulzi]

Table "user" has 10000 records

$query = User::find();
foreach ($query->each() as $user) {
    echo $user->id; // out 10000 ids
}

but:

$query = User::find()->with('profile');
foreach ($query->each() as $user) {
    echo $user->id; // out 100 ids
}

each() break without exception. It is not normal, because User may have a default scope.

[resurtm]

Not able to reproduce with the small portions of data (five users with five profiles) and the following code:

$query = User::find()->with('profile');
foreach ($query->each(2) as $user) {
    echo $user->id . '<br/>';
}

// ...

class User extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 't_user';
    }

    public function getProfile()
    {
        return $this->hasOne(Profile::className(), ['user_id' => 'id']);
    }
}

// ...

class Profile extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 't_profile';
    }

    public function getUser()
    {
        return $this->hasOne(User::className(), ['id' => 'user_id']);
    }
}

Trying to generate the same amount of data and test this again ATM. Also @paulzi, it would be nice to see your models' declarations, relations, DDL, and final (MySQL?) queries.

[samdark]

@resurtm thanks for trying to reproduce it.

[paulzi]

I have a bug in MS SQL Server 2014, throught FreeTDS.
The bug is present even when ->each(2), when the amount of data in the more than two (get only two rows).

[resurtm]

Somehow related to #10305 and #10371. The query generated by code above is:

SELECT * FROM `t_profile`
WHERE `user_id` IN (2901 /* other 2098+ integer values here */ 3000)

@paulzi, the fix (or maybe the workaround) is to set $batchSize parameter of the each() method to less values. Try $query->each(50) or even $query->each(10).

[paulzi]

The problem is not in with(), and the other query during each():

// without other queries
$result = 0;
foreach (User::find()->each(2) as $user) {
    $result++;
}
echo $result; // echo 16715

// with other queries
$result = 0;
foreach (User::find()->each(2) as $user) {
    $result++;
    Yii::$app->db->createCommand("SELECT 1")->queryScalar(); // other query!
}
echo $result; // echo 2

But i test in clear PDO, likely problems in dblib:

<?php
$dbh = new PDO('dblib:host=192.168.10.202;dbname=test', 'test', '******');

$query = $dbh->query('SELECT id from tbl_user');
$query->execute();

$result = [];
$i = 0;
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
    $result[] = $row;

    // second query
    $query2 = $dbh->query('SELECT 1');
    $query2->execute();
    $result2 = $query2->fetchAll(PDO::FETCH_ASSOC);
    $query2->closeCursor();
    $query2 = null;
    $result2 = null;

    $i++;
    if ($i > 10) {
        break;
    }
}
echo count($result); // echo 1

[o-rey]

As of SQL Server, that's a feature, not a bug.
https://bugs.php.net/bug.php?id=65945

[aliechti]

#10023