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

Allow unused Common Table Expressions [CORE5674] #5940

Closed
firebird-automations opened this issue Dec 1, 2017 · 6 comments
Closed

Allow unused Common Table Expressions [CORE5674] #5940

firebird-automations opened this issue Dec 1, 2017 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @hvlad

Is related to CORE1793

When fixing bug CORE1793 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

Commits: 6e37527 784ef83 ef20918 be2c227 0c97fec 2900b34

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue is related to CORE1793 [ CORE1793 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.8 [ 10809 ]

Fix Version: 3.0.3 [ 10810 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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 ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Fixed, thanks

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

2 participants