Hello Ann, On first sight, I'd say your first list of records is a list with the correct values. But, given that the new 'default value' isn't stored, but applied on reading the record, this fails when the default is modified, right?
Would it be correct to say that when adding a new non-null column, (with a default clause, ) it would make sense to choose between applying the new value to NULL valued records? alter table mytable add myfield varchar(20) not null default 'Jim' <some command to apply the value here>; Wouldn't this make absolutely clear the old records have a value instead of applying the default value on reading the record? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL, SQL Anywhere, MySQL, InterBase, NexusDB and Firebird. -----Original Message----- It's been a long time, but I think that's an ancient behavior that Jim and I argued about many years ago. Maybe even in Rdb$ELN, InterBase's ancestor. Unless my memory fails me (again) the internal format rectifier doesn't go through all intermediate formats, it just converts from the stored format to the format requested. I came up with some places where not considering the intermediate formats produced different results, but most were errors. Jim thought it was idiotic to go through a lot of extra work to discover errors that had been corrected. My use case may have involved changing a field from varchar to double and back when the field contains alphabetic characters. The non-errors might be that changing a field from double (format 1) to float (format 2) and back (format 3) had the result that format 1 records had truncated values (at the low end) when seen as format 2, and went back to full precision when viewed as format 3. The case at hand includes a relatively new feature - new fields that are not null and include a default. Create a table with no field called "NewField". (Format 1) Store a record with the primary key of 1. Alter the table adding "NewField", not null, default "Ann". (Format 2) Store a record with the primary key 2 and no value for "New Field" Alter the record again, changing the default to "Jim". (Format 3) Store a record with the primary key 3 and no value for "New Field" Read all the records. 1 Jim 2 Ann 3 Jim The situation is that Firebird converts record 1 from format 1 to format 3 without going through format 2. If it had gone through format 2, the initial default value would be applied and you'd see 1 Ann 2 Ann 3 Jim Should the behavior be changed? It's ancient. It has benefits (e.g. Changing a column from double to float and back). The benefits are in dumb cases. The new behavior might be more standard conformant, if the standard allows addind Not Null columns with defaults and the Standards Committee assumed that default values were added in the most crude way possible. Cheers, Ann Just explained this to Jim who said "That's a dumb case. Who cares?" ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
