Issue Details (XML | Word | Printable)

Key: CORE-5674
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Vlad Khorsun
Votes: 0
Watchers: 1
Operations

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

Allow unused Common Table Expressions

Created: 01/Dec/17 09:48 AM   Updated: 12/Dec/17 11:35 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.5.8, 3.0.3, 4.0 Beta 1

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
When fixing bug CORE-1793 unused CTEs was forbidden - it was simplest solution to avoid crash in the engine.
Crash happens due to uninitialized input parameters as unused CTE is not handled by syntax analyzer at all.
But it is too stong limitation and users complains about it (not often but anyway).

Improvement is to allow queries with unused CTEs and convert error "CTE "XXX" is not used in query" into warning.

Also, it will handle unused CTEs by syntax analyzer and catch all errors, for example

WITH RECURSIVE
  X AS (SELECT R.RDB$RELATION_NAME FROM RDB$RELATIONS1 R WHERE R.RDB$RELATION_ID = 1),
  Y AS (SELECT R.RDB$RELATION_NAME FROM RDB$RELATIONS R WHERE R.RDB$RELATION_ID = :ID2)
SELECT * FROM Y

currently works despite of reference on non-existing table RDB$RELATIONS1 in CTE X


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 12/Dec/17 06:30 AM - edited
Consider CTE in which there are two cases where first of its part (say, its name is: 'A') can be called:
1) E ==> A
2) B ==> C ==> D ==> E ==> A

If we allow only first case ( E ==> A ) by concude 2nd into comment then not only "B" but also "C" and "D" are not in use.

This is example:

with
a as(
  select 0 a from rdb$database
)
,b as(
  select 1 x from c rows 1
)
,c as(
  select 2 x from d rows 1
)
,d as(
  select 3 x from e rows 1
)
,e as(
  select a x from a rows 1
)
select * from e
-- union all select * from b
;

So, if we run it then only _one_ CTE will be shown as not in use:
===
-- SQL warning code = -104
-- -CTE "B" is not used in query
===

All of its "descendants" ("C" and "D") will not be mentioned.
Is it OK ?

Vlad Khorsun added a comment - 12/Dec/17 11:35 AM
Fixed, thanks