[Maria-developers] changes in virtual column patch

Hi! Here is first version of my changes of virtual column patch. I also propose make following changes: 1) to make syntax even more compatible: - Add optional 'GENERATED ALWAYS' instead of removed mandatory 'VIRTUAL'. - Replase 'STORED' keyword with 'MATERIALIZED' and maybe make decision if nothing mention after field definition whether it will be stored depended on sql_mode (in oracle mode it will be stored) and add 'VIRTUAL' key word which force our current default (pure virtual column). 2) rename virtual_column_info class according MySQL codding style for classes (Virtual_column_info) What I've done: - VIRTUAL removed. - in error messages virtual replaced with computed. - test suite fixed according to above changes. - numeric error codes in --error in test suite replaced by sumbolic names - added forgotten Item::check_vcol_func_processor() for many items (which lead for several crashing bugs). - strange 'switch' which tests only top most item type replaced with tree walking so now there is no difference in function checks (and so error messages) depending on where prohibited function used in the expression. - because of above check in fix_fields_vcol_func() removed by #ifdef PARANOID usage (should be fixed in the final patch). - fixed bug in altering computed field put in partitioning function expression - Added test for subquery in computed column expression which tests subquery allowance and return ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED instead of previously tested syntax error. === modified file 'BUILD/SETUP.sh' --- BUILD/SETUP.sh 2009-03-22 12:16:09 +0000 +++ BUILD/SETUP.sh 2009-03-24 16:06:06 +0000 @@ -169,7 +169,7 @@ max_no_embedded_configs="$SSL_LIBRARY -- max_no_ndb_configs="$SSL_LIBRARY --with-plugins=max-no-ndb --with-embedded-server --with-libevent" max_configs="$SSL_LIBRARY --with-plugins=max --with-embedded-server -with-libevent" # Disable NDB in maria max builds -max_configs=$max_no_ndb_configs +#max_configs=$max_no_ndb_configs # # CPU and platform specific compilation flags. === modified file 'include/mysql_com.h' --- include/mysql_com.h 2008-10-10 15:28:41 +0000 +++ include/mysql_com.h 2009-03-24 10:47:20 +0000 @@ -67,7 +67,14 @@ enum enum_server_command COM_END }; - +/* The length of the header part for each virtual column in the .frm file. */ +#define FRM_VCOL_HEADER_SIZE 3 +/* + Maximum length of the expression statement defined for virtual columns. +*/ +#define VIRTUAL_COLUMN_EXPRESSION_MAXLEN 255 - FRM_VCOL_HEADER_SIZE +/* sql type field stored in .frm files for each virtual field. */ +#define MYSQL_TYPE_VIRTUAL 245 /* Length of random string sent by server on handshake; this is also length of obfuscated password, recieved from client === modified file 'sql/field.cc' --- sql/field.cc 2009-02-19 09:01:25 +0000 +++ sql/field.cc 2009-03-26 21:46:28 +0000 @@ -1312,7 +1312,8 @@ Field::Field(uchar *ptr_arg,uint32 lengt key_start(0), part_of_key(0), part_of_key_not_clustered(0), part_of_sortkey(0), unireg_check(unireg_check_arg), field_length(length_arg), null_bit(null_bit_arg), - is_created_from_null_item(FALSE) + is_created_from_null_item(FALSE), + vcol_info(0), stored_in_db(TRUE) { flags=null_ptr ? 0: NOT_NULL_FLAG; comment.str= (char*) ""; @@ -9495,6 +9496,8 @@ void Create_field::init_for_tmp_table(en ((decimals_arg & FIELDFLAG_MAX_DEC) << FIELDFLAG_DEC_SHIFT) | (maybe_null ? FIELDFLAG_MAYBE_NULL : 0) | (is_unsigned ? 0 : FIELDFLAG_DECIMAL)); + vcol_info= 0; + stored_in_db= TRUE; } @@ -9514,6 +9517,7 @@ void Create_field::init_for_tmp_table(en @param fld_interval_list Interval list (if any) @param fld_charset Field charset @param fld_geom_type Field geometry type (if any) + @param fld_vcol_info Virtual column data @retval FALSE on success @@ -9526,7 +9530,8 @@ bool Create_field::init(THD *thd, char * uint fld_type_modifier, Item *fld_default_value, Item *fld_on_update_value, LEX_STRING *fld_comment, char *fld_change, List<String> *fld_interval_list, - CHARSET_INFO *fld_charset, uint fld_geom_type) + CHARSET_INFO *fld_charset, uint fld_geom_type, + virtual_column_info *fld_vcol_info) { uint sign_len, allowed_type_modifier= 0; ulong max_field_charlength= MAX_FIELD_CHARLENGTH; @@ -9557,6 +9562,34 @@ bool Create_field::init(THD *thd, char * interval_list.empty(); comment= *fld_comment; + vcol_info= fld_vcol_info; + stored_in_db= TRUE; + + /* Initialize data for a virtual field */ + if ((uchar)fld_type == (uchar)MYSQL_TYPE_VIRTUAL) + { + DBUG_ASSERT(vcol_info); + DBUG_ASSERT(vcol_info->expr_item); + stored_in_db= vcol_info->get_field_stored(); + /* + Walk through the Item tree checking if all items are valid + to be part of the virtual column + */ + if (vcol_info->expr_item->walk(&Item::check_vcol_func_processor, 0, NULL)) + { + my_error(ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(0), field_name); + DBUG_RETURN(TRUE); + } + + /* + Make a field created for the real type. + Note that "real" and virtual fields differ from each other + only by Field::vcol_info, which is always 0 for normal columns. + vcol_info is updated for fields later in procedure open_binary_frm. + */ + sql_type= fld_type= vcol_info->get_real_type(); + } + /* Set NO_DEFAULT_VALUE_FLAG if this field doesn't have a default value and it is NOT NULL, not an AUTO_INCREMENT field and not a TIMESTAMP. @@ -9847,7 +9880,7 @@ bool Create_field::init(THD *thd, char * } case MYSQL_TYPE_DECIMAL: DBUG_ASSERT(0); /* Was obsolete */ - } + } /* Remember the value of length */ char_length= length; @@ -9946,7 +9979,6 @@ uint pack_length_to_packflag(uint type) return 0; // This shouldn't happen } - Field *make_field(TABLE_SHARE *share, uchar *ptr, uint32 field_length, uchar *null_pos, uchar null_bit, uint pack_flag, @@ -10140,6 +10172,8 @@ Create_field::Create_field(Field *old_fi charset= old_field->charset(); // May be NULL ptr comment= old_field->comment; decimals= old_field->decimals(); + vcol_info= old_field->vcol_info; + stored_in_db= old_field->stored_in_db; /* Fix if the original table had 4 byte pointer blobs */ if (flags & BLOB_FLAG) === modified file 'sql/field.h' --- sql/field.h 2009-02-19 09:01:25 +0000 +++ sql/field.h 2009-03-26 21:35:01 +0000 @@ -45,6 +45,67 @@ inline uint get_set_pack_length(int elem return len > 4 ? 8 : len; } +class virtual_column_info: public Sql_alloc +{ +public: + Item *expr_item; + LEX_STRING expr_str; + Item *item_free_list; + virtual_column_info() + : expr_item(0), item_free_list(0), + field_type((enum enum_field_types)MYSQL_TYPE_VIRTUAL), + stored_in_db(FALSE), implicitly_stored_in_db(FALSE),data_inited(FALSE) + { + expr_str.str= NULL; + expr_str.length= 0; + }; + ~virtual_column_info() {} + enum_field_types get_real_type() + { + DBUG_ASSERT(data_inited); + return data_inited ? field_type : (enum enum_field_types)MYSQL_TYPE_VIRTUAL; + } + void set_field_type(enum_field_types fld_type) + { + /* Calling this function can only be done once. */ + DBUG_ASSERT(!data_inited); + data_inited= TRUE; + field_type= fld_type; + } + bool get_field_stored() + { + DBUG_ASSERT(data_inited); + return data_inited ? stored_in_db : TRUE; + } + void set_field_stored(bool stored) + { + stored_in_db= stored; + } + bool is_field_implicitly_stored() + { + return implicitly_stored_in_db; + } + void set_field_implicitly_stored() + { + implicitly_stored_in_db= TRUE; + } +private: + /* + The following data is only updated by the parser and read + when a Create_field object is created/initialized. + */ + enum_field_types field_type; /* Real field type*/ + /* Indication that the field is physically stored in the database*/ + my_bool stored_in_db; + /* Indication that the field used in partitioning expression */ + my_bool implicitly_stored_in_db; + /* + This flag is used to prevent other applications from + reading and using incorrect data. + */ + my_bool data_inited; +}; + class Field { Field(const Item &); /* Prevent use of these */ @@ -103,6 +164,15 @@ public: */ bool is_created_from_null_item; + /* Virtual column data */ + virtual_column_info *vcol_info; + /* + Indication that the field is phycically stored in tables + rather than just generated on SQL queries. + As of now, FALSE can only be set for generated-only virtual columns. + */ + bool stored_in_db; + Field(uchar *ptr_arg,uint32 length_arg,uchar *null_ptr_arg, uchar null_bit_arg, utype unireg_check_arg, const char *field_name_arg); @@ -2044,6 +2114,16 @@ public: uint8 row,col,sc_length,interval_id; // For rea_create_table uint offset,pack_flag; + + /* Virtual column expression statement */ + virtual_column_info *vcol_info; + /* + Indication that the field is phycically stored in tables + rather than just generated on SQL queries. + As of now, FALSE can only be set for generated-only virtual columns. + */ + bool stored_in_db; + Create_field() :after(0) {} Create_field(Field *field, Field *orig_field); /* Used to make a clone of this object for ALTER/CREATE TABLE */ @@ -2060,7 +2140,8 @@ public: char *decimals, uint type_modifier, Item *default_value, Item *on_update_value, LEX_STRING *comment, char *change, List<String> *interval_list, CHARSET_INFO *cs, - uint uint_geom_type); + uint uint_geom_type, + virtual_column_info *vcol_info); }; === modified file 'sql/filesort.cc' --- sql/filesort.cc 2009-02-19 09:01:25 +0000 +++ sql/filesort.cc 2009-03-24 10:47:20 +0000 @@ -563,6 +563,8 @@ static ha_rows find_all_keys(SORTPARAM * { if ((error= select->quick->get_next())) break; + if (!error) + update_virtual_fields_marked_for_write(sort_form); file->position(sort_form->record[0]); DBUG_EXECUTE_IF("debug_filesort", dbug_print_record(sort_form, TRUE);); } @@ -580,6 +582,8 @@ static ha_rows find_all_keys(SORTPARAM * else { error=file->rnd_next(sort_form->record[0]); + if (!error) + update_virtual_fields_marked_for_write(sort_form); if (!flag) { my_store_ptr(ref_pos,ref_length,record); // Position to row === modified file 'sql/ha_partition.cc' --- sql/ha_partition.cc 2009-02-19 09:01:25 +0000 +++ sql/ha_partition.cc 2009-03-24 10:47:20 +0000 @@ -2408,7 +2408,7 @@ int ha_partition::open(const char *name, DBUG_RETURN(1); m_start_key.length= 0; m_rec0= table->record[0]; - m_rec_length= table_share->reclength; + m_rec_length= table_share->stored_rec_length; alloc_len= m_tot_parts * (m_rec_length + PARTITION_BYTES_IN_POS); alloc_len+= table_share->max_key_length; if (!m_ordered_rec_buffer) === modified file 'sql/ha_partition.h' --- sql/ha_partition.h 2009-02-19 09:01:25 +0000 +++ sql/ha_partition.h 2009-03-24 10:47:20 +0000 @@ -245,6 +245,7 @@ public: DBUG_RETURN(0); } virtual void change_table_ptr(TABLE *table_arg, TABLE_SHARE *share); + bool check_if_supported_virtual_columns(void) { return TRUE;} virtual bool check_if_incompatible_data(HA_CREATE_INFO *create_info, uint table_changes); private: === modified file 'sql/handler.h' --- sql/handler.h 2009-02-19 09:01:25 +0000 +++ sql/handler.h 2009-03-24 10:47:20 +0000 @@ -1755,6 +1755,12 @@ public: LEX_STRING *engine_name() { return hton_name(ht); } + /* + This procedure defines if the storage engine supports virtual columns. + Default FALSE means "not supported". + */ + virtual bool check_if_supported_virtual_columns(void) { return FALSE;} + protected: /* Service methods for use by storage engines. */ void ha_statistic_increment(ulong SSV::*offset) const; === modified file 'sql/item.cc' --- sql/item.cc 2009-02-19 09:01:25 +0000 +++ sql/item.cc 2009-03-26 21:24:20 +0000 @@ -677,9 +677,26 @@ bool Item_field::register_field_in_read_ TABLE *table= (TABLE *) arg; if (field->table == table || !table) bitmap_set_bit(field->table->read_set, field->field_index); + if (field->vcol_info && field->vcol_info->expr_item) + return field->vcol_info->expr_item->walk(&Item::register_field_in_read_map, + 1, arg); return 0; } +/* + Mark field in bitmap supplied as *arg + +*/ + +bool Item_field::register_field_in_bitmap(uchar *arg) +{ + MY_BITMAP *bitmap= (MY_BITMAP *) arg; + DBUG_ASSERT(bitmap); + if (!bitmap) + return 1; + bitmap_set_bit(bitmap, field->field_index); + return 0; +} bool Item::check_cols(uint c) { @@ -4188,6 +4205,20 @@ error: return TRUE; } +/** + Marks virtual columns as implicitly stored +*/ + +bool Item_field::vcol_in_partition_func_processor(uchar *int_arg) +{ + DBUG_ASSERT(fixed); + if (field->vcol_info) + { + field->vcol_info->set_field_implicitly_stored(); + } + return TRUE; +} + Item *Item_field::safe_charset_converter(CHARSET_INFO *tocs) { === modified file 'sql/item.h' --- sql/item.h 2009-02-19 09:01:25 +0000 +++ sql/item.h 2009-03-26 20:59:23 +0000 @@ -889,6 +889,11 @@ public: virtual bool is_expensive_processor(uchar *arg) { return 0; } virtual bool register_field_in_read_map(uchar *arg) { return 0; } /* + The next function differs from the previous one that a bitmap to be updated + is passed as uchar *arg. + */ + virtual bool register_field_in_bitmap(uchar *arg) { return 0; } + /* Check if a partition function is allowed SYNOPSIS check_partition_func_processor() @@ -940,11 +945,28 @@ public: fields. */ virtual bool check_partition_func_processor(uchar *bool_arg) { return TRUE;} + virtual bool vcol_in_partition_func_processor(uchar *bool_arg) { return TRUE;} virtual bool subst_argument_checker(uchar **arg) - { + { if (*arg) - *arg= NULL; - return TRUE; + *arg= NULL; + return TRUE; + } + /* + Check if an expression/function is allowed for a virtual column + SYNOPSIS + check_vcol_func_processor() + int_arg is just ignored + RETURN VALUE + TRUE Function not accepted + FALSE Function accepted + */ + virtual bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); } virtual Item *equal_fields_propagator(uchar * arg) { return this; } @@ -1298,6 +1320,13 @@ public: { return value_item->send(protocol, str); } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_name_const::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; bool agg_item_collations(DTCollation &c, const char *name, @@ -1315,6 +1344,7 @@ public: virtual Item_num *neg()= 0; Item *safe_charset_converter(CHARSET_INFO *tocs); bool check_partition_func_processor(uchar *int_arg) { return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; #define NO_CACHED_FIELD_INDEX ((uint)(-1)) @@ -1474,7 +1504,10 @@ public: bool collect_item_field_processor(uchar * arg); bool find_item_in_field_list_processor(uchar *arg); bool register_field_in_read_map(uchar *arg); + bool register_field_in_bitmap(uchar *arg); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool vcol_in_partition_func_processor(uchar *int_arg); + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} void cleanup(); bool result_as_longlong() { @@ -1534,6 +1567,7 @@ public: Item *safe_charset_converter(CHARSET_INFO *tocs); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; class Item_null_result :public Item_null @@ -1547,7 +1581,14 @@ public: save_in_field(result_field, no_conversions); } bool check_partition_func_processor(uchar *int_arg) {return TRUE;} -}; + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_PRINT("info", (" Item name: %s", full_name())); + return TRUE; + } +}; /* Item represents one placeholder ('?') of prepared statement */ @@ -1688,6 +1729,7 @@ public: /** Item is a argument to a limit clause. */ bool limit_clause_param; void set_param_type_and_swap_value(Item_param *from); + }; @@ -1723,6 +1765,7 @@ public: { return (uint)(max_length - test(value < 0)); } bool eq(const Item *, bool binary_cmp) const; bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -1741,6 +1784,7 @@ public: Item_num *neg (); uint decimal_precision() const { return max_length; } bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -1782,6 +1826,7 @@ public: bool eq(const Item *, bool binary_cmp) const; void set_decimal_value(my_decimal *value_par); bool check_partition_func_processor(uchar *bool_arg) { return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -1939,6 +1984,7 @@ public: } virtual void print(String *str, enum_query_type query_type); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} /** Return TRUE if character-set-introducer was explicitly specified in the @@ -2006,6 +2052,13 @@ public: } bool check_partition_func_processor(uchar *int_arg) {return TRUE;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_static_string_func::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -2017,6 +2070,14 @@ public: CHARSET_INFO *cs= NULL): Item_string(name_arg, length, cs) {} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_partition_func_safe_string::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } }; @@ -2096,6 +2157,7 @@ public: bool eq(const Item *item, bool binary_cmp) const; virtual Item *safe_charset_converter(CHARSET_INFO *tocs); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -2126,6 +2188,7 @@ public: save_in_field(result_field, no_conversions); } void cleanup(); + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -2251,7 +2314,14 @@ public: if (ref && result_type() == ROW_RESULT) (*ref)->bring_value(); } - + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_ref::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } }; @@ -2484,6 +2554,14 @@ public: table_map used_tables() const { return (table_map) 1L; } bool const_item() const { return 0; } bool is_null() { return null_value; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_sum::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } }; @@ -2614,6 +2692,13 @@ public: return arg->walk(processor, walk_subquery, args) || (this->*processor)(args); } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_insert_value::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -2710,6 +2795,14 @@ private: BEFORE INSERT of BEFORE UPDATE trigger. */ bool read_only; + virtual bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_trigger_field::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } }; @@ -2769,6 +2862,15 @@ public: { return this == item; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_cache::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } + }; === modified file 'sql/item_func.cc' --- sql/item_func.cc 2009-02-19 09:01:25 +0000 +++ sql/item_func.cc 2009-03-24 10:47:20 +0000 @@ -3894,10 +3894,30 @@ bool Item_func_set_user_var::register_fi TABLE *table= (TABLE *) arg; if (result_field->table == table || !table) bitmap_set_bit(result_field->table->read_set, result_field->field_index); + if (result_field->vcol_info && result_field->vcol_info->expr_item) + return result_field->vcol_info-> + expr_item->walk(&Item::register_field_in_read_map, 1, arg); } return 0; } +/* + Mark field in bitmap supplied as *arg + +*/ + +bool Item_func_set_user_var::register_field_in_bitmap(uchar *arg) +{ + MY_BITMAP *bitmap = (MY_BITMAP *) arg; + DBUG_ASSERT(bitmap); + if (result_field) + { + if (!bitmap) + return 1; + bitmap_set_bit(bitmap, result_field->field_index); + } + return 0; +} /** Set value to user variable. === modified file 'sql/item_func.h' --- sql/item_func.h 2009-02-19 09:01:25 +0000 +++ sql/item_func.h 2009-03-24 20:19:28 +0000 @@ -339,6 +339,7 @@ public: void fix_length_and_dec(); bool fix_fields(THD *thd, Item **ref); longlong val_int() { DBUG_ASSERT(fixed == 1); return value; } + bool check_vcol_func_processor(uchar *int_arg) { return TRUE;} }; @@ -398,6 +399,7 @@ public: Item_func_additive_op(Item *a,Item *b) :Item_num_op(a,b) {} void result_precision(); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -433,6 +435,7 @@ public: my_decimal *decimal_op(my_decimal *); void result_precision(); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -465,6 +468,7 @@ public: } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -479,6 +483,7 @@ public: void result_precision(); void fix_length_and_dec(); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -495,6 +500,7 @@ public: void fix_num_length_and_dec(); uint decimal_precision() const { return args[0]->decimal_precision(); } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -508,6 +514,7 @@ public: const char *func_name() const { return "abs"; } void fix_length_and_dec(); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; // A class to handle logarithmic and trigonometric functions @@ -663,6 +670,7 @@ public: double real_op(); my_decimal *decimal_op(my_decimal *); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -675,6 +683,7 @@ public: double real_op(); my_decimal *decimal_op(my_decimal *); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; /* This handles round and truncate */ @@ -704,6 +713,13 @@ public: bool const_item() const { return 0; } void update_used_tables(); bool fix_fields(THD *thd, Item **ref); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_rand::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } private: void seed_random (Item * val); }; @@ -986,6 +1002,13 @@ public: max_length= args[0]->max_length; } bool fix_fields(THD *thd, Item **ref); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_last_insert_id::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -999,6 +1022,13 @@ public: const char *func_name() const { return "benchmark"; } void fix_length_and_dec() { max_length=1; maybe_null=0; } virtual void print(String *str, enum_query_type query_type); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_benchmark::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1014,6 +1044,13 @@ public: used_tables_cache|= RAND_TABLE_BIT; } longlong val_int(); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_sleep::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1263,6 +1300,13 @@ class Item_func_get_lock :public Item_in longlong val_int(); const char *func_name() const { return "get_lock"; } void fix_length_and_dec() { max_length=1; maybe_null=1;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_get_lock::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; class Item_func_release_lock :public Item_int_func @@ -1273,6 +1317,13 @@ public: longlong val_int(); const char *func_name() const { return "release_lock"; } void fix_length_and_dec() { max_length=1; maybe_null=1;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_release_lock::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; /* replication functions */ @@ -1286,6 +1337,13 @@ public: longlong val_int(); const char *func_name() const { return "master_pos_wait"; } void fix_length_and_dec() { max_length=21; maybe_null=1;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_master_pos_wait::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1356,6 +1414,7 @@ public: } void save_org_in_field(Field *field) { (void)save_in_field(field, 1, 0); } bool register_field_in_read_map(uchar *arg); + bool register_field_in_bitmap(uchar *arg); bool set_entry(THD *thd, bool create_if_not_exists); void cleanup(); }; @@ -1528,6 +1587,14 @@ public: bool fix_index(); void init_search(bool no_order); + bool check_vcol_func_processor(uchar *int_arg) + { + /* TODO: consider adding in support for the MATCH-based virtual columns */ + DBUG_ENTER("Item_func_match::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1547,6 +1614,13 @@ public: longlong val_int(); const char *func_name() const { return "is_free_lock"; } void fix_length_and_dec() { decimals=0; max_length=1; maybe_null=1;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_is_free_lock::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; class Item_func_is_used_lock :public Item_int_func @@ -1557,6 +1631,13 @@ public: longlong val_int(); const char *func_name() const { return "is_used_lock"; } void fix_length_and_dec() { decimals=0; max_length=10; maybe_null=1;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_is_used_lock::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; /* For type casts */ @@ -1576,6 +1657,13 @@ public: longlong val_int(); const char *func_name() const { return "row_count"; } void fix_length_and_dec() { decimals= 0; maybe_null=0; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_row_count::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1684,6 +1772,13 @@ public: { return sp_result_field; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_sp::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1694,6 +1789,13 @@ public: longlong val_int(); const char *func_name() const { return "found_rows"; } void fix_length_and_dec() { decimals= 0; maybe_null=0; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_found_rows::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -1708,5 +1810,12 @@ public: void fix_length_and_dec() { max_length= 21; unsigned_flag=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_uuid_short::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; === modified file 'sql/item_row.h' --- sql/item_row.h 2008-02-22 10:30:33 +0000 +++ sql/item_row.h 2009-03-24 10:47:20 +0000 @@ -76,4 +76,5 @@ public: bool check_cols(uint c); bool null_inside() { return with_null; }; void bring_value(); -}; + bool check_vcol_func_processor(uchar *int_arg) {return FALSE; } + }; === modified file 'sql/item_strfunc.h' --- sql/item_strfunc.h 2009-02-19 09:01:25 +0000 +++ sql/item_strfunc.h 2009-03-24 10:47:20 +0000 @@ -335,6 +335,14 @@ public: String *val_str(String *); void fix_length_and_dec() { maybe_null=1; max_length = 13; } const char *func_name() const { return "encrypt"; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_encrypt::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } + }; #include "sql_crypt.h" @@ -372,6 +380,13 @@ public: call */ virtual const char *fully_qualified_func_name() const = 0; + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_sysconst::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -635,6 +650,13 @@ public: maybe_null=1; max_length=MAX_BLOB_WIDTH; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_load_file::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -831,5 +853,12 @@ public: } const char *func_name() const{ return "uuid"; } String *val_str(String *); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_uuid::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; === modified file 'sql/item_subselect.h' --- sql/item_subselect.h 2008-02-22 10:30:33 +0000 +++ sql/item_subselect.h 2009-03-26 19:13:42 +0000 @@ -126,6 +126,13 @@ public: virtual void reset_value_registration() {} enum_parsing_place place() { return parsing_place; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_sum::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } /** Get the SELECT_LEX structure associated with this Item. === modified file 'sql/item_sum.h' --- sql/item_sum.h 2008-12-09 19:43:10 +0000 +++ sql/item_sum.h 2009-03-25 21:24:46 +0000 @@ -384,6 +384,13 @@ public: Item *get_arg(int i) { return args[i]; } Item *set_arg(int i, THD *thd, Item *new_val); uint get_arg_count() { return arg_count; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_sum::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -664,6 +671,13 @@ public: } void fix_length_and_dec() {} enum Item_result result_type () const { return hybrid_type; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_avg_field::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -732,6 +746,13 @@ public: } void fix_length_and_dec() {} enum Item_result result_type () const { return hybrid_type; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_variance_field::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; === modified file 'sql/item_timefunc.h' --- sql/item_timefunc.h 2008-12-23 14:21:01 +0000 +++ sql/item_timefunc.h 2009-03-24 10:47:20 +0000 @@ -70,6 +70,7 @@ public: enum_monotonicity_info get_monotonicity_info() const; longlong val_int_endpoint(bool left_endp, bool *incl_endp); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -86,6 +87,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -111,6 +113,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -124,6 +127,7 @@ public: enum Item_result result_type () const { return STRING_RESULT; } void fix_length_and_dec(); bool check_partition_func_processor(uchar *int_arg) {return TRUE;} + bool check_vcol_func_processor(uchar *int_arg) {return FALSE;} }; @@ -140,6 +144,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -156,6 +161,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -172,6 +178,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -188,6 +195,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -204,6 +212,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -234,6 +243,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -252,6 +262,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -282,6 +293,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; class Item_func_dayname :public Item_func_weekday @@ -294,6 +306,7 @@ class Item_func_dayname :public Item_fun enum Item_result result_type () const { return STRING_RESULT; } void fix_length_and_dec(); bool check_partition_func_processor(uchar *int_arg) {return TRUE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -310,6 +323,18 @@ public: decimals=0; max_length=10*MY_CHARSET_BIN_MB_MAXLEN; } + bool check_vcol_func_processor(uchar *int_arg) + { + /* + TODO: Allow UNIX_TIMESTAMP called with an argument to be a part + of the expression for a virtual column + */ + DBUG_ENTER("Item_func_unix_timestamp::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } + }; @@ -325,6 +350,7 @@ public: max_length=10*MY_CHARSET_BIN_MB_MAXLEN; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -444,6 +470,14 @@ public: */ virtual void store_now_in_TIME(MYSQL_TIME *now_time)=0; bool result_as_longlong() { return TRUE; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_curtime::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } + }; @@ -480,6 +514,13 @@ public: void fix_length_and_dec(); bool get_date(MYSQL_TIME *res, uint fuzzy_date); virtual void store_now_in_TIME(MYSQL_TIME *now_time)=0; + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_curdate::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -520,6 +561,13 @@ public: void fix_length_and_dec(); bool get_date(MYSQL_TIME *res, uint fuzzy_date); virtual void store_now_in_TIME(MYSQL_TIME *now_time)=0; + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_func_now::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; @@ -577,6 +625,7 @@ public: const char *func_name() const { return "from_days"; } bool get_date(MYSQL_TIME *res, uint fuzzy_date); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -704,6 +753,7 @@ class Item_extract :public Item_int_func bool eq(const Item *item, bool binary_cmp) const; virtual void print(String *str, enum_query_type query_type); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; @@ -952,6 +1002,7 @@ public: maybe_null=1; } bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_vcol_func_processor(uchar *int_arg) { return FALSE;} }; === modified file 'sql/item_xmlfunc.cc' --- sql/item_xmlfunc.cc 2009-01-31 21:22:44 +0000 +++ sql/item_xmlfunc.cc 2009-03-24 21:10:53 +0000 @@ -220,6 +220,14 @@ public: collation.collation= pxml->charset(); } const char *func_name() const { return "nodeset"; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_nodeset_func::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } + }; @@ -526,6 +534,13 @@ public: enum Type type() const { return XPATH_NODESET_CMP; }; const char *func_name() const { return "xpath_nodeset_to_const_comparator"; } bool is_bool_func() { return 1; } + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_nodeset_to_const_comparator::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } longlong val_int() { === modified file 'sql/item_xmlfunc.h' --- sql/item_xmlfunc.h 2007-11-21 12:00:09 +0000 +++ sql/item_xmlfunc.h 2009-03-24 10:47:20 +0000 @@ -40,6 +40,13 @@ public: } void fix_length_and_dec(); String *parse_xml(String *raw_xml, String *parsed_xml_buf); + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_xml_str_func::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_RETURN(TRUE); + } }; === modified file 'sql/lex.h' --- sql/lex.h 2008-04-28 16:24:05 +0000 +++ sql/lex.h 2009-03-24 15:40:47 +0000 @@ -388,6 +388,7 @@ static SYMBOL symbols[] = { { "PARSER", SYM(PARSER_SYM)}, { "PAGE", SYM(PAGE_SYM)}, { "PAGE_CHECKSUM", SYM(PAGE_CHECKSUM_SYM)}, + { "PARSE_VCOL_EXPR", SYM(PARSE_VCOL_EXPR_SYM)}, { "PARTIAL", SYM(PARTIAL)}, { "PARTITION", SYM(PARTITION_SYM)}, { "PARTITIONING", SYM(PARTITIONING_SYM)}, @@ -514,6 +515,7 @@ static SYMBOL symbols[] = { { "STATUS", SYM(STATUS_SYM)}, { "STOP", SYM(STOP_SYM)}, { "STORAGE", SYM(STORAGE_SYM)}, + { "STORED", SYM(STORED_SYM)}, { "STRAIGHT_JOIN", SYM(STRAIGHT_JOIN)}, { "STRING", SYM(STRING_SYM)}, { "SUBJECT", SYM(SUBJECT_SYM)}, === modified file 'sql/mysql_priv.h' --- sql/mysql_priv.h 2009-03-12 22:27:35 +0000 +++ sql/mysql_priv.h 2009-03-24 10:47:20 +0000 @@ -1312,6 +1312,8 @@ find_field_in_table(THD *thd, TABLE *tab bool allow_rowid, uint *cached_field_index_ptr); Field * find_field_in_table_sef(TABLE *table, const char *name); +int update_virtual_fields_marked_for_write(TABLE *table, + bool ignore_stored= TRUE); #endif /* MYSQL_SERVER */ @@ -1432,14 +1434,16 @@ bool add_field_to_list(THD *thd, LEX_STR LEX_STRING *comment, char *change, List<String> *interval_list, CHARSET_INFO *cs, - uint uint_geom_type); + uint uint_geom_type, + virtual_column_info *vcol_info); Create_field * new_create_field(THD *thd, char *field_name, enum_field_types type, char *length, char *decimals, uint type_modifier, Item *default_value, Item *on_update_value, LEX_STRING *comment, char *change, List<String> *interval_list, CHARSET_INFO *cs, - uint uint_geom_type); + uint uint_geom_type, + virtual_column_info *vcol_info); void store_position_for_column(const char *name); bool add_to_list(THD *thd, SQL_LIST &list,Item *group,bool asc); bool push_new_name_resolution_context(THD *thd, === modified file 'sql/procedure.h' --- sql/procedure.h 2008-03-21 15:48:28 +0000 +++ sql/procedure.h 2009-03-24 21:48:56 +0000 @@ -43,6 +43,14 @@ public: init_make_field(tmp_field,field_type()); } unsigned int size_of() { return sizeof(*this);} + bool check_vcol_func_processor(uchar *int_arg) + { + DBUG_ENTER("Item_sum::check_vcol_func_processor"); + DBUG_PRINT("info", + ("check_vcol_func_processor returns TRUE: unsupported function")); + DBUG_ASSERT(0); /* this is not possible */ + DBUG_RETURN(TRUE); + } }; class Item_proc_real :public Item_proc === modified file 'sql/records.cc' --- sql/records.cc 2008-07-17 18:26:55 +0000 +++ sql/records.cc 2009-03-24 10:47:20 +0000 @@ -323,6 +323,7 @@ static int rr_quick(READ_RECORD *info) break; } } + update_virtual_fields_marked_for_write(info->table); return tmp; } @@ -395,6 +396,8 @@ int rr_sequential(READ_RECORD *info) break; } } + if (!tmp) + update_virtual_fields_marked_for_write(info->table); return tmp; } === modified file 'sql/share/errmsg.txt' --- sql/share/errmsg.txt 2008-12-10 09:02:25 +0000 +++ sql/share/errmsg.txt 2009-03-25 21:24:46 +0000 @@ -6154,3 +6154,30 @@ WARN_PLUGIN_BUSY ER_VARIABLE_IS_READONLY eng "%s variable '%s' is read-only. Use SET %s to assign the value" + +ER_VCOL_BASED_ON_VCOL + eng "A computed column cannot be based on a computed column" + +ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED + eng "Function or expression is not allowed for column '%s'." + +ER_DATA_CONVERSION_ERROR_FOR_VIRTUAL_COLUMN + eng "Generated value for computed column '%s' cannot be converted to type '%s'." + +ER_PRIMARY_KEY_BASED_ON_VIRTUAL_COLUMN + eng "Primary key cannot be defined upon a computed column." + +ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN + eng "Key/Index cannot be defined on a non-stored computed column." + +ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN + eng "Cannot define foreign key with %s clause on a computed column." + +ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN + eng "The value specified for computed column '%s' in table '%s' ignored." + +ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN + eng "'%s' is not yet supported for computed columns." + +ER_CONST_EXPR_IN_VCOL + eng "Constant expression in computed column function is not allowed." === modified file 'sql/sp_head.cc' --- sql/sp_head.cc 2009-02-19 09:01:25 +0000 +++ sql/sp_head.cc 2009-03-24 10:47:20 +0000 @@ -815,6 +815,8 @@ sp_head::create_result_field(uint field_ m_return_field_def.interval, field_name ? field_name : (const char *) m_name.str); + field->vcol_info= m_return_field_def.vcol_info; + field->stored_in_db= m_return_field_def.stored_in_db; if (field) field->init(table); @@ -2194,7 +2196,8 @@ sp_head::fill_field_definition(THD *thd, &lex->interval_list, lex->charset ? lex->charset : thd->variables.collation_database, - lex->uint_geom_type)) + lex->uint_geom_type, + lex->vcol_info)) return TRUE; if (field_def->interval_list.elements) === modified file 'sql/sql_base.cc' --- sql/sql_base.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_base.cc 2009-03-24 10:47:20 +0000 @@ -5875,6 +5875,23 @@ find_field_in_table(THD *thd, TABLE *tab if (field_ptr && *field_ptr) { + if ((*field_ptr)->vcol_info) + { + if (thd->mark_used_columns != MARK_COLUMNS_NONE) + { + Item *vcol_item= (*field_ptr)->vcol_info->expr_item; + DBUG_ASSERT(vcol_item); + vcol_item->walk(&Item::register_field_in_read_map, 1, (uchar *) 0); + /* + Set the virtual field for write here if + 1) this procedure is called for a read-only operation (SELECT), and + 2) the virtual column is not phycically stored in the table + */ + if (thd->mark_used_columns != MARK_COLUMNS_WRITE && + !(*field_ptr)->stored_in_db) + bitmap_set_bit((*field_ptr)->table->write_set, (*field_ptr)->field_index); + } + } *cached_field_index_ptr= field_ptr - table->field; field= *field_ptr; } @@ -7857,6 +7874,17 @@ insert_fields(THD *thd, Name_resolution_ { /* Mark fields as used to allow storage engine to optimze access */ bitmap_set_bit(field->table->read_set, field->field_index); + /* + Mark virtual fields for write and others that the virtual fields + depend on for read. + */ + if (field->vcol_info) + { + Item *vcol_item= field->vcol_info->expr_item; + DBUG_ASSERT(vcol_item); + vcol_item->walk(&Item::register_field_in_read_map, 1, (uchar *) 0); + bitmap_set_bit(field->table->write_set, field->field_index); + } if (table) { table->covering_keys.intersect(field->part_of_key); @@ -8068,7 +8096,10 @@ fill_record(THD * thd, List<Item> &field Item *value, *fld; Item_field *field; TABLE *table= 0; + List<TABLE> tbl_list; + bool abort_on_warning_saved= thd->abort_on_warning; DBUG_ENTER("fill_record"); + tbl_list.empty(); /* Reset the table->auto_increment_field_not_null as it is valid for @@ -8102,14 +8133,54 @@ fill_record(THD * thd, List<Item> &field table= rfield->table; if (rfield == table->next_number_field) table->auto_increment_field_not_null= TRUE; + if (rfield->vcol_info && + value->type() != Item::DEFAULT_VALUE_ITEM && + value->type() != Item::NULL_ITEM && + table->s->table_category != TABLE_CATEGORY_TEMPORARY) + { + thd->abort_on_warning= FALSE; + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN, + ER(ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN), + rfield->field_name, table->s->table_name.str); + thd->abort_on_warning= abort_on_warning_saved; + } if ((value->save_in_field(rfield, 0) < 0) && !ignore_errors) { my_message(ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR), MYF(0)); goto err; } + tbl_list.push_back(table); } + /* Update virtual fields*/ + thd->abort_on_warning= FALSE; + if (tbl_list.head()) + { + List_iterator_fast<TABLE> t(tbl_list); + TABLE *prev_table= 0; + while ((table= t++)) + { + /* + Do simple optimization to prevent unnecessary re-generating + values for virtual fields + */ + if (table != prev_table) + { + prev_table= table; + if (table->vfield) + { + if (update_virtual_fields_marked_for_write(table, FALSE)) + { + goto err; + } + } + } + } + } + thd->abort_on_warning= abort_on_warning_saved; DBUG_RETURN(thd->is_error()); err: + thd->abort_on_warning= abort_on_warning_saved; if (table) table->auto_increment_field_not_null= FALSE; DBUG_RETURN(TRUE); @@ -8145,9 +8216,31 @@ fill_record_n_invoke_before_triggers(THD Table_triggers_list *triggers, enum trg_event_type event) { - return (fill_record(thd, fields, values, ignore_errors) || - (triggers && triggers->process_triggers(thd, event, - TRG_ACTION_BEFORE, TRUE))); + bool result; + result= (fill_record(thd, fields, values, ignore_errors) || + (triggers && triggers->process_triggers(thd, event, + TRG_ACTION_BEFORE, TRUE))); + /* + Re-calculate virtual fields to cater for cases when base columns are + updated by the triggers. + */ + if (!result && triggers) + { + TABLE *table= 0; + List_iterator_fast<Item> f(fields); + Item *fld; + Item_field *item_field; + if (fields.elements) + { + fld= (Item_field*)f++; + item_field= fld->filed_for_view_update(); + if (item_field && item_field->field && + (table= item_field->field->table) && + table->vfield) + result= update_virtual_fields_marked_for_write(table, FALSE); + } + } + return result; } @@ -8175,11 +8268,14 @@ bool fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors) { List_iterator_fast<Item> v(values); + List<TABLE> tbl_list; Item *value; TABLE *table= 0; + bool abort_on_warning_saved= thd->abort_on_warning; DBUG_ENTER("fill_record"); Field *field; + tbl_list.empty(); /* Reset the table->auto_increment_field_not_null as it is valid for only one row. @@ -8199,12 +8295,52 @@ fill_record(THD *thd, Field **ptr, List< table= field->table; if (field == table->next_number_field) table->auto_increment_field_not_null= TRUE; + if (field->vcol_info && + value->type() != Item::DEFAULT_VALUE_ITEM && + value->type() != Item::NULL_ITEM && + table->s->table_category != TABLE_CATEGORY_TEMPORARY) + { + thd->abort_on_warning= FALSE; + push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN, + ER(ER_WARNING_NON_DEFAULT_VALUE_FOR_VIRTUAL_COLUMN), + field->field_name, table->s->table_name.str); + thd->abort_on_warning= abort_on_warning_saved; + } if (value->save_in_field(field, 0) < 0) goto err; + tbl_list.push_back(table); + } + /* Update virtual fields*/ + thd->abort_on_warning= FALSE; + if (tbl_list.head()) + { + List_iterator_fast<TABLE> t(tbl_list); + TABLE *prev_table= 0; + while ((table= t++)) + { + /* + Do simple optimization to prevent unnecessary re-generating + values for virtual fields + */ + if (table != prev_table) + { + prev_table= table; + if (table->vfield) + { + if (update_virtual_fields_marked_for_write(table, FALSE)) + { + goto err; + } + } + } + } } + thd->abort_on_warning= abort_on_warning_saved; DBUG_RETURN(thd->is_error()); err: + thd->abort_on_warning= abort_on_warning_saved; if (table) table->auto_increment_field_not_null= FALSE; DBUG_RETURN(TRUE); @@ -8240,9 +8376,22 @@ fill_record_n_invoke_before_triggers(THD Table_triggers_list *triggers, enum trg_event_type event) { - return (fill_record(thd, ptr, values, ignore_errors) || - (triggers && triggers->process_triggers(thd, event, - TRG_ACTION_BEFORE, TRUE))); + bool result; + result= (fill_record(thd, ptr, values, ignore_errors) || + (triggers && triggers->process_triggers(thd, event, + TRG_ACTION_BEFORE, TRUE))); + /* + Re-calculate virtual fields to cater for cases when base columns are + updated by the triggers. + */ + if (!result && triggers && *ptr) + { + TABLE *table= (*ptr)->table; + if (table->vfield) + result= update_virtual_fields_marked_for_write(table, FALSE); + } + return result; + } === modified file 'sql/sql_class.cc' --- sql/sql_class.cc 2009-03-12 22:27:35 +0000 +++ sql/sql_class.cc 2009-03-24 10:47:20 +0000 @@ -193,6 +193,57 @@ bool foreign_key_prefix(Key *a, Key *b) #endif } +/* + Check if the foreign key options are compatible with columns + on which the FK is created. + + RETURN + 0 Key valid + 1 Key invalid +*/ +bool Foreign_key::validate(List<Create_field> &table_fields) +{ + Create_field *sql_field; + Key_part_spec *column; + List_iterator<Key_part_spec> cols(columns); + List_iterator<Create_field> it(table_fields); + DBUG_ENTER("Foreign_key::validate"); + while ((column= cols++)) + { + it.rewind(); + while ((sql_field= it++) && + my_strcasecmp(system_charset_info, + column->field_name, + sql_field->field_name)) {} + if (!sql_field) + { + my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name); + DBUG_RETURN(TRUE); + } + if (type == Key::FOREIGN_KEY && sql_field->vcol_info) + { + if (delete_opt == FK_OPTION_SET_NULL) + { + my_error(ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN, MYF(0), + "ON DELETE SET NULL"); + DBUG_RETURN(TRUE); + } + if (update_opt == FK_OPTION_SET_NULL) + { + my_error(ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN, MYF(0), + "ON UPDATE SET NULL"); + DBUG_RETURN(TRUE); + } + if (update_opt == FK_OPTION_CASCADE) + { + my_error(ER_WRONG_FK_OPTION_FOR_VIRTUAL_COLUMN, MYF(0), + "ON UPDATE CASCADE"); + DBUG_RETURN(TRUE); + } + } + } + DBUG_RETURN(FALSE); +} /**************************************************************************** ** Thread specific functions === modified file 'sql/sql_class.h' --- sql/sql_class.h 2009-03-12 22:27:35 +0000 +++ sql/sql_class.h 2009-03-24 10:47:20 +0000 @@ -249,6 +249,8 @@ public: */ virtual Key *clone(MEM_ROOT *mem_root) const { return new (mem_root) Foreign_key(*this, mem_root); } + /* Used to validate foreign key options */ + bool validate(List<Create_field> &table_fields); }; typedef struct st_mysql_lock === modified file 'sql/sql_handler.cc' --- sql/sql_handler.cc 2008-02-19 12:58:08 +0000 +++ sql/sql_handler.cc 2009-03-24 10:47:20 +0000 @@ -636,6 +636,8 @@ retry: } goto ok; } + /* Generate values for virtual fields */ + update_virtual_fields_marked_for_write(table); if (cond && !cond->val_int()) continue; if (num_rows >= offset_limit_cnt) === modified file 'sql/sql_insert.cc' --- sql/sql_insert.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_insert.cc 2009-03-24 10:47:21 +0000 @@ -279,6 +279,9 @@ static int check_insert_fields(THD *thd, table->timestamp_field->field_index); } } + /* Mark all virtual columns for write*/ + if (table->vfield) + table->mark_virtual_columns(); } // For the values we need select_priv #ifndef NO_EMBEDDED_ACCESS_CHECKS === modified file 'sql/sql_lex.cc' --- sql/sql_lex.cc 2009-02-15 10:58:34 +0000 +++ sql/sql_lex.cc 2009-03-24 10:47:21 +0000 @@ -340,6 +340,7 @@ void lex_start(THD *thd) lex->reset_query_tables_list(FALSE); lex->expr_allows_subselect= TRUE; lex->use_only_table_context= FALSE; + lex->parse_vcol_expr= FALSE; lex->name.str= 0; lex->name.length= 0; === modified file 'sql/sql_lex.h' --- sql/sql_lex.h 2009-01-15 18:11:25 +0000 +++ sql/sql_lex.h 2009-03-24 10:47:21 +0000 @@ -1521,6 +1521,7 @@ typedef struct st_lex : public Query_tab LEX_USER *grant_user; XID *xid; THD *thd; + virtual_column_info *vcol_info; /* maintain a list of used plugins for this LEX */ DYNAMIC_ARRAY plugins; @@ -1603,6 +1604,14 @@ typedef struct st_lex : public Query_tab syntax error back. */ bool expr_allows_subselect; + /* + A special command "PARSE_VCOL_EXPR" is defined for the parser + to translate an expression statement of a virtual column \ + (stored in the *.frm file as a string) into an Item object. + The following flag is used to prevent other applications to use + this command. + */ + bool parse_vcol_expr; thr_lock_type lock_option; enum SSL_type ssl_type; /* defined in violite.h */ === modified file 'sql/sql_parse.cc' --- sql/sql_parse.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_parse.cc 2009-03-24 10:47:21 +0000 @@ -5907,7 +5907,8 @@ bool add_field_to_list(THD *thd, LEX_STR LEX_STRING *comment, char *change, List<String> *interval_list, CHARSET_INFO *cs, - uint uint_geom_type) + uint uint_geom_type, + virtual_column_info *vcol_info) { register Create_field *new_field; LEX *lex= thd->lex; @@ -5993,7 +5994,7 @@ bool add_field_to_list(THD *thd, LEX_STR if (!(new_field= new Create_field()) || new_field->init(thd, field_name->str, type, length, decimals, type_modifier, default_value, on_update_value, comment, change, - interval_list, cs, uint_geom_type)) + interval_list, cs, uint_geom_type, vcol_info)) DBUG_RETURN(1); lex->alter_info.create_list.push_back(new_field); === modified file 'sql/sql_partition.cc' --- sql/sql_partition.cc 2009-02-15 10:58:34 +0000 +++ sql/sql_partition.cc 2009-03-26 21:13:30 +0000 @@ -964,8 +964,9 @@ bool fix_fields_part_func(THD *thd, Item save_use_only_table_context= thd->lex->use_only_table_context; thd->lex->use_only_table_context= TRUE; - + error= func_expr->fix_fields(thd, (Item**)0); + func_expr->walk(&Item::vcol_in_partition_func_processor, 0, NULL); thd->lex->use_only_table_context= save_use_only_table_context; === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_select.cc 2009-03-24 10:47:21 +0000 @@ -11717,6 +11717,7 @@ join_read_system(JOIN_TAB *tab) empty_record(table); // Make empty record return -1; } + update_virtual_fields_marked_for_write(table); store_record(table,record[1]); } else if (!table->status) // Only happens with left join @@ -11765,6 +11766,7 @@ join_read_const(JOIN_TAB *tab) return report_error(table, error); return -1; } + update_virtual_fields_marked_for_write(table); store_record(table,record[1]); } else if (!(table->status & ~STATUS_NULL_ROW)) // Only happens with left join @@ -11854,6 +11856,8 @@ join_read_always_key(JOIN_TAB *tab) return report_error(table, error); return -1; /* purecov: inspected */ } + if (!error) + update_virtual_fields_marked_for_write(table); return 0; } @@ -11881,6 +11885,7 @@ join_read_last_key(JOIN_TAB *tab) return report_error(table, error); return -1; /* purecov: inspected */ } + update_virtual_fields_marked_for_write(table); return 0; } @@ -11909,6 +11914,7 @@ join_read_next_same(READ_RECORD *info) table->status= STATUS_GARBAGE; return -1; } + update_virtual_fields_marked_for_write(table); return 0; } @@ -11922,12 +11928,14 @@ join_read_prev_same(READ_RECORD *info) if ((error=table->file->index_prev(table->record[0]))) return report_error(table, error); + update_virtual_fields_marked_for_write(table); if (key_cmp_if_same(table, tab->ref.key_buff, tab->ref.key, tab->ref.key_length)) { table->status=STATUS_NOT_FOUND; error= -1; } + update_virtual_fields_marked_for_write(table); return error; } @@ -11996,6 +12004,8 @@ join_read_first(JOIN_TAB *tab) report_error(table, error); return -1; } + if (not error) + update_virtual_fields_marked_for_write(tab->table); return 0; } @@ -12006,6 +12016,8 @@ join_read_next(READ_RECORD *info) int error; if ((error=info->file->index_next(info->record))) return report_error(info->table, error); + if (not error) + update_virtual_fields_marked_for_write(info->table); return 0; } @@ -12031,6 +12043,8 @@ join_read_last(JOIN_TAB *tab) table->file->ha_index_init(tab->index, 1); if ((error= tab->table->file->index_last(tab->table->record[0]))) return report_error(table, error); + if (not error) + update_virtual_fields_marked_for_write(tab->table); return 0; } @@ -12041,6 +12055,8 @@ join_read_prev(READ_RECORD *info) int error; if ((error= info->file->index_prev(info->record))) return report_error(info->table, error); + if (not error) + update_virtual_fields_marked_for_write(info->table); return 0; } @@ -12062,6 +12078,8 @@ join_ft_read_first(JOIN_TAB *tab) if ((error= table->file->ft_read(table->record[0]))) return report_error(table, error); + if (not error) + update_virtual_fields_marked_for_write(table); return 0; } @@ -12071,6 +12089,7 @@ join_ft_read_next(READ_RECORD *info) int error; if ((error= info->file->ft_read(info->table->record[0]))) return report_error(info->table, error); + update_virtual_fields_marked_for_write(info->table); return 0; } === modified file 'sql/sql_show.cc' --- sql/sql_show.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_show.cc 2009-03-24 15:39:10 +0000 @@ -1175,6 +1175,17 @@ int store_create_info(THD *thd, TABLE_LI field->sql_type(type); packet->append(type.ptr(), type.length(), system_charset_info); + if (field->vcol_info) + { + packet->append(STRING_WITH_LEN(" AS (")); + packet->append(field->vcol_info->expr_str.str, + field->vcol_info->expr_str.length, + system_charset_info); + packet->append(STRING_WITH_LEN(")")); + if (field->stored_in_db) + packet->append(STRING_WITH_LEN(" STORED")); + } + if (field->has_charset() && !(thd->variables.sql_mode & (MODE_MYSQL323 | MODE_MYSQL40))) { @@ -1205,7 +1216,8 @@ int store_create_info(THD *thd, TABLE_LI packet->append(STRING_WITH_LEN(" NULL")); } - if (get_field_default_value(thd, table, field, &def_value, 1)) + if (!field->vcol_info && + get_field_default_value(thd, table, field, &def_value, 1)) { packet->append(STRING_WITH_LEN(" DEFAULT ")); packet->append(def_value.ptr(), def_value.length(), system_charset_info); @@ -3876,6 +3888,8 @@ static int get_schema_column_record(THD field->unireg_check != Field::TIMESTAMP_DN_FIELD) table->field[16]->store(STRING_WITH_LEN("on update CURRENT_TIMESTAMP"), cs); + if (field->vcol_info) + table->field[16]->store(STRING_WITH_LEN("VIRTUAL"), cs); table->field[18]->store(field->comment.str, field->comment.length, cs); if (schema_table_store_record(thd, table)) === modified file 'sql/sql_table.cc' --- sql/sql_table.cc 2009-02-19 09:01:25 +0000 +++ sql/sql_table.cc 2009-03-26 21:17:10 +0000 @@ -2166,7 +2166,8 @@ int prepare_create_field(Create_field *s (sql_field->decimals << FIELDFLAG_DEC_SHIFT)); break; } - if (!(sql_field->flags & NOT_NULL_FLAG)) + if (!(sql_field->flags & NOT_NULL_FLAG) || + (sql_field->vcol_info)) /* Make virtual columns allow NULL values */ sql_field->pack_flag|= FIELDFLAG_MAYBE_NULL; if (sql_field->flags & NO_DEFAULT_VALUE_FLAG) sql_field->pack_flag|= FIELDFLAG_NO_DEFAULT; @@ -2480,6 +2481,8 @@ mysql_prepare_create_table(THD *thd, HA_ null_fields--; sql_field->flags= dup_field->flags; sql_field->interval= dup_field->interval; + sql_field->vcol_info= dup_field->vcol_info; + sql_field->stored_in_db= dup_field->stored_in_db; it2.remove(); // Remove first (create) definition select_field_pos--; break; @@ -2512,7 +2515,23 @@ mysql_prepare_create_table(THD *thd, HA_ sql_field->offset= record_offset; if (MTYP_TYPENR(sql_field->unireg_check) == Field::NEXT_NUMBER) auto_increment++; - record_offset+= sql_field->pack_length; + /* + For now skip fields that are not physically stored in the database + (virtual fields) and update their offset later + (see the next loop). + */ + if (sql_field->stored_in_db) + record_offset+= sql_field->pack_length; + } + /* Update virtual fields' offset*/ + it.rewind(); + while ((sql_field=it++)) + { + if (!sql_field->stored_in_db) + { + sql_field->offset= record_offset; + record_offset+= sql_field->pack_length; + } } if (timestamps_with_niladic > 1) { @@ -2562,6 +2581,8 @@ mysql_prepare_create_table(THD *thd, HA_ if (key->type == Key::FOREIGN_KEY) { fk_key_count++; + if (((Foreign_key *)key)->validate(alter_info->create_list)) + DBUG_RETURN(TRUE); Foreign_key *fk_key= (Foreign_key*) key; if (fk_key->ref_columns.elements && fk_key->ref_columns.elements != fk_key->columns.elements) @@ -2848,6 +2869,17 @@ mysql_prepare_create_table(THD *thd, HA_ } } #endif + if (!sql_field->stored_in_db) + { + /* Key fields must always be physically stored. */ + my_error(ER_KEY_BASED_ON_GENERATED_VIRTUAL_COLUMN, MYF(0)); + DBUG_RETURN(TRUE); + } + if (key->type == Key::PRIMARY && sql_field->vcol_info) + { + my_error(ER_PRIMARY_KEY_BASED_ON_VIRTUAL_COLUMN, MYF(0)); + DBUG_RETURN(TRUE); + } if (!(sql_field->flags & NOT_NULL_FLAG)) { if (key->type == Key::PRIMARY) @@ -5357,6 +5389,18 @@ compare_tables(TABLE *table, DBUG_RETURN(0); } + /* + Check if the altered column is a stored virtual field. + TODO: Mark such a column with an alter flag only if + the expression functions are not equal. + */ + if (field->stored_in_db && field->vcol_info || + field->vcol_info && field->vcol_info->is_field_implicitly_stored()) + { + *need_copy_table= ALTER_TABLE_DATA_CHANGED; + DBUG_RETURN(0); + } + /* Don't pack rows in old tables if the user has requested this. */ if (create_info->row_type == ROW_TYPE_DYNAMIC || (tmp_new_field->flags & BLOB_FLAG) || @@ -5713,6 +5757,13 @@ mysql_prepare_alter_table(THD *thd, TABL if (def) { // Field is changed def->field=field; + if (field->stored_in_db != def->stored_in_db) + { + my_error(ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN, + MYF(0), + "Changing the STORED status"); + goto err; + } if (!def->after) { new_create_list.push_back(def); @@ -5925,6 +5976,9 @@ mysql_prepare_alter_table(THD *thd, TABL Key *key; while ((key=key_it++)) // Add new keys { + if (key->type == Key::FOREIGN_KEY && + ((Foreign_key *)key)->validate(new_create_list)) + goto err; if (key->type != Key::FOREIGN_KEY) new_key_list.push_back(key); if (key->name && @@ -7327,6 +7381,12 @@ copy_data_between_tables(TABLE *from,TAB copy_ptr->do_copy(copy_ptr); } prev_insert_id= to->file->next_insert_id; + update_virtual_fields_marked_for_write(to, FALSE); + if (thd->is_error()) + { + error= 1; + break; + } error=to->file->ha_write_row(to->record[0]); to->auto_increment_field_not_null= FALSE; if (error) === modified file 'sql/sql_yacc.yy' --- sql/sql_yacc.yy 2009-02-19 09:01:25 +0000 +++ sql/sql_yacc.yy 2009-03-24 11:09:35 +0000 @@ -886,6 +886,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** %token PAGE_CHECKSUM_SYM %token PARAM_MARKER %token PARSER_SYM +%token PARSE_VCOL_EXPR_SYM %token PARTIAL /* SQL-2003-N */ %token PARTITIONING_SYM %token PARTITIONS_SYM @@ -1007,6 +1008,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** %token STD_SYM %token STOP_SYM %token STORAGE_SYM +%token STORED_SYM %token STRAIGHT_JOIN %token STRING_SYM %token SUBDATE_SYM @@ -1143,7 +1145,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** text_string opt_gconcat_separator %type <num> - type int_type real_type order_dir lock_option + type int_type real_type order_dir lock_option field_def udf_type if_exists opt_local opt_table_options table_options table_option opt_if_not_exists opt_no_write_to_binlog delete_option opt_temporary all_or_any opt_distinct @@ -1299,6 +1301,8 @@ bool my_yyoverflow(short **a, YYSTYPE ** init_key_options key_options key_opts key_opt key_using_alg server_def server_options_list server_option definer_opt no_definer definer + parse_vcol_expr vcol_opt_attribute vcol_opt_attribute_list + vcol_attribute END_OF_INPUT %type <NONE> call sp_proc_stmts sp_proc_stmts1 sp_proc_stmt @@ -1430,6 +1434,7 @@ statement: | lock | optimize | keycache + | parse_vcol_expr | partition_entry | preload | prepare @@ -4735,8 +4740,9 @@ field_spec: lex->default_value= lex->on_update_value= 0; lex->comment=null_lex_str; lex->charset=NULL; + lex->vcol_info= 0; } - type opt_attribute + field_def { LEX *lex=Lex; if (add_field_to_list(lex->thd, &$1, (enum enum_field_types) $3, @@ -4744,11 +4750,83 @@ field_spec: lex->default_value, lex->on_update_value, &lex->comment, lex->change,&lex->interval_list,lex->charset, - lex->uint_geom_type)) + lex->uint_geom_type, + lex->vcol_info)) MYSQL_YYABORT; } ; +field_def: + type opt_attribute {} + | type AS '(' virtual_column_func ')' vcol_opt_attribute + { + $$= (enum enum_field_types)MYSQL_TYPE_VIRTUAL; + Lex->vcol_info->set_field_type((enum enum_field_types) $1); + } + ; + +vcol_opt_attribute: + /* empty */ {} + | vcol_opt_attribute_list {} + ; + +vcol_opt_attribute_list: + vcol_opt_attribute_list vcol_attribute {} + | vcol_attribute + ; + +vcol_attribute: + UNIQUE_SYM + { + LEX *lex=Lex; + lex->type|= UNIQUE_FLAG; + lex->alter_info.flags|= ALTER_ADD_INDEX; + } + | UNIQUE_SYM KEY_SYM + { + LEX *lex=Lex; + lex->type|= UNIQUE_KEY_FLAG; + lex->alter_info.flags|= ALTER_ADD_INDEX; + } + | COMMENT_SYM TEXT_STRING_sys { Lex->comment= $2; } + | STORED_SYM + { + Lex->vcol_info->set_field_stored(TRUE); + } + ; + +parse_vcol_expr: + PARSE_VCOL_EXPR_SYM '(' virtual_column_func ')' + { + /* + "PARSE_VCOL_EXPR" can only be used by the SQL server + when reading a '*.frm' file. + Prevent the end user from invoking this command. + */ + if (!Lex->parse_vcol_expr) + { + my_message(ER_SYNTAX_ERROR, ER(ER_SYNTAX_ERROR), MYF(0)); + MYSQL_YYABORT; + } + } + ; + +virtual_column_func: + remember_name expr remember_end + { + Lex->vcol_info= new virtual_column_info(); + if (!Lex->vcol_info) + { + mem_alloc_error(sizeof(virtual_column_info)); + MYSQL_YYABORT; + } + uint expr_len= (uint)($3 - $1) - 1; + Lex->vcol_info->expr_str.str= (char* ) sql_memdup($1 + 1, expr_len); + Lex->vcol_info->expr_str.length= expr_len; + Lex->vcol_info->expr_item= $2; + } + ; + type: int_type opt_field_length field_options { $$=$1; } | real_type opt_precision field_options { $$=$1; } @@ -5836,8 +5914,9 @@ alter_list_item: lex->comment=null_lex_str; lex->charset= NULL; lex->alter_info.flags|= ALTER_CHANGE_COLUMN; + lex->vcol_info= 0; } - type opt_attribute + field_def { LEX *lex=Lex; if (add_field_to_list(lex->thd,&$3, @@ -5846,7 +5925,8 @@ alter_list_item: lex->default_value, lex->on_update_value, &lex->comment, $3.str, &lex->interval_list, lex->charset, - lex->uint_geom_type)) + lex->uint_geom_type, + lex->vcol_info)) MYSQL_YYABORT; } opt_place @@ -13274,6 +13354,7 @@ sf_tail: lex->length= lex->dec= NULL; lex->interval_list.empty(); lex->type= 0; + lex->vcol_info= 0; } type /* $11 */ { /* $12 */ === modified file 'sql/table.cc' --- sql/table.cc 2009-02-19 09:01:25 +0000 +++ sql/table.cc 2009-03-26 21:28:14 +0000 @@ -33,6 +33,9 @@ LEX_STRING GENERAL_LOG_NAME= {C_STRING_W /* SLOW_LOG name */ LEX_STRING SLOW_LOG_NAME= {C_STRING_WITH_LEN("slow_log")}; +/* Keyword for parsing virtual column functions */ +LEX_STRING parse_vcol_keyword= { C_STRING_WITH_LEN("PARSE_VCOL_EXPR ") }; + /* Functions defined in this file */ void open_table_error(TABLE_SHARE *share, int error, int db_errno, @@ -663,10 +666,11 @@ static int open_binary_frm(THD *thd, TAB uint interval_count, interval_parts, read_length, int_length; uint db_create_options, keys, key_parts, n_length; uint key_info_length, com_length, null_bit_pos; + uint vcol_screen_length; uint extra_rec_buf_length; uint i,j; bool use_hash; - char *keynames, *names, *comment_pos; + char *keynames, *names, *comment_pos, *vcol_screen_pos; uchar *record; uchar *disk_buff, *strpos, *null_flags, *null_pos; ulong pos, record_offset, *rec_per_key, rec_buff_length; @@ -836,6 +840,7 @@ static int open_binary_frm(THD *thd, TAB strpos+= (strmov(keynames, (char *) strpos) - keynames)+1; share->reclength = uint2korr((head+16)); + share->stored_rec_length= share->reclength; if (*(head+26) == 1) share->system= 1; /* one-record-database */ #ifdef HAVE_CRYPTED_FRM @@ -1040,24 +1045,28 @@ static int open_binary_frm(THD *thd, TAB int_length= uint2korr(head+274); share->null_fields= uint2korr(head+282); com_length= uint2korr(head+284); + vcol_screen_length= uint2korr(head+286); + share->vfields= 0; + share->stored_fields= share->fields; share->comment.length= (int) (head[46]); share->comment.str= strmake_root(&share->mem_root, (char*) head+47, share->comment.length); - DBUG_PRINT("info",("i_count: %d i_parts: %d index: %d n_length: %d int_length: %d com_length: %d", interval_count,interval_parts, share->keys,n_length,int_length, com_length)); - + DBUG_PRINT("info",("i_count: %d i_parts: %d index: %d n_length: %d int_length: %d com_length: %d vcol_screen_length: %d", interval_count,interval_parts, share->keys,n_length,int_length, com_length, vcol_screen_length)); if (!(field_ptr = (Field **) alloc_root(&share->mem_root, (uint) ((share->fields+1)*sizeof(Field*)+ interval_count*sizeof(TYPELIB)+ (share->fields+interval_parts+ keys+3)*sizeof(char *)+ - (n_length+int_length+com_length))))) + (n_length+int_length+com_length+ + vcol_screen_length))))) goto err; /* purecov: inspected */ share->field= field_ptr; read_length=(uint) (share->fields * field_pack_length + - pos+ (uint) (n_length+int_length+com_length)); + pos+ (uint) (n_length+int_length+com_length+ + vcol_screen_length)); if (read_string(file,(uchar**) &disk_buff,read_length)) goto err; /* purecov: inspected */ #ifdef HAVE_CRYPTED_FRM @@ -1078,7 +1087,11 @@ static int open_binary_frm(THD *thd, TAB memcpy((char*) names, strpos+(share->fields*field_pack_length), (uint) (n_length+int_length)); comment_pos= names+(n_length+int_length); - memcpy(comment_pos, disk_buff+read_length-com_length, com_length); + memcpy(comment_pos, disk_buff+read_length-com_length-vcol_screen_length, + com_length); + vcol_screen_pos= names+(n_length+int_length+com_length); + memcpy(vcol_screen_pos, disk_buff+read_length-vcol_screen_length, + vcol_screen_length); fix_type_pointers(&interval_array, &share->fieldnames, 1, &names); if (share->fieldnames.count != share->fields) @@ -1146,10 +1159,13 @@ static int open_binary_frm(THD *thd, TAB for (i=0 ; i < share->fields; i++, strpos+=field_pack_length, field_ptr++) { uint pack_flag, interval_nr, unireg_type, recpos, field_length; + uint vcol_info_length=0; enum_field_types field_type; CHARSET_INFO *charset=NULL; Field::geometry_type geom_type= Field::GEOM_GEOMETRY; LEX_STRING comment; + virtual_column_info *vcol_info= 0; + bool fld_stored_in_db= TRUE; if (new_frm_ver >= 3) { @@ -1184,6 +1200,18 @@ static int open_binary_frm(THD *thd, TAB goto err; } } + + if ((uchar)field_type == (uchar)MYSQL_TYPE_VIRTUAL) + { + DBUG_ASSERT(interval_nr); // Expect non-null expression + /* + The interval_id byte in the .frm file stores the length of the + expression statement for a virtual column. + */ + vcol_info_length= interval_nr; + interval_nr= 0; + } + if (!comment_length) { comment.str= (char*) ""; @@ -1195,6 +1223,33 @@ static int open_binary_frm(THD *thd, TAB comment.length= comment_length; comment_pos+= comment_length; } + + if (vcol_info_length) + { + /* + Get virtual column data stored in the .frm file as follows: + byte 1 = 1 (always 1 to allow for future extensions) + byte 2 = sql_type + byte 3 = flags (as of now, 0 - no flags, 1 - field is physically stored) + byte 4-... = virtual column expression (text data) + */ + vcol_info= new virtual_column_info(); + if ((uint)vcol_screen_pos[0] != 1) + { + error= 4; + goto err; + } + field_type= (enum_field_types) (uchar) vcol_screen_pos[1]; + fld_stored_in_db= (bool) (uint) vcol_screen_pos[2]; + vcol_info->expr_str.str= (char *)memdup_root(&share->mem_root, + vcol_screen_pos+ + (uint)FRM_VCOL_HEADER_SIZE, + vcol_info_length- + (uint)FRM_VCOL_HEADER_SIZE); + vcol_info->expr_str.length= vcol_info_length-(uint)FRM_VCOL_HEADER_SIZE; + vcol_screen_pos+= vcol_info_length; + share->vfields++; + } } else { @@ -1285,6 +1340,8 @@ static int open_binary_frm(THD *thd, TAB reg_field->field_index= i; reg_field->comment=comment; + reg_field->vcol_info= vcol_info; + reg_field->stored_in_db= fld_stored_in_db; if (field_type == MYSQL_TYPE_BIT && !f_bit_as_char(pack_flag)) { if ((null_bit_pos+= field_length & 7) > 7) @@ -1309,8 +1366,17 @@ static int open_binary_frm(THD *thd, TAB if (use_hash) (void) my_hash_insert(&share->name_hash, (uchar*) field_ptr); // never fail + if (!reg_field->stored_in_db) + { + share->stored_fields--; + if (share->stored_rec_length>=recpos) + share->stored_rec_length= recpos-1; + } } *field_ptr=0; // End marker + /* Sanity checks: */ + DBUG_ASSERT(share->fields>=share->stored_fields); + DBUG_ASSERT(share->reclength>=share->stored_rec_length); /* Fix key->name and key_part->field */ if (key_parts) @@ -1597,6 +1663,268 @@ static int open_binary_frm(THD *thd, TAB DBUG_RETURN(error); } /* open_binary_frm */ +/* + Clear flag GET_FIXED_FIELDS_FLAG in all fields of the table. + This routine is used for error handling purposes. + + SYNOPSIS + clear_field_flag() + table TABLE object for which virtual columns are set-up + + RETURN VALUE + NONE +*/ +static void clear_field_flag(TABLE *table) +{ + Field **ptr; + DBUG_ENTER("clear_field_flag"); + + for (ptr= table->field; *ptr; ptr++) + (*ptr)->flags&= (~GET_FIXED_FIELDS_FLAG); + DBUG_VOID_RETURN; +} + +/* + The function uses the feature in fix_fields where the flag + GET_FIXED_FIELDS_FLAG is set for all fields in the item tree. + This field must always be reset before returning from the function + since it is used for other purposes as well. + + SYNOPSIS + fix_fields_vcol_func() + thd The thread object + vcol_info The item tree reference of the virtual columnfunction + table The table object + field_name The name of the processed field + + RETURN VALUE + TRUE An error occurred, something was wrong with the + function. + FALSE Ok, a partition field array was created +*/ + +bool fix_fields_vcol_func(THD *thd, + virtual_column_info *vcol_info, + TABLE *table, + const char *field_name) +{ + Item* func_expr= vcol_info->expr_item; + uint dir_length, home_dir_length; + bool result= TRUE; + TABLE_LIST tables; + TABLE_LIST *save_table_list, *save_first_table, *save_last_table; + int error; + Name_resolution_context *context; + const char *save_where; + char* db_name; + char db_name_string[FN_REFLEN]; + bool save_use_only_table_context; + Field **ptr, *field; + enum_mark_columns save_mark_used_columns= thd->mark_used_columns; + DBUG_ASSERT(func_expr); + DBUG_ENTER("fix_fields_vcol_func"); + + /* + Set-up the TABLE_LIST object to be a list with a single table + Set the object to zero to create NULL pointers and set alias + and real name to table name and get database name from file name. + */ + + bzero((void*)&tables, sizeof(TABLE_LIST)); + tables.alias= tables.table_name= (char*) table->s->table_name.str; + tables.table= table; + tables.next_local= 0; + tables.next_name_resolution_table= 0; + strmov(db_name_string, table->s->normalized_path.str); + dir_length= dirname_length(db_name_string); + db_name_string[dir_length - 1]= 0; + home_dir_length= dirname_length(db_name_string); + db_name= &db_name_string[home_dir_length]; + tables.db= db_name; + + thd->mark_used_columns= MARK_COLUMNS_NONE; + + context= thd->lex->current_context(); + table->map= 1; //To ensure correct calculation of const item + table->get_fields_in_item_tree= TRUE; + save_table_list= context->table_list; + save_first_table= context->first_name_resolution_table; + save_last_table= context->last_name_resolution_table; + context->table_list= &tables; + context->first_name_resolution_table= &tables; + context->last_name_resolution_table= NULL; + func_expr->walk(&Item::change_context_processor, 0, (uchar*) context); + save_where= thd->where; + thd->where= "virtual column function"; + + /* Save the context before fixing the fields*/ + save_use_only_table_context= thd->lex->use_only_table_context; + thd->lex->use_only_table_context= TRUE; + /* Fix fields referenced to by the virtual column function */ + error= func_expr->fix_fields(thd, (Item**)0); + /* Restore the original context*/ + thd->lex->use_only_table_context= save_use_only_table_context; + context->table_list= save_table_list; + context->first_name_resolution_table= save_first_table; + context->last_name_resolution_table= save_last_table; + + if (unlikely(error)) + { + DBUG_PRINT("info", ("Field in virtual column function not part of table")); + clear_field_flag(table); + goto end; + } + thd->where= save_where; +#ifdef PARANOID + /* + Walk through the Item tree checking if all items are valid + to be part of the virtual column + */ + error= func_expr->walk(&Item::check_vcol_func_processor, 0, NULL); + if (error) + { + my_error(ER_VIRTUAL_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(0), field_name); + clear_field_flag(table); + goto end; + } +#endif + if (unlikely(func_expr->const_item())) + { + my_error(ER_CONST_EXPR_IN_VCOL, MYF(0)); + clear_field_flag(table); + goto end; + } + /* Ensure that this virtual column is not based on another virtual field. */ + ptr= table->field; + while ((field= *(ptr++))) + { + if ((field->flags & GET_FIXED_FIELDS_FLAG) && + (field->vcol_info)) + { + my_error(ER_VCOL_BASED_ON_VCOL, MYF(0)); + clear_field_flag(table); + goto end; + } + } + /* + Cleanup the fields marked with flag GET_FIXED_FIELDS_FLAG + when calling fix_fields. + */ + clear_field_flag(table); + result= FALSE; + +end: + table->get_fields_in_item_tree= FALSE; + thd->mark_used_columns= save_mark_used_columns; + table->map= 0; //Restore old value + DBUG_RETURN(result); +} + +/* + Unpack the definition of a virtual column + + SYNOPSIS + unpack_vcol_info_from_frm() + thd Thread handler + table Table with the checked field + field Pointer to Field object + error_reported updated flag for the caller that no other error + messages are to be generated. + + RETURN VALUES + TRUE Failure + FALSE Success +*/ +bool unpack_vcol_info_from_frm(THD *thd, + TABLE *table, + Field *field, + LEX_STRING *vcol_expr, + bool *error_reported) +{ + DBUG_ENTER("unpack_vcol_info_from_frm"); + DBUG_ASSERT(vcol_expr); + + /* + Step 1: Construct a statement for the parser. + The parsed string needs to take the following format: + "PARSE_VCOL_EXPR (<expr_string_from_frm>)" + */ + char *vcol_expr_str; + int str_len= 0; + CHARSET_INFO *old_character_set_client; + + if (!(vcol_expr_str= (char*) alloc_root(&table->mem_root, + vcol_expr->length + + parse_vcol_keyword.length + 3))) + { + DBUG_RETURN(TRUE); + } + memcpy(vcol_expr_str, + (char*) parse_vcol_keyword.str, + parse_vcol_keyword.length); + str_len= parse_vcol_keyword.length; + memcpy(vcol_expr_str + str_len, "(", 1); + str_len++; + memcpy(vcol_expr_str + str_len, + (char*) vcol_expr->str, + vcol_expr->length); + str_len+= vcol_expr->length; + memcpy(vcol_expr_str + str_len, ")", 1); + str_len++; + memcpy(vcol_expr_str + str_len, "\0", 1); + str_len++; + Parser_state parser_state(thd, vcol_expr_str, str_len); + + /* + Step 2: Setup thd for parsing. + */ + Query_arena *backup_stmt_arena_ptr= thd->stmt_arena; + Query_arena backup_arena; + Query_arena vcol_arena(&table->mem_root, Query_arena::INITIALIZED); + thd->set_n_backup_active_arena(&vcol_arena, &backup_arena); + thd->stmt_arena= &vcol_arena; + + thd->lex->parse_vcol_expr= TRUE; + old_character_set_client= thd->variables.character_set_client; + + /* + Step 3: Use the parser to build an Item object from. + */ + if (parse_sql(thd, &parser_state, NULL)) + { + goto parse_err; + } + /* From now on use vcol_info generated by the parser. */ + field->vcol_info= thd->lex->vcol_info; + + /* Validate the Item tree. */ + if (fix_fields_vcol_func(thd, + field->vcol_info, + table, + field->field_name)) + { + *error_reported= TRUE; + field->vcol_info= 0; + goto parse_err; + } + thd->stmt_arena= backup_stmt_arena_ptr; + thd->restore_active_arena(&vcol_arena, &backup_arena); + field->vcol_info->item_free_list= vcol_arena.free_list; + + DBUG_RETURN(FALSE); + +parse_err: + thd->lex->parse_vcol_expr= FALSE; + thd->free_items(); + thd->stmt_arena= backup_stmt_arena_ptr; + thd->restore_active_arena(&vcol_arena, &backup_arena); + thd->variables.character_set_client= old_character_set_client; + DBUG_RETURN(TRUE); +} + +/* + Read data from a binary .frm file from MySQL 3.23 - 5.0 into TABLE_SHARE +*/ /* Open a table based on a TABLE_SHARE @@ -1631,7 +1959,7 @@ int open_table_from_share(THD *thd, TABL uint records, i, bitmap_size; bool error_reported= FALSE; uchar *record, *bitmaps; - Field **field_ptr; + Field **field_ptr, **vfield_ptr; DBUG_ENTER("open_table_from_share"); DBUG_PRINT("enter",("name: '%s.%s' form: 0x%lx", share->db.str, share->table_name.str, (long) outparam)); @@ -1784,6 +2112,34 @@ int open_table_from_share(THD *thd, TABL } } + /* + Process virtual columns, if any. + */ + if (!(vfield_ptr = (Field **) alloc_root(&outparam->mem_root, + (uint) ((share->vfields+1)* + sizeof(Field*))))) + goto err; + + outparam->vfield= vfield_ptr; + + for (field_ptr= outparam->field; *field_ptr; field_ptr++) + { + if ((*field_ptr)->vcol_info) + { + if (unpack_vcol_info_from_frm(thd, + outparam, + *field_ptr, + &(*field_ptr)->vcol_info->expr_str, + &error_reported)) + { + error= 4; // in case no error is reported + goto err; + } + *(vfield_ptr++)= *field_ptr; + } + } + *vfield_ptr= 0; // End marker + #ifdef WITH_PARTITION_STORAGE_ENGINE if (share->partition_info_len && outparam->file) { @@ -1851,6 +2207,20 @@ partititon_err: } #endif + /* Check virtual columns against table's storage engine. */ + if (share->vfields && + ((outparam->file && + !outparam->file->check_if_supported_virtual_columns()) || + (!outparam->file && share->db_type() && + share->db_type()->db_type == DB_TYPE_CSV_DB))) // Workaround for CSV + { + my_error(ER_UNSUPPORTED_ACTION_ON_VIRTUAL_COLUMN, + MYF(0), + "Specified storage engine"); + error_reported= TRUE; + goto err; + } + /* Allocate bitmaps */ bitmap_size= share->column_bitmap_size; @@ -1965,7 +2335,11 @@ int closefrm(register TABLE *table, bool if (table->field) { for (Field **ptr=table->field ; *ptr ; ptr++) + { + if ((*ptr)->vcol_info) + free_items((*ptr)->vcol_info->item_free_list); delete *ptr; + } table->field= 0; } delete table->file; @@ -3934,6 +4308,25 @@ const char *Field_iterator_table::name() return (*ptr)->field_name; } +Item *create_table_vcol_field(THD *thd, Item **field_ref, + const char *fld_name, + const char *tbl_name) +{ + Item *field= *field_ref; + DBUG_ENTER("create_table_vcol_field"); + DBUG_ASSERT(field); + + if (!field->fixed) + { + if (field->fix_fields(thd, field_ref)) + { + DBUG_RETURN(0); + } + field= *field_ref; + } + field->set_name(fld_name, strlen(fld_name), system_charset_info); + DBUG_RETURN(field); +} Item *Field_iterator_table::create_item(THD *thd) { @@ -4388,7 +4781,14 @@ void st_table::mark_columns_used_by_inde KEY_PART_INFO *key_part_end= (key_part + key_info[index].key_parts); for (;key_part != key_part_end; key_part++) + { bitmap_set_bit(bitmap, key_part->fieldnr-1); + if (key_part->field->vcol_info && + key_part->field->vcol_info->expr_item) + key_part->field->vcol_info-> + expr_item->walk(&Item::register_field_in_bitmap, + 1, (uchar *) bitmap); + } } @@ -4515,6 +4915,8 @@ void st_table::mark_columns_needed_for_u file->column_bitmaps_signal(); } } + /* Mark all virtual columns as writable */ + mark_virtual_columns(); DBUG_VOID_RETURN; } @@ -4541,8 +4943,42 @@ void st_table::mark_columns_needed_for_i } if (found_next_number_field) mark_auto_increment_column(); + /* Mark all virtual columns as writable */ + mark_virtual_columns(); } +/* + @brief Update the write and read table bitmap to allow + using procedure save_in_field for all virtual columns + in the table. + + @return void + + @detail + Each virtual field is set in the write column map. + All fields that the virtual columns are based on are set in the + read bitmap. +*/ + +void st_table::mark_virtual_columns(void) +{ + Field **vfield_ptr, *tmp_vfield; + bool bitmap_updated= FALSE; + + for (vfield_ptr= vfield; *vfield_ptr; vfield_ptr++) + { + tmp_vfield= *vfield_ptr; + DBUG_ASSERT(tmp_vfield->vcol_info && tmp_vfield->vcol_info->expr_item); + tmp_vfield->vcol_info->expr_item->walk(&Item::register_field_in_read_map, + 1, (uchar *) 0); + bitmap_set_bit(read_set, tmp_vfield->field_index); + bitmap_set_bit(write_set, tmp_vfield->field_index); + // TODO: consider updating column maps for index + bitmap_updated= TRUE; + } + if (bitmap_updated) + file->column_bitmaps_signal(); +} /** @brief Check if this is part of a MERGE table with attached children. @@ -4797,6 +5233,54 @@ size_t max_row_length(TABLE *table, cons return length; } +/* + Calculate data for each virtual field marked for write in the + corresponding column map. + + SYNOPSIS + update_virtual_fields_marked_for_write() + table The TABLE object + ignore_stored Indication whether physically stored virtual + fields do not need updating. + This value is false when during INSERT and UPDATE + and true in all other cases. + + RETURN + 0 - Success + >0 - Error occurred during the generation/calculation of a virtual field value + +*/ + +int update_virtual_fields_marked_for_write(TABLE *table, + bool ignore_stored) +{ + DBUG_ENTER("update_virtual_fields_marked_for_write"); + Field **vfield_ptr, *vfield; + int error= 0; + if (!table || !table->vfield) + DBUG_RETURN(0); + + /* Iterate over virtual fields in the table */ + for (vfield_ptr= table->vfield; *vfield_ptr; vfield_ptr++) + { + vfield= (*vfield_ptr); + DBUG_ASSERT(vfield->vcol_info && vfield->vcol_info->expr_item); + /* Only update those fields that are marked in the write_set bitmap */ + if (bitmap_is_set(table->write_set, vfield->field_index) && + (not (ignore_stored && vfield->stored_in_db))) + { + /* Generate the actual value of the virtual fields */ + error= vfield->vcol_info->expr_item->save_in_field(vfield, 0); + DBUG_PRINT("info", ("field '%s' - updated", vfield->field_name)); + } + else + { + DBUG_PRINT("info", ("field '%s' - skipped", vfield->field_name)); + } + } + DBUG_RETURN(0); +} + /***************************************************************************** ** Instansiate templates *****************************************************************************/ === modified file 'sql/table.h' --- sql/table.h 2009-02-19 09:01:25 +0000 +++ sql/table.h 2009-03-24 10:47:21 +0000 @@ -352,6 +352,8 @@ typedef struct st_table_share ulong version, mysql_version; ulong timestamp_offset; /* Set to offset+1 of record */ ulong reclength; /* Recordlength */ + ulong stored_rec_length; /* Stored record length + (no generated-only virtual fields) */ plugin_ref db_plugin; /* storage engine plugin */ inline handlerton *db_type() const /* table_type for handler */ @@ -370,6 +372,8 @@ typedef struct st_table_share uint key_block_size; /* create key_block_size, if used */ uint null_bytes, last_null_bit_pos; uint fields; /* Number of fields */ + uint stored_fields; /* Number of stored fields + (i.e. without generated-only ones) */ uint rec_buff_length; /* Size of table->record[] buffer */ uint keys, key_parts; uint max_key_length, max_unique_length, total_key_length; @@ -391,6 +395,7 @@ typedef struct st_table_share uint error, open_errno, errarg; /* error from open_table_def() */ uint column_bitmap_size; uchar frm_version; + uint vfields; /* Number of virtual fields */ bool null_field_first; bool system; /* Set if system table (one record) */ bool crypted; /* If .frm file is crypted */ @@ -655,6 +660,7 @@ struct st_table { Field *next_number_field; /* Set if next_number is activated */ Field *found_next_number_field; /* Set on open */ Field_timestamp *timestamp_field; + Field **vfield; /* Pointer to virtual fields*/ /* Table's triggers, 0 if there are no of them */ Table_triggers_list *triggers; @@ -811,6 +817,7 @@ struct st_table { void mark_columns_needed_for_update(void); void mark_columns_needed_for_delete(void); void mark_columns_needed_for_insert(void); + void mark_virtual_columns(void); inline void column_bitmaps_set(MY_BITMAP *read_set_arg, MY_BITMAP *write_set_arg) { === modified file 'sql/unireg.cc' --- sql/unireg.cc 2009-02-19 09:01:25 +0000 +++ sql/unireg.cc 2009-03-24 10:47:21 +0000 @@ -584,7 +584,7 @@ static bool pack_header(uchar *forminfo, { uint length,int_count,int_length,no_empty, int_parts; uint time_stamp_pos,null_fields; - ulong reclength, totlength, n_length, com_length; + ulong reclength, totlength, n_length, com_length, vcol_info_length; DBUG_ENTER("pack_header"); if (create_fields.elements > MAX_FIELDS) @@ -595,8 +595,8 @@ static bool pack_header(uchar *forminfo, totlength= 0L; reclength= data_offset; - no_empty=int_count=int_parts=int_length=time_stamp_pos=null_fields= - com_length=0; + no_empty=int_count=int_parts=int_length=time_stamp_pos=null_fields=0; + com_length=vcol_info_length=0; n_length=2L; /* Check fields */ @@ -625,6 +625,27 @@ static bool pack_header(uchar *forminfo, ER_UNKNOWN_ERROR, ER(ER_UNKNOWN_ERROR), buff); field->comment.length= tmp_len; } + if (field->vcol_info) + { + tmp_len= system_charset_info->cset->charpos(system_charset_info, + field->vcol_info->expr_str.str, + field->vcol_info->expr_str.str + + field->vcol_info->expr_str.length, + VIRTUAL_COLUMN_EXPRESSION_MAXLEN); + + if (tmp_len < field->vcol_info->expr_str.length) + { + my_error(ER_WRONG_STRING_LENGTH, MYF(0), + field->vcol_info->expr_str.str,"VIRTUAL COLUMN EXPRESSION", + (uint) VIRTUAL_COLUMN_EXPRESSION_MAXLEN); + DBUG_RETURN(1); + } + /* + Sum up the length of the expression string and mandatory header bytes + to the total length. + */ + vcol_info_length+= field->vcol_info->expr_str.length+(uint)FRM_VCOL_HEADER_SIZE; + } totlength+= field->length; com_length+= field->comment.length; @@ -644,8 +665,6 @@ static bool pack_header(uchar *forminfo, !time_stamp_pos) time_stamp_pos= (uint) field->offset+ (uint) data_offset + 1; length=field->pack_length; - /* Ensure we don't have any bugs when generating offsets */ - DBUG_ASSERT(reclength == field->offset + data_offset); if ((uint) field->offset+ (uint) data_offset+ length > reclength) reclength=(uint) (field->offset+ data_offset + length); n_length+= (ulong) strlen(field->field_name)+1; @@ -712,7 +731,8 @@ static bool pack_header(uchar *forminfo, /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record_length(table_options),reclength); if (info_length+(ulong) create_fields.elements*FCOMP+288+ - n_length+int_length+com_length > 65535L || int_count > 255) + n_length+int_length+com_length+vcol_info_length > 65535L || + int_count > 255) { my_message(ER_TOO_MANY_FIELDS, ER(ER_TOO_MANY_FIELDS), MYF(0)); DBUG_RETURN(1); @@ -720,7 +740,7 @@ static bool pack_header(uchar *forminfo, bzero((char*)forminfo,288); length=(info_length+create_fields.elements*FCOMP+288+n_length+int_length+ - com_length); + com_length+vcol_info_length); int2store(forminfo,length); forminfo[256] = (uint8) screens; int2store(forminfo+258,create_fields.elements); @@ -737,7 +757,8 @@ static bool pack_header(uchar *forminfo, int2store(forminfo+280,22); /* Rows needed */ int2store(forminfo+282,null_fields); int2store(forminfo+284,com_length); - /* Up to forminfo+288 is free to use for additional information */ + int2store(forminfo+286,vcol_info_length); + /* forminfo+288 is free to use for additional information */ DBUG_RETURN(0); } /* pack_header */ @@ -776,7 +797,7 @@ static bool pack_fields(File file, List< ulong data_offset) { reg2 uint i; - uint int_count, comment_length=0; + uint int_count, comment_length, vcol_info_length=0; uchar buff[MAX_FIELD_WIDTH]; Create_field *field; DBUG_ENTER("pack_fields"); @@ -789,6 +810,7 @@ static bool pack_fields(File file, List< while ((field=it++)) { uint recpos; + uint cur_vcol_expr_len= 0; buff[0]= (uchar) field->row; buff[1]= (uchar) field->col; buff[2]= (uchar) field->sc_length; @@ -811,6 +833,17 @@ static bool pack_fields(File file, List< buff[14]= (uchar) field->charset->number; else buff[14]= 0; // Numerical + if (field->vcol_info) + { + /* + Use the interval_id place in the .frm file to store the length of + virtual field's data. + */ + buff[12]= cur_vcol_expr_len= field->vcol_info->expr_str.length + + (uint)FRM_VCOL_HEADER_SIZE; + vcol_info_length+= cur_vcol_expr_len+(uint)FRM_VCOL_HEADER_SIZE; + buff[13]= (uchar) MYSQL_TYPE_VIRTUAL; + } int2store(buff+15, field->comment.length); comment_length+= field->comment.length; set_if_bigger(int_count,field->interval_id); @@ -905,6 +938,35 @@ static bool pack_fields(File file, List< DBUG_RETURN(1); } } + if (vcol_info_length) + { + it.rewind(); + int_count=0; + while ((field=it++)) + { + /* + Pack each virtual field as follows: + byte 1 = 1 (always 1 to allow for future extensions) + byte 2 = sql_type + byte 3 = flags (as of now, 0 - no flags, 1 - field is physically stored) + byte 4-... = virtual column expression (text data) + */ + if (field->vcol_info && field->vcol_info->expr_str.length) + { + Item *item= field->vcol_info->expr_item; + buff[0]= (uchar)1; + buff[1]= (uchar) field->sql_type; + buff[2]= (uchar) field->stored_in_db; + if (my_write(file, buff, 3, MYF_RW)) + DBUG_RETURN(1); + if (my_write(file, + (uchar*) field->vcol_info->expr_str.str, + field->vcol_info->expr_str.length, + MYF_RW)) + DBUG_RETURN(1); + } + } + } DBUG_RETURN(0); } === modified file 'storage/innobase/handler/ha_innodb.cc' --- storage/innobase/handler/ha_innodb.cc 2009-02-19 09:01:25 +0000 +++ storage/innobase/handler/ha_innodb.cc 2009-03-24 10:47:21 +0000 @@ -2437,12 +2437,12 @@ ha_innobase::open( } /* Create buffers for packing the fields of a record. Why - table->reclength did not work here? Obviously, because char + table->stored_rec_length did not work here? Obviously, because char fields when packed actually became 1 byte longer, when we also stored the string length as the first byte. */ upd_and_key_val_buff_len = - table->s->reclength + table->s->max_key_length + table->s->stored_rec_length + table->s->max_key_length + MAX_REF_PARTS * 3; if (!(uchar*) my_multi_malloc(MYF(MY_WME), &upd_buff, upd_and_key_val_buff_len, @@ -2517,7 +2517,7 @@ retry: prebuilt = row_create_prebuilt(ib_table); - prebuilt->mysql_row_len = table->s->reclength; + prebuilt->mysql_row_len = table->s->stored_rec_length;; prebuilt->default_rec = table->s->default_values; ut_ad(prebuilt->default_rec); @@ -3220,11 +3220,11 @@ build_template( dict_index_t* clust_index; mysql_row_templ_t* templ; Field* field; - ulint n_fields; + ulint n_fields, n_stored_fields; ulint n_requested_fields = 0; ibool fetch_all_in_key = FALSE; ibool fetch_primary_key_cols = FALSE; - ulint i; + ulint i, sql_idx, innodb_idx=0; /* byte offset of the end of last requested column */ ulint mysql_prefix_len = 0; @@ -3285,11 +3285,12 @@ build_template( } n_fields = (ulint)table->s->fields; /* number of columns */ + n_stored_fields= (ulint)table->s->stored_fields; /* number of stored columns */ if (!prebuilt->mysql_template) { prebuilt->mysql_template = (mysql_row_templ_t*) mem_alloc_noninline( - n_fields * sizeof(mysql_row_templ_t)); + n_stored_fields * sizeof(mysql_row_templ_t)); } prebuilt->template_type = templ_type; @@ -3299,15 +3300,17 @@ build_template( /* Note that in InnoDB, i is the column number. MySQL calls columns 'fields'. */ - for (i = 0; i < n_fields; i++) { + for (sql_idx = 0; sql_idx < n_fields; sql_idx++) { templ = prebuilt->mysql_template + n_requested_fields; - field = table->field[i]; + field = table->field[sql_idx]; + if (!field->stored_in_db) + goto skip_field; if (UNIV_LIKELY(templ_type == ROW_MYSQL_REC_FIELDS)) { /* Decide which columns we should fetch and which we can skip. */ register const ibool index_contains_field = - dict_index_contains_col_or_prefix(index, i); + dict_index_contains_col_or_prefix(index, innodb_idx); if (!index_contains_field && prebuilt->read_just_key) { /* If this is a 'key read', we do not need @@ -3322,8 +3325,8 @@ build_template( goto include_field; } - if (bitmap_is_set(table->read_set, i) || - bitmap_is_set(table->write_set, i)) { + if (bitmap_is_set(table->read_set, sql_idx) || + bitmap_is_set(table->write_set, sql_idx)) { /* This field is needed in the query */ goto include_field; @@ -3331,7 +3334,7 @@ build_template( if (fetch_primary_key_cols && dict_table_col_in_clustered_key( - index->table, i)) { + index->table, innodb_idx)) { /* This field is needed in the query */ goto include_field; @@ -3344,14 +3347,14 @@ build_template( include_field: n_requested_fields++; - templ->col_no = i; + templ->col_no = innodb_idx; if (index == clust_index) { templ->rec_field_no = dict_col_get_clust_pos_noninline( - &index->table->cols[i], index); + &index->table->cols[innodb_idx], index); } else { templ->rec_field_no = dict_index_get_nth_col_pos( - index, i); + index, innodb_idx); } if (templ->rec_field_no == ULINT_UNDEFINED) { @@ -3377,7 +3380,7 @@ include_field: mysql_prefix_len = templ->mysql_col_offset + templ->mysql_col_len; } - templ->type = index->table->cols[i].mtype; + templ->type = index->table->cols[innodb_idx].mtype; templ->mysql_type = (ulint)field->type(); if (templ->mysql_type == DATA_MYSQL_TRUE_VARCHAR) { @@ -3386,16 +3389,18 @@ include_field: } templ->charset = dtype_get_charset_coll_noninline( - index->table->cols[i].prtype); - templ->mbminlen = index->table->cols[i].mbminlen; - templ->mbmaxlen = index->table->cols[i].mbmaxlen; - templ->is_unsigned = index->table->cols[i].prtype + index->table->cols[innodb_idx].prtype); + templ->mbminlen = index->table->cols[innodb_idx].mbminlen; + templ->mbmaxlen = index->table->cols[innodb_idx].mbmaxlen; + templ->is_unsigned = index->table->cols[innodb_idx].prtype & DATA_UNSIGNED; if (templ->type == DATA_BLOB) { prebuilt->templ_contains_blob = TRUE; } skip_field: - ; + if (field->stored_in_db) { + innodb_idx++; + } } prebuilt->n_template = n_requested_fields; @@ -3848,7 +3853,7 @@ calc_row_difference( ulint n_changed = 0; dfield_t dfield; dict_index_t* clust_index; - uint i; + uint sql_idx, innodb_idx= 0; n_fields = table->s->fields; clust_index = dict_table_get_first_index_noninline(prebuilt->table); @@ -3856,8 +3861,10 @@ calc_row_difference( /* We use upd_buff to convert changed fields */ buf = (byte*) upd_buff; - for (i = 0; i < n_fields; i++) { - field = table->field[i]; + for (sql_idx = 0; sql_idx < n_fields; sql_idx++) { + field = table->field[sql_idx]; + if (!field->stored_in_db) + continue; o_ptr = (byte*) old_row + get_field_offset(table, field); n_ptr = (byte*) new_row + get_field_offset(table, field); @@ -3875,7 +3882,7 @@ calc_row_difference( field_mysql_type = field->type(); - col_type = prebuilt->table->cols[i].mtype; + col_type = prebuilt->table->cols[innodb_idx].mtype; switch (col_type) { @@ -3930,7 +3937,7 @@ calc_row_difference( /* Let us use a dummy dfield to make the conversion from the MySQL column format to the InnoDB format */ - dict_col_copy_type_noninline(prebuilt->table->cols + i, + dict_col_copy_type_noninline(prebuilt->table->cols + innodb_idx, &dfield.type); if (n_len != UNIV_SQL_NULL) { @@ -3951,9 +3958,11 @@ calc_row_difference( ufield->exp = NULL; ufield->field_no = dict_col_get_clust_pos_noninline( - &prebuilt->table->cols[i], clust_index); + &prebuilt->table->cols[innodb_idx], clust_index); n_changed++; } + if (field->stored_in_db) + innodb_idx++; } uvect->n_fields = n_changed; @@ -4939,7 +4948,7 @@ create_table_def( /* We pass 0 as the space id, and determine at a lower level the space id where to store the table */ - table = dict_mem_table_create(table_name, 0, n_cols, flags); + table = dict_mem_table_create(table_name, 0, form->s->stored_fields, flags); if (path_of_temp_table) { table->dir_path_of_temp_table = @@ -4948,6 +4957,8 @@ create_table_def( for (i = 0; i < n_cols; i++) { field = form->field[i]; + if (!field->stored_in_db) + continue; col_type = get_innobase_type_from_mysql_type(&unsigned_type, field); @@ -5236,7 +5247,7 @@ ha_innobase::create( } #endif - if (form->s->fields > 1000) { + if (form->s->stored_fields > 1000) { /* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020, but we play safe here */ @@ -5746,10 +5757,10 @@ ha_innobase::records_in_range( KEY* key; dict_index_t* index; uchar* key_val_buff2 = (uchar*) my_malloc( - table->s->reclength + table->s->stored_rec_length + table->s->max_key_length + 100, MYF(MY_FAE)); - ulint buff2_len = table->s->reclength + ulint buff2_len = table->s->stored_rec_length + table->s->max_key_length + 100; dtuple_t* range_start; dtuple_t* range_end; === modified file 'storage/innobase/handler/ha_innodb.h' --- storage/innobase/handler/ha_innodb.h 2008-12-14 20:59:50 +0000 +++ storage/innobase/handler/ha_innodb.h 2009-03-24 10:47:21 +0000 @@ -199,6 +199,7 @@ class ha_innobase: public handler int cmp_ref(const uchar *ref1, const uchar *ref2); bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); + bool check_if_supported_virtual_columns(void) { return TRUE;} }; /* Some accessor functions which the InnoDB plugin needs, but which === modified file 'storage/myisam/ha_myisam.cc' --- storage/myisam/ha_myisam.cc 2008-10-10 15:28:41 +0000 +++ storage/myisam/ha_myisam.cc 2009-03-24 10:47:21 +0000 @@ -230,7 +230,7 @@ int table2myisam(TABLE *table_arg, MI_KE record= table_arg->record[0]; recpos= 0; recinfo_pos= recinfo; - while (recpos < (uint) share->reclength) + while (recpos < (uint) share->stored_rec_length) { Field **field, *found= 0; minpos= share->reclength; === modified file 'storage/myisam/ha_myisam.h' --- storage/myisam/ha_myisam.h 2008-06-28 12:45:15 +0000 +++ storage/myisam/ha_myisam.h 2009-03-24 10:47:22 +0000 @@ -132,6 +132,7 @@ class ha_myisam: public handler int assign_to_keycache(THD* thd, HA_CHECK_OPT* check_opt); int preload_keys(THD* thd, HA_CHECK_OPT* check_opt); bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); + bool check_if_supported_virtual_columns(void) { return TRUE;} #ifdef HAVE_REPLICATION int dump(THD* thd, int fd); int net_read_dump(NET* net);