Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31472

Wrong results in queries to I_S for table/schema having capitals in the name

    XMLWordPrintable

Details

    Description

      If the server has lower_case_table_names=2(I tested on Windows, and did not verify on MacOS) and table or schema name contains capital letter like

      CREATE TABLE TestAccount(id INT NOT NULL);
      

      then various queries comparing those names will return those in all lower case letters like:

      select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database() AND TABLE_NAME = 'TestAccount';
      +--------------+
      | TABLE_NAME   |
      +--------------+
      | testaccount |
      +--------------+
      1 row in set (0.000 sec)
      select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database();
      +--------------+
      | TABLE_NAME   |
      +--------------+
      | TestAccount  |
      +--------------+
      1 row in set (0.001 sec)
      

      It will happen on any table. Or for schema name.
      More over, it's not possible to compare such name in case sensitive manner

      select TABLE_NAME FROM INFORMATION_SCHEMA.statistics WHERE TABLE_SCHEMA=database() AND TABLE_NAME = BINARY 'TestAccount';
      0 rows in set (0.000 sec)
      

      e.g. application can query schema table names from TABLES, get it with real cases of all letters, and then compare that name BINARY in COLUMNS or other table, and get nothing. If developer wants the application to be portable, I can imagine that he/she will want to compare names case sensitively.
      I've tested this with 10.11, but I am pretty sure it's not specific to this version - initial ODBC report mentioned 10.3

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              Lawrin Lawrin Novitsky
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.