Bug #8873 | Merge table with INDEX statements out of order returns NULL for index colums | ||
---|---|---|---|
Submitted: | 1 Mar 2005 20:13 | Modified: | 8 Oct 2008 19:40 |
Reporter: | Edward Sweeney | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Merge storage engine | Severity: | S2 (Serious) |
Version: | 4.1.8a, 4.1, 5.0, 5.1, 6.0 bzr | OS: | Linux (Linux 2.4.21-4 ELsmp) |
Assigned to: | CPU Architecture: | Any |
[1 Mar 2005 20:13]
Edward Sweeney
[1 Mar 2005 21:09]
MySQL Verification Team
Hello Edward, As far as I understand your MERGE table definition is different from sub-table definitions. That is why you get unexpected result (unexpected definition -> unexpected result). Though in this case error or warning message should be provided during MERGE table creation. This is a bit compelx to fix it now, but it will be fixed in the future MySQL releases.
[1 Mar 2005 22:15]
Edward Sweeney
If you are saying that the column definition on the merge table is different from the sub-tables, that is incorrect. If you are saying that the Index definition on the Merge table is different from the sub-tables, you would be correct if the order of definition of the indexes mattered to the successful creation of the Merge table--but it doesn't. The merge table gets created successfully but, when queried, returns invalid results for some columns. The Merge table does not have the same syntax as a sub-table anyway so I don't understand in what sense you mean "identical". My point is that the order in which indexes get created moves the "identity" from WHAT indexes exist to HOW they were created.
[29 Sep 2008 21:31]
Konstantin Osipov
We have been fixing quite a few merge issues in 5.1 and 6.0. Note to the verifier: please check in which version the bug is actually fixed. Thank you.
[8 Oct 2008 19:40]
Sveta Smirnova
Bug is not fixed yet. Test case: $cat src/tests/t/bug8873.test create table t1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)); create table t2 like t1; create table t3 like t1; create table t4 like t1; create table t5 like t1; create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); insert into t1 values(1,1,1,1,1,1,1,1,1,1); insert into t2 select * from t1; insert into t3 select * from t1; insert into t4 select * from t1; insert into t5 select * from t1; select f5, f6 from tm1; select * from tm1; drop table tm1; alter table t1 drop index f5; alter table t2 drop index f5; alter table t3 drop index f5; alter table t4 drop index f5; alter table t5 drop index f5; alter table t1 add index(f5,f6); alter table t2 add index(f5,f6); alter table t3 add index(f5,f6); alter table t4 add index(f5,f6); alter table t5 add index(f5,f6); analyze table t1; analyze table t2; analyze table t3; analyze table t4; analyze table t5; create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); flush table tm1; select f5, f6 from tm1; Result in 4.1-bzr: =====mysql-4.1===== =====bug8873===== create table t1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)); create table t2 like t1; create table t3 like t1; create table t4 like t1; create table t5 like t1; create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); insert into t1 values(1,1,1,1,1,1,1,1,1,1); insert into t2 select * from t1; insert into t3 select * from t1; insert into t4 select * from t1; insert into t5 select * from t1; select f5, f6 from tm1; f5 f6 1 1 1 1 1 1 1 1 1 1 select * from tm1; f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 drop table tm1; alter table t1 drop index f5; alter table t2 drop index f5; alter table t3 drop index f5; alter table t4 drop index f5; alter table t5 drop index f5; alter table t1 add index(f5,f6); alter table t2 add index(f5,f6); alter table t3 add index(f5,f6); alter table t4 add index(f5,f6); alter table t5 add index(f5,f6); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status OK analyze table t3; Table Op Msg_type Msg_text test.t3 analyze status OK analyze table t4; Table Op Msg_type Msg_text test.t4 analyze status OK analyze table t5; Table Op Msg_type Msg_text test.t5 analyze status OK create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); flush table tm1; select f5, f6 from tm1; f5 f6 0 0 0 0 0 0 0 0 0 0 Result since 5.0-bzr (same with 5.1 and 6.0): =====mysql-5.0===== =====bug8873===== create table t1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)); create table t2 like t1; create table t3 like t1; create table t4 like t1; create table t5 like t1; create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); insert into t1 values(1,1,1,1,1,1,1,1,1,1); insert into t2 select * from t1; insert into t3 select * from t1; insert into t4 select * from t1; insert into t5 select * from t1; select f5, f6 from tm1; f5 f6 1 1 1 1 1 1 1 1 1 1 select * from tm1; f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 drop table tm1; alter table t1 drop index f5; alter table t2 drop index f5; alter table t3 drop index f5; alter table t4 drop index f5; alter table t5 drop index f5; alter table t1 add index(f5,f6); alter table t2 add index(f5,f6); alter table t3 add index(f5,f6); alter table t4 add index(f5,f6); alter table t5 add index(f5,f6); analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status OK analyze table t2; Table Op Msg_type Msg_text test.t2 analyze status OK analyze table t3; Table Op Msg_type Msg_text test.t3 analyze status OK analyze table t4; Table Op Msg_type Msg_text test.t4 analyze status OK analyze table t5; Table Op Msg_type Msg_text test.t5 analyze status OK create table tm1(f1 int not null, f2 int not null, f3 int not null, f4 int not null, f5 int not null, f6 int not null, f7 int not null, f8 int not null, f9 int not null, f10 int not null, index(f1,f2), index(f3,f4), index(f5,f6), index(f7,f8), index(f9,f10)) engine=merge union(t1,t2,t3,t4,t5); flush table tm1; select f5, f6 from tm1; f5 f6 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811 -1515870811