[FIX] res.partner: current name_search() implementation tends to skip a few valid results during early autocompletion - mitigation attempt

The main reason for the semi-random behavior
observed during auto-completion is the
missing ORDER BY clause in the pre-filtering
SQL query.

The ORDER BY clause is expensive but inevitable
if we want to apply a correct LIMIT, otherwise
we would return random `limit` results among
all the possible matches.

The current SQL query seems convoluted due
to the duplicated CASE clause but it
performs slightly better than the equivalent
CTE-based (WITH...) query, so it was preferred.


There is still a chance of returning too
few results due to double limit application,
as further discussed in bug 1203727
[MERGE] res.partner: current name_search() implementation tends to skip a few valid results during early autocompletion - mitigation attempt

The main reason for the semi-random behavior
observed during auto-completion is the
missing ORDER BY clause in the pre-filtering
SQL query.

The ORDER BY clause is expensive but inevitable
if we want to apply a correct LIMIT, otherwise
we would return random `limit` results among
all the possible matches.

The current SQL query seems convoluted due
to the duplicated CASE clause but it
performs slightly better than the equivalent
CTE-based (WITH...) query, so it was preferred.


There is still a chance of returning too
few results due to double limit application,
as further discussed in bug 1203727