Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Potential bug when using parameters in a derived table

  • 12 December 2018
  • 7 replies
  • 872 views
L

Looker 4.22.18


Here is a paraphrase of the error I am getting:


A LookML model issue prevented this query from running.

Variable not found “min_date”.

company/very_complex_table_that_uses_parameters_to_speed_up_joins.view.lookml:30


BEGIN SETUP




  • include: very_complex_table_that_uses_parameters_to_speed_up_joins.view.lookml




  • view: grouped_complex_table_that_is_needed_to_run_notified_queries

    view_label: ‘’

    derived_table:

    sql: |

    select blah… from

    (select blah… from

    (select blah from

    ${very_complex_table_that_uses_parameters_to_speed_up_joins.SQL_TABLE_NAME}

    )

    ) a join (select blah … from …) b




END SETUP


The problem is that the parameter min_date exists in the underlying table and has a default value, and looks based on that table run fine. But when I look at the SQL code that looker generates for the lookml that include the complex table, I see {%parameter min_date%}. Adding a parameter min_date parameter into the new lookml doesn’t seem to help.


This seems like a bug, any help on resolving this would be much appreciated

7 replies

izzy
Userlevel 7
Badge +1

Yep, I tested this out and got the same result-- I think this is due to the way that looker grabs LookML when you reference a derived table with that SQL_TABLE_NAME syntax (or rather, the way it doesn’t grab LookML).


Note that if you built an explore that joins the tables together, the query will execute just fine-- because they’re joined together in the LookML model, it’s fetching the entire LookML scope including parameters and liquid. When you use SQL_TABLE_NAME however, it’s really just doing a lookup on view name to find the matching PDT table name in the database (which are long hashes) or the SQL definition-- it doesn’t actually check the full LookML and see if there’s anything it needs to bring in unless the views are joined.


I’ll ask some engineers about this, seems like it might be a bug to me too. Would joining the very_complex_table into the explore work in this case or is it too expensive a join?

L

The main source of data is the very_complex_table_that_uses_parameters_to_speed_up_joins, which I am applying “group by” to. The reason I am using SQL “group by” is because looker doesn’t provide notification based on calculated columns, so I replicate the grouping logic in another lookml, and then running my notification query based on the presence or absence of those results.


So in short, I have to use SQL_TABLE_NAME

L

@izzy joining will work, but if I use the joined table as SQL_TABLE_NAME, I still get the same problem of looker not replacing the parameter.

izzy
Userlevel 7
Badge +1

Hmm, what do you mean “If I use the joined table as SQL_TABLE_NAME”? I just gave it a shot and it seemed to work using this as the base table:


view: testbase {

  derived_table: {

    sql: SELECT 1 FROM test.test WHERE 1= {% parameter theparam %} ;;

  }



  parameter: theparam {

    type: number

    default_value: "1"

  }

and this as the top table:


view: testdt {

  derived_table: {

  sql:select * from ${testbase.SQL_TABLE_NAME} ;;

}



  dimension: the1 {

    type: number

    sql: 1 ;;

  }



}

An explore like


explore: testdt {

  join: testbase {

    sql_on: ${testbase.the1} = ${testdt.the1} ;;

  }

}

writes this SQL, with the parameter replaced properly: image


even though an explore without them joined together like


explore: testdt {}

writes this SQL:

image
and returns the variable not found error.


Regardless, it’s still something I think we’ll want to fix in the product or make more clear-- this might just help immediately 🙂.

L

@Izzy, I was able to use your answer to get the desired effect. I didn’t want the table to be joined because one is aggregated, but I figured out that if I use the following join logic everything works as required.


- explore: testdt

  joins:

    - join: testbase

      relationship: one_to_one

      type: left_outer

      sql_on: |

        0=1

Thanks for your help, it’s been invaluable.

izzy
Userlevel 7
Badge +1

You’re welcome! That’s very clever, joining it from a LookML perspective so that the references can be tracked down, but not actually joining the records-- Nice job!!


I’ll post again here if I find out anything interesting from engineers about this.

K

Is this still outstanding bug? I think I experienced this today as well. Using a liquid template ( {% if view.field._is_in_query} ) on a derived table that’s later referenced using ${table.SQL_TABLE_NAME} produces a variable not found error. 

Reply


Terms of Service