0

I am not able to use nocase collation within IN clause.

When I am querying data there are no rows (expected result due to case sensitive collation).

import duckdb
con = duckdb.connect(database=':memory:')

con.execute("SELECT col1, col2 FROM (values ('b', 'd'), ('c', 'a')) a (col1, col2) where col1 = 'C'")
print(con.fetchall())  # output: []

When I change the collation to nocase everything works like a charm.

_ = con.execute("PRAGMA default_collation=nocase")
con.execute("SELECT col1, col2 FROM (values ('b', 'd'), ('c', 'a')) a (col1, col2) where col1 = 'C'")
print(con.fetchall())  # output: [('c', 'a')]

But when I use capital C in IN clause there is again no result:

_ = con.execute("PRAGMA default_collation=nocase")
con.execute("SELECT col1, col2 FROM (values ('b', 'd'), ('c', 'a')) a (col1, col2) where col1  in ('C') ")
print(con.fetchall())  # output []

I am using duckdb==0.6.0. Any Ideas?

Peter Trcka
  • 1,279
  • 1
  • 16
  • 21

0 Answers0