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

Avoid data retrieval if the WHERE clause always evaluates to FALSE [CORE1287] #1708

Closed
firebird-automations opened this issue May 30, 2007 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

firebird-automations commented May 30, 2007

Submitted by: Alexander Tyutik (tut)

Is duplicated by CORE6394
Is duplicated by CORE5394

Votes: 14

In query like this

SELECT * FROM SOME_TABLE WHERE 1 = 0

It is obviously that condition in WHERE clause is always FALSE, but FB will read all records from SOME_TABLE without any benefits.

Now real example where this improvement can be useful. I need to write recursive CTE and specify level deep limit. I wrote such test query:

EXECUTE BLOCK
RETURNS (
  ID INTEGER,
  LEV SMALLINT)
AS
  DECLARE MAX_LEV SMALLINT = 0;
BEGIN
  FOR WITH RECURSIVE TR AS (
      SELECT T.*, 1 AS LEV FROM "Tree" T WHERE PARENT_ID IS NULL
      UNION ALL
      SELECT T.*, TR.LEV + 1 AS LEV FROM "Tree" T, TR WHERE PARENT_ID = TR.ID AND TR.LEV < :MAX_LEV)
    SELECT ID, LEV FROM TR INTO :ID, :LEV AS CURSOR CUR DO
  BEGIN
    SUSPEND;
  END
END

and in this query part

SELECT T.*, TR.LEV + 1 AS LEV FROM "Tree" T, TR WHERE PARENT_ID = TR.ID AND TR.LEV < :MAX_LEV

will be make unnecessary work for each leaf and performance will be worse.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 12231 ] => Firebird [ 15586 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE5394 [ CORE5394 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Optimizer improvements when WHERE clasuse always evaluates to FALSE => Optimizer improvements when WHERE clause always evaluates to FALSE

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE6394 [ CORE6394 ]

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

3 participants