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

DUPLICATE KEY Errors on SELECT .. GROUP BY that uses temporary and filesort

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.37, 10.0.10
    • Fix Version/s: 10.0.14
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Windows, Linux

      Description

      Import the table provided into a MariaDB 5.5 and/or 10.0 instance, ensure your max_heap_table_size and tmp_table_size are 32M or less, and issue these queries:

      SELECT * FROM t_album2 GROUP BY Composer;
      SELECT Composer FROM t_album2 GROUP BY Composer;

      It returns DUPLICATE KEY errors:
      ERROR 1034 (HY000): Duplicate key 1 for record at 583508 against record at 11380

      Here is the table structure:

      CREATE TABLE `t_album` (
      `id_album` int(11) NOT NULL AUTO_INCREMENT,
      `Composer` varchar(255) COLLATE utf8_swedish_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`id_album`)
      ) ENGINE=InnoDB AUTO_INCREMENT=1050654 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

      The GROUP BY must be in the query to trigger the error.

      Notice that if I increase max_heap_table_size and tmp_table_size (512M seems to work here), then the query runs as expected.

      SET SESSION max_heap_table_size=536870912; 
      SET SESSION tmp_table_size=536870912;

      After-thought: With the same tmp/heap table sizes, the query runs fine in MySQL 5.5.37, though the bug looks somewhat similar to this open MySQL bug:

      http://bugs.mysql.com/bug.php?id=58081

        Attachments

          Activity

            People

            Assignee:
            monty Michael Widenius
            Reporter:
            ccalender Chris Calender
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: