bzr push into mysql-trunk-wl5854 branch (marko.makela:3761 to 3762)

[marko.makela]

 3762 Marko Mäkelä	2012-05-07
      Remove duplicate tests, and add some DML into the mix.

    modified:
      mysql-test/suite/innodb/r/innodb-alter-nullable.result
      mysql-test/suite/innodb/t/innodb-alter-nullable.test
 3761 Marko Mäkelä	2012-05-02
      WL#5854 phase 1: MODIFY...NULL for ROW_FORMAT=REDUNDANT
      
      check_if_supported_inplace_alter(): Allow ALTER_COLUMN_NULLABLE for
      ROW_FORMAT=REDUNDANT tables.
      
      innobase_set_nullable(): Allow a column to be set NULL in
      a ROW_FORMAT=REDUNDANT table.
      
      commit_inplace_alter_table(): Invoke innobase_set_nullable() when needed.

    added:
      mysql-test/suite/innodb/r/innodb-alter-nullable.result
      mysql-test/suite/innodb/t/innodb-alter-nullable.test
    modified:
      storage/innobase/handler/handler0alter.cc
=== modified file 'mysql-test/suite/innodb/r/innodb-alter-nullable.result'
--- a/mysql-test/suite/innodb/r/innodb-alter-nullable.result	revid:marko.makela@stripped
+++ b/mysql-test/suite/innodb/r/innodb-alter-nullable.result	revid:marko.makela@strippedxxo0ze
@@ -2,6 +2,8 @@ CREATE TABLE tr (c1 INT PRIMARY KEY, c2
 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
 CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
 ENGINE=InnoDB;
+INSERT INTO tr VALUES (1,2,3),(4,5,6),(7,8,9);
+INSERT INTO tc VALUES (1,2,3),(4,5,6),(7,8,9);
 ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
 ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
 ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
@@ -20,39 +22,53 @@ ALTER TABLE tr CHANGE c2 c2 INT NOT NULL
 ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
 ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
 ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+SET SQL_MODE='STRICT_ALL_TABLES';
+UPDATE tr SET c2=NULL;
+ERROR 23000: Column 'c2' cannot be null
+UPDATE tc SET c2=NULL;
+ERROR 23000: Column 'c2' cannot be null
+SELECT * FROM tr;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
+SELECT * FROM tc;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
 ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
 ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
 ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE'
+BEGIN;
+UPDATE tr SET c2=NULL;
+UPDATE tc SET c2=NULL;
+ERROR 23000: Column 'c2' cannot be null
+SELECT * FROM tr;
+c1	c2	c3
+1	NULL	3
+4	NULL	6
+7	NULL	9
+SELECT * FROM tc;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
+ROLLBACK;
+SELECT * FROM tr;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
+SELECT * FROM tc;
+c1	c2	c3
+1	2	3
+4	5	6
+7	8	9
 ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
 ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
 ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE'
-DROP TABLE tc, tr;
-CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
-ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
-CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
-ENGINE=InnoDB;
-ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
-ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
-ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
-ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE'
-ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
-ERROR 42S22: Unknown column 'c2' in 'tr'
-ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
-ERROR 42S22: Unknown column 'c2' in 'tr'
-ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
-ERROR 42S22: Unknown column 'c2' in 'tr'
-ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
-ERROR 42S22: Unknown column 'c2' in 'tr'
-ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
-ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE'
-ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
-ERROR 42000: This version of MySQL doesn't yet support 'ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE'
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
+WHERE NAME LIKE 'test/t[rc]';
+TABLE_ID	NAME	FLAG	N_COLS	SPACE
 DROP TABLE tc, tr;

=== modified file 'mysql-test/suite/innodb/t/innodb-alter-nullable.test'
--- a/mysql-test/suite/innodb/t/innodb-alter-nullable.test	revid:marko.makela@stripped20120502105852-0rnowlt6pnlscfmp
+++ b/mysql-test/suite/innodb/t/innodb-alter-nullable.test	revid:[e-mail]-20120507095121-c3um1wjb44xxo0ze
@@ -1,8 +1,11 @@
 --source include/have_innodb.inc
 
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
 # WL#5854 part 1: MODIFY...NULL
-# First, this is only implemented for ROW_FORMAT=REDUNDANT tables,
-# because that one does not change the storage format of B-tree records.
+# For ROW_FORMAT=REDUNDANT tables, this does not change the storage format
+# of the B-tree records.
 #
 # Implementing the rest of WL#5854 will require a multi-versioned
 # data dictionary (multiple SYS_TABLES and clustered index for the table).
@@ -13,6 +16,9 @@ ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
 CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
 ENGINE=InnoDB;
 
+INSERT INTO tr VALUES (1,2,3),(4,5,6),(7,8,9);
+INSERT INTO tc VALUES (1,2,3),(4,5,6),(7,8,9);
+
 # This one will be a no-op.
 # MySQL should perhaps issue an error, because it refuses to modify
 # the PRIMARY KEY column c1 from NOT NULL to NULL.
@@ -40,65 +46,51 @@ ALTER TABLE tc CHANGE c2 c2 INT NOT NULL
 ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
 ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
 
-ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
---error ER_NOT_SUPPORTED_YET
-ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
-
-ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
---error ER_NOT_SUPPORTED_YET
-ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
-
-DROP TABLE tc, tr;
---source include/have_innodb.inc
-
-# WL#5854 part 1: MODIFY...NULL
-# This is only implemented for ROW_FORMAT=REDUNDANT tables,
-# because that one does not change the storage format of B-tree records.
-#
-# Implementing the change for later InnoDB formats would require a
-# multi-versioned data dictionary (and multi-versioned SYS_COLUMNS at that).
-# Implementing ADD COLUMN and DROP COLUMN only needs a multi-versioned
-# clustered index, and simple "append" versioning of SYS_COLUMNS.
-
-CREATE TABLE tr (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
-ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
-
-CREATE TABLE tc (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 INT)
-ENGINE=InnoDB;
+connect (con1,localhost,root,,);
+connection con1;
 
-# This one will be a no-op.
-# MySQL should perhaps issue an error, because it refuses to modify
-# the PRIMARY KEY column c1 from NOT NULL to NULL.
-ALTER TABLE tr CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
-ALTER TABLE tc CHANGE c1 c1 INT NULL FIRST, ALGORITHM=INPLACE;
+SET SQL_MODE='STRICT_ALL_TABLES';
 
---error ER_NOT_SUPPORTED_YET
-ALTER TABLE tr MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
---error ER_NOT_SUPPORTED_YET
-ALTER TABLE tc MODIFY c3 INT NOT NULL, ALGORITHM=INPLACE;
+--error ER_BAD_NULL_ERROR
+UPDATE tr SET c2=NULL;
+--error ER_BAD_NULL_ERROR
+UPDATE tc SET c2=NULL;
 
-# Request some conflicting changes for a single column.
---error ER_BAD_FIELD_ERROR
-ALTER TABLE tr CHANGE c2 c2 INT, CHANGE c2 c2 INT NOT NULL;
---error ER_BAD_FIELD_ERROR
-ALTER TABLE tr MODIFY c2 INT, MODIFY c2 INT NOT NULL;
---error ER_BAD_FIELD_ERROR
-ALTER TABLE tr MODIFY c2 INT UNSIGNED, MODIFY c2 INT;
---error ER_BAD_FIELD_ERROR
-ALTER TABLE tr MODIFY c2 CHAR(1) NOT NULL, MODIFY c2 INT NOT NULL;
+SELECT * FROM tr;
+SELECT * FROM tc;
 
-# No-ops.
-ALTER TABLE tr CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tc CHANGE c2 c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tr MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
-ALTER TABLE tc MODIFY c2 INT NOT NULL, ALGORITHM=INPLACE;
+connection default;
 
+# These should change the column to NULL.
 ALTER TABLE tr MODIFY c2 INT, ALGORITHM=INPLACE;
 --error ER_NOT_SUPPORTED_YET
 ALTER TABLE tc MODIFY c2 INT, ALGORITHM=INPLACE;
 
+connection con1;
+BEGIN;
+UPDATE tr SET c2=NULL;
+--error ER_BAD_NULL_ERROR
+UPDATE tc SET c2=NULL;
+SELECT * FROM tr;
+SELECT * FROM tc;
+ROLLBACK;
+SELECT * FROM tr;
+SELECT * FROM tc;
+
+disconnect con1;
+connection default;
+
+# These should be no-ops.
 ALTER TABLE tr MODIFY c2 INT NULL, ALGORITHM=INPLACE;
 --error ER_NOT_SUPPORTED_YET
 ALTER TABLE tc MODIFY c2 INT NULL, ALGORITHM=INPLACE;
 
+SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
+WHERE NAME LIKE 'test/t[rc]';
+
+connection default;
 DROP TABLE tc, tr;
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc

No bundle (reason: useless for push emails).