Repeated queries on the same instance of Selection

6 years ago

DavidKrmela
Member | 5
+
0
-

In version 2.1.0 I discovered the bad behavior after repeated queries on the same instance of Nette\Database\Selection with cached columns from previous request.

First request:

$selection = $context->table('test')->limit(3); // $context is instance of Nette\Database\Context

foreach ($selection as $row) {
    $row->id;
}

Queries:

SELECT `id` FROM `test` LIMIT 3
// ok: returns rows [1, 2, 3]

Second request:

$selection = $context->table('test')->limit(3);

foreach ($selection as $row) {
    $row->id;
    $row->name; // we added $row->name
}

foreach ($selection->limit(3, 3) as $row) { // new query on the same selection
    $row->id;
}

Queries:

SELECT `id` FROM `test` LIMIT 3
// ok: returns rows [1, 2, 3]

SELECT * FROM `test` WHERE (`test`.`id` IN (1, 2, 3))
// ok: returns refreshed rows [1, 2, 3]

SELECT * FROM `test` WHERE (`test`.`id` IN (1, 2, 3)) LIMIT 3 OFFSET 3
// fail: nothing returns
// it should be: 'SELECT * FROM `test` LIMIT 3 OFFSET 3' and returns [4, 5, 6]

6 years ago

hrach
Member | 1816
+
0
-

Isn't possible you don't have to more than 3 entries? This works for me…
If you have still problem, please provide sandbox with this bug… thanks!

6 years ago

DavidKrmela
Member | 5
+
0
-

Here it is: nette-2.1.0-database-bug.zip

You only need to import sql and run /www/bug

Problem is that Selection when refreshing rows (because missing columns) adds condition ‘WHERE primary_key IN(…keys)’ and this condition there remains for next queries. On next request it's ok because is no need to refresh rows.

6 years ago

hrach
Member | 1816
+
0
-

Really nice catch! Thanks! Will be fixed in few minutes!

6 years ago

Milo
Nette Core | 1149
+
0
-

@hrach Didn't we discuss somewhere, that OFFSET, LIMIT must not be used without ORDER BY? Result is unpredictable otherwise.

6 years ago

hrach
Member | 1816
+
0
-

Yes, we discussed that our refetch algorithm should take care of it. And the bugfix for that introduced this bug.

Btw, fixed :)

Last edited by hrach (2014-01-15 22:41)