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

Unexpected results when selecting on information_schema

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.1, 5.5.30, 5.3.12
    • Fix Version/s: 10.0.2, 5.5.31, 5.3.13
    • Component/s: None
    • Labels:
    • Environment:
      Arch Linux (up-to-date 2013/03/27) under Virtuozzo. Issue occurs when connected through ssh tunnel with Sequel Pro and when using mysqli in php (at least).

      Description

      I have a metabase which contains references to databases on a user group basis (so every user has a group column, and databases are linked to groups). In my php script, I used to be able to get the allowed tables for a user (under MySQL 5.5) with the following query:

      SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") AND 
      	`db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

      The last check (to the information_schema database) was only included to make sure no non-existent databases are presented to the user. After upgrading to MariaDB 5.5.30, this is not working anymore, it simply returns an empty result set.

      Currently, I've removed the extra check on the existence of the database:

      SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") ORDER BY `db` DESC;

      which works, but does risk showing non-existent databases.
      I've also tried to work with an inner join:

      SELECT * FROM `web_dbs` INNER JOIN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) AS scm ON `web_dbs`.`db`=scm.`SCHEMA_NAME` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user")  ORDER BY `web_dbs`.`db` DESC;

      which also works, but is semantically less attractive to me.

      I expected MariaDB to work with the same query that worked under MySQL 5.5, so I think this should be fixed.

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              martenjacobs Marten Jacobs
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: