[evanelias]

> It's more explicit than MySQL, so in that way it's better.

It sounds like you're under the impression that MySQL just makes everything case-insensitive and is silent about this? That's decidedly not the case.

MySQL 8 ships with 41 different character sets, supporting a total of 286 different collations. Collation names explicitly include "ci" (case-insensitive) vs "cs" (case-sensitive), as well as "ai" (accent-insensitive) vs "as" (accent-sensitive), and also the language/region conventions used for sorting purposes.

You can choose collation at the column-level granularity, as well as setting defaults at the table, schema, and server levels. It's completely explicit and very configurable.

[arp242]

There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.

[evanelias]

Eh, just from a SQL query alone, there's no way to see that (lower(email)) is indexed, or see column data types etc. That metadata lives in the table definition, which is a normal place for it, so it seems reasonable for the collation / case-insensitivity to not be explicit in the query text.

Besides, MySQL also supports functional indexes, so you could do the (lower(email)) approach in MySQL too if you really want!

[arp242]

No, you can't see everything, but you can see the exact comparison it's making. Is that useful? You can decide that for yourself but I like being able to see as much of the logic (and thus verify the correctness) in the query itself. Also helps with copy/paste and some other things.

I never said you can't use functional indexes in MySQL. Someone said "you can't do this in PostgreSQL" and I just commented "here's (one way) to do this, maybe that's helpful some day". That's it.

[evanelias]

My apologies, I misunderstood "It's more explicit than MySQL" to imply that you were saying that approach couldn't be used in MySQL.