Empty SQL Response

Hello Retool!

One of my SQL queries that worked for two years is now broken. Part of the data sent is dynamic and comes from a multi select. I've found the bug, it's when I give the value of the contract name in the where condition. I can give it with raw values but not dynamic values. When I copy the SQL code into the state of my query and run it in console, it works without a hitch, but not from Retool where I only get an empty table as a response. Do you know why I receive an emtpy response?

Here is the code I send contractInputs is a multi select:

Here is the code from the state (I paste it into the console and add single quote around contrat_name values ex='XXX-XXX-20201' and around the dates :

select date,
       contract_id,
       contract_name,
       min_charge_days,
       contract_name +
       IIF(contract_amendment = 0, '', ' [' + cast(contract_amendment as varchar(10)) + ']') as contract_fullname,
       product,
       license_mrr,
       maintenance_mrr,
       total_mrr,
       license_mrr_EUR,
       maintenance_mrr_EUR,
       total_mrr_EUR,
       currency
from CONTRACT_MRR_PREVAILING
         inner join VISTA_CONTRACT on CONTRACT_MRR_PREVAILING.contract_id = VISTA_CONTRACT.id
where contract_name IN (XXX-XXX-2021, XXX-XXX-2021)
  and date >= 2000-02-01
  and date <= 2100-02-01
order by date desc

I think the part using array in SQL query is not straight forward

where contract_name in ( {{contractsInput.values.toString()}} )

We use prepared statement by default so the whole contractsInput.values will be passed in as a string. Here is a guide to do it in Retool

e.g. for PostgresQL:

SELECT
  *
FROM
  users
WHERE
  id = ANY({{ [1, 2, 3] }})

I used to send ContractInput.values and it worked, but it hasn't worked for a few days now, even though I haven't changed a thing. That's why I try to send : contractInput.values.toString()

Hey @AntoineOA, can you share what resource your are using for this query (ie postgres, sql server, etc)?