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.