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

[firebird-automations]

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]

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]

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]

Modified by: @dyemanov

Link: This issue replaces [CORE3763](https://github.com/FirebirdSQL/firebird/issues?q=CORE3763+in%3Atitle) \[ [CORE3763](https://github.com/FirebirdSQL/firebird/issues?q=CORE3763+in%3Atitle) \]

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

Link: This issue relate to [CORE4963](https://github.com/FirebirdSQL/firebird/issues?q=CORE4963+in%3Atitle) \[ [CORE4963](https://github.com/FirebirdSQL/firebird/issues?q=CORE4963+in%3Atitle) \]