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

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

        Details

        • Type: Bug
        • Status: Open
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 5.3.12, 5.5.39, 10.0.13
        • Fix Version/s: 10.0, 5.5
        • Component/s: None
        • Labels:
          None

          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,...>.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    psergey Sergei Petrunia
                    Reporter:
                    elenst Elena Stepanova
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: