Dmitry Yemanov
Dmitry Yemanov
> Personally I think the `UNLIST` should _require_ the correlation clause (_including_ the column list), so there would be no need to specify defaults. So far I tend to agree.
They're somewhat separated -- `UNLIST(A AS INT) AS T` -- but maybe still not very friendly.
Personally, I can accept multiple separators in `UNLIST`. This does not absolutely match the `LIST` function but could be useful if you don't know what particular separator was used in...
Both LEFT JOINs are converted into INNER here, so the optimizer attempts to evaluate possible table permutations. Condition `0+ih.createempid = 999999` significantly reduces the expected cardinality, thus IH is placed...
It would also be useful to see stats (execution time & fetches) for v4, v5 and v5 without +0 in the last condition.
It does it because it allows more optimization abilities (different join orders). As for forcing it to avoid that, see #7910 - it's exactly about that. If you're OK with...
> So the number of rows with CreateEmpID = 999999 at the moment is 771402 and the selectivity is 0.000399680...... So you ask for 80% of rows while the optimizer...
So far we were doing the same ;-) But it cannot solve all the issues, because the optimizer intelligence is limited, regardless of how good it is.
Implemented in Firebird 5.0, currently used by IN predicate only.
@pavel-zotov Should be fixed now.