Details
-
Type:
Bug
-
Status: In Review
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: N/A
-
Component/s: None
-
Labels:
Description
The problem appeared in 5.3 tree with the following revision:
revno: 3793 [merge]
revision-id: igor@askmonty.org-20140804170551-76ohozgq308a9f3a
parent: sergii@pisem.net-20140801100455-jtqd7ofg3xwau6j1
parent: igor@askmonty.org-20140801051743-7i2297y96icyx9vx
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3
timestamp: Mon 2014-08-04 10:05:51 -0700
message:
Merge.
------------------------------------------------------------
revno: 3774.1.1
revision-id: igor@askmonty.org-20140801051743-7i2297y96icyx9vx
parent: psergey@askmonty.org-20140318080632-c1pql218bfrx26y2
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.3-mdev5721
timestamp: Thu 2014-07-31 22:17:43 -0700
message:
Fixed bug mdev-5721.
Do not define a look-up key for a temporary table if its length
exceeds the maximum length of such keys.
Test case:
CREATE TABLE t1 (i INT, state VARCHAR(1024)) ENGINE=MyISAM; INSERT INTO t1 VALUES (2,'Louisiana'),(9,'Maine'); CREATE TABLE t2 (state VARCHAR(1024), j INT) ENGINE=MyISAM; CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v2 AS SELECT * FROM t2; INSERT INTO t2 VALUES ('Louisiana',9),('Alaska',5); SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN v2 ON t1_2.i = j ) ON t1_1.state = v2.state; DROP VIEW v2; DROP TABLE t1, t2;
Expected result:
i state i state state j 2 Louisiana 9 Maine Louisiana 9 9 Maine NULL NULL NULL NULL
Actual result:
i state i state state j 2 Louisiana NULL NULL NULL NULL 9 Maine NULL NULL NULL NULL
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It looks like problem is here. It should exclude allkeys, but exclude none.
The second test suite is a different bug you can report it separately or I will.
Sent for review.
http://lists.askmonty.org/pipermail/commits/2014-October/006694.html
looks like this hasn't been committed yet (not in 5.5 or 10.0 trunk/10.0 branch)
yes
The same problem exists for derived tables (AKA subquery in the FROM clause):
set @@optimizer_switch='derived_merge=OFF';
SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN (SELECT * FROM t2) v2 ON t1_2.i = j ) ON t1_1.state = v2.state;
i state i state state j
2 Louisiana NULL NULL NULL NULL
9 Maine NULL NULL NULL NULL