Using LOWER() in a condition is slow as it can't use an index. It was necessary in D6 to ensure consistent, case insensitive comparisons as LIKE in MySQL does by default but requires ILIKE IN PostgreSQL.

DBTNG automatically uses the case insensitive version of LIKE wich allows to remove the LOWER().

CommentFileSizeAuthor
lower.patch544 bytesberdir

Comments

simon georges’s picture

simon georges commented

Patch looks simple enough, but can you justify what you're saying about the DBTNG LIKE comparison ? I didn't find any reference to detail that. I'm willing to commit that as soon as I understand it ;-)

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Issue tags: +Needs tests

Sure, that's defined in http://api.drupal.org/api/drupal/includes--database--pgsql--database.inc... for PostgreSQL.

It might also be useful to add a test which can be run on MySQL and PostgreSQL for this, before commiting it..

simon georges’s picture

simon georges commented

What about other database systems, like SQLLite, ... ? Do you know if some could eventually have a problem with the LIKE ? (I promise, it's my last question before the commit ;-)).

miro_dietiker’s picture

miro_dietiker
Location Switzerland
commented

It's the job of the DB abstration layer to cover this cleanly.

You see, originally it was a string condition (that is NOT altered for DB backends).
While the new code suggestion adds a condition of type "LIKE" that allows to implement this condition type in custom SQL dialect.

simon georges’s picture

simon georges commented

Agreed. And committed.
Should we let the issue open because it needs tests, or considering it's all part of the DB Layer and, thus, already covered by our existing tests ?

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Status: Needs review » Needs work

Keeping this open to add some tests sounds good to me..

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Title: Replace LOWER() with LIKE condition » Add tests for replacing LOWER() with LIKE condition
anavarre’s picture

anavarre
Primary language French
Location 🇪🇺
commented

For the record, Login Toboggan got the same issue: http://drupal.org/node/1138208

berdir’s picture

berdir
Primary language German
Location Switzerland
commented
Assigned: Unassigned » corvus_ch

corvus_ch’s picture

corvus_ch commented
Assigned: corvus_ch » Unassigned
Issue summary: View changes