Strange behaviour with MySql case sensitive table name in windows

In my windows machine when I select table names from mysql using the following query I'm getting table names as case sensitive.

mysql> select table_schema, table_name 
from information_schema.tables where table_schema='test';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| test         | TableOne   |
| test         | TableTwo   |
+--------------+------------+

2 rows in set (0.00 sec)

But when I select by table name i'm getting different result.

mysql> select table_schema, table_name from information_schema.tables 
where table_schema='test' and table_name = 'TableOne';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+

| test         | tableone   |
+--------------+------------+
1 row in set (0.00 sec)

What made it even stranger is this.

mysql> select table_schema, table_name from information_schema.tables 
where table_schema='test' and table_name like 'TableOne';
+--------------+------------+

| table_schema | table_name |
+--------------+------------+
| test         | TableOne   |
+--------------+------------+
1 row in set (0.00 sec)

Answer

There is a MySql variable called lower_case_table_names. When this is set to 0 table names are case sensitive. But this is not advised on a case insensitive machine like windows. The next option is 1. In this option all table names are converted to lower case even before it is stored. You'll always get lower case table names in this case. In my case the value of this variable is set to 2. And in this case MySql stores table names as such, but when we compare table names, it'll convert them to lower case and compare.

So In the first case table name is not compared, hence we get the original value.

In the second case we are comparing the table name, so mysql convert table name to lower case for comparison. But strangely they are returning the converted value, not the original.

And finally in the third case we are using like operator which itself is case insensitive, hence mysql doesn't bother to convert table name to lower case, and we get the original result.