revision-id: 861c6c32fe0df073d365f4525f68ca6e57140759 (mariadb-10.2.16-54-g861c6c32fe0) parent(s): f9e4ae816bb1bb84e8f456900cc7378b14981c72 865e807125d1d3fb2d784ea33f1154ba999466ec author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2018-08-01 14:38:07 +0200 message: Merge 10.1 into 10.2 CMakeLists.txt | 6 + cmake/install_macros.cmake | 9 + cmake/mysql_add_executable.cmake | 3 + include/m_ctype.h | 28 ++- include/sql_common.h | 4 +- mysql-test/include/ctype_mdev13118.inc | 15 ++ mysql-test/r/ctype_binary.result | 23 ++ mysql-test/r/ctype_eucjpms.result | 23 ++ mysql-test/r/ctype_euckr.result | 29 +++ mysql-test/r/ctype_gbk.result | 23 ++ mysql-test/r/ctype_latin1.result | 23 ++ mysql-test/r/ctype_ucs.result | 23 ++ mysql-test/r/ctype_ujis.result | 23 ++ mysql-test/r/ctype_utf16.result | 23 ++ mysql-test/r/ctype_utf16le.result | 29 +++ mysql-test/r/ctype_utf32.result | 23 ++ mysql-test/r/ctype_utf8.result | 23 ++ mysql-test/r/ctype_utf8mb4.result | 23 ++ mysql-test/r/grant.result | 4 +- mysql-test/r/join_outer.result | 50 ++++ mysql-test/r/join_outer_jcl6.result | 50 ++++ mysql-test/r/rename.result | 4 + mysql-test/r/sp_notembedded.result | 2 - mysql-test/r/subselect_mat.result | 93 ++++++++ mysql-test/r/subselect_mat_cost_bugs.result | 2 +- mysql-test/r/subselect_sj_mat.result | 93 ++++++++ mysql-test/r/union.result | 16 ++ .../galera/r/galera_sst_mysqldump_with_key.result | 90 ++++++- .../galera/t/galera_sst_mysqldump_with_key.test | 7 +- .../r/default_row_format_compatibility.result | 2 +- mysql-test/suite/innodb/r/innodb-wl5522.result | 264 ++++++++++++++++++++- .../suite/innodb/r/row_format_redundant.result | 2 +- mysql-test/suite/innodb/t/innodb-wl5522.test | 254 +++++++++++++++++++- mysql-test/suite/parts/r/truncate_locked.result | 7 + mysql-test/suite/parts/t/truncate_locked.test | 10 + .../r/sysvars_innodb,32bit,xtradb.rdiff-disabled | 2 +- .../r/sysvars_innodb,xtradb.rdiff-disabled | 6 +- mysql-test/t/bootstrap.test | 9 + mysql-test/t/ctype_binary.test | 3 + mysql-test/t/ctype_eucjpms.test | 2 + mysql-test/t/ctype_euckr.test | 10 + mysql-test/t/ctype_gbk.test | 3 + mysql-test/t/ctype_latin1.test | 3 + mysql-test/t/ctype_ucs.test | 4 + mysql-test/t/ctype_ujis.test | 4 + mysql-test/t/ctype_utf16.test | 5 + mysql-test/t/ctype_utf16le.test | 13 + mysql-test/t/ctype_utf32.test | 8 + mysql-test/t/ctype_utf8.test | 7 + mysql-test/t/ctype_utf8mb4.test | 8 + mysql-test/t/grant.test | 3 + mysql-test/t/join_outer.test | 48 ++++ mysql-test/t/rename.test | 7 + mysql-test/t/subselect_sj_mat.test | 79 ++++++ mysql-test/t/union.test | 15 ++ mysys/my_rename.c | 5 +- scripts/CMakeLists.txt | 2 + scripts/mysql_install_db.pl.in | 2 +- scripts/mysql_install_db.sh | 2 +- scripts/mysql_system_tables_fix.sql | 50 ++-- scripts/mysqld_multi.sh | 2 +- sql-common/client.c | 4 + sql/item.cc | 3 +- sql/item_strfunc.cc | 112 ++++----- sql/opt_subselect.cc | 66 ++++++ sql/share/CMakeLists.txt | 12 +- sql/sql_acl.cc | 13 +- sql/sql_select.cc | 13 +- sql/sql_table.cc | 2 + sql/sql_truncate.cc | 3 +- sql/tztime.cc | 2 +- storage/connect/filamvct.cpp | 5 - storage/innobase/buf/buf0buddy.cc | 2 +- storage/innobase/buf/buf0dump.cc | 2 +- storage/innobase/fts/fts0fts.cc | 57 ++--- storage/innobase/handler/ha_innodb.cc | 37 ++- storage/innobase/handler/i_s.cc | 8 +- storage/innobase/include/dict0stats.h | 2 +- storage/innobase/row/row0import.cc | 88 +++++-- storage/myisam/ha_myisam.cc | 6 +- storage/myisam/mi_check.c | 6 +- storage/myisam/mi_locking.c | 6 +- storage/xtradb/buf/buf0buddy.cc | 2 +- storage/xtradb/buf/buf0dump.cc | 2 +- storage/xtradb/fts/fts0fts.cc | 57 ++--- storage/xtradb/handler/ha_innodb.cc | 37 ++- storage/xtradb/handler/i_s.cc | 19 +- storage/xtradb/row/row0import.cc | 78 ++++-- storage/xtradb/trx/trx0trx.cc | 4 +- storage/xtradb/trx/trx0undo.cc | 3 +- strings/ctype-bin.c | 8 +- strings/ctype-euc_kr.c | 4 +- strings/ctype-mb.c | 100 ++------ strings/ctype-simple.c | 22 +- strings/ctype-ucs2.c | 78 +++--- strings/ctype-ujis.c | 8 +- strings/ctype-utf8.c | 20 +- support-files/CMakeLists.txt | 2 + 98 files changed, 2065 insertions(+), 433 deletions(-) diff --cc CMakeLists.txt index 48f573b772e,0b48ca20485..990cc5f7097 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@@ -175,7 -177,14 +175,13 @@@ IF(UNIX OPTION(WITH_VALGRIND "Valgrind instrumentation" OFF) ENDIF() OPTION (WITH_UNIT_TESTS "Compile MySQL with unit tests" ON) -MARK_AS_ADVANCED(CYBOZU) + IF (WITHOUT_SERVER) + SET (SKIP_COMPONENTS "Server|IniFiles|SuportFiles|Readme") + ELSE() + SET (SKIP_COMPONENTS "N-O-N-E") + ENDIF() + OPTION(NOT_FOR_DISTRIBUTION "Allow linking with GPLv2-incompatible system libraries. Only set it you never plan to distribute the resulting binaries" OFF) INCLUDE(check_compiler_flag) diff --cc mysql-test/r/ctype_binary.result index 976c3df6e0e,627ba8a35a5..228949904ec --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@@ -3048,6 -3047,29 +3048,29 @@@ DROP TABLE t1 SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; _binary 0x7E _binary X'7E' _binary B'01111110' ~ ~ ~ + SET NAMES utf8, character_set_connection=binary; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varbinary(10) NOT NULL ++ `t` varbinary(10) DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + abcdefghi-abcdefghi + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_eucjpms.result index bc7b4b9ef9d,160c4758122..507d0021a1f --- a/mysql-test/r/ctype_eucjpms.result +++ b/mysql-test/r/ctype_eucjpms.result @@@ -33844,6 -33868,29 +33844,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _eucjpms 0x8EA0; ERROR HY000: Invalid eucjpms character string: '8EA0' + SET NAMES eucjpms; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET eucjpms NOT NULL ++ `t` varchar(10) CHARACTER SET eucjpms DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_euckr.result index 3fb6f98b2eb,a23ced76df9..cc50ddd20be --- a/mysql-test/r/ctype_euckr.result +++ b/mysql-test/r/ctype_euckr.result @@@ -25405,566 -25429,31 +25405,595 @@@ A1A1A1A1A1A1202020202020202020202020202 # End of 5.6 tests # # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=euckr; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET euckr NOT NULL ++ `t` varchar(10) CHARACTER SET euckr DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # End of 10.0 tests + # ++# +# Start of 10.2 tests +# +# +# MDEV-9711 NO PAD Collatons +# +SET character_set_connection=euckr; +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_korean_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_korean_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=euckr COLLATE=euckr_korean_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +41000000000000000000 +41202000000000000000 +20410000000000000000 +20412000000000000000 +41202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_ci'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_korean_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_korean_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_korean_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=euckr COLLATE=euckr_korean_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_korean_nopad_ci'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +41000000000000000000 +41202000000000000000 +20410000000000000000 +20412000000000000000 +41202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_korean_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_korean_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_korean_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_korean_ci'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_korean_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_korean_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=euckr COLLATE=euckr_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_nopad_bin'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +61000000000000000000 +61202000000000000000 +20610000000000000000 +20612000000000000000 +61202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'euckr_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE euckr_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=euckr COLLATE=euckr_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); +SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_nopad_bin'; +INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; +HEX(a) a +2061 a +206120 a +61 a +612020 a +61202020 a +SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; +COUNT(DISTINCT a) +5 +SELECT HEX(MAX(a)), MAX(a) FROM t1; +HEX(MAX(a)) MAX(a) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'euckr_nopad_bin'; +INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); +SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; +HEX(a) cnt +6161 1 +61612020 1 +6162 2 +DROP TABLE t2; +# +# Weights +# +SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; +HEX(WEIGHT_STRING(a AS CHAR(10))) +61000000000000000000 +61202000000000000000 +20610000000000000000 +20612000000000000000 +61202020000000000000 +DROP TABLE t1; +# +# IF, CASE, LEAST +# +SELECT IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'euckr_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'euckr_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'euckr_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'euckr_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'euckr_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'euckr_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'euckr_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +# +# End of 10.2 tests +# diff --cc mysql-test/r/ctype_gbk.result index 5244d6eb333,ac8de1e229c..4e5c9695415 --- a/mysql-test/r/ctype_gbk.result +++ b/mysql-test/r/ctype_gbk.result @@@ -5077,6 -5099,29 +5077,29 @@@ E05C5 E05B DROP TABLE t1; # Start of ctype_E05C.inc + SET NAMES utf8, character_set_connection=gbk; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET gbk NOT NULL ++ `t` varchar(10) CHARACTER SET gbk DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # MDEV-9886 Illegal mix of collations with a view comparing a field to a binary constant # diff --cc mysql-test/r/ctype_latin1.result index cbf5f145d22,3b9b2633480..9899342bdcd --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@@ -8022,6 -7989,29 +8022,29 @@@ 0 DROP VIEW v1; DROP TABLE t1; + SET NAMES latin1; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) NOT NULL ++ `t` varchar(10) DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ucs.result index f6d3d86e2cd,9c598780edf..3caac471a7c --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@@ -5685,6 -5705,29 +5685,29 @@@ c YWJjZGVmZ2hp-YWJjZGVmZ2hp DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; + SET NAMES utf8, character_set_connection=ucs2; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ucs2 NOT NULL ++ `t` varchar(10) CHARACTER SET ucs2 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_ujis.result index cb9de83fedc,9146356b5f9..0b1cf606f0b --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@@ -26149,6 -26173,29 +26149,29 @@@ HEX(a) CHAR_LENGTH(a DROP TABLE t1; SELECT _ujis 0x8EA0; ERROR HY000: Invalid ujis character string: '8EA0' + SET NAMES ujis; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET ujis NOT NULL ++ `t` varchar(10) CHARACTER SET ujis DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16.result index 8de21a14bd9,ba72a859351..a105017b726 --- a/mysql-test/r/ctype_utf16.result +++ b/mysql-test/r/ctype_utf16.result @@@ -2142,6 -2138,29 +2142,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMES utf8, character_set_connection=utf16; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16 NOT NULL ++ `t` varchar(10) CHARACTER SET utf16 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf16le.result index 0fedff7d3bf,c7c8c210014..6d8d7ddca2a --- a/mysql-test/r/ctype_utf16le.result +++ b/mysql-test/r/ctype_utf16le.result @@@ -2326,6 -2324,35 +2326,35 @@@ DFFFFFDFFFFF9CFFFF9DFFFF9EFFF # End of 5.6 tests # # + # Start of 10.0 tests + # + SET NAMES utf8, character_set_connection=utf16le; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf16le NOT NULL ++ `t` varchar(10) CHARACTER SET utf16le DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; + # + # Start of 10.0 tests + # + # # Start of 10.1 tests # # diff --cc mysql-test/r/ctype_utf32.result index 59ce4c2f445,024f8aa7875..28b9caf6ffe --- a/mysql-test/r/ctype_utf32.result +++ b/mysql-test/r/ctype_utf32.result @@@ -2241,6 -2237,29 +2241,29 @@@ EXECUTE stmt USING @arg00 CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; + SET NAMEs utf8, character_set_connection=utf32; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf32 NOT NULL ++ `t` varchar(10) CHARACTER SET utf32 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8.result index 46b7386d993,7b486f0f443..de524055d51 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@@ -10286,6 -10278,29 +10286,29 @@@ SELECT * FROM v1 c ß DROP VIEW v1; + SET NAMES utf8; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8 NOT NULL ++ `t` varchar(10) CHARACTER SET utf8 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/ctype_utf8mb4.result index 7e5e99354ea,de9ba321ad9..27ffd5552fa --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@@ -3478,6 -3469,29 +3478,29 @@@ t1 CREATE TABLE `t1` ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; SET NAMES default; + SET NAMES utf8mb4; + # + # MDEV-13118 Wrong results with LOWER and UPPER and subquery + # + SET @save_optimizer_switch=@@optimizer_switch; + SET optimizer_switch=_latin1'derived_merge=on'; + CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( - `t` varchar(10) CHARACTER SET utf8mb4 NOT NULL ++ `t` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL + ) ENGINE=MyISAM DEFAULT CHARSET=latin1 + INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; + c2 + abcdefghi-abcdefghi + abcdefghi-abcdefghi + SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; + c2 + ABCDEFGHI-ABCDEFGHI + ABCDEFGHI-ABCDEFGHI + DROP TABLE t1; + SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # diff --cc mysql-test/r/grant.result index 81b41a083a3,7abaf834975..44eb59fbf9e --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@@ -1750,9 -1683,9 +1750,10 @@@ revoke insert on mysqltest.t4 f revoke create, insert on mysqltest.t5 from mysqltest@localhost; revoke create, insert on mysqltest.t6 from mysqltest@localhost; drop user mysqltest@localhost; +disconnect user1; drop database mysqltest; use test; + call mtr.add_suppression("Can't open and lock privilege tables"); FLUSH PRIVILEGES without procs_priv table. RENAME TABLE mysql.procs_priv TO mysql.procs_gone; FLUSH PRIVILEGES; @@@ -1846,9 -1774,6 +1847,7 @@@ BEGI SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END ;|| - Warnings: - Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges +connection default; SHOW GRANTS FOR 'user1'@'localhost'; Grants for user1@localhost GRANT USAGE ON *.* TO 'user1'@'localhost' @@@ -1858,8 -1783,7 +1857,9 @@@ SHOW GRANTS FOR 'user2' Grants for user2@% GRANT USAGE ON *.* TO 'user2'@'%' GRANT CREATE, CREATE ROUTINE ON `db1`.* TO 'user2'@'%' + GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`proc2` TO 'user2'@'%' +disconnect con1; +disconnect con2; DROP PROCEDURE db1.proc1; DROP PROCEDURE db1.proc2; REVOKE ALL ON db1.* FROM 'user1'@'localhost'; diff --cc mysql-test/r/sp_notembedded.result index 190f8f78d23,712f299014c..8edb9e79b70 --- a/mysql-test/r/sp_notembedded.result +++ b/mysql-test/r/sp_notembedded.result @@@ -225,13 -203,7 +225,11 @@@ VALUES('%', 'mysqltest_1', password('') Warnings: Warning 1364 Field 'authentication_string' doesn't have a default value FLUSH PRIVILEGES; +connect con1, localhost, mysqltest_1,,; +connection con1; CREATE PROCEDURE p1(i INT) BEGIN END; - Warnings: - Warning 1404 Failed to grant EXECUTE and ALTER ROUTINE privileges +disconnect con1; +connection default; DROP PROCEDURE p1; DELETE FROM mysql.user WHERE User='mysqltest_1'; FLUSH PRIVILEGES; diff --cc mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result index 93ab4a3f3d4,bdf3844f2c5..0329e105a63 --- a/mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result +++ b/mysql-test/suite/galera/r/galera_sst_mysqldump_with_key.result @@@ -59,8 -49,95 +59,96 @@@ INSERT INTO t1 VALUES ('node1_to_be_rol INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); +connection node_2; - Loading wsrep provider ... + Starting server ... + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + INSERT INTO t1 VALUES ('node2_committed_after'); + COMMIT; + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + COMMIT; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + INSERT INTO t1 VALUES ('node1_committed_after'); + COMMIT; + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + ROLLBACK; + SELECT COUNT(*) = 35 FROM t1; + COUNT(*) = 35 + 1 + SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; + COUNT(*) = 0 + 1 + COMMIT; + SET AUTOCOMMIT=ON; + SELECT COUNT(*) = 35 FROM t1; + COUNT(*) = 35 + 1 + SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; + COUNT(*) = 0 + 1 + DROP TABLE t1; + COMMIT; + SET AUTOCOMMIT=ON; + Performing State Transfer on a server that has been killed and restarted + CREATE TABLE t1 (f1 CHAR(255)) ENGINE=InnoDB; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + INSERT INTO t1 VALUES ('node1_committed_before'); + COMMIT; + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + INSERT INTO t1 VALUES ('node2_committed_before'); + COMMIT; + Killing server ... + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + INSERT INTO t1 VALUES ('node1_committed_during'); + COMMIT; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + INSERT INTO t1 VALUES ('node1_to_be_committed_after'); + SET AUTOCOMMIT=OFF; + START TRANSACTION; + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + INSERT INTO t1 VALUES ('node1_to_be_rollbacked_after'); + Performing --wsrep-recover ... + Starting server ... + Using --wsrep-start-position when starting mysqld ... SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES ('node2_committed_after'); diff --cc mysql-test/suite/innodb/r/innodb-wl5522.result index 4ef92cdfed9,fb80580f797..4ef8a2ee131 --- a/mysql-test/suite/innodb/r/innodb-wl5522.result +++ b/mysql-test/suite/innodb/r/innodb-wl5522.result @@@ -104,15 -99,6 +104,15 @@@ a b 822 Devotion asdfuihknaskdf 821 Cavalry ..asdasdfaeraf DROP TABLE t1; +ALTER TABLE t2 ROW_FORMAT=DYNAMIC; +ALTER TABLE t2 DISCARD TABLESPACE; +# List after t2 DISCARD +t2.frm +ALTER TABLE t2 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) ++ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) +ALTER TABLE t2 IMPORT TABLESPACE; +ERROR HY000: Schema mismatch (Expected FSP_SPACE_FLAGS=0x*, .ibd file contains 0x*.) +DROP TABLE t2; SET GLOBAL innodb_file_per_table = 1; SELECT @@innodb_file_per_table; @@innodb_file_per_table @@@ -586,10 -576,10 +586,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -598,10 -588,22 +598,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x0) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x0; .cfg file uses ROW_FORMAT=REDUNDANT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -772,10 -774,10 +784,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x1) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -784,10 -786,22 +796,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -961,10 -975,10 +985,10 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -973,10 -987,22 +997,22 @@@ c1 INT NOT NULL AUTO_INCREMENT PRIMARY c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; ALTER TABLE t1 DISCARD TABLESPACE; SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' +ERROR HY000: Tablespace has been discarded for table `t1` restore: t1 .ibd and .cfg files ALTER TABLE t1 IMPORT TABLESPACE; - ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x21) + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x29 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC) unlink: t1.ibd unlink: t1.cfg DROP TABLE t1; @@@ -1035,6 -1061,220 +1071,220 @@@ c1 c 42 1 43 1 DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + INSERT INTO t1(c2) VALUES(1); + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + INSERT INTO t1(c2) SELECT c2 FROM t1; + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) + ) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + FLUSH TABLES t1 FOR EXPORT; + backup: t1 + UNLOCK TABLES; + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + CHECK TABLE t1; + Table Op Msg_type Msg_text + test.t1 check status OK + unlink: t1.cfg + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) -) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ++) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x0 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB + ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + ERROR HY000: Schema mismatch (Table flags don't match, server table has 0x23 and the meta-data file has 0x29; .cfg file uses ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8) + unlink: t1.ibd + unlink: t1.cfg + DROP TABLE t1; + CREATE TABLE t1( + c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED; + ALTER TABLE t1 DISCARD TABLESPACE; + SELECT * FROM t1; -ERROR HY000: Tablespace has been discarded for table 't1' ++ERROR HY000: Tablespace has been discarded for table `t1` + restore: t1 .ibd and .cfg files + ALTER TABLE t1 IMPORT TABLESPACE; + CHECK TABLE t1; + Table Op Msg_type Msg_text + test.t1 check status OK + unlink: t1.cfg + SHOW CREATE TABLE t1; + Table Create Table + t1 CREATE TABLE `t1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) -) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED ++) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED + SELECT * FROM t1; + c1 c2 + 1 1 + 2 1 + 3 1 + 4 1 + 6 1 + 7 1 + 8 1 + 9 1 + 13 1 + 14 1 + 15 1 + 16 1 + 17 1 + 18 1 + 19 1 + 20 1 + 28 1 + 29 1 + 30 1 + 31 1 + 32 1 + 33 1 + 34 1 + 35 1 + 36 1 + 37 1 + 38 1 + 39 1 + 40 1 + 41 1 + 42 1 + 43 1 + DROP TABLE t1; call mtr.add_suppression("Got error -1 when reading table '.*'"); call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*"); call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded"); diff --cc mysql-test/suite/innodb/r/row_format_redundant.result index a2d5bbef8df,db31c32559f..07b7a29675e --- a/mysql-test/suite/innodb/r/row_format_redundant.result +++ b/mysql-test/suite/innodb/r/row_format_redundant.result @@@ -51,29 -45,4 +51,29 @@@ ERROR HY000: Table 't3' is read onl TRUNCATE TABLE t1; TRUNCATE TABLE t2; TRUNCATE TABLE t3; -DROP TABLE t1,t2,t3; +corrupted SYS_TABLES.MIX_LEN for test/t1 +corrupted SYS_TABLES.MIX_LEN for test/t2 +corrupted SYS_TABLES.MIX_LEN for test/t3 +TRUNCATE TABLE t1; +ERROR 42S02: Table 'test.t1' doesn't exist in engine +TRUNCATE TABLE t2; +TRUNCATE TABLE t3; +SELECT COUNT(*) FROM t1; +ERROR 42S02: Table 'test.t1' doesn't exist in engine +SELECT COUNT(*) FROM t2; +COUNT(*) +0 +SELECT COUNT(*) FROM t3; +COUNT(*) +0 +RENAME TABLE t1 TO tee_one; +ERROR HY000: Error on rename of './test/t1' to './test/tee_one' (errno: 155 "The table does not exist in the storage engine") +DROP TABLE t1; +Warnings: +Warning 1932 Table 'test.t1' doesn't exist in engine +DROP TABLE t2,t3; - FOUND 49 /\[ERROR\] InnoDB: Table `test`\.`t1` in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=255\b/ in mysqld.1.err ++FOUND 50 /\[ERROR\] InnoDB: Table `test`\.`t1` in InnoDB data dictionary contains invalid flags\. SYS_TABLES\.TYPE=1 SYS_TABLES\.MIX_LEN=255\b/ in mysqld.1.err +ib_buffer_pool +ib_logfile0 +ib_logfile1 +ibdata1 diff --cc mysql-test/t/ctype_euckr.test index 950c60c47e5,71660314279..46955360217 --- a/mysql-test/t/ctype_euckr.test +++ b/mysql-test/t/ctype_euckr.test @@@ -197,23 -197,14 +197,33 @@@ set collation_connection=euckr_bin --echo # End of 5.6 tests --echo # - + --echo # + --echo # Start of 10.0 tests + --echo # + + SET NAMES utf8, character_set_connection=euckr; + --source include/ctype_mdev13118.inc + + --echo # + --echo # End of 10.0 tests + --echo # + +--echo # +--echo # Start of 10.2 tests +--echo # + +--echo # +--echo # MDEV-9711 NO PAD Collatons +--echo # +SET character_set_connection=euckr; +let $coll='euckr_korean_nopad_ci'; +let $coll_pad='euckr_korean_ci'; +--source include/ctype_pad_all_engines.inc + +let $coll='euckr_nopad_bin'; +let $coll_pad='euckr_bin'; +--source include/ctype_pad_all_engines.inc + +--echo # +--echo # End of 10.2 tests +--echo # diff --cc scripts/mysql_install_db.sh index 1d640abef7c,40fdf798d71..9e0c66cc55b --- a/scripts/mysql_install_db.sh +++ b/scripts/mysql_install_db.sh @@@ -346,10 -343,9 +346,10 @@@ els basedir="@prefix@" bindir="@bindir@" resolveip="$bindir/resolveip" - mysqld="@libexecdir@/mysqld" + mysqld="@sbindir@/mysqld" srcpkgdatadir="@pkgdatadir@" buildpkgdatadir="@pkgdatadir@" + plugindir="@pkgplugindir@" fi # Set up paths to SQL scripts required for bootstrap diff --cc scripts/mysql_system_tables_fix.sql index 2a826bb2df0,a982fc87ec9..e724c3e2d23 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@@ -162,14 -171,8 +171,14 @@@ alter table func comment='User defined # and reset all char columns to correct width ALTER TABLE user MODIFY Host char(60) NOT NULL default '', - MODIFY User char(80) NOT NULL default '', + MODIFY User char(80) binary NOT NULL default '', ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; + +# In MySQL 5.7.6 the Password column is removed. Recreate it to preserve the number +# of columns MariaDB expects in the user table. +ALTER TABLE user + ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER User; + ALTER TABLE user MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, diff --cc sql/item.cc index a501ff6fdc6,f1b57c64410..58d2b7dbfc0 --- a/sql/item.cc +++ b/sql/item.cc @@@ -10406,8 -9579,9 +10406,9 @@@ bool Item_type_holder::join_types(THD * geometry_type= Field_geom::geometry_type_merge(geometry_type, item->get_geometry_type()); - if (Field::result_merge_type(fld_type) == DECIMAL_RESULT) + if (Field::result_merge_type(real_field_type()) == DECIMAL_RESULT) { + collation.set_numeric(); decimals= MY_MIN(MY_MAX(decimals, item->decimals), DECIMAL_MAX_SCALE); int item_int_part= item->decimal_int_part(); int item_prec = MY_MAX(prev_decimal_int_part, item_int_part) + decimals; diff --cc storage/connect/filamvct.cpp index a660461e9ee,dd827d084fa..71174c3530f mode 100644,100755..100644 --- a/storage/connect/filamvct.cpp +++ b/storage/connect/filamvct.cpp diff --cc storage/innobase/handler/ha_innodb.cc index a8594fd37b3,5667efea5df..d8e805862f7 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@@ -13649,7 -13000,7 +13680,7 @@@ ha_innobase::rename_table ++trx->will_lock; trx_set_dict_operation(trx, TRX_DICT_OP_INDEX); - dberr_t error = innobase_rename_table(trx, from, to); - error = innobase_rename_table(thd, trx, from, to); ++ dberr_t error = innobase_rename_table(thd, trx, from, to); DEBUG_SYNC(thd, "after_innobase_rename_table"); diff --cc storage/innobase/include/dict0stats.h index 6687ff79795,72501bf9429..7f25c26f1e2 --- a/storage/innobase/include/dict0stats.h +++ b/storage/innobase/include/dict0stats.h @@@ -110,22 -129,6 +110,22 @@@ dict_stats_deinit dict_table_t* table) /*!< in/out: table */ MY_ATTRIBUTE((nonnull)); +#ifdef WITH_WSREP +/** Update the table modification counter and if necessary, +schedule new estimates for table and index statistics to be calculated. +@param[in,out] table persistent or temporary table +@param[in] thd current session */ +void dict_stats_update_if_needed(dict_table_t* table, THD* thd) + MY_ATTRIBUTE((nonnull(1))); +#else +/** Update the table modification counter and if necessary, +schedule new estimates for table and index statistics to be calculated. +@param[in,out] table persistent or temporary table */ +void dict_stats_update_if_needed_func(dict_table_t* table) + MY_ATTRIBUTE((nonnull)); - # define dict_stats_update_if_needed(t, thd) dict_stats_update_if_needed(t) ++# define dict_stats_update_if_needed(t, thd) dict_stats_update_if_needed_func(t) +#endif + /*********************************************************************//** Calculates new estimates for table and index statistics. The statistics are used in query optimization. diff --cc storage/innobase/row/row0import.cc index 3689c21588d,02e1c3d33a3..de3fa36c7ae --- a/storage/innobase/row/row0import.cc +++ b/storage/innobase/row/row0import.cc @@@ -113,19 -111,19 +113,19 @@@ struct row_index_t struct row_import { row_import() UNIV_NOTHROW : - m_table(), - m_version(), - m_hostname(), - m_table_name(), - m_autoinc(), + m_table(NULL), + m_version(0), + m_hostname(NULL), + m_table_name(NULL), + m_autoinc(0), - m_page_size(0), + m_page_size(0, 0, false), - m_flags(), - m_n_cols(), - m_cols(), - m_col_names(), - m_n_indexes(), - m_indexes(), - m_missing(true) {} + m_flags(0), + m_n_cols(0), + m_cols(NULL), + m_col_names(NULL), + m_n_indexes(0), + m_indexes(NULL), + m_missing(true) { } ~row_import() UNIV_NOTHROW; @@@ -1229,11 -1298,57 +1229,56 @@@ row_import::match_schema { /* Do some simple checks. */ - if ((m_table->flags ^ m_flags) & ~DICT_TF_MASK_DATA_DIR) { - ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, - "Table flags don't match, server table has 0x%x" - " and the meta-data file has 0x" ULINTPFx, - m_table->flags, m_flags); + if (ulint mismatch = (m_table->flags ^ m_flags) + & ~DICT_TF_MASK_DATA_DIR) { + const char* msg; + if (mismatch & DICT_TF_MASK_ZIP_SSIZE) { + if ((m_table->flags & DICT_TF_MASK_ZIP_SSIZE) + && (m_flags & DICT_TF_MASK_ZIP_SSIZE)) { + switch (m_flags & DICT_TF_MASK_ZIP_SSIZE) { + case 0U << DICT_TF_POS_ZIP_SSIZE: + goto uncompressed; + case 1U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=1"; + break; + case 2U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=2"; + break; + case 3U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=4"; + break; + case 4U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=8"; + break; + case 5U << DICT_TF_POS_ZIP_SSIZE: + msg = "ROW_FORMAT=COMPRESSED" + " KEY_BLOCK_SIZE=16"; + break; + default: + msg = "strange KEY_BLOCK_SIZE"; + } + } else if (m_flags & DICT_TF_MASK_ZIP_SSIZE) { + msg = "ROW_FORMAT=COMPRESSED"; + } else { + goto uncompressed; + } + } else { + uncompressed: + msg = (m_flags & DICT_TF_MASK_ATOMIC_BLOBS) + ? "ROW_FORMAT=DYNAMIC" + : (m_flags & DICT_TF_MASK_COMPACT) + ? "ROW_FORMAT=COMPACT" + : "ROW_FORMAT=REDUNDANT"; + } - - ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, - "Table flags don't match, server table has 0x%x" - " and the meta-data file has 0x%lx;" - " .cfg file uses %s", - m_table->flags, ulong(m_flags), msg); ++ ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_TABLE_SCHEMA_MISMATCH, ++ "Table flags don't match, server table has 0x%x" ++ " and the meta-data file has 0x" ULINTPFx ";" ++ " .cfg file uses %s", ++ m_table->flags, m_flags, msg); return(DB_ERROR); } else if (m_table->n_cols != m_n_cols) { diff --cc strings/ctype-ucs2.c index 80bb6745b19,0791b0b742c..687796bdcbb --- a/strings/ctype-ucs2.c +++ b/strings/ctype-ucs2.c @@@ -1406,19 -1378,9 +1407,18 @@@ my_hash_sort_utf16_nopad(CHARSET_INFO * } +static void +my_hash_sort_utf16(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= cs->cset->lengthsp(cs, (const char *) s, slen); + my_hash_sort_utf16_nopad(cs, s, lengthsp, nr1, nr2); +} + + static size_t - my_casedn_utf16(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_casedn_utf16(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; my_charset_conv_mb_wc mb_wc= cs->cset->mb_wc; @@@ -2218,22 -1993,9 +2220,21 @@@ my_tosort_utf32(MY_UNICASE_INFO *uni_pl } +static size_t +my_lengthsp_utf32(CHARSET_INFO *cs __attribute__((unused)), + const char *ptr, size_t length) +{ + const char *end= ptr + length; + DBUG_ASSERT((length % 4) == 0); + while (end > ptr + 3 && end[-1] == ' ' && !end[-2] && !end[-3] && !end[-4]) + end-= 4; + return (size_t) (end - ptr); +} + + static size_t - my_caseup_utf32(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_caseup_utf32(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; @@@ -2277,19 -2045,9 +2280,18 @@@ my_hash_sort_utf32_nopad(CHARSET_INFO * } +static void +my_hash_sort_utf32(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= my_lengthsp_utf32(cs, (const char *) s, slen); + my_hash_sort_utf32_nopad(cs, s, lengthsp, nr1, nr2); +} + + static size_t - my_casedn_utf32(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + my_casedn_utf32(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; @@@ -3124,16 -2831,8 +3129,15 @@@ my_hash_sort_ucs2_nopad(CHARSET_INFO *c } +static void my_hash_sort_ucs2(CHARSET_INFO *cs, const uchar *s, size_t slen, + ulong *nr1, ulong *nr2) +{ + size_t lengthsp= my_lengthsp_mb2(cs, (const char *) s, slen); + my_hash_sort_ucs2_nopad(cs, s, lengthsp, nr1, nr2); +} + - static size_t my_casedn_ucs2(CHARSET_INFO *cs, char *src, size_t srclen, - char *dst __attribute__((unused)), - size_t dstlen __attribute__((unused))) + static size_t my_casedn_ucs2(CHARSET_INFO *cs, const char *src, size_t srclen, + char *dst, size_t dstlen) { my_wc_t wc; int res; diff --cc strings/ctype-utf8.c index a73de37bbd7,8fab93890f4..c095cc7d74d --- a/strings/ctype-utf8.c +++ b/strings/ctype-utf8.c @@@ -5140,7 -5063,15 +5140,7 @@@ my_toupper_utf8mb3(MY_UNICASE_INFO *uni } - static size_t my_caseup_utf8(CHARSET_INFO *cs, char *src, size_t srclen, -static inline void -my_tosort_utf8mb3(MY_UNICASE_INFO *uni_plane, my_wc_t *wc) -{ - MY_UNICASE_CHARACTER *page; - if ((page= uni_plane->page[(*wc >> 8) & 0xFF])) - *wc= page[*wc & 0xFF].sort; -} - + static size_t my_caseup_utf8(CHARSET_INFO *cs, const char *src, size_t srclen, char *dst, size_t dstlen) { my_wc_t wc;