Explores with conditional joins using liquid variables started including unused views in queries

[ocelcole1]

Hello, 

We have a couple explores that are set up so additional views are joined to the base view and also conditionally joined to each other in the others are included in the query. 

They look something like the following:

explore: my_explore {
view_name: base_view

join: view_a {
type: left_outer
relationship: one_to_many
sql_on: ${base_view.view_a_id} = ${view_a.view_a_id};;
}

join: view_b {
type: left_outer
relationship: one_to_many
sql_on: ${base_view.view_b_id} = ${view_b.view_b_id}
{% if view_a._in_query %} and view_a.user_id = view_b.user_id
{% endif %};;
}

 A week ago, you could include a dimension from `view_b` in the explore and the SQL generated by looker wouldn't include `view_a` at all. 

Now, even if there are no dimensions from `view_a` included in the explore, looker is joining in `view_a` regardless.  The liquid logic seems to be working and the join condition on `user_id` isn't included unless a `view_a` dimension is included, but that's not preventing `view_a` from being joined in regardless.

Was there a recent change to how this works that would have broken these explores? Has anyone else experienced this issue?

[sarah_robinson]

I am having the same issue and it is causing a bunch of issues for us.

[sarah_robinson]

@ocelcole1Have you found any workarounds?

From what I have tested, it would seem that looker is evaluating

{% if view_a._in_query %} and view_a.user_id = view_b.user_id {% endif %}

as if it were

{% if view_a._in_query %} and ${view_a.user_id} = {view_b.user_id} {% endif %}

and so even if nothing from the query is selected, it being referenced in the join

evaluates `{% if view_a._in_query %}` as True. Additionally, if instead we use

{% if view_a._is_selected %} and view_a.user_id = view_b.user_id {% endif %}

Looker will exclude that join clause from the view_b join, but will still join view_a in the generated sql because it is being referenced.

[ocelcole1]

Hey @sarah_robinson , we ended up toggling on Legacy LookML Runtime in the legacy features options and it fixed the issue.

We opened up a ticket with Looker support to address the issue in the new Runtime, since the legacy option will go away in 5 months, but we haven't heard back. 

But yeah, my testing showed the same problem that you outlined above.

[amahajan2]

I am facing this issue even with the legacy runtime turned on

[salazaralvarado]

---

@ocelcole1 wrote:

explore: my_explore { view_name: base_view join: view_a { type: left_outer relationship: one_to_many sql_on: ${base_view.view_a_id} = ${view_a.view_a_id};; } join: view_b { type: left_outer relationship: one_to_many sql_on: ${base_view.view_b_id} = ${view_b.view_b_id} {% if view_a._in_query %} and view_a.user_id = view_b.user_id {% endif %};; }

---

[amahajan2]

this is my code

explore: marc_master_article {
from: main_dummy
join: marc_master_article_site_v {
type: left_outer
sql_on: 1=1 and {% if marc_master_article_site_v._in_query %}
1=1
{% else %}
1=222
{% endif %};;
relationship: one_to_many

}
join: dim_article_v {
type: left_outer
sql_on:1=1 and {%if marc_master_article_site_v._in_query%}
${dim_article_v.article} = ${marc_master_article_site_v.article}
{% endif %};;
relationship: one_to_many
}

}