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

query on I_S crashes the server

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 5.5.20
    • None
    • None
    • None

    Description

      SQL to reproduce:

      drop table if exists test.t1;
      create table test.t1 (c1 int primary key, c2 char(5));
      set optimizer_switch="derived_with_keys=on";
      SELECT COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, COLUMNS.COLUMN_NAME, UNIQUES.INDEX_NAME, UNIQUES.COLUMN_NAMES, UNIQUES.COUNT_COLUMN_IN_INDEX, COLUMNS.DATA_TYPE, COLUMNS.CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COUNT( * ) AS COUNT_COLUMN_IN_INDEX, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC) AS COLUMN_NAMES, SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC), ',', 1) AS FIRST_COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE NON_UNIQUE=0 GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS UNIQUES ON ( COLUMNS.TABLE_SCHEMA = UNIQUES.TABLE_SCHEMA AND COLUMNS.TABLE_NAME = UNIQUES.TABLE_NAME AND COLUMNS.COLUMN_NAME = UNIQUES.FIRST_COLUMN_NAME ) WHERE COLUMNS.TABLE_SCHEMA = 'test' AND COLUMNS.TABLE_NAME = 't1' ORDER BY COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME, CASE UNIQUES.INDEX_NAME WHEN 'PRIMARY' THEN 0 ELSE 1 END, CASE IFNULL(CHARACTER_SET_NAME, '') WHEN '' THEN 0 ELSE 1 END, CASE DATA_TYPE WHEN 'tinyint' THEN 0 WHEN 'smallint' THEN 1 WHEN 'int' THEN 2 WHEN 'bigint' THEN 3 ELSE 100 END, COUNT_COLUMN_IN_INDEX;

      If we set optimizer_switch="derived_with_keys=off" then we get no crash.

      Reported by a user via IRC. The query on I_S is run by the openark online alter table tool.

      Attachments

        Activity

          People

            igor Igor Babaev
            axel Axel Schwenke
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.