Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

WHERE col IN Query with empty array as parameter

From example where-col-in example and this answer, WHERE IN clauses should have query with parameters with following syntax

const response = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])

where data is an array.

Now, when data is an empty array, it produces the following query

 SELECT * FROM users WHERE id IN ()

which is a syntax error.

Consider following statements:

  • this works

    const x = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[1, 2, 3]]);
    
  • this does not work

    const y = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [[]]);
    

A similar error reported for squel library has answers on how knex and ruby's sequel behaves in such scenario.

Is this a bug or am I doing something wrong? Could there be an alternate syntax which works for both scenarios.


For instance, an alternate query using ANY works for both situations:

await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[1, 2, 3]]);
await db.any(`SELECT * FROM table WHERE id = ANY($1)`, [[]]);

What should be the best way to have WHERE col IN queries which could also handle empty arrays as params?

like image 442
Naisheel Verdhan Avatar asked Mar 09 '18 06:03

Naisheel Verdhan


People also ask

How do you check if an entire array is empty?

To check if an array is null, use equal to operator and check if array is equal to the value null. In the following example, we will initialize an integer array with null. And then use equal to comparison operator in an If Else statement to check if array is null. The array is empty.

How do you return all records if parameter is null?

Inside the stored procedure, the parameter value is first tested for Null using the ISNULL function and then checked whether it is Blank (Empty). If the parameter has value then only matching records will be returned, while if the parameter is Null or Blank (Empty) then all records from the table will be returned.

How do I check if a list is empty in SQL?

The len() function is used to find the number of elements in the list. So, to check if the list is empty or not using len(), we can pass the empty list to the len() function, and if we get 0, that means the list is empty.


2 Answers

Library pg-promise gives you complete freedom in generating any SQL you want, it does not validate or control it in any way, as it is not an ORM.

CSV Filter is generic, it can be used in various context for generating queries. So when you are using it specifically for IN ($1:csv), it doesn't know it, and produces again generic output.

As per the comments I left in this issue, the right approach is to check whether you have any data in your array, and if not - do not execute the query at all. First, the query would be invalid, and even if you patch it with some empty logic, that means it won't generate any result, and executing such a query becomes a waste of IO.

let result = [];
if (data.length) {
    result = await db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data]);
}
/* else: do nothing */
like image 91
vitaly-t Avatar answered Nov 08 '22 18:11

vitaly-t


Common Answer

Is this a bug or am I doing something wrong?

Not a bug, but a flaw for most SQL frameworks. It is very difficult to handle such parameters, so most frameworks just leave the empty list as it is to generate invalid SQL XXX in ().

Could there be an alternate syntax which works for both scenarios.

A simple approach is:

if(data is empty) data = [ -1 ]   //fill a non-existing id
db.any('SELECT * FROM table WHERE id IN ($1:csv)', [data])

What about knex or sequel?

They are Query Builder frameworks, so they have chances to generate special SQL to handle empty lists. Popular methods used by Query Builder frameworks to handle WHERE id in () OR ...:

  • WHERE (id!=id) OR ...
  • WHERE (1=0) OR ...
  • WHERE (1!=1) OR ...
  • WHERE false OR ...
  • etc

Personally I do not like id!=id :)

For Some Framework

You may check its manual to see if there is some way to handle empty lists, eg: can the framework replace the empty list with a non-existing value?

like image 40
shawn Avatar answered Nov 08 '22 19:11

shawn