From 74deaae946f060318900b93249e1d48f4d43c9af Mon Sep 17 00:00:00 2001 From: "Tatiana A. Nurnberg" Date: Wed, 24 Jun 2009 13:02:20 +0200 Subject: Bug#43508: Renaming timestamp or date column triggers table copy Altering a table to update a column with types DATE or TIMESTAMP would incorrectly be seen as a significant change that necessitates a slow copy+rename operation instead of a fast update. There were two problems: The character set is magically set for TIMESTAMP to be "binary", but that was done too deep in field use code for ALTER TABLE to know of it. Now, put that in the constructor for Field_timestamp. Also, when we set the character set for the new replacement/ comparison field, also raise the "binary" field flag that tells us we should compare it exactly. That is necessary to match the old stored definition. Next is the problem that the default length for TIMESTAMP and DATE fields is different than the length read from the .frm . The compressed size is written to the file, but the human-readable, part-delimited length is used as default length. IIRC, for timestamp it was 19!=14, and for date it was 8!=10. Length mismatch causes a table copy. Also, clean up a place where a comparison function alters one of its parameters and replace it with an assertion of the condition it mutates. --- sql/field.cc | 47 +++++++++++++++++++---------------------------- 1 file changed, 19 insertions(+), 28 deletions(-) (limited to 'sql/field.cc') diff --git a/sql/field.cc b/sql/field.cc index ed085de1db3..ea80e809c4e 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -1,4 +1,4 @@ -/* Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc. +/* Copyright 2000-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by @@ -4723,6 +4723,7 @@ Field_timestamp::Field_timestamp(uchar *ptr_arg, uint32 len_arg, { /* For 4.0 MYD and 4.0 InnoDB compatibility */ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; + field_charset= &my_charset_bin; if (!share->timestamp_field && unireg_check != NONE) { /* This timestamp has auto-update */ @@ -4743,6 +4744,7 @@ Field_timestamp::Field_timestamp(bool maybe_null_arg, { /* For 4.0 MYD and 4.0 InnoDB compatibility */ flags|= ZEROFILL_FLAG | UNSIGNED_FLAG; + field_charset= &my_charset_bin; if (unireg_check != TIMESTAMP_DN_FIELD) flags|= ON_UPDATE_NOW_FLAG; } @@ -6504,20 +6506,9 @@ uint Field::is_equal(Create_field *new_field) } -/* If one of the fields is binary and the other one isn't return 1 else 0 */ - -bool Field_str::compare_str_field_flags(Create_field *new_field, uint32 flag_arg) -{ - return (((new_field->flags & (BINCMP_FLAG | BINARY_FLAG)) && - !(flag_arg & (BINCMP_FLAG | BINARY_FLAG))) || - (!(new_field->flags & (BINCMP_FLAG | BINARY_FLAG)) && - (flag_arg & (BINCMP_FLAG | BINARY_FLAG)))); -} - - uint Field_str::is_equal(Create_field *new_field) { - if (compare_str_field_flags(new_field, flags)) + if (field_flags_are_binary() != new_field->field_flags_are_binary()) return 0; return ((new_field->sql_type == real_type()) && @@ -8283,7 +8274,7 @@ uint Field_blob::max_packed_col_length(uint max_length) uint Field_blob::is_equal(Create_field *new_field) { - if (compare_str_field_flags(new_field, flags)) + if (field_flags_are_binary() != new_field->field_flags_are_binary()) return 0; return ((new_field->sql_type == get_blob_type_from_length(max_data_length())) @@ -9569,7 +9560,7 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, } if (length == 0) - fld_length= 0; /* purecov: inspected */ + fld_length= NULL; /* purecov: inspected */ } sign_len= fld_type_modifier & UNSIGNED_FLAG ? 0 : 1; @@ -9721,8 +9712,7 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, case MYSQL_TYPE_TIMESTAMP: if (fld_length == NULL) { - /* Compressed date YYYYMMDDHHMMSS */ - length= MAX_DATETIME_COMPRESSED_WIDTH; + length= MAX_DATETIME_WIDTH; } else if (length != MAX_DATETIME_WIDTH) { @@ -9787,7 +9777,7 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, sql_type= MYSQL_TYPE_NEWDATE; /* fall trough */ case MYSQL_TYPE_NEWDATE: - length= 10; + length= MAX_DATE_WIDTH; break; case MYSQL_TYPE_TIME: length= 10; @@ -9868,6 +9858,17 @@ bool Create_field::init(THD *thd, char *fld_name, enum_field_types fld_type, DBUG_RETURN(TRUE); } + switch (fld_type) { + case MYSQL_TYPE_DATE: + case MYSQL_TYPE_NEWDATE: + case MYSQL_TYPE_TIME: + case MYSQL_TYPE_DATETIME: + case MYSQL_TYPE_TIMESTAMP: + charset= &my_charset_bin; + flags|= BINCMP_FLAG; + default: break; + } + DBUG_RETURN(FALSE); /* success */ } @@ -9976,16 +9977,6 @@ Field *make_field(TABLE_SHARE *share, uchar *ptr, uint32 field_length, null_bit= ((uchar) 1) << null_bit; } - switch (field_type) { - case MYSQL_TYPE_DATE: - case MYSQL_TYPE_NEWDATE: - case MYSQL_TYPE_TIME: - case MYSQL_TYPE_DATETIME: - case MYSQL_TYPE_TIMESTAMP: - field_charset= &my_charset_bin; - default: break; - } - if (f_is_alpha(pack_flag)) { if (!f_is_packed(pack_flag)) -- cgit v1.2.1 From 1ca80ed19e14d8d5837fa900ad75ec1a7a7856e1 Mon Sep 17 00:00:00 2001 From: Davi Arnaut Date: Mon, 2 Nov 2009 09:21:39 -0200 Subject: Bug#48370: Absolutely wrong calculations with GROUP BY and decimal fields when using IF Bug#45261: Crash, stored procedure + decimal Revert fix for Bug#45261 due to unforeseen bugs. --- sql/field.cc | 85 ------------------------------------------------------------ 1 file changed, 85 deletions(-) (limited to 'sql/field.cc') diff --git a/sql/field.cc b/sql/field.cc index 7edbd37d7da..354c911e1c0 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -2480,97 +2480,12 @@ Field_new_decimal::Field_new_decimal(uint32 len_arg, { precision= my_decimal_length_to_precision(len_arg, dec_arg, unsigned_arg); set_if_smaller(precision, DECIMAL_MAX_PRECISION); - DBUG_ASSERT(precision >= dec); DBUG_ASSERT((precision <= DECIMAL_MAX_PRECISION) && (dec <= DECIMAL_MAX_SCALE)); bin_size= my_decimal_get_binary_size(precision, dec); } -/** - Create a field to hold a decimal value from an item. - - @remark The MySQL DECIMAL data type has a characteristic that needs to be - taken into account when deducing the type from a Item_decimal. - - But first, let's briefly recap what is the new MySQL DECIMAL type: - - The declaration syntax for a decimal is DECIMAL(M,D), where: - - * M is the maximum number of digits (the precision). - It has a range of 1 to 65. - * D is the number of digits to the right of the decimal separator (the scale). - It has a range of 0 to 30 and must be no larger than M. - - D and M are used to determine the storage requirements for the integer - and fractional parts of each value. The integer part is to the left of - the decimal separator and to the right is the fractional part. Hence: - - M is the number of digits for the integer and fractional part. - D is the number of digits for the fractional part. - - Consequently, M - D is the number of digits for the integer part. For - example, a DECIMAL(20,10) column has ten digits on either side of - the decimal separator. - - The characteristic that needs to be taken into account is that the - backing type for Item_decimal is a my_decimal that has a higher - precision (DECIMAL_MAX_POSSIBLE_PRECISION, see my_decimal.h) than - DECIMAL. - - Drawing a comparison between my_decimal and DECIMAL: - - * M has a range of 1 to 81. - * D has a range of 0 to 81. - - There can be a difference in range if the decimal contains a integer - part. This is because the fractional part must always be on a group - boundary, leaving at least one group for the integer part. Since each - group is 9 (DIG_PER_DEC1) digits and there are 9 (DECIMAL_BUFF_LENGTH) - groups, the fractional part is limited to 72 digits if there is at - least one digit in the integral part. - - Although the backing type for a DECIMAL is also my_decimal, every - time a my_decimal is stored in a DECIMAL field, the precision and - scale are explicitly capped at 65 (DECIMAL_MAX_PRECISION) and 30 - (DECIMAL_MAX_SCALE) digits, following my_decimal truncation procedure - (FIX_INTG_FRAC_ERROR). -*/ - -Field_new_decimal * -Field_new_decimal::new_decimal_field(const Item *item) -{ - uint32 len; - uint intg= item->decimal_int_part(), scale= item->decimals; - - DBUG_ASSERT(item->decimal_precision() >= item->decimals); - - /* - Employ a procedure along the lines of the my_decimal truncation process: - - If the integer part is equal to or bigger than the maximum precision: - Truncate integer part to fit and the fractional becomes zero. - - Otherwise: - Truncate fractional part to fit. - */ - if (intg >= DECIMAL_MAX_PRECISION) - { - intg= DECIMAL_MAX_PRECISION; - scale= 0; - } - else - { - uint room= min(DECIMAL_MAX_PRECISION - intg, DECIMAL_MAX_SCALE); - if (scale > room) - scale= room; - } - - len= my_decimal_precision_to_length(intg + scale, scale, item->unsigned_flag); - - return new Field_new_decimal(len, item->maybe_null, item->name, scale, - item->unsigned_flag); -} - - int Field_new_decimal::reset(void) { store_value(&decimal_zero); -- cgit v1.2.1