Issue Details (XML | Word | Printable)

Key: CORE-1413
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: freemanzav
Votes: 0
Watchers: 0
Operations

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

Scalar subquery issue

Created: 14/Aug/07 01:18 AM   Updated: 24/Oct/09 05:51 PM
Component/s: Engine
Affects Version/s: 2.0.0
Fix Version/s: None


 Description  « Hide
Server Version: LI-V2.0.0.12748 Firebird 2.0.
CREATE TABLE TABLE1 (
    FILED1 INTEGER,
    FILED2 INTEGER
);

INSERT INTO TABLE1 (FILED1, FILED2) VALUES (1, 0);
INSERT INTO TABLE1 (FILED1, FILED2) VALUES (2, 1);
INSERT INTO TABLE1 (FILED1, FILED2) VALUES (1, 0);
INSERT INTO TABLE1 (FILED1, FILED2) VALUES (2, 1);


CREATE INDEX TABLE1_IDX1 ON TABLE1 (FILED1, FILED2);

Query
------------------------------------------------
SELECT (SELECT FIRST 1 R1.FILED1 FROM TABLE1 R1 WHERE R1.FILED1 = R.FILED1
AND R1.FILED1 = (SELECT MAX(R2.FILED1) FROM TABLE1 R2) ) FROM TABLE1 R

Plan
------------------------------------------------
PLAN (R2 NATURAL)
PLAN (R1 INDEX (TABLE1_IDX1))
PLAN (R NATURAL)

Enchanced Info:
Indexed reads - 20

There are 20 unindexed readings. It seems like query "SELECT MAX(R2.FILED1) FROM TABLE1 R2 " executes for every iteration.




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.