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

Unexpected results when querying information_schema

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.38, 10.0.11
    • Fix Version/s: 5.5.39, 10.0.13
    • Component/s: None
    • Labels:
    • Environment:
      Arch Linux (up-to-date 2014/06/02) under Virtuozzo. Issue occurs when connected through ssh tunnel with Sequel Pro and when using mysqli in php (at least).

      Description

      This issue is similar (but not exactly the same) to an issue I've reported earlier under MDEV-4335.

      I'm using information_schema.SCHEMATA to make sure that a database exists before switching to it in my PHP app.

      The issue:

      The following query unexpectedly returns an empty result set on this version (I've had no issues on previous versions, except the one in the issue I've mentioned before):

      SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

      This is unexpected, because the following query returns multiple rows, while it should be more restrictive:

      SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username` = '[REDACTED]') AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

      Upon further research I found out that removing the ORDER BY clause from the first query helps, so for now I will change the first query into the following, which does work (but will probably have decreased performance):

      SELECT * FROM (SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA)) tbl ORDER BY `db`;

        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: