Character sets and collations should always search in SYSTEM first, even if it's not on the search path

[mrotteveel]

Currently this fails:

SQL> set search_path to public;
SQL> select cast('abc' as varchar(50) character set utf8) from system.rdb$database;
Statement failed, SQLSTATE = HY004
Dynamic SQL Error
-SQL error code = -204
-Data type unknown
-CHARACTER SET "PUBLIC"."UTF8" is not defined

And this really should never happen, no matter what the search path is.

Following the rules of the SQL standard of section 4.3.1 and section 5.4 syntax rules 12 and 13a, unqualified character sets and collations should always lookup the system character sets and collations (which in the standard live in INFORMATION_SCHEMA, and for us in SYSTEM). We can relax this a bit to also search the search path, but using the character sets/collations from SYSTEM first should be implemented.

[asfernandes]

As designed. We don't blindly follow the SQL standard by some time.

Do you remember number of bugs about CHAR concatenation?

[mrotteveel]

I strongly disagree with you summarily closing this and I have reopened it.

[mrotteveel]

And you say "as designed", because you decided so, simply ignoring my earlier arguments to the contrary.

[dyemanov]

Adriano, what are the arguments to go against the standard in this case?

[asfernandes]

Adriano, what are the arguments to go against the standard in this case?

The arguments was already placed in this topic in firebird-devel. I'll not re-read or re-write them.

But I can think of arguments right now.

The standard do not think on previous versions of a product so they invented a branch rule for a thing with would be a nightmare using the "standard" rules for others objects.

Schemas are there so users can override objects. If Mark do not want one to have a great RDB$DATABASE than his one, which multiple rows, for example, that's his problem. Firebird will still act correct. System queries will use the SYSTEM.RDB$DATABASE. Queries using the ANOTHER_SCHEMA.RDB$DATABASE will raise multiple rows error correctly.

The same for charsts and collations, specially collations as we don't allow creation of custom charsets.

If one wan't to override a system name, it's his problem. We should not treat users as babys.

The weird standard rules is just it, weird. And with our compatibility rules it covers things very well, but looks like Mark insist to treat users as babys.

[mrotteveel]

This ticket is only about character sets and collations; do not drag my suggestion about RDB$DATABASE into it to cloud the issue.

The rules the standard defines for character sets and collations are IMHO sane and logical, and prevent unwanted behaviour (not being able to reference a system character set unqualified if SYSTEM is not on the search path) or confusing behaviour (selecting a user-defined collation or character set instead of a system character set or collation if they have the same name, with behaviour varying with the search path). Not searching SYSTEM first for character sets or collations is going to cause a lot of pain for us and our users.

This is not about babying users, it is about rational and understandable behaviour that conforms to what people will actually expect to work, and preventing a nightmare to support, and giving users yet another reason to not migrate to a newer version.

And otherwise, we should make character sets and collations objects that do not live inside schemas.

The same for charsts and collations, specially collations as we don't allow creation of custom charsets.

Maybe not since Firebird 3.0, but may I point you to the misc/intl.sql (still included in the distributions to this day) which worked at least up to Firebird 2.5?

[mrotteveel]

The arguments was already placed in this topic in firebird-devel. I'll not re-read or re-write them.

The only argument from you that I can distill in that thread was that you didn't like weird rules from the standard introducing exceptions to the normal object lookup rules, and IMO that is not much of an argument at all.

[mrotteveel]

Not searching SYSTEM first for character sets or collations is going to cause a lot of pain for us and our users.

And note that this is already a compromise to the rule specified in the standard to avoid too much backwards incompatible behaviour for custom collations (because the standard requires user-defined character sets and collations to (nearly) always be qualified with the schema).