Unable to connect dashboard number filter to number variable of SQL query card

[amit-sides]

Describe the bug

I have a dashboard with multiple cards.
Some of them have a number variable called "Days".
My dashboard has a number filter called "Days" as well that is connected to all my query cards.
Today I tried adding a new SQL query card with a number variable, but I was unable to connect it to the card.
I also noticed that when I disconnect that number filter from a card I previously connected to the filter, I'm unable to re-connect it afterwards, so I don't think it's an issue of the SQL query, but rather something was changed regarding the connection process between the filter and the cards.

Video example:
https://github.com/metabase/metabase/assets/69360073/bd404400-5881-43cb-8a54-1e717c425788

To Reproduce

1. Have a dashboard with an SQL query card that contains a number variable.
2. Edit the dashboard and add a number filter.
3. Connect the number filter to the number variable of the SQL query card.
4. See error in console.

Expected behavior

The number filter should connect to the number variable of the SQL query card.

Logs

app-main.3617….js:35 Uncaught (in promise) 
n1.$h {message: 'Unknown type of ref', data: n1.h, cause: null, name: 'Error', description: undefined, …}
cause: null
columnNumber: undefined
data: n1.h {M: null, N: 1, D: Array(2), J: null, C: 16647951, …}
description: undefined
fileName: undefined
lineNumber: undefined
message: "Unknown type of ref"
name: "Error"
number: undefined
stack: "Error: Unknown type of ref\n    at new n1.$h (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:92504)\n    at n1.ai (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:92814)\n    at n1.bi (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:92771)\n    at Function.n [as j] (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:886095)\n    at Function.e [as K] (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:885176)\n    at Function.t [as v] (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:885670)\n    at http://<reducted>/app/dist/app-main.3617<reducted>.js:262:967478\n    at eW (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:61674)\n    at n1.g.fa (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:138554)\n    at n1.y (http://<reducted>/app/dist/app-main.3617<reducted>.js:262:50551)"
[[Prototype]]: Error
da: ƒ (e,t,n)
pa: {}
toString: ƒ ()
constructor: ƒ (e,t,n)
[[Prototype]]: Object

Information about your Metabase installation

You're on version v0.50.4
Built on 2024-06-13

Severity

annoying

Additional context

No response

[ranquild]

@amit-sides I couldn't reproduce this issue with a new SQL query and a number variable. Could you reproduce the issue using the "Sample Database"?

[uladzimirdev]

@amit-sides could you please share more details about your configuration?
we'd like to know:

• filter configuration
• sql for a query (a screenshot from editor)
• network responses from /dashboard/* and /card/*

[paoliniluis]

@amit-sides this question comes from an old version of Metabase, is that correct? can you give us the question metadata? you need to hit /api/card/

[ranquild]

One thing is that Column to filter on is only shown for questions/models and not for SQL queries. So it looks like a GUI question and not a SQL query, i.e. not what is in the description.

[paoliniluis]

ok so I wasn't able to repro with a question coming back from v38... so I guess that we really need to figure out how to hit this one

[amit-sides]

Sorry for the delay people, thanks for the help! @ranquild @paoliniluis @uladzimirdev
I managed to minimize my query to ensure this isn't caused by other factors in it, here is my query right now (I'm using sqlite db, I think):

select registration_datetime
from BALANCE
WHERE 1=1
[[AND registration_datetime > date('now', "-" || {{d}} || " days")]]
limit 10

Here is a video showing the same problem with this query:
https://github.com/metabase/metabase/assets/69360073/b3601585-7601-41e1-883b-6ac5b5ebd302

It took me some time, but I managed to create a similar query with the sample data db, as @ranquild requested:

SELECT
  "PUBLIC"."FEEDBACK"."ID" AS "ID",
  "PUBLIC"."FEEDBACK"."ACCOUNT_ID" AS "ACCOUNT_ID",
  "PUBLIC"."FEEDBACK"."EMAIL" AS "EMAIL",
  "PUBLIC"."FEEDBACK"."DATE_RECEIVED" AS "DATE_RECEIVED",
  "PUBLIC"."FEEDBACK"."RATING" AS "RATING",
  "PUBLIC"."FEEDBACK"."RATING_MAPPED" AS "RATING_MAPPED",
  "PUBLIC"."FEEDBACK"."BODY" AS "BODY"
FROM
  "PUBLIC"."FEEDBACK"
Where 1=1
[[AND "DATE_RECEIVED" > NOW() + INTERVAL '-{{d}}' day]]
order by "DATE_RECEIVED" asc
LIMIT
  10

and again, video showing the same problem with this one:
https://github.com/metabase/metabase/assets/69360073/d817accd-1846-4484-8daf-77ea559dcf8d

[amit-sides]

@amit-sides this question comes from an old version of Metabase, is that correct? can you give us the question metadata? you need to hit /api/card/

Not sure what you meant by it, but: If you mean that I created the question in an older version, then I'm not sure but I think it's irrelevant since I created today the feedback test question (second video in my comment above) and it still happens with it.

[amit-sides]

One thing is that Column to filter on is only shown for questions/models and not for SQL queries. So it looks like a GUI question and not a SQL query, i.e. not what is in the description.

You are probably right, I don't fully understand the naming of things in metabase. I wanted to emphasize I use SQL query and not the UX builder for questions.
Either way, hopefully my videos explain it better :)

[amit-sides]

@amit-sides could you please share more details about your configuration? we'd like to know:

• filter configuration
• sql for a query (a screenshot from editor)
• network responses from /dashboard/* and /card/*

I added videos and queries. I prefer not to share network responses right now because it might leak my IP and other things, but if all the info I provided isn't enough I will try to capture the responses and censor them as last option

[uladzimirdev]

@amit-sides thanks, really helpful! it should be enough for a fix, we'll let you know if we need more data

[uladzimirdev]

@amit-sides the fix will be released in 0.50.7