MDEV-18990 Wrong result when binary column is used as a condition in hexadecimal modebb-10.4-MDEV-18990

4 files changed, 174 insertions, 0 deletions

diff --git a/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_deinit.inc b/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_deinit.inc
new file mode 100644
index 00000000000..f8405973768
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_deinit.inc
@@ -0,0 +1,11 @@
+--let $MASTER_1_COMMENT_2_1= $MASTER_1_COMMENT_2_1_BACKUP
+--let $CHILD2_1_DROP_TABLES= $CHILD2_1_DROP_TABLES_BACKUP
+--let $CHILD2_1_CREATE_TABLES= $CHILD2_1_CREATE_TABLES_BACKUP
+--let $CHILD2_1_SELECT_TABLES= $CHILD2_1_SELECT_TABLES_BACKUP
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../../t/test_deinit.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
diff --git a/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_init.inc b/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_init.inc
new file mode 100644
index 00000000000..190f5c0fd50
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/include/select_by_hex_init.inc
@@ -0,0 +1,25 @@
+--disable_warnings
+--disable_query_log
+--disable_result_log
+--source ../../t/test_init.inc
+--enable_result_log
+--enable_query_log
+--enable_warnings
+--let $MASTER_1_COMMENT_2_1_BACKUP= $MASTER_1_COMMENT_2_1
+let $MASTER_1_COMMENT_2_1=
+  COMMENT='table "tbl_a", srv "s_2_1"';
+--let $CHILD2_1_DROP_TABLES_BACKUP= $CHILD2_1_DROP_TABLES
+let $CHILD2_1_DROP_TABLES=
+  DROP TABLE IF EXISTS tbl_a;
+--let $CHILD2_1_CREATE_TABLES_BACKUP= $CHILD2_1_CREATE_TABLES
+let $CHILD2_1_CREATE_TABLES=
+  CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    txt_bin binary(16) NOT NULL,
+    PRIMARY KEY (pkey)
+  ) $CHILD2_1_ENGINE DEFAULT CHARACTER SET utf8;
+--let $CHILD2_1_SELECT_TABLES_BACKUP= $CHILD2_1_SELECT_TABLES
+let $CHILD2_1_SELECT_TABLES=
+  SELECT pkey, HEX(txt_bin) FROM tbl_a ORDER BY pkey;
+let $CHILD2_1_SELECT_ARGUMENT1=
+  SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
diff --git a/storage/spider/mysql-test/spider/regression/e1121/r/select_by_hex.result b/storage/spider/mysql-test/spider/regression/e1121/r/select_by_hex.result
new file mode 100644
index 00000000000..a8da1757196
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/r/select_by_hex.result
@@ -0,0 +1,64 @@
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+this test is for MDEV-18990
+
+drop and create databases
+connection master_1;
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+connection child2_1;
+SET @old_log_output = @@global.log_output;
+SET GLOBAL log_output = 'TABLE,FILE';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+
+create table and insert
+connection child2_1;
+CHILD2_1_CREATE_TABLES
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+CREATE TABLE tbl_a (
+pkey int NOT NULL,
+txt_bin binary(16) NOT NULL,
+PRIMARY KEY (pkey)
+) MASTER_1_ENGINE DEFAULT CHARACTER SET utf8 MASTER_1_COMMENT_2_1
+INSERT INTO tbl_a (pkey,txt_bin) VALUES (0,0x15101408BFF8380088000C458048000C);
+FLUSH TABLES;
+
+test 1
+connection child2_1;
+TRUNCATE TABLE mysql.general_log;
+connection master_1;
+SET NAMES utf8;
+SELECT pkey, HEX(txt_bin) FROM auto_test_local.tbl_a WHERE txt_bin = 0x15101408BFF8380088000C458048000C;
+pkey	HEX(txt_bin)
+0	15101408BFF8380088000C458048000C
+connection child2_1;
+SET NAMES utf8;
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %';
+argument
+select t0.`pkey` `pkey`,(hex(t0.`txt_bin`)) `HEX(txt_bin)` from `auto_test_remote`.`tbl_a` t0 where (t0.`txt_bin` = _binary'\xBF\xF88\0\x88\0E\x80H\0')
+SELECT argument FROM mysql.general_log WHERE argument LIKE '%select %'
+SELECT pkey, HEX(txt_bin) FROM tbl_a ORDER BY pkey;
+pkey	HEX(txt_bin)
+0	15101408BFF8380088000C458048000C
+
+deinit
+connection master_1;
+DROP DATABASE IF EXISTS auto_test_local;
+connection child2_1;
+DROP DATABASE IF EXISTS auto_test_remote;
+SET GLOBAL log_output = @old_log_output;
+for master_1
+for child2
+child2_1
+child2_2
+child2_3
+for child3
+
+end of test
diff --git a/storage/spider/mysql-test/spider/regression/e1121/t/select_by_hex.test b/storage/spider/mysql-test/spider/regression/e1121/t/select_by_hex.test
new file mode 100644
index 00000000000..d9d07bf6eb7
--- /dev/null
+++ b/storage/spider/mysql-test/spider/regression/e1121/t/select_by_hex.test
@@ -0,0 +1,74 @@
+--source ../include/select_by_hex_init.inc
+--echo
+--echo this test is for MDEV-18990
+--echo
+--echo drop and create databases
+
+--connection master_1
+--disable_warnings
+CREATE DATABASE auto_test_local;
+USE auto_test_local;
+
+--connection child2_1
+SET @old_log_output = @@global.log_output;
+SET GLOBAL log_output = 'TABLE,FILE';
+CREATE DATABASE auto_test_remote;
+USE auto_test_remote;
+--enable_warnings
+
+--echo
+--echo create table and insert
+
+--connection child2_1
+--disable_query_log
+echo CHILD2_1_CREATE_TABLES;
+eval $CHILD2_1_CREATE_TABLES;
+--enable_query_log
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+--disable_query_log
+echo CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    txt_bin binary(16) NOT NULL,
+    PRIMARY KEY (pkey)
+) MASTER_1_ENGINE DEFAULT CHARACTER SET utf8 MASTER_1_COMMENT_2_1;
+eval CREATE TABLE tbl_a (
+    pkey int NOT NULL,
+    txt_bin binary(16) NOT NULL,
+    PRIMARY KEY (pkey)
+) $MASTER_1_ENGINE DEFAULT CHARACTER SET utf8 $MASTER_1_COMMENT_2_1;
+--enable_query_log
+INSERT INTO tbl_a (pkey,txt_bin) VALUES (0,0x15101408BFF8380088000C458048000C);
+FLUSH TABLES;
+
+--echo
+--echo test 1
+
+--connection child2_1
+TRUNCATE TABLE mysql.general_log;
+
+--connection master_1
+SET NAMES utf8;
+SELECT pkey, HEX(txt_bin) FROM auto_test_local.tbl_a WHERE txt_bin = 0x15101408BFF8380088000C458048000C;
+
+--connection child2_1
+SET NAMES utf8;
+eval $CHILD2_1_SELECT_ARGUMENT1;
+eval $CHILD2_1_SELECT_TABLES;
+
+--echo
+--echo deinit
+--disable_warnings
+
+--connection master_1
+DROP DATABASE IF EXISTS auto_test_local;
+
+--connection child2_1
+DROP DATABASE IF EXISTS auto_test_remote;
+SET GLOBAL log_output = @old_log_output;
+
+--enable_warnings
+--source ../include/select_by_hex_deinit.inc
+--echo
+--echo end of test