|
Hi all, In /randgen/lib/GenTest/App/Gendata.pm:340 this block exists: if ($field_copy[FIELD_TYPE] =~ m{timestamp}sio ) { if (defined $self->[GD_NOTNULL]) { $field->[FIELD_SQL] .= ' NOT NULL'; } else { $field->[FIELD_SQL] .= ' NULL DEFAULT 0'; } } This results in the following behavior: #1 When a timestamp field is created, and RQG is running with --notnull, the resulting timestamp column will be created like this: `col_timestamp_not_null` timestamp not null NOT NULL, (the twice not null is likely since --notnull enables adding 'not null' and the second timestamp check adds another 'NOT NULL') #2 When a timestamp field is created, and RQG is running without --notnull, the resulting timestamp column will be created like this: `col_timestamp` timestamp NULL DEFAULT 0, Conclusions: #A The addition of 'NOT NULL' for timestamp columns when using --notnull seems excessive/not needed #B The addition of 'NULL DEFAULT 0' gives two issues: #B.1 When wanting to use a secondary clause on the timestamp column in the types field, like this (pseudo code): $fields = {types => ['timestamp default NULL'],}; It will result in the DEFAULT value being overwritten for runs without --notnull: `col_timestamp` timestamp default NULL NULL DEFAULT 0, Proof of concept (the last DEFAULT clause always overwrite the previous ones): Two different tables resulting: mysql> create table time4 (id timestamp null default 0 default null); Query OK, 0 rows affected (0.01 sec) mysql> show create table time4\G Create Table: CREATE TABLE `time4` ( `id` timestamp NULL DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> create table time5 (id timestamp null default null default 0); Query OK, 0 rows affected (0.01 sec) mysql> show create table time5\G Create Table: CREATE TABLE `time5` ( `id` timestamp NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #B.2 This is limiting test coverage. As per the example above, a timestamp column can be created as DEFAULT NULL instead of DEFAULT '0000-00-00 00:00:00' (i.e. DEFAULT 0). But, since "DEFAULT 0" is always automatically added when not using --notnull, we would never be able get: - DEFAULT NULL (when using --notnull) - since it would always add "DEFAULT 0" at the end resulting in "DEFAULT '0000-00-00 00:00:00'" - DEFAULT CURRENT_TIMESTAMP [ON UPDATE CURRENT_TIMESTAMP] (auto added to the first timestamp field in any table) (when using -notnull) for the same reason etc. I think I understand however why this block was implemented. It is like because one can also specify a default clause in the $fields block. And, if one specifies a timestamp type, with a wrong default clause - let's take for instance: mysql> create table time4 (id timestamp default null); ERROR 1067 (42000): Invalid default value for 'id' then table creation would fail. So - my question is - how can we change this block to make it more versatile (i.e. allowing one to specify things like CURRENT_TIMESTAMP as a default, and also various default clauses even from the types array) without loosing [much] table creation success? Thanks for any input you can provide. Changing this will improve RQG in terms of timestamp handling - which is presumably something which is often used by customers. --
Kind regards, God Bless,
|
_______________________________________________ Mailing list: https://launchpad.net/~randgen Post to : [email protected] Unsubscribe : https://launchpad.net/~randgen More help : https://help.launchpad.net/ListHelp
