Case insensitivity not applied while coalescing

(1) By Amjith (amjith) on 2022-10-17 22:51:57 [link] [source]

I have a table with case-insensitive columns. When I do SELECT DISTINCT email FROM apps; I get the right values but if I use COALESCE function then the DISTINCT clause isn't effective anymore.

Here's an example.

sqlite> CREATE TABLE apps (                                                                                                                                      
        appname VARCHAR COLLATE "NOCASE",                                                                                                                        
        email VARCHAR COLLATE "NOCASE",
        alt_email VARCHAR COLLATE "NOCASE");
sqlite> insert into apps values ("app1", "app@example.com", "app@example.com");
sqlite> insert into apps values ("app2", "app@Example.com", "app@example.com");                                                                                  
sqlite> select * from apps;
app1|app@example.com|app@example.com
app2|app@Example.com|app@example.com
sqlite> select distinct email from apps;
app@example.com
sqlite> select distinct coalesce(email, alt_email) from apps;
app@example.com
app@Example.com
sqlite>

Shouldn't the select distinct coalesce(email, alt_email) from apps; statement also return only one email instead of the two values with case variation?

(2) By Keith Medcalf (kmedcalf) on 2022-10-18 00:17:09 in reply to 1 [source]

Note:

sqlite> CREATE TABLE apps (
   ...> appname VARCHAR COLLATE 'NOCASE',
   ...> email VARCHAR COLLATE 'NOCASE',
   ...> alt_email VARCHAR COLLATE 'NOCASE');
sqlite> insert into apps values ('app1', 'app@example.com', 'app@example.com');
sqlite> insert into apps values ('app2', 'app@Example.com', 'app@example.com');
sqlite> select * from apps;
┌─────────┬───────────────────┬───────────────────┐
│ appname │       email       │     alt_email     │
├─────────┼───────────────────┼───────────────────┤
│ 'app1'  │ 'app@example.com' │ 'app@example.com' │
│ 'app2'  │ 'app@Example.com' │ 'app@example.com' │
└─────────┴───────────────────┴───────────────────┘
sqlite> select distinct email from apps;
┌───────────────────┐
│       email       │
├───────────────────┤
│ 'app@example.com' │
└───────────────────┘
sqlite> select distinct coalesce(email, alt_email) from apps;
┌────────────────────────────┐
│ coalesce(email, alt_email) │
├────────────────────────────┤
│ 'app@example.com'          │
│ 'app@Example.com'          │
└────────────────────────────┘
sqlite> select distinct coalesce(email, alt_email) collate nocase from apps;
┌───────────────────────────────────────────┐
│ coalesce(email, alt_email) collate nocase │
├───────────────────────────────────────────┤
│ 'app@example.com'                         │
└───────────────────────────────────────────┘
sqlite>

coalesce(email, alt_email) is an expression, hence has no affinity and no collation (or rather, the default binary collation), and is upon what DISTINCT operates. Once you "attach" a collation to the expression, the DISTINCT operator uses that collation.

(3) By Amjith (amjith) on 2022-10-19 02:34:51 in reply to 2 [link] [source]

Thank you!

That makes sense.