Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Scalar subquery issue [CORE1413] #1831

Open
firebird-automations opened this issue Aug 14, 2007 · 3 comments
Open

Scalar subquery issue [CORE1413] #1831

firebird-automations opened this issue Aug 14, 2007 · 3 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: freemanzav (freemanzav)

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12810 ] => Firebird [ 15333 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.0.0 [ 10091 ]

Component: Engine [ 10000 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants