Your query is most likely incorrect. The LIST function returns a
VARCHAR, so if x returns, say, two rows with 5 and 7, then list will
create the string '5,7' (or '7,5', your query does not specify any
order) and IN ('5,7') is very different from IN('5', '7'). Your query
would only return rows if tbl_2.pk_id actually was '5,7' and that would
be a very unusual value to have in a pk field.
The optimizer of Firebird has improved for each version, but 2.5 may not
be clever enough to understand that your particular subselect referring
to the CTE in the IN part treats the CTE as if it was constant (Firebird
has to be able to cope with more complex cases where the subselect can
change for each row and those cases could never use an index). Myself, I
stopped using IN (<subselect>) with Firebird last century, and have
preferred using EXISTS ever since. However, in your case I think I would
have simplified your query rather than used EXISTS, does this return
what you want?
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 distinct tbl_2.pk_id
from tbl_2
join x on tbl_2.pk_id = x.tbl2_pk_id
HTH,
Set
Den 30.12.2019 17:57, skrev Germán Balbi bal...@yahoo.com
[firebird-support]:
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?
Thank
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
Virus-free. www.avg.com
<http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
