[object Object] bug inside case() function

[jackcuneo]

Hi! Starting midday today, a couple of dashboards my users rely upon broke. Upon investigation, it appears that this break was caused by the case() function returning "[object Object]" instead of a date.

Here is an example table calculation where this is occurring.

lookup(
  case(
    when(
      ${max_date_in_past} = yes
     ,add_days(max(diff_days(now(), ${reports_created_time_series.calendar_date})), trunc_days(now()))
    )
   ,when(
      ${min_date_in_future} = yes
     ,add_days(min(diff_days(now(), ${reports_created_time_series.calendar_date})), trunc_days(now()))    
    )
   ,trunc_days(now())
  )
 ,${reports_created_time_series.calendar_date}
 ,${running_total}
)

This code overall returns null. However, if you remove the outer lookup() and its second and third arguments (so that you're left with just the case() statement), I keep getting "[object Object]".

If I unit test further by running each individual argument inside the case statement, they all return the expected outputs. I am not positive, but I think that the specific parameter for the case statement that is causing this is the "else" parameter. I think this because when my inputs cause either of the when() statements to evaluate to true, the output seems to be correct. But when all when() statements are false and therefore the "else" would apply, I get this "[object Object]" issue. Replacing the value/function in the "else" position doesn't seem to help.

Can someone on the Looker side investigate this?

[Amanda-Google]

Hi there -

This might require a bit more troubleshooting and seems like a SQL error more than a Looker error. The steps I would suggest:

• Double check that the output you expect after each THEN statement has valid results
• It looks like your parentheses might be one off - the CASE WHEN appears to end after the trunc(days(now)), should it end after a different field
• It might be easier to troubleshoot with using WHEN, THEN and ELSE, END etc vs just " , " 

[jackcuneo]

Hmm... this is written using Looker's case() syntax. I have not found the option to use WHEN THEN ELSE. If that were possible I'd definitely do that. Can you provide guidance if this is an option?

I've unit tested each piece of this, and I've checked the syntax against Looker's documentation a bunch of times. Yes, the case statement ends after trunc_days(now()) because this is the else_value argument.

If something is off, I'm not seeing it. If you do see something that would cause this to be incorrect, can you let me know?