Set operations: pushdown Take without Offset/OrderBy for SqlServer

[roji]

Note: this is relevant only after #16244 is done.

Take without Offset+OrderBy is usually translated as TOP(n) for SqlServer, but that isn't possible directly on set operations. We need to push the set operation down into a subquery to apply TOP(n) outside. This depends on provider-specific customization of SelectExpression logic.

On Sqlite and PostgreSQL there's no problem with adding LIMIT 1 directly on the set operation (SELECT 1 AS x UNION SELECT 2 LIMIT 1).

[mburbea]

You can also consider a translation like

select 1 as x, null as __throwaway__
union
select 2, null
order by 2
offset 0 rows
fetch next 1 rows only

Which requires no extra subquery. But we'd have to add a dummy constant to both sides of the union. Sql server will realize the constant is the same on both sides and throw it away, and produce the exact same plan. (it will still appear in the output list but that can just be ignored when materializing the results.)

[roji]

Nice trick with ordering by a null constant - there are also other ways to get random ordering in databases without dummy projections. We should actually do some quick measurement to see which technique is best (the subquery may actually work better than the extra order by).

On an unrelated note, the set operation already generates null constants for situations where two different entity types are used as operands, and not all properties are shared by both sides.

[mburbea]

The order by constant gets eliminated if it's the same constant in both tables. Unfortunately, using order by (select 1) doesn't work in union as the order by clause applied to a union must talk about a column in the union.

That said, Sql server rarely works on the Principle of least astonishment.

exec sp_executeSQL N'
select 1 x,null as __throwaway__
union
select 2, null as __throwaway__
order by 2
offset @a row
fetch next @b row only',N'@a int,@b int',0,1

Returns 1 row.

exec sp_executeSQL N'
select x
from (
select 1 x,null as __throwaway__
union
select 2, null as __throwaway__
order by 2
offset @a row
fetch next @b row only
) z',N'@a int,@b int',0,1

returns 2 rows.

Wrapping the expression to eliminate the extra throwaway column causes sql server to treat the order by/offset/fetch next as being applied to the second table in the union.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=330793a28bdf2eafff46d140fa655a5a