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

[firebird-automations]

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]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE5394 [ CORE5394 ]

[firebird-automations]

Modified by: @dyemanov

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

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by CORE6394 [ CORE6394 ]