""mmenaz"" <mmenaz@...> wrote in message
news:a3v9fa+g9a5@......

> FireBird RC2 (IBO + QuickDesk)
> Hi, I would like to know why the following query does not return in a

ordered dataset, while the other, omitting the join, does! I've also tried
the syntax "order by 1", but with no luck! I'm very surprised, maybe I'm
forgetting something obvious... maybe...

Surprised? This is one of the oldest bugs tracked at SF for FB.
You may want to send your congratulations to the IB team. They put that bug
in IB5.0. Knowing for hundreds of reports that they broke the engine, they
elected to leave the change in place. They call it a "feature". The problem
is that the "improvement" looked well in the manuals for marketing purposes
and removing it in IB5.1 or IB5.5 would be a setback.

Some clever person discovered that the engine sometimes does redundant,
unnecessary sort operations, specially when merge-sort and simple sort
happen at the same time. Then they decided to eliminate those redundacies.
There are two ways to do that: the right and the wrong. They did the wrong
way.
;-)

To be able to determine that the result of a merge-sort is not changed
before the sort is seen (so you can remove the last sort), a bunch of checks
should be done. Apparently, the code misses some cases and the engine
changes the order of the result set due to other operations (index walking,
etc.) without being tracked properly, so a needed sort is removed. Worse,
they made the thing so brute that if you try to force the plan to take the
missing sort, it will be ignored altogether. The IB5's Rel_Notes explicitly
says that you can't force the plan to take an extra sort. "We want to make
sure you will enjoy the feature" may be the lesson here.

When you have to decide between:
A) Having some redundant ops as before but get the data sorted.
B) Reduce the internal work and hence the answer time, but at the cost of
wrong results.

I definitely prefer A, but if you have already published B in your official
documentation without noticing the bug, marketing-wise people say it's a
very bad press to remove an announced feature.

The question is: how much cases do you miss that can be optimized safely
against the cases that you can't get right today without a nasty workaround,
if you removed this feature?

Try this:
SELECT MOVCON_RIGHE.RIGA_CONTABILE_ID + 0,
[snip]
ORDER BY 1;

If your ID is string instead of numeric, do ..._ID || '' instead of adding
zero.

Post your full SQL plan for the statement that doesn't sort and for the
change (workaround) suggested above so the difference becomes evident.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing