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

A query triggers a logic bug and a performance bug

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 11.7.2
    • N/A
    • Optimizer
    • None
    • docker

    Description

      q1: SELECT /*+ JOIN_ORDER(tables, views) */ * FROM tables CROSS JOIN views ON tables.table_name != views.table_name
      q2: SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name;
      Since q1 is equivalent to q2, q1 EXCEPT q2 should always return an empty set and consume minimal execution time. However, the query returns a non-empty result and comsume 5.843 sec computation time.

      Repeat it as following:
      MariaDB > use information_schema;
      MariaDB [information_schema]> SELECT /*+ JOIN_ORDER(tables, views) */ * FROM tables CROSS JOIN views ON tables.table_name != views.table_name EXCEPT (SELECT * FROM tables CROSS JOIN views ON tables.table_name != views.table_name);
      ...
      8668 rows in set (5.843 sec)

      Attachments

        Activity

          People

            danblack Daniel Black
            jinhui lai jinhui lai
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.