From 34789efd3191d5a140c65dd17b459024c741c085 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 7 Oct 2004 13:02:39 +0400 Subject: Fix for bug #5915 "ALTER TABLE behaves differently when converting column to auto_increment in 4.1". Now we are enforcing NO_AUTO_VALUE_ON_ZERO mode during ALTER TABLE only if we are converting one auto_increment column to another auto_increment column (this also includes most common case when we don't do anything with such column). Also now when we convert some column to TIMESTAMP NOT NULL column with ALTER TABLE we convert NULL values to current timestamp, (as we do this in INSERT). One can still get old behavior by setting system TIMESTAMP variable to 0. mysql-test/r/auto_increment.result: Added tests for ALTER TABLE converting columns containing NULL and 0 values to AUTO_INCREMENT columns. mysql-test/r/type_timestamp.result: Removed test for creation of TIMESTAMP(19) columns (it is 4.0 specific). Added test for ALTER TABLE converting columns containing NULL values to TIMESTAMP columns. mysql-test/t/auto_increment.test: Added tests for ALTER TABLE converting columns containing NULL and 0 values to AUTO_INCREMENT columns. mysql-test/t/type_timestamp.test: Removed test for creation of TIMESTAMP(19) columns (it is 4.0 specific). Added test for ALTER TABLE converting columns containing NULL values to TIMESTAMP columns. sql/field_conv.cc: Fix bug #5915 "ALTER TABLE behaves differently when converting column to auto_increment in 4.1". Also now when we are converting some column to TIMESTAMP column, we are converting NULL values to CURRENT_TIMESTAMP (as it was initially planned). do_copy_timestamp(): Fixed comment. do_copy_next_number(): We should also set auto_increment_field_not_null to FALSE if we have NULL in source field. Copy_field::set(): Moved setting of copy functions for TIMESTAMP and AUTO_INCREMENT fields to proper place (this was dead code before). sql/sql_table.cc: Fix for bug #5915 "ALTER TABLE behaves differently when converting column to auto_increment in 4.1". Instead of always forcing NO_AUTO_VALUE_ON_ZERO in ALTER TABLE it is better to do this only if we are converting one auto_increment column to another auto_increment column (this also includes most common case when we don't do anything with such column). --- mysql-test/r/auto_increment.result | 52 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 52 insertions(+) (limited to 'mysql-test/r/auto_increment.result') diff --git a/mysql-test/r/auto_increment.result b/mysql-test/r/auto_increment.result index f5ec5f1f852..428b2769fdb 100644 --- a/mysql-test/r/auto_increment.result +++ b/mysql-test/r/auto_increment.result @@ -289,3 +289,55 @@ a b 0 13 500 14 drop table t1; +create table t1 (a bigint); +insert into t1 values (1), (2), (3), (NULL), (NULL); +alter table t1 modify a bigint not null auto_increment primary key; +select * from t1; +a +1 +2 +3 +4 +5 +drop table t1; +create table t1 (a bigint); +insert into t1 values (1), (2), (3), (0), (0); +alter table t1 modify a bigint not null auto_increment primary key; +select * from t1; +a +1 +2 +3 +4 +5 +drop table t1; +create table t1 (a bigint); +insert into t1 values (0), (1), (2), (3); +set sql_mode=NO_AUTO_VALUE_ON_ZERO; +alter table t1 modify a bigint not null auto_increment primary key; +set sql_mode= ''; +select * from t1; +a +0 +1 +2 +3 +drop table t1; +create table t1 (a int auto_increment primary key , b int null); +set sql_mode=NO_AUTO_VALUE_ON_ZERO; +insert into t1 values (0,1),(1,2),(2,3); +select * from t1; +a b +0 1 +1 2 +2 3 +set sql_mode= ''; +alter table t1 modify b varchar(255); +insert into t1 values (0,4); +select * from t1; +a b +0 1 +1 2 +2 3 +3 4 +drop table t1; -- cgit v1.2.1