I've come across what seems to be another ODBC driver bug - quite obscure this time.

It seems to happen when you use the DB/Text or syntax (a "/" character) in a WHERE clause which also uses an IN predicate.

Take the following example staff data:

First_name: Joe
Last_name: Bloggs
Organisation: Acme Corp

First_name: John
Last_name: Doe
Organisation: Acme Corp

This query retrieves both records, as you would expect:

SELECT first_name, last_name FROM mytextbase WHERE (last_name = 'bloggs/doe' OR first_name = 'bloggs/doe') AND organisation = 'Acme Corp'

However, this query, which is just another way of saying exactly the same thing, returns no records:

SELECT first_name, last_name FROM mytextbase WHERE (last_name = 'bloggs/doe' OR first_name = 'bloggs/doe') AND organisation IN ('Acme Corp')

It's not that the IN predicate doesn't work at all though. If you don't use the DB/Text OR syntax, the query does what you'd expect:

SELECT first_name, last_name FROM mytextbase WHERE (last_name = 'bloggs' OR first_name = 'bloggs') AND organisation IN ('Acme Corp')

Also, if you only use one DB/Text OR operator, it also works:

SELECT first_name, last_name FROM mytextbase WHERE last_name = 'bloggs/doe' AND organisation IN ('Acme Corp')

For the moment, I've used a workaround which is to use the "/" operator a third time instead of the IN predicate:

SELECT first_name, last_name FROM mytextbase WHERE (last_name = 'bloggs/doe' OR first_name = 'bloggs/doe') AND organisation = 'Acme Corp/Another organisation'