From: Date: October 3 2008 7:18am Subject: bzr push into mysql-6.0-falcon-team branch (cpowers:2850 to 2851) Bug#39795 List-Archive: http://lists.mysql.com/commits/55170 X-Bug: 39795 Message-Id: <20081003051837.878B91DB0669@xeno.mysql.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 2851 Christopher Powers 2008-10-03 Bug#39795 "Falcon: Online add index does not support index with non-null columns" Lift restriction on adding online indexes having non-null columns. modified: mysql-test/suite/falcon/r/falcon_online_index.result mysql-test/suite/falcon/t/falcon_online_index.test storage/falcon/ha_falcon.cpp 2850 Christopher Powers 2008-10-02 [merge] (no message) modified: storage/falcon/Cache.cpp storage/falcon/Cache.h === modified file 'mysql-test/suite/falcon/r/falcon_online_index.result' --- a/mysql-test/suite/falcon/r/falcon_online_index.result 2008-09-10 15:08:56 +0000 +++ b/mysql-test/suite/falcon/r/falcon_online_index.result 2008-10-03 05:15:40 +0000 @@ -72,14 +72,14 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 #-------- Testing implicit OFFLINE --------# ALTER TABLE t3 ADD INDEX ix_c (c); -affected rows: 1000 -info: Records: 1000 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 DROP INDEX ix_c ON t3; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t3 ADD INDEX ix_cd (c, d); -affected rows: 1000 -info: Records: 1000 Duplicates: 0 Warnings: 0 +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 DROP INDEX ix_cd ON t3; affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 0 @@ -108,10 +108,8 @@ a b c d e ALTER ONLINE TABLE t3 DROP INDEX ix_b; #-------- ONLINE: ALTER ADD not-null with default --------# ALTER ONLINE TABLE t3 ADD INDEX ix_c (c); -ERROR 42000: This version of MySQL doesn't yet support 'ALTER ONLINE TABLE t3 ADD INDEX ix_c (c)' #-------- ONLINE: ALTER ADD not-null --------# ALTER ONLINE TABLE t3 ADD INDEX ix_d (d); -ERROR 42000: This version of MySQL doesn't yet support 'ALTER ONLINE TABLE t3 ADD INDEX ix_d (d)' #-------- ONLINE: ALTER ADD same key multiple times --------# ALTER ONLINE TABLE t1 ADD INDEX index_c (c); ALTER ONLINE TABLE t1 ADD INDEX index_c (c); @@ -171,6 +169,8 @@ ALTER ONLINE TABLE t3 ADD INDEX ix_asc_b SHOW INDEXES FROM t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment t3 0 PRIMARY 1 a NULL 500 NULL NULL BTREE +t3 1 ix_c 1 c NULL 500 NULL NULL BTREE +t3 1 ix_d 1 d NULL 500 NULL NULL BTREE t3 1 ix_desc_b 1 b NULL 500 NULL NULL YES BTREE t3 1 ix_asc_b 1 b NULL 500 NULL NULL YES BTREE DROP ONLINE INDEX ix_desc_b ON t3; @@ -265,6 +265,8 @@ t1 1 index_int 1 c NULL 10 NULL NULL YES SHOW INDEXES FROM t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment t3 0 PRIMARY 1 a NULL 500 NULL NULL BTREE +t3 1 ix_c 1 c NULL 500 NULL NULL BTREE +t3 1 ix_d 1 d NULL 500 NULL NULL BTREE t3 1 index_int 1 b NULL 500 NULL NULL YES BTREE t3 1 index_multi 1 b NULL 250 NULL NULL YES BTREE t3 1 index_multi 2 e NULL 500 NULL NULL YES BTREE @@ -283,6 +285,8 @@ t1 0 PRIMARY 1 a NULL 10 NULL NULL BTRE SHOW INDEXES FROM t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment t3 0 PRIMARY 1 a NULL 500 NULL NULL BTREE +t3 1 ix_c 1 c NULL 500 NULL NULL BTREE +t3 1 ix_d 1 d NULL 500 NULL NULL BTREE #-------- Test: Combined ADD/DROP INDEX in a single statement --------# ALTER TABLE t1 ADD INDEX index_int (c); ALTER TABLE t1 ADD INDEX index_char (d), DROP INDEX index_int; @@ -344,7 +348,7 @@ a b c d a b c d 16 TestRow16 32 Char16 31 62 32 SomeString 31 for testing EXPLAIN SELECT * FROM t1, t3 WHERE t3.b=2 AND (t1.c = t3.c OR t1.a=t3.d); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ref ix_b ix_b 5 const 100 +1 SIMPLE t3 ref ix_c,ix_d,ix_b ix_b 5 const 100 1 SIMPLE t1 ALL PRIMARY,ix_a,ix_c NULL NULL NULL 20 Range checked for each record (index map: 0xB) SELECT * FROM t1, t3 WHERE t3.b=2 AND (t1.c = t3.c OR t1.a=t3.d); a b c d a b c d e @@ -367,6 +371,8 @@ t2 0 PRIMARY 1 a NULL 16 NULL NULL BTRE SHOW INDEXES FROM t3; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment t3 0 PRIMARY 1 a NULL 500 NULL NULL BTREE +t3 1 ix_c 1 c NULL 500 NULL NULL BTREE +t3 1 ix_d 1 d NULL 500 NULL NULL BTREE DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; === modified file 'mysql-test/suite/falcon/t/falcon_online_index.test' --- a/mysql-test/suite/falcon/t/falcon_online_index.test 2008-09-10 15:08:56 +0000 +++ b/mysql-test/suite/falcon/t/falcon_online_index.test 2008-10-03 05:15:40 +0000 @@ -163,14 +163,12 @@ ALTER ONLINE TABLE t3 DROP INDEX ix_b; --echo #-------- ONLINE: ALTER ADD not-null with default --------# -# Test that ALTER ONLINE ... ADD INDEX fails with ONLINE for non-nullable column having default value ---error ER_NOT_SUPPORTED_YET +# Test that ALTER ONLINE ... ADD INDEX succeeds with ONLINE for non-nullable column having default value ALTER ONLINE TABLE t3 ADD INDEX ix_c (c); --echo #-------- ONLINE: ALTER ADD not-null --------# -# Test that ALTER ONLINE ... ADD INDEX fails with ONLINE for non-nullable columns ---error ER_NOT_SUPPORTED_YET +# Test that ALTER ONLINE ... ADD INDEX succeeds with ONLINE for non-nullable columns ALTER ONLINE TABLE t3 ADD INDEX ix_d (d); --echo #-------- ONLINE: ALTER ADD same key multiple times --------# === modified file 'storage/falcon/ha_falcon.cpp' --- a/storage/falcon/ha_falcon.cpp 2008-09-16 17:58:49 +0000 +++ b/storage/falcon/ha_falcon.cpp 2008-10-03 05:15:40 +0000 @@ -2180,46 +2180,6 @@ int StorageInterface::check_if_supported } } - // TODO for Add Index: - // 1. Check for supported ALTER combinations - // 2. Can error message be improved for non-null columns? - - if (alter_flags->is_set(HA_ADD_INDEX) || alter_flags->is_set(HA_ADD_UNIQUE_INDEX)) - { - for (unsigned int n = 0; n < altered_table->s->keys; n++) - { - if (n != altered_table->s->primary_key) - { - KEY *key = altered_table->key_info + n; - KEY *tableEnd = table->key_info + table->s->keys; - KEY *tableKey; - - // Determine if this is a new index - - for (tableKey = table->key_info; tableKey < tableEnd; tableKey++) - if (!strcmp(tableKey->name, key->name)) - break; - - // Verify that each part is nullable - - if (tableKey >= tableEnd) - for (uint p = 0; p < key->key_parts; p++) - { - KEY_PART_INFO *keyPart = key->key_part + p; - if (keyPart && !keyPart->field->real_maybe_null()) - { - DBUG_PRINT("info",("Online add index columns must be nullable")); - DBUG_RETURN(HA_ALTER_NOT_SUPPORTED); - } - } - } - } - } - - if (alter_flags->is_set(HA_DROP_INDEX) || alter_flags->is_set(HA_DROP_UNIQUE_INDEX)) - { - } - DBUG_RETURN(HA_ALTER_SUPPORTED_NO_LOCK); }