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

Wrong result (missing rows) with joins, SQ, ORDER BY, semijoin=on

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.3.12, 5.5.39, 10.0.13, 10.1.30
    • Fix Version/s: 5.5.59
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      5.5.59

      Description

      Test case:

      CREATE TABLE t1 (f1 VARCHAR(3)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('foo');
       
      CREATE TABLE t2 (f2 VARCHAR(3)) ENGINE=MyISAM;
      INSERT INTO t2 VALUES ('bar'),('baz');
       
      CREATE TABLE t3 (i3_key INT, f3_key VARCHAR(3), f3 VARCHAR(3), KEY(f3_key,i3_key)) ENGINE=MyISAM;
      INSERT INTO t3 VALUES (0,'qux','qux'),(8,'bar','bar');
       
      SELECT CONCAT( f1, f2 ) AS field FROM t1, t2 WHERE f1 = ANY ( 
        SELECT f1
        FROM t1 LEFT JOIN ( t3 AS t3a, t3 AS t3b ) ON ( t3b.f3_key = t3a.f3 ) 
        WHERE t3a.f3 < f1 OR t3b.f3 != f1 
      )
      ORDER BY field;

      Actual result:

      field
      foobar

      Result without ORDER BY or with semijoin=off:

      field
      foobar
      foobaz

      The problem appeared in 5.3 tree with the following revision:

      revno: 3604 [merge]
      revision-id: igor@askmonty.org-20121122183039-dm575rqkw40558qp
      parent: sergii@pisem.net-20121117155015-4ab41ncach4iavao
      parent: igor@askmonty.org-20121122055504-vdyz74a9d7n48x0o
      committer: Igor Babaev <igor@askmonty.org>
      branch nick: maria-5.3
      timestamp: Thu 2012-11-22 10:30:39 -0800
      message:
        Merge
          ------------------------------------------------------------
          revno: 3602.1.1
          revision-id: igor@askmonty.org-20121122055504-vdyz74a9d7n48x0o
          parent: knielsen@knielsen-hq.org-20121120125749-l55gvliervi5nnaf
          committer: Igor Babaev <igor@askmonty.org>
          branch nick: maria-5.3-mdev645
          timestamp: Wed 2012-11-21 21:55:04 -0800
          message:
            Fixed LP bug #1002146 (bug mdev-645).
            If the setting of system variables does not allow to use join buffer
            for a join query with GROUP BY <f1,...> / ORDER BY <f1,...> then
            filesort is not needed if the first joined table is scanned in
            the order compatible with order specified by the list <f1,...>.

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: