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

LP:798597 - Incorrect "Duplicate entry" error with views and GROUP BY in maria-5.3

    XMLWordPrintable

Details

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

    Description

      The following query:

      SELECT COUNT, v2.f3, v2.f10, v2.f1
      FROM t1 LEFT JOIN (v2, t3) ON 1
      GROUP BY v2.f3, v2.f10, v2.f1 ;

      returns the following error:

      1062: 'Duplicate entry 'NULL-NULL-0' for key 'group_key'

      The "group_key" is not reflected in the EXPLAIN and derived* optimizer switches have no effect.

      explain:

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
      1 SIMPLE t3 ALL NULL NULL NULL NULL 0 Using where
      1 SIMPLE t2 ALL NULL NULL NULL NULL 2

      test case:

      CREATE TABLE t1 ( f1 int) ;
      INSERT INTO t1 VALUES (19),(20);

      CREATE TABLE t2 (f1 int not null, f3 int, f10 int) ;
      INSERT INTO t2 VALUES (19,1,NULL),(20,5,0);
      CREATE VIEW v2 AS SELECT * FROM t2;

      CREATE TABLE t3 (f1 int);

      EXPLAIN SELECT COUNT, v2.f3, v2.f10, v2.f1
      FROM t1 LEFT JOIN (v2, t3) ON 1
      GROUP BY v2.f3, v2.f10, v2.f1 ;

      --error 0
      SELECT COUNT, v2.f3, v2.f10, v2.f1
      FROM t1 LEFT JOIN (v2, t3) ON 1
      GROUP BY v2.f3, v2.f10, v2.f1 ;

      Attachments

        Activity

          People

            monty Michael Widenius
            philipstoev Philip Stoev (Inactive)
            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.