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

Jump to bottom

Expression index based on select statement should not be created [CORE3247] #3616

Open
firebird-automations opened this issue Nov 19, 2010 · 4 comments
Open

Expression index based on select statement should not be created [CORE3247] #3616

firebird-automations opened this issue Nov 19, 2010 · 4 comments
Labels
affect-version: 2.1.3 component: engine priority: minor type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Replaces CORE3763
Relate to CORE4963

Now is possible create expression index on statement like this

CREATE INDEX test ON UZYTK computed by((select count(*) FROM UZYTK_GR UG WHERE UG.ID_UZYTK=http://UZYTK.ID))
First this index have true values only after restore process or after reactivate index.

this will be good in some scenarios
but optimizer never use this index at all

select * from UZYTK WHERE (select count(*) FROM UZYTK_GR UG WHERE UG.ID_UZYTK=http://UZYTK.ID)=5
PLAN (UZYTK NATURAL)
@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Using a SELECT statement is not invalid, it depends on the usage/context.

The example provided is certainly "ill-advised" (at best), but the let's not kill a valuable feature because an uninformed developer might make a mistake in their schema definition by not thinking through their design.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

I do not know who and why delete my comment? :/

But there are not any working expression index with select statement
then select should be revoked from expression index creation
or optimizer should follow that index definition to build plan for query

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue replaces CORE3763 [ CORE3763 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to CORE4963 [ CORE4963 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.1.3 component: engine priority: minor type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations