Issue Details (XML | Word | Printable)

Key: CORE-2142
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Cosmin Apreutesei
Votes: 2
Watchers: 4
Operations

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

delete from T where T.C in (select T.C from T group by T.C having count(1) = 1) never stops

Created: 23/Oct/08 03:44 PM   Updated: 11/Oct/14 09:01 AM
Component/s: None
Affects Version/s: 2.1.1
Fix Version/s: None

Environment: fb 2.1.1 SS, linux32bit
Issue Links:
Relate
 


 Description  « Hide
my exact query was:

delete from streets where street_type in (
select s.street_type from streets s group by s.street_type having count(1) = 1
)

the streets table gets 12 rows in the subselect, and has a total of 21000 rows.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 24/Oct/08 01:11 AM
What plan does this query use?

Cosmin Apreutesei added a comment - 24/Oct/08 08:38 AM
Plan
PLAN SORT ((S NATURAL))
PLAN (STREETS NATURAL)

Adapted Plan
PLAN SORT ((S NATURAL)) PLAN (STREETS NATURAL)

.. I have no index on street_type.

Dmitry Yemanov added a comment - 24/Oct/08 10:53 AM
Then it sounds more like a "designed pitfall" rather than a bug. In Firebird, you have to index the street_type field in order to get a good performance.

Cosmin Apreutesei added a comment - 24/Oct/08 01:42 PM
I know, I just thought it's missing an opportunity for optimization since the inner select returns only 12 rows. It gets the feeling of never stopping (I killed the query after about 5 minutes) even when the inner select returns 0 rows.


Dmitry Yemanov added a comment - 24/Oct/08 01:59 PM
This is planned for improvement, maybe in the next version.