Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
create table ten(a int primary key); |
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); |
create table one_k(a int primary key); |
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; |
create table t1 (a varchar(750), b varchar(750)); |
insert into t1 select a,a from one_k; |
create table t2 as select a from t1 limit 10; |
create ALGORITHM=TEMPTABLE view v1 as select a,b from t1; |
set optimizer_switch='derived_with_keys=on'; |
set join_cache_level=4; |
explain select * from t1, v1 where t1.a=v1.a;
|
id select_type table type possible_keys key key_len ref rows Extra
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 1000 Using where
|
1 PRIMARY <derived2> hash_ALL key0 #hash#key0 753 test.t1.a 1000 Using join buffer (flat, BNLH join)
|
2 DERIVED t1 ALL NULL NULL NULL NULL 1000
|
In the explain output we see for <derived2> key #hash#key0 , where key0 is the key on the derived table created by the derived_with_keys optimization.
If the keys on derived table are created and it is the best choice plan then we will always do a ref access on the derived table with key0 (key on derived table).
If the hash join is preferred then we need to drop the derived keys, we cannot perform hash join with these keys
Attachments
Issue Links
- relates to
-
MDEV-16307 Incorrect results when using BNLH join instead of BNL join with views
- Closed