| Subject | Re: [firebird-support] The predicate where table1.timestamp1 > coalesce(table2.timestamp1, table2.timestamp2, '1.1.2006') does not work since Firebird 2.1 |
|---|---|
| Author | Helen Borrie |
| Post date | 2009-01-25T11:59:53Z |
At 08:25 PM 25/01/2009, you wrote:
Incidentally, I was a little concerned about the logic of your test:
where h.log_tsc >
coalesce(z.upd_tsc, z.create_tsc, timestamp '1.1.2006')
Do you realise that for cases where the comparison is performed on the literal date, the output will include any records that are EQUAL to '1.1.2006', since the time will be midnight on that date, i.e., you have a 23hrs 59 minutes 59 seconds interval on 1.1.2006 where all timestamps < midnight on 2.1.2006 will be matched.
./heLen
>Thank your all for your replies,That exception occurs for many kinds of mismatch in data types. We should stop trying to guess - a test case in Tracker would be interesting. Make sure you provide the proper information about the subrelease of 2.1 you are using. V.2.1.1 has been the current release version now for 6 months and 2.1.2 has been in the field for testing since last week. You can find links for the bugfix lists for both 2.1.1 and 2.1.2 on their respective download pages and the bugfix doc is in the /doc directory of each installation.
>
>But the exception exists and I suppose that it broke many parts of my
>applications after the migration from 2.0 to 2.1.
>Please let me know if the automatic type conversions will not take place anymore in such queries. Or it could be fixed in some further Firebird builds?Not a change I have heard of. One change occurred between 2.0 and 2.1, where implicit conversion between a number type and a character type is no longer performed for indexed searches. If you have a data type mismatch between any of the search keys, it might be a clue to the source of your problem.
>By the way, I was not able to migrate any of my databases from 2.0 toThe same problem that applies to unconverted metadata blobs also applies to data blobs that migrated to 2.1 still encoded in UNICODE_FSS.
>2.1. As I had read in "Release Notes" before the migration, the metadata
>upgrade operations should be performed after the ODS upgrade to 2.1. I
>know only one way to upgrade the ODS version - backup/restore. But each
>backup/restore cycle of any of my databases failed, because of error
>during the restore operation. The error messages were about text blob
>conversions from UNICODE_FSS to UTF8. It were data fields, not metadata.
>Now the problem is in the past, because I have done the following:It sounds like an effective solution. ;-)
>
>1) New databases were created by scripts. The databases were populated
>with bare tables, without indices and constraints.
>2) I wrote a console utility for copying database table by table. (I had
>tried fbcopy 1.52 and some other utilities before, but all of them had
>failed with the same problem of text blobs character transcoding)
>3) All the data were copied using the utility.
>4) The rest database objects were created by scripts.
Incidentally, I was a little concerned about the logic of your test:
where h.log_tsc >
coalesce(z.upd_tsc, z.create_tsc, timestamp '1.1.2006')
Do you realise that for cases where the comparison is performed on the literal date, the output will include any records that are EQUAL to '1.1.2006', since the time will be midnight on that date, i.e., you have a 23hrs 59 minutes 59 seconds interval on 1.1.2006 where all timestamps < midnight on 2.1.2006 will be matched.
./heLen