ALTER TABLE change character set or collation

[Dimitry Sibiryakov]

Hello All,

it looks like ALTER TABLE silently ignores new charset and collation when
user attempts to change type of a column like this: "alter table t3 alter a type
varchar(25) character set win1251 collate win1251".

Shouldn't it throw an error instead?
Should change of collation to be allowed? AFAIU indexes are refreshed
afterward so it should be handled right.

--
WBR, SD.

[Mark Rotteveel]

On 11-09-2023 17:55, 'Dimitry Sibiryakov' via firebird-devel wrote:
>   Hello All,
>
>   it looks like ALTER TABLE silently ignores new charset and collation
> when user attempts to change type of a column like this: "alter table t3
> alter a type varchar(25) character set win1251 collate win1251".

That statement results in error:

"""
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 63
-collate
"""

Are you trying this out with a modified parse.7, instead of the normal
syntax?

>   Shouldn't it throw an error instead?
>   Should change of collation to be allowed? AFAIU indexes are refreshed
> afterward so it should be handled right.

It is currently not possible to alter the collation of a column, as
documented in the language reference, see [1].

Mark

[1]:
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref40/firebird-40-language-reference.html#fblangref40-ddl-tbl-altrtblnogo
--
Mark Rotteveel

[Mark Rotteveel]

On 11-09-2023 18:08, 'Mark Rotteveel' via firebird-devel wrote:
> Are you trying this out with a modified parse.7, instead of the normal
> syntax?

I meant parse.y...


--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 11.09.2023 18:08:
>
> Are you trying this out with a modified parse.7, instead of the normal syntax?

Yes. I implemented collation to be a part of data type as per standard but
found the strange behavior of ALTER during testing. Without COLLATE it is the
same: character set is silently preserved while field length is changed.

--
WBR, SD.

[Mark Rotteveel]

Then I'm not clear on what you're asking. The feature you're describing
doesn't exist in Firebird yet, yet you're asking why it doesn't work
like you expect?

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 11.09.2023 18:15:
>>    Yes. I implemented collation to be a part of data type as per standard but
>> found the strange behavior of ALTER during testing. Without COLLATE it is the
>> same: character set is silently preserved while field length is changed.
>
> Then I'm not clear on what you're asking. The feature you're describing doesn't
> exist in Firebird yet, yet you're asking why it doesn't work like you expect?

Reduce my example to "alter table t3 alter a type varchar(25) character set
win1251" and may be it will be a little more clear. In this form the query is
accepted by current Firebird versions.

I asked two questions:

1) Whether the query should throw error [in existing Firebird versions] (and
thus current behavior is a bug).

2) If changing of collation and charset should be allowed in future Firebird
versions as a part of changing of data type.

--
WBR, SD.

[Mark Rotteveel]

On 11-09-2023 18:21, 'Dimitry Sibiryakov' via firebird-devel wrote:
>   Reduce my example to "alter table t3 alter a type varchar(25)
> character set win1251" and may be it will be a little more clear. In
> this form the query is accepted by current Firebird versions.
>
>   I asked two questions:
>
> 1) Whether the query should throw error [in existing Firebird versions]
> (and thus current behavior is a bug).

Given it doesn't actually change the character set (weird, I never
noticed that before), I think it should result in an error.

> 2) If changing of collation and charset should be allowed in future
> Firebird versions as a part of changing of data type.

I guess the problem is that changing the character set requires a full
validation, except for changing from ASCII, or to UTF8. Basically all
other changes could result in transliteration errors for characters not
shared by both character sets.

In other words, changing just the collation is probably simpler than
also changing the character set ;)

Mark
--
Mark Rotteveel