MySQL and MariaDB on Linux Treat Table Names as Case Sensitive

Code that creates a table as Users and later reads users works on SQLite and PostgreSQL. On MySQL or MariaDB running on Linux it fails, since users is another table that doesn’t exist, while the same code on a Mac works, which makes the bug easy to miss.

MySQL and MariaDB keep each table in files named after it, and file names on Linux are case-sensitive (lower_case_table_names = 0, the default there). On Windows and macOS the default makes names case-insensitive. SQLite and PostgreSQL ignore case in unquoted names.

What Each Engine Answered

The example

CREATE TABLE Users (id INTEGER);
SELECT COUNT(*) FROM users;

gave these answers:

What to Write Instead

This form answers the same on every engine:

CREATE TABLE users (id INTEGER);
SELECT COUNT(*) FROM users;

Use lower-case table names and spell each one the same way everywhere.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:2:22: error: The table is created as Users and named users here: MySQL and MariaDB on Linux find no table users. [table-name-case]
    users
    Use lower-case table names and spell each one the same way everywhere.

1 problem: 1 error.

baresql rules table-name-case prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.