Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problematic key value (when attempt to insert duplicate in PK/UK) is not shown where length of key >= 127 characters [CORE4786] #5085

Closed
firebird-automations opened this issue May 8, 2015 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

SQL> insert into test(s) values( rpad('', 126, 'qwertyuioplkjhgfdsazxcvbnm') );
SQL> insert into test(s) values( rpad('', 126, 'qwertyuioplkjhgfdsazxcvbnm') );
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TEST_PK" on table "TEST"
-Problematic key value is ("S" = 'qwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcv
bnmqwertyuioplkjhgfdsazxc')
SQL>

SQL> recreate table test(s varchar(127), constraint test_pk primary key(s) ); commit;
SQL> insert into test(s) values( rpad('', 127, 'qwertyuioplkjhgfdsazxcvbnm') );
SQL> insert into test(s) values( rpad('', 127, 'qwertyuioplkjhgfdsazxcvbnm') );
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TEST_PK" on table "TEST"
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 126, actual 127

Commits: b4a6d47 3887b09 ea57454 5d5c473 FirebirdSQL/fbt-repository@ee8be5c FirebirdSQL/fbt-repository@103f0ee FirebirdSQL/fbt-repository@340b236 FirebirdSQL/fbt-repository@ee139ef

====== Test Details ======

WI-T3.0.0.31844: output if key that violates PK/UK ignores fact that key can be in multi-byte encoding. That leads to non-readable sequence of characters in STDERR.
LI-T3.0.0.31846: all starts work fine, including for unicode text with two- and three- bytes per character.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Fixed for v3, please test. Note that the textual key representation has a limit of 250 characters, so longer values will be truncated.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

SQL> recreate table test(s varchar(300) unique using index test_unq); commit;
SQL> insert into test values( rpad('', 245, 'qwertyuioplkjhgfdsazxcvbnm') || '12345' );
SQL> insert into test values( rpad('', 245, 'qwertyuioplkjhgfdsazxcvbnm') || '12345' );
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_1" on table "TEST"
-Problematic key value is ("S" = 'qwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcv
bnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqwertyuioplkjhgfdsazxcvbnmqw
ertyuiopl1234...)

Yes, it works better now, but I can see 249 (not 250) characters :-) // revision 61512.

IMO, this limit should be documented ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0 Beta 2 [ 10586 ]

Version: 3.0 Beta 2 [ 10586 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.5.5 [ 10670 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

Regressed in Version/s: 3.0 Beta 2, 2.5.5 [ 10586, 10670 ]

QA Status: Done successfully

Test Details: TODO: check what max. length will be for non-ascii characters (both narrow charsets like win1252 and UTF8).

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully => Done with caveats

Test Details: TODO: check what max. length will be for non-ascii characters (both narrow charsets like win1252 and UTF8). => Currently (WI-T3.0.0.31844) output if key that violates PK/UK ignores fact that key can be in multi-byte encoding:
That leads to non-readable sequence of characters in STDERR.
Sent letter with test case to dimitr, 27 мая в 21:12.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Consider following script encoded in UTF8:

set names utf8;
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\c4786.fdb';

recreate table test_utf8(s varchar(169) character set utf8 constraint test_cs_utf8_unq unique using index test_cs_utf8_unq);
commit;

insert into test_utf8 values( rpad('', 164, 'A') || '12345' );
insert into test_utf8 values( rpad('', 164, 'A') || '12345' );
insert into test_utf8 values( rpad('', 164, 'Á') || '12345' ); -- multi-byte character "a-acute", U+00C1
insert into test_utf8 values( rpad('', 164, 'Á') || '12345' );

It will produce STDERR:

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TEST_CS_UTF8_UNQ" on table "TEST_UTF8"
-Problematic key value is ("S" = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA12345')

Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "TEST_CS_UTF8_UNQ" on table "TEST_UTF8"
-Problematic key value is ("S" = 'ÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁÁî..)

Note: after last 'Á' in the second message we see NOT 'dot' character but "î".
This is because output ignores multi-byte nature of key that violates PK/UK.

If we open this STDERR in some editor that doesn`t support UTF8, we'll see there:

"S" = 'Г_Г_Г_Г_ <skipped> Г_Г...)

-- and no "_" after final "Г" that should be there.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Reopened [ 4 ] => Reopened [ 4 ]

Test Details: Currently (WI-T3.0.0.31844) output if key that violates PK/UK ignores fact that key can be in multi-byte encoding:
That leads to non-readable sequence of characters in STDERR.
Sent letter with test case to dimitr, 27 мая в 21:12.

=>

Currently (WI-T3.0.0.31844) output if key that violates PK/UK ignores fact that key can be in multi-byte encoding:
That leads to non-readable sequence of characters in STDERR.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Fix is committed, please test. Beware that the UTF8 value will be printed shorter, as the limit is actually 250 bytes, not characters.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Tested on multi-byte text keys with two- and three- bytes for character representation. Result: OK, no 'first-only-byte' effect as before.
Unicode symbols with four bytes per character (e.g. georgian alphabet or musical notes) can`t be stored correctly on my machine: they are displayed as 'small squares' instead.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done with caveats => Done successfully

Test Details: Currently (WI-T3.0.0.31844) output if key that violates PK/UK ignores fact that key can be in multi-byte encoding:
That leads to non-readable sequence of characters in STDERR.

=>

WI-T3.0.0.31844: output if key that violates PK/UK ignores fact that key can be in multi-byte encoding. That leads to non-readable sequence of characters in STDERR.
LI-T3.0.0.31846: all starts work fine, including for unicode text with two- and three- bytes per character.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment