Identify common table expressions (WITH) as composable for raw SQL

[roji]

It turns out that WITH is fully composable in PostgreSQL, MySQL and Sqlite - SqlServer is the odd one out; the below SQL works as expected.

Composed WITH SQL

SELECT * FROM (
    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
        (
            SELECT 1, 0, 1
            UNION ALL
            SELECT n + 1, next_fib_n, fib_n + next_fib_n
            FROM fibonacci WHERE n < 10
        )
    SELECT * FROM fibonacci
) x
WHERE x.n > 3;

Our default implementation of CheckComposableSql currently identifies only SELECTs as composable. In Npgsql I've overridden CheckComposableSql to never throw, since WITH is composable, and there may be other cases. This is one of the very few cases (only case?) where we actually parse SQL, and I don't think it makes a lot of sense - I personally believe the value of catching this in EF Core is limited, compared to just allowing the database to error.

We can:

• Remove the composability check altogether (like I've done in Npgsql)
• Modify it to identify WITH as composable, but override in SQL Server to throw for that.
• Do nothing, and require all other providers to override and identify WITH (or do nothing like Npgsql)

/cc @lauxjpn

[lauxjpn]

Solution 1 or 2 should be preferred, if SQL Server has the only implementation that doesn't play ball.
We'll remove the functionality of CheckComposableSql for Pomelo until a decision has been made.

[roji]

Triage decision: we'll modify the relational check to identify WITH as composable, and override in SQL Server to disallow it.