Repeated queries on the same instance of Selection

Notice: This thread is very old.
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]
hrach
Member | 1834
+
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!

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.

hrach
Member | 1834
+
0
-

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

Milo
Nette Core | 1283
+
0
-

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

hrach
Member | 1834
+
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)