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

LP:715069 - Wrong result with GROUP BY inside subquery and materialization=off in maria-5.3-mwl89

    XMLWordPrintable

Details

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

    Description

      Not repeatable with maria-5.3 . The query below does not return any rows even though the row 8,8 matches both the ON and the WHERE predicates.

      CREATE TABLE t1 ( f1 int(11), f2 int(11), f10 varchar(1), PRIMARY KEY (f1)) ;
      INSERT INTO t1 VALUES (8,8,'u'),(10,5,'o');

      SET SESSION optimizer_switch = 'materialization=off';
      SELECT alias2.f1 , alias2.f2
      FROM t1 AS alias1
      RIGHT JOIN t1 AS alias2 ON alias2.f10
      WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t1 GROUP BY f2 , f1 );

      explain:

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where
      1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index
      2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort

      correct result:

      SET SESSION optimizer_switch = 'materialization=on';
      SELECT alias2.f1 , alias2.f2 FROM t1 AS alias1 RIGHT JOIN t1 AS alias2 ON alias2.f10 WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t1 GROUP BY f2 , f1 );
      f1 f2
      8 8

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            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.