Issue Details (XML | Word | Printable)

Key: CORE-634
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Vlad Khorsun
Reporter: Pavel Cisar
Votes: 3
Watchers: 3

If you were logged in you would be able to see more operations.
Firebird Core

Bad treatment of FIRST/SKIP in subselect

Created: 08/Nov/01 12:00 AM   Updated: 18/Sep/16 09:19 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 1, 4.0 Initial, 3.0.0

Issue Links:

SF_ID: 479483
QA Status: Covered by another test(s)
Test Details: See test core_3362_basic.fbt

 Description  « Hide
SFID: 479483#
Submitted By: pcisar

Commands like

delete from table where col in (select col first 5
from table);

doesn't delete up to five rows, but whole table.
Obviously subselect is executed for each row in master
table, but it should not when FIRST is in game.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alice F. Bird added a comment - 14/Jun/06 09:41 AM
Date: 2004-07-19 20:38
Sender: serralta
Logged In: YES

When this will be fixed ? I think its a very important fix..

Alice F. Bird added a comment - 14/Jun/06 09:41 AM
Date: 2002-07-17 23:59
Sender: nobody
Logged In: NO

yea, there is big problem with correlated subqueries in IB
(and also firebird). some cases are described in PLANalyzer
help, but it is not complete and there is more situations,
where IB takes subquery as correlated, even it may not.

Eugenk Konkov added a comment - 26/Dec/07 10:03 AM
Another testcase for subqueries:

create table test(id int, val char(5));
insert into test values(1, 'red');
insert into test values(2, 'green');
insert into test values(3, 'blue');
insert into test values(2, 'green');*/
delete from test
where test.ID in (select id from test GROUP BY id HAVING count(id)>1);

Results for "select * from test" must be:
id value
-- -----
01 red
03 blue

and NOT:
01 red
02 green
03 blue