Subject: Re: SQL conformity regarding SQLSTATE
To: pgsql-sql@lists.postgresql.org
References: <c2c1a96d-dae9-6667-bf11-eb423ee90385@purtz.de>
 <31999.1513697414@sss.pgh.pa.us>
From: =?UTF-8?Q?J=c3=bcrgen_Purtz?= <juergen@purtz.de>
Message-ID: <c5fb7c99-0f02-75cf-9b97-95693fc6adc7@purtz.de>
Date: Wed, 20 Dec 2017 11:08:20 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.0
MIME-Version: 1.0
In-Reply-To: <31999.1513697414@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-GB
Precedence: bulk

>
> There are various SQLSTATE codes that we borrowed from DB2 and other
> RDBMSes; to the extent that those violate the spec, we're in good
> company.
In good company or in bad society ???
>> Here is a list of values, which violate the above rule as the values are
>> in the range which is reserved for the standard but (actually) are not
>> defined by the standard. I compared our list in the version 10
>> documentation with the SQL:2011 standard. (Unfortunately I have no
>> access to SQL:2016. Maybe, some values of my list are defined there.)
>> 01008, 03000, 0B000, 23502 - 23514, 39001, 42501 - 42939, F0000, F0001.
> Actually, I'm pretty sure we were looking at SQL99 when we made our
> original list.  I see 01008, 03000, 0B000, and 39001 there; are they
> really not in later specs?
Yes, I double checked it. Maybe they where used in one of the parts 5, 
6, 7,8, or 12. Those parts are no longer part of the SQL standard.

0B000 may be switched into the 23 class? 39001 "invalid SQLSTATE 
returned" sounds dubious for me - it declares itself to be 'invalid'.
> The 23xxx and 42xxx codes are there because SQL99 provides ridiculously
> few subclasses for those classes.  I think many of those might've been
> borrowed from DB2, but in any case they're in the legal range for
> extension subclasses, so I don't follow your complaint.
OK, my error. 23xxx and 42xxx subclasses are in the legal range.
> Class F0 seems like a mistake ... maybe we could get away with changing
> those two assignments, since it seems unlikely that any client code is
> looking for those values.
>
>> b) Shall we add a comment into 'errcodes.txt' to remind everybody to the
>> mentioned rule?
> You mean the one at lines 64ff?
Yes. But as you said: it is still there. No action necessary.
>> c) Is it possible to rearrange the rows of 'errcode.txt' in a way that
>> reflects the natural sort order of SQLSTATE?
> I'd have said it was already.

There is no sorting of subclasses within their class in 'errcode.txt'.

Summary: 01008, 03000, 0B000, 39001, F0000, and F0001 do not conform to 
the standard.


Kind regards
Jürgen Purtz