Multiple Crimes

January 29, 2011

mysql> select "a" = "A";
+-----------+
| "a" = "A" |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

WTF? (via Nuxeo)

Filed under mysql

5 comments

Comments are closed. These are preserved from the original site.

me — January 29, 2011

You are using a case-insensitive collation?

mysql> SHOW VARIABLES LIKE 'collation%';

Michael Janssen — January 29, 2011

This is true in almost all of the SQL databases that I know, and I would actually have it no other way for the default. Most of the time, you don't care about case sensitivity. All the other times, you can use binary, or change the collation of the table to turn off the insensitiveness.

siccegge — January 30, 2011

Huch almost all databases?

postgres=> select 'a' = 'A';
?column?
----------
f
(1 row)

sqlite> select 'a' = 'A';
0

That's after fixing the double-quotes that are not allowed by standard SQL

djadala — January 30, 2011

also oracle:

SQL> select case when ('a'='A') then 1 else 0 end from dual ;

CASEWHEN('A'='A')THEN1ELSE0END
------------------------------
0

SQL> select case when ('a'='a') then 1 else 0 end from dual ;

CASEWHEN('A'='A')THEN1ELSE0END
------------------------------
1

Mike — February 14, 2011

> This is true in almost all of the SQL databases that I know, and I would actually have it no other way for the default.

ha

psql=> select 'a' = 'A';
?column?
----------
f
(1 row)