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.
Context:
Implementation:
get_show_sql
should not wrap in a subquery. Instead, it should simply appendlimit {{ limit }}
to the end of the query.limit
clause, but it preserves ordering in all other cases.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 reimplementingget_show_sql
within their projects.Acceptance tests:
BaseShowLimit.test_limit
is validating only that thelimit
is templated into the query (important!)