Table Name Case Sensitivity

Our main database for our organization is Oracle-based and we have tons of complicated queries already created for it. I’ve recently spun back up a MemSQL environment for me to copy some of our Oracle tables into and we had one of our Researchers trying to run an existing query in MemSQL and one of the issues they ran into was that they had typed in their table names in lowercase rather than in uppercase which MemSQL expects.

I ran a quick Google search about the issue and came back with the following suggestion which mentions creating a VIEW for each table name with the lowercase (or other mixed-case) variation you might need to support:

That’s not a bad solution to implement, however I am wondering about why this has to be an issue in the first place?

Particularly because it seems to be that the SQL Parser for MemSQL really only cares about this requirement in the FROM clause of the query being provided because in the example I helped my colleague out with, I only made the adjustments there and was able to get the query working (elsewhere in the query’s SELECT and WHERE/AND, GROUP BY, and ORDER BY clauses the field names were provided in mixed case which MemSQL had no problems in interpreting/processing correctly).

Memsql is case sensitive when it comes to table snd view names. But both lower and upper case sql will work.

Support for case-insensitive table names is on our near-term roadmap. Expect it in the next major release. I cannot give you a specific date at this point.

There’s a new variable table_name_case_sensitivity in 7.0.11 and later that lets you turn on support for case-insensitive table names. It has a strong restriction: the cluster must be empty of user databases when you change it. So do it up front.

What about case insensitive column names?

Hi @paul.ernsdorff – sorry I never saw this. Just came back to it. Case insensitive column names are the default.

MemSQL 7.1, which just shipped, now allows you to restore a backup taken with a different setting of table_name_case_sensitivity than the one you’re restoring into. The main use case is to move to move an existing database to a cluster that has table_name_case_sensitivity = OFF. Be sure you have no duplicate names like t and T before you do your backup. You can check that like so:

select table_schema, lower(table_name), count(*) as c
from information_schema.TABLES
group by 1, 2
having c > 1;

Note that the TABLES table includes views as well so there’s no need to separately query the VIEWS table.