MySQL Bugs: #8873: Merge table with INDEX statements out of order returns NULL for index colums

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:	None
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

Description:
We have a test setup with 9 identical sub-tables defined, each with 5 indexes created in the same order. The CREATE merge table has an INDEX (col1, col2) statement corresponding with each of the indexes and in the order of the index creation. Everything works fine, checking with explain plans on test queries for "partition elimination".

Then the following steps are taken:

1. Drop merge table
2. Drop Index #3 on all tables
3. re-create index #3
4. Analyze all sub-tables
5. CREATE MERGE table (unchanged)
6. FLUSH TABLE

Now, when querying the Merge table and selecting on the second column in Index defintion #3, the query returns NULLs for the column and no error is produced. The underlying sub-tables are all NOT NULL for this column. BUG: We should not be able to return invalid results in the MERGE table query.

How to repeat:
The bug is repeatable by having the INDEX syntax in the Merge table defintion in a different order than the indexes were created on the sub-tables.

Suggested fix:
Have the MERGE table engine use a dictionary to find the appropriate indexes and column order on each of the sub-tables and store that information at CREATE time instead of the (changeable) ordinal creation order of the indexes.

--Or--change the Merge table INDEX syntax to use the actual NAME of the INDEX instead of the column definitions it contains. The existence of the INDEX can be checked at CREATE merge table time, the same way it currently checks for valid column names. But this still implies some dictionary objects that are currently not present and an Optimizer that can use the INDEX names instead of the columns.

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.

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.

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.

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