Bug #20360 mysqltest: inconsistent case comparison of SQLSTATE error values
Submitted: 9 Jun 2006 14:19 Modified: 7 Nov 2006 18:17
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Tests Severity:S3 (Non-critical)
Version:4.1 and up OS:
Assigned to: Magnus Blåudd CPU Architecture:Any

[9 Jun 2006 14:19] Paul Dubois
Description:
For mysqltest, the error command allows arguments
that are numeric error codes, or SQLSTATE values
preceded by 'S'.  Example:

--error 1051
--error S42S02

But for SQLSTATE values, mysqltest does not behave consistently
with regard to the lettercase of the leading S or letters in the
value.

How to repeat:
% mysqltest test
--error s42s02
mysqltest: At line 1: Invalid argument in error s42s02
not ok

This shows that it is necessary to capitalize the leading 'S'.

% mysqltest test
--error S42s02
drop table t;
mysqltest: At line 2: query 'drop table t' failed with wrong sqlstate 42S02: 'Unknown table 't'', instead of 42s02...
not ok

This shows that it is *not* necessary to capitalize the 'S' that
occurs within the SQLSTATE value, but that if it is not capitalized,
the value is not recognized when that error does in fact occur.

One might think that the problem here is that case-sensitive
comparison is used, but I suspect that the problem really is
that "42s02" should be rejected as an invalid SQLSTATE value.
Apparently SQLSTATE values must consist of digits '0'..'9' and
*uppercase* charcters 'A'..'Z'.

(Note: in MySQL 4.0, mysqltest does not require the leading 'S'
to be capitalized.)

Suggested fix:
Perhaps the fix is to reject invalid SQLSTATE values
such as 42s02 when they are specified in the error
command, and require that they contain only digits
and uppercase characters.
[11 Jun 2006 8:11] Valeriy Kravchuk
Thank you for a problem report. Verified just as described with 4.1-BK and 5.0.23-BK:

openxs@suse:~/dbs/5.0> bin/mysqltest test
--error s42s02
mysqltest: At line 1: Invalid argument in error s42s02
not ok
openxs@suse:~/dbs/5.0> bin/mysqltest test
--error S42s02
drop table t;
mysqltest: At line 2: query 'drop table t' failed with wrong sqlstate 42S02: 'Un
known table 't'', instead of 42s02...
not ok
[12 Oct 2006 17:52] Magnus Blåudd
Pushed to 4.1-maint, 5.0-maint and 5.1-maint
[7 Nov 2006 10:58] Magnus Blåudd
SQLSTATE string
 - Must be SQLSTATE_LENGTH long
 - May contain only digits[0-9] and _uppercase_ letters
[7 Nov 2006 18:17] Paul Dubois
No changelog entry needed, but I have updated the mysqltest
manual with the required syntax for SQLSTATE values.