Download custom SQL with a required parameter is broken

1

I have a custom SQL report with a single required parameter. The required parameter is used twice in the query (it’s a UNION). I’m able to run the report fine, but the report cannot be downloaded. The error I receive is ‘membercode’ is a required parameter.

To recreate it;

  1. create a custom SQL report with a required parameter with no default value. Have the parameter occur twice in the SQL via a UNION
  2. save it
  3. run it by providing the value
  4. view the results
  5. select download

NOTE: it does download properly when the report is in Edit mode. This is NOT a viable solution to give business users.

This is a pretty serious blocking issue for me. Its our main report that joins two data sets.

Metabase version: v0.20.3
Browser: Chrome on Mac (also doesn’t work on Safari Mac)

2

Can you replicate this problem using a query against the sample dataset? That would be really helpful, as it would give us a failing case we can fix.

3

Here is an example query against the Sample Database that replicates the problem. You can download the data from this query when you’re in edit mode, but you can’t download the data after you have saved the query and come back to run it.

SELECT created_at,
reviewer,
product_id
FROM (
SELECT
“PUBLIC”.“REVIEWS”.“CREATED_AT” AS created_at,
“PUBLIC”.“REVIEWS”.“BODY” AS body,
“PUBLIC”.“REVIEWS”.“RATING” AS rating,
“PUBLIC”.“REVIEWS”.“REVIEWER” AS reviewer,
“PUBLIC”.“REVIEWS”.“PRODUCT_ID” AS product_id,
“PUBLIC”.“REVIEWS”.“ID” AS id
FROM “PUBLIC”.“REVIEWS”
WHERE “PUBLIC”.“REVIEWS”.“PRODUCT_ID” = {{productID}}
UNION ALL
SELECT
“PUBLIC”.“REVIEWS”.“CREATED_AT” AS created_at,
“PUBLIC”.“REVIEWS”.“BODY” AS body,
“PUBLIC”.“REVIEWS”.“RATING” AS rating,
“PUBLIC”.“REVIEWS”.“REVIEWER” AS reviewer,
“PUBLIC”.“REVIEWS”.“PRODUCT_ID” AS product_id,
“PUBLIC”.“REVIEWS”.“ID” AS id
FROM “PUBLIC”.“REVIEWS”
WHERE “PUBLIC”.“REVIEWS”.“PRODUCT_ID” = {{productID}}
) AS derived
where product_id = {{productID}}
ORDER BY created_at

4

are you treating productID as a number or a field filter?

5

in this example its treated as a number (required).

I know this example is a little bogus but it shows the error. My real query combines medical and prescription claims (two different tables) to create a chronological log of all data for a specific search field (and the search field is an indexed field on both tables).

The only way to get around this bug right now is create a new table in my database that pre-combines all fields I need to query against. With millions of records in my database I would rather not have to create a duplicate table that holds this combined info. It will be a huge and totally unnecessary table.

I would rather just use UNION ALL and an indexed query into each SELECT that is part of the UNION. This is how it would normally be done. The query itself works great in Metabase (returns sub-second!) but unfortunately the data isn’t downloadable which is a requirement.

Thanks!

6

Ok, I replicated this in 0.20.3, but we seem to have fixed it in our next version.

If you’re able, try out our release candidate at http://downloads.metabase.com/v0.21.0-rc1/metabase.jar and see if it still returns that error.

7

Thanks for the quick response! I did build my temporary workaround so I’m good for a couple of weeks until the next data set comes in. I don’t feel comfortable downloading the RC yet, but I’ll upgrade as soon as it goes GA.