cubicweb #694445 related entity generates weird RQL which in turn generates weird SQL which fails on SQL Server [resolved]

on the Pylos project we have

linked_rset = entity.related(rschema.type, roles[rschema.type])

This generates:

rql=u'Any X,AA ORDERBY Z DESC WHERE X modification_date Z, E eid %(x)s, X in_case E, X modification_date AA'

Which generates:

SELECT T1.C0,T1.C1 FROM (SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_Node AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL
SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_RunExecution AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL\nSELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_PipeLine AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL
SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_GasType AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL
SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_ConsumptionSector AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL
SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_LinearizedFunction AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL\nSELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_Global AS _X
WHERE _X.cw_in_case=%(x)s
UNION ALL
SELECT _X.cw_eid AS C0, _X.cw_modification_date AS C1, _X.cw_modification_date AS C2
FROM cw_Market AS _X
WHERE _X.cw_in_case=%(x)s
ORDER BY 3 DESC) AS T1

which fails on SQL Server because:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
priorityimportant
typebug
done in3.6.1
load0.200
load left0.000
closed by<not specified>