Collation for a table column

[Dimitry Sibiryakov]

Hello All,

Current Firebird grammar allow collation only to be at the end of column
definition. It comply to standard grammar at first glance:

<column definition> ::=
<column name> [ <data type> | <domain name> ] [ <reference scope check> ]
[ <default clause> | <identity column specification> | <generation
clause> ]
[ <column constraint definition> ... ] [ <collate clause> ]

But at the same time the standard seems to allows collation to be a part of
data type:

<data type> ::=
<predefined type>
| <row type>
| <path-resolved user-defined type name>
| <reference type>
| <collection type>

<predefined type> ::=
<character string type> [ CHARACTER SET <character set specification>
] [ <collate clause> ]
| <national character string type> [ <collate clause> ]
| <binary large object string type>
| <numeric type>
| <boolean type>
| <datetime type>
| <interval type>

Do I read it wrongly?
What standard says if collation is mentioned twice for the same column?

--
WBR, SD.

[Mark Rotteveel]

That grammar is slightly out of date, which SQL standard did you consult?

In SQL:2023, the grammar is:

"""
<column definition> ::=
<column name> [ <data type or domain name> ]

[ <default clause> | <identity column specification> |
<generation clause>

| <system time period start column specification>
| <system time period end column specification> ]

[ <column constraint definition>... ]
[ <collate clause>

<data type or domain name> ::=
<data type>
| <domain name>
"""

and:

"""
<data type> ::=
<predefined type>
| <row type>
| <path-resolved user-defined type name>
| <reference type>
| <collection type>

<predefined type> ::=
<character string type> [ CHARACTER SET <character set specification> ]
[ <collate clause> ]
| <national character string type> [ <collate clause> ]

| <binary string type>

| <numeric type>
| <boolean type>
| <datetime type>
| <interval type>

| <JSON type>
"""

In any case, relevant rules from "11.4 <column definition>" are:

"""
12) Case:
a) If <column definition> immediately contains <domain name>, then it
shall not also immediately contain <collate clause>.
b) Otherwise, <collate clause> shall not be both specified in <data
type> and immediately contained in <column definition>. If <collate
clause> is immediately contained in <column definition>,then it is
equivalent to specifying an equivalent <collate clause> in <data type>.
"""

In other words, the standard does not allow the collate clause for
domains (we do), and for (pre-defined) data types, you are allowed to
specify the collate clause as part of the data type, or at the end of
the column definition, but not both.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 09.09.2023 10:20:
> That grammar is slightly out of date, which SQL standard did you consult?

The only grammar I found in the Net is a draft of SQL:2003.

--
WBR, SD.

[Mark Rotteveel]

I would highly recommend buying a copy of the standard (or at least part
2, that is ISO/IEC 9075-2:2023) from your countries ISO representative,
instead of working from incomplete information.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 09.09.2023 11:31:
>>    The only grammar I found in the Net is a draft of SQL:2003.
>
> I would highly recommend buying a copy of the standard (or at least part 2, that
> is ISO/IEC 9075-2:2023) from your countries ISO representative, instead of
> working from incomplete information.

I don't care about standards much enough for that. IMHO if ISO wants
developers to follow their rules - they should pay for it not vice versa.

--
WBR, SD.