I'm marking this critical because we want to get it worked out before we start converting all of core. :-)

The new DB API includes the ability to "remap" WHERE clause operators in dynamic queries. That is, if the user passes in "LIKE" we can turn that into "ILIKE" on Postgres, which is case-insensitive like MySQL. Neat!

However, that then begs the question of whether or not we should map LIKE to ILIKE like that. Don't we sometimes want case-sensitive LIKE? Frankly I don't know, but we should work out some set of rules for how we want to map certain operators to certain other operators; and remember, it's for dynamic queries only. Static queries (db_query()) don't get remapped.

Discuss. :-)

Comments

chx’s picture

chx commented

So there are two things we can do, here. Call the case insensitive operator "LIKE". For MySQL coders this is how the world behaves. For PostgreSQL we can remap to "ILIKE" for consistency. Then we need to invent our own case sensitive version of LIKE -- for example "CSLIKE" which maps to "LIKE ':argument' COLLATE utf8_bin" for MySQL (@Crell, can the code do that now?) and maps to "LIKE" for PostgreSQL.

We also can decide that "LIKE" is case sensitive and "ILIKE" is case insensitive. The PostgreSQL folks will be happy. MySQL folks much less so. Also, because most contrib workers are on MySQL solely, they will, no doubt @#$%^&*( this up and not even their tests will reveal these bugs easily. I dislike this situation.

Crell’s picture

Crell commented

Hm. No, right now we can't do a full fragment replacement, just an operator replacement. We can probably modify the code to support it, but I'm not sure off hand how difficult it would be.

Paul Natsuo Kishimoto’s picture

Paul Natsuo Kishimoto commented

...found this poking around the bottom of the D7 critical issue queue.

includes/database/pgsql/database.inc currently has a default LIKE → ILIKE mapping. In #333054: Make all varchar columns case sensitive (regression) (was make page cache case sensitive) the mapping was removed, and then the removal was reverted.

The other issue remains active, is critical for D7 and contains the discussion called for here. Although this issue is older, can it be marked 'duplicate'?

Crell’s picture

Crell commented
Status: Active » Closed (duplicate)

Hm. Wow, people really don't read issues I guess. :-) Thanks for the note. I'm marking this as a dupe.