On 2019-12-30 17:57, Germán Balbi [email protected] [firebird-support] wrote: > Hi. > I have this problem in FB 2.5 > > with x as ( > SELECT * > FROM tbl_x1 > JOIN tbl_fk1 ON cond_1 > WHERE cond_w1 > UNION > SELECT * > FROM tbl_x1 > JOIN tbl_fk1 ON cond_2 > WHERE cond_w2 > ) > > select tbl_2.pk_id > from tbl_2 > where tbl_2.pk_id in (select list(x.tbl2_pk_id) from x > > The plan in the X table is OK, but in tbl_2 is natural > > How can I specify the right plan?
The condition `tbl_2.pk_id in (select list(x.tbl2_pk_id) from x` will be false unless there is only one row in x. As far as I'm aware, that condition cannot use an index, it can only use natural. What are you trying to do? Maybe using `where exists (select * from x where x.tbl2_pk_id = tbl2.pk_id)` will have the desired effect.
