Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.39, 10.0.13, 10.1.30
-
None
-
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,...>.
|