This is a bug report. Tl;dr version: Looker creates a PDT when the query calls for that PDT Liquid-conditionally, even if the Liquid condition is not met. This leads to the query’s not running.
We have MySQL and Snowflake almost-copies of the same table and sometimes want to query either one. I wrote a dimension like this —
dimension: foo {
sql: (select foo from t2 [...]) ;;
}
— but it leads to syntax errors in Snowflake dialect. So I instead created a PDT for Snowflake, a copy of the original table with foo appended —
view: t_copy { # Snowflake only
derived_table: {
sql_trigger_value: select count(*)from t ;;
sql:
select t2.foo
, t.*
from t
left join t2
on [...]
;;
}
}
— and changed the original t view to have —
sql_table_name: {% if _dialect._name == 'mysql' %} t
{% elsif _dialect._name == 'snowflake' %} ${t_copy.SQL_TABLE_NAME}
{% endif %} ;;
[...]
dimension: foo {
sql: {% if _dialect._name == 'mysql' %} (select foo from t2 [...])
{% elsif _dialect._name == 'snowflake' %} ${TABLE}.foo
{% endif %} ;;
}
This should, I’d think, work fine. In case the dialect is Snowflake, Looker should look at the PDT; in case the dialect is MySQL, it should completely ignore the PDT. Which is good, because Looker doesn’t have PDT-writing permissions on MySQL.
But when try to use this in MySQL, I get the standard Looker-doesn’t-have-PDT-writing-permissions error. That is, Looker is trying to write a PDT, even though there’s no reason to (the query doesn’t use it). Looking at the generated SQL, I see that Looker is trying to first create a table and then proceed to not use it at all in the subsequent query. It should not create the table; please fix this bug.
