[#CORE-634] Bad treatment of FIRST/SKIP in subselect

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:

Duplicate

This issue is duplicated by:

~~CORE-3862~~ Gouping error on deleting

Relate

This issue relate to:

~~CORE-3317~~ Success of deleting rows depending on order of row insertion

~~CORE-3362~~ Cursors should ignore changes made by the same statement

This issue is related to:

~~QA-501~~ Test for CORE-634

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:

[ Permalink | « Hide ]

Alice F. Bird added a comment - 14/Jun/06 09:41 AM

Date: 2004-07-19 20:38
Sender: serralta
Logged In: YES
user_id=1086939

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

[ Permalink | « Hide ]

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.

[ Permalink | « Hide ]

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