"ctype_ucs.test" fails sporadically

[Yura Sorokin]

"ctype_ucs.test" fails on 5.5 with the following output
------------
Comment:

Logfile:
CURRENT_TEST: main.ctype_ucs
--- /mnt/workspace/percona-server-5.5-param/BUILD_TYPE/release/Host/centos5-64/mysql-test/r/ctype_ucs.result 2015-12-30 16:14:58.000000000 +0300
+++ /mnt/workspace/percona-server-5.5-param/BUILD_TYPE/release/Host/centos5-64/mysql-test/r/ctype_ucs.reject 2015-12-30 16:36:53.000000000 +0300
@@ -200,11 +200,14 @@
 CREATE TABLE t1(a INT);
 LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2
 (@b) SET a=REVERSE(@b);
+Warnings:
+Warning 1366 Incorrect integer value: '?' for column 'a' at row 1
+Warning 1366 Incorrect integer value: '?' for column 'a' at row 2
 # should return 2 zeroes (as the value is truncated)
 SELECT * FROM t1;
 a
 0
-1
+0
 DROP TABLE t1;
 # Problem # 2 : if you write and read ucs2 data to a file they're lost
 SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2;

mysqltest: Result content mismatch
------------

[Yura Sorokin (yura-sorokin)]

The problem seems to be with "ctype_common.inc" file when included with the following variables set

**************************************
SET @test_character_set= 'ucs2';
SET @test_collation= 'ucs2_general_ci';
-- source include/ctype_common.inc
**************************************

This include, although properly saves and restores all the session variables it uses, has one side effect. By changing the default database via "USE d1"/"USE test" this file implicitly changes "@@character_set_database" session variable.

This variable, in turn, affects how string literals without explicit "_xxx" prefix are interpreted with regards to character set associated with them.

Therefore, "SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt'" from "ctype_ucs.test" will generate different files depending on whether "@@character_set_database" is set to "ucs2" or "latin1".

According to the comment from the same file, the author of the original commit expected the select statement to produce two zeros (which means that he wanted 'tmpp.txt' to be written in 'latin1' encoding)
******************************************************
--echo # should return 2 zeroes (as the value is truncated)
SELECT * FROM t1;
******************************************************

However, in the same commit we have different values recorded in "ctype_ucs.result"
******************************************************
# should return 2 zeroes (as the value is truncated)
SELECT * FROM t1;
a
0
1
******************************************************

[Yura Sorokin (yura-sorokin)]

The root cause of these fails seems to be connected to the upstream bug
http://bugs.mysql.com/bug.php?id=79989

[Yura Sorokin (yura-sorokin)]

In 5.7 (in contrast to 5.5 and 5.6), because of the different default value of the "sql_mode" variable, which now includes "STRICT_TRANS_TABLES", "ER_TRUNCATED_WRONG_VALUE_FOR_FIELD" (1366 Incorrect aaa value: 'bbb' for column 'ccc' at row ddd) is no longer considered a warning but is raised to an error.

[Yura Sorokin (yura-sorokin)]

https://github.com/percona/percona-server/pull/314
https://github.com/percona/percona-server/pull/315
https://github.com/percona/percona-server/pull/316

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-957