Subject Re: [firebird-support] Re: Alternative for UNIQUE constraint
Author Ann W. Harrison
> A CHAR(N) and a VARCHAR(N) containing the "same" characters are not equivalent.

That's true.
> To give a simple example, suppose you have column A VARCHAR(10)
> column B CHAR(10). Suppose you place the string 'magic' in both
> columns, viz.
> update aTable
> set A = 'magic', B = 'magic'

The two field contents are different. Field A contains
"magic ", while field B contains an invisible two
byte integer with the value 5 followed by "magic "
when both are fully expanded as they are in memory.

When stored on disk, field A contains a binary 5 followed
by "magic" followed by a binary -5, followed by " ". Field
B contains a binary 7 followed by a two byte binary 5 and
"magic", followed by binary -5 and " ". If you ask Firebird
to return A and B as null terminated strings, A is returned
as "magic " and field B is returned as "magic".


> then you wanted
> select .... from ...
> where atable.A = atable.B
> then that row you updated back there would not be returned.

Yes it will, because the rules strings, both CHAR and VARCHAR
say that when comparing for equality, the shorter field (B in
this case) is extended with spaces to the length of the longer
field (A in this case) - and "magic " is the same as
"magic ".

However, if you ask for records WHERE atable.a LIKE atable.b,
you won't get the row because spaces are significant in a
LIKE comparison. In this case

atable.a LIKE (atable.b || "%") is true
atable.b LIKE (atable.a || "%") is false
atable.b LIKE (RTRIM (atalbe.a)) is true

Good luck,