DDC-3645: [GH-1353] Paginator fixes take3

[doctrinebot]

Jira issue originally created by user @doctrinebot:

This issue is created automatically through a Github pull request on behalf of zeroedin-bill:

Url: #1353

Message:

This PR fixes issues reported in #1347 and #1351.

• Ordering a query by a column from an association which uses SINGLE_TABLE inheritance no longer throws an error.
• Limiting a query which is ordered by fields from a joined -To-Many association now works as expected.

This patch converts LimitSubqueryOutputWalker to use the ROW_NUMBER window function for queries on platforms that support it. Other platforms use a modified version of the previous method. The modification is to not select the ORDER BY columns in the wrapping query. The reason those columns were selected like that in the first place is that ORDER BY on columns not in the select list is unsupported on many platforms. MySQL and SQLite are fine with it, and don't support ROW_NUMBER.

The ROW_NUMBER solution is actually much simpler.

LimitSubqueryOutputWalker SQL generation tests for PG and Oracle have been changed.

_There are 2 failing expectedException tests right now._ The LimitSubqueryWalker needs to be modified to throw an exception when it detects a query that does the following things together:

• Joins a -To-Many association
• Orders by a column from the associated entity
• Limits the result

As with the previous patch, this depends on changes to SQLServerPlatform2008 that have not yet been merged. SQLServerPlatform2008 in DBAL/master currently mangles the generated queries when trying to apply LIMIT/OFFSET.

[doctrinebot]

• is duplicated by DDC-3637: [GH-1347] problem with LimitSubqueryOutputWalker when use InheritanceType
• is duplicated by DDC-3642: [GH-1351] Failing test cases regarding to #1325 #1337

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1353] was assigned:
#1353

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1353] was labeled:
#1353

[doctrinebot]

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1353] was closed:
#1353

[doctrinebot]

Issue was closed with resolution "Fixed"