order/limit nested inside union/intersect

[gavinking]

Currently our BNF is:

selectStatement
	: queryExpression

queryExpression
	: orderedQuery (setOperator orderedQuery)*

orderedQuery
	: (query | LEFT_PAREN queryExpression RIGHT_PAREN) queryOrder?

query
	: selectClause fromClause? whereClause? (groupByClause havingClause?)?
	| fromClause whereClause? (groupByClause havingClause?)? selectClause?

queryOrder
	: orderByClause limitClause? offsetClause? fetchClause?

This is more complex than I would have expected because it allows order by and limit/offset inside the tree of set union and intersect operations.

But I'm not convinced we should allow this. Naively, it doesn't make sense to do set operations on arbitrary bits of sets, or on ordered sets, and I don't know how many databases even support it. (Not sure.)

The only think I can think of is: perhaps this makes sense for reporting queries, but even in that case I'm struggling to see the value.

Do ya'll think we should simplify this, and only allow order by and limit/offset at the root level?

[beikov]

I'd like to not unnecessarily restrict this as the major databases support this just fine and for those that don't, window functions are used to emulate this. Allowing this only at the root level is exactly what we had in 5.x, but people, me included, want to do stuff like ... where id in (select ... limit 10), so we should definitely keep this.

[gavinking]

OK fine, if there is some actual usecase for it, that's OK.

I will close the discussion then.

[gavinking]

Well, one more thing: does it actually work and is it tested in our test suite?

[beikov]

We have tests with uses of limit in subqueries, yes, but not combined with set operations yet. You can add them though if you want. Either into SubqueryLimitOffsetTest or SubqueryPaginationTest