Change the default behavior of get_show_sql to avoid wrapping in subquery

[jtcohen6]

Context:

• [Feature] Avoid wrapping in subqueries where possible and respect order by clauses with dbt show --limit dbt-core#9600
• internal slack thread

Implementation:

• By default, get_show_sql should not wrap in a subquery. Instead, it should simply append limit {{ limit }} to the end of the query.
• This does not work for queries that already contain a limit clause, but it preserves ordering in all other cases.
• Because this only changes the behavior for dbt show, which is a development (not production) workflow, I do not believe this needs to be gated behind a behavior change flag. Users can always opt back into the previous behavior by reimplementing get_show_sql within their projects.

{% macro get_show_sql(compiled_code, sql_header, limit) -%}
  {%- if sql_header -%}
  {{ sql_header }}
  {%- endif -%}
  {{ compiled_code }}
  {% if limit is not none %}
  limit {{ limit }}
  {%- endif -%}
{% endmacro %}

Acceptance tests:

• I expect all existing tests to pass, since BaseShowLimit.test_limit is validating only that the limit is templated into the query (important!)
• Ideally, we'd write a new test that reliably reproduces incorrect ordering when the query is wrapped in a subquery, and then prove that it is always correctly sorted. Unfortunately I haven't managed to come up with a simple reproduction case of this.

[jtcohen6]

From estimation:

• existing macro, same signature
• not reimplemented in most adapters
• could add a test for the known failure mode (limit repeated twice: once within actual query, once by supplying --limit flag)

[VersusFacit]

See merge comments in #team-dev-adapters