The current version of show.sql does not handle all cases

[stegus64]

show.sql is very important since it is used to preview query results when using the dbt Power user plugin for VSCode and from inside dbt cloud.

The current version of show.sql looks like this:

{% macro fabric__get_limit_sql(sql, limit) %}

    {% if limit == -1 or limit is none %}
        {% if sql.strip().lower().startswith('with') %}
            {{ sql }}
        {% else -%}
            select *
            from (
                {{ sql }}
            ) as model_limit_subq
        {%- endif -%}
    {% else -%}
        {% if sql.strip().lower().startswith('with') %}
            {{ sql }} order by (select null)
            offset 0 rows fetch first {{ limit }} rows only
        {% else -%}
            select *
            from (
                {{ sql }}
            ) as model_limit_subq order by (select null)
            offset 0 rows fetch first {{ limit }} rows only
        {%- endif -%}
    {%- endif -%}
{% endmacro %}

It generates incorrect code for several different cases:

Line comment on the last line when using with

WITH 
result as (
SELECT * 
FROM Table
)
select * from result  -- Ending comment

ORDER BY at the end

SELECT * 
FROM Table
ORDER BY a,b

Comment before with

-- This is a comment
WITH
result as (
SELECT * 
FROM Table
)
SELECT * FROM result

ORDER BY inside the query

-- This is a comment
WITH
result as (
SELECT * , row_number() over (order by a) as rn
FROM Table
)
SELECT * FROM result

All these cases can be handled with a much simpler version of the macro.

Like this:

{% macro fabric__get_limit_sql(sql, limit) %}
    {%- if limit == -1 or limit is none -%}
        {{ sql }}
    {#- Special processing if the last non-blank line starts with order by -#}
    {%- elif sql.strip().splitlines()[-1].strip().lower().startswith('order by') -%}
        {{ sql }} 
        offset 0 rows  fetch first {{ limit }} rows only
    {%- else -%}
        {{ sql }} 
        order by (select null) offset 0 rows fetch first {{ limit }} rows only
    {%- endif -%}
{% endmacro %}