Issue Details (XML | Word | Printable)

Key: CORE-1287
Type: Improvement Improvement
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Alexander Tyutik
Votes: 12
Watchers: 7
Operations

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

Optimizer improvements when WHERE clause always evaluates to FALSE

Created: 30/May/07 06:30 AM   Updated: 08/Nov/16 02:29 PM
Component/s: None
Affects Version/s: 2.1 Alpha 1
Fix Version/s: None

Issue Links:
Duplicate
 


 Description  « Hide
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 usefull. 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.

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