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

Unexpected results when selecting on information_schema

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.1, 5.5.30, 5.3.12
    • 10.0.2, 5.5.31, 5.3.13
    • None
    • 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

            psergei Sergei Petrunia
            martenjacobs Marten Jacobs
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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