If you’re getting an error that reads “Binder Error: ‘list_has_all’ cannot compare lists of different types” in DuckDB, it appears that you’re passing two different types of lists to the list_has_all() function.

DuckDB’s list_has_all() function accepts two lists as arguments. These need to be of the same type.

To fix this issue, make sure that both lists are of the same type.

Example of Error

Here’s an example of code that produces the error:

SELECT list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        [1,2]
    );

Output:

Binder Error: 'list_has_all' cannot compare lists of different types: 'VARCHAR' and 'INTEGER'

In this case my first list contains strings, but my second list contains numbers. That’s a no-no in DuckDB.

We can get the same error when passing a nested list:

SELECT list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        [['Zebra', 'Cat']]
    );

Output:

Binder Error: 'list_has_all' cannot compare lists of different types: 'VARCHAR' and 'VARCHAR[]'

In this case the second list contains a nested list (i.e. a list within a list), but the first list doesn’t. DuckDB doesn’t like this, and so an error is returned.

Solution

The solution is to pass two lists of the same type.

So to fix the first query, we might do something like this:

SELECT list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        ['Bird','Zebra']
    );

Output:

true

This time it ran without error. That’s because both lists contain strings.

As for the second list, we could either change the first list, or change the second one.

Here’s an example of changing the first list:

SELECT list_has_all(
        [['Cat', 'Dog', 'Bird', 'Zebra']], 
        [['Zebra', 'Cat']]
    );

Output:

false

Here, simply surrounded the first list in square brackets, so that it became a nested list (like the second list).

Another way of dealing with the issue is to unnest or flatten the second list. To flatten the list, we can use the flatten() function:

SELECT list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        flatten([['Zebra', 'Cat']])
    );

Output:

true

The flatten() function concatenates nested lists into one single list.

Here’s an example that uses the unnest() function:

SELECT list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        unnest([['Zebra', 'Cat']])
    );

Output:

true

The unnest function removes the nesting so that the inner list becomes a standalone, unnested list. By default, it only goes one level deep, but we can specify a maximum depth by specifying max_depth := 2 (or whatever depth we need) if required:

SELECT 
    list_has_all(
        ['Cat', 'Dog', 'Bird', 'Zebra'], 
        unnest([[['Zebra', 'Cat']]], max_depth := 2)
    );

Output:

true