Open Nepherpitou opened 1 year ago
I've only seen this when there's a nested in (which you can get around with a cte). There also seems to be something going on with whether or not there's whitespace after the in:
sql.unsafe(`drop table if exists t; create table t as (select 1 x);`).then(() => {
// success: not nested
sql`select x from t where x in ${sql([1])}`
.then((data) => console.log(data)) // Result [{x: 1}]
.catch((err) => console.log(err.message));
// failure: nested without space after outer 'in'
sql`select x from t where x in(select x from t where x in ${sql([1])})`
.then((data) => console.log(data))
.catch((err) => console.log(err.message)); // str.replace is not a function
// failure: nested without space after inner 'in'
sql`select x from t where x in (select x from t where x in${sql([1])})`
.then((data) => console.log(data))
.catch((err) => console.log(err.message)); // str.replace is not a function
// success: space after outer and inner 'in'
sql`select x from t where x in (select x from t where x in ${sql([1])})`
.then((data) => console.log(data)) // Result [{x: 1}]
.catch((err) => console.log(err.message));
});
@Nepherpitou just tried exactly your example and didn't see any issue. Do you have something I can repro?
Very nice tests @rytido !! Thank you - I'll look at fixing those and including your tests.
For what it worth, I had encountered this issue too and the comment by @rytido helped me work around it. This was my original query:
const val1 = "1";
const valuesArray = [
["2", "3", "4"]
];
sql`
DELETE FROM my_table
WHERE
col1 = ${val1} AND
(col2, col3, col4) IN (${sql(valuesArray)})
`;
Removing the space directly after the "IN" made it work:
sql`
DELETE FROM my_table
WHERE
col1 = ${val1} AND
(col2, col3, col4) IN(${sql(valuesArray)})
`;
Queries with
WHERE IN ${sql()}
fails with error starting from version 3.4.0Error: