readyset
readyset copied to clipboard
readysettech
MySQL binary collate type promotion
Description
-- start readyset - this requires the table to be received via replication
mysql> CREATE TABLE t (id INT PRIMARY KEY, `col_1` CHAR(10) NOT NULL DEFAULT 'AXw', `col_2` CHAR(10) COLLATE binary NOT NULL DEFAULT 'AXw');
Query OK, 0 rows affected (0,019 sec)
mysql> INSERT INTO `t` VALUES (0, 'AXw', 'AXw');
Query OK, 1 row affected (0,005 sec)
mysql> SELECT * FROM t;
+----+-------+------------------------+
| id | col_1 | col_2 |
+----+-------+------------------------+
| 0 | AXw | 0x41587700000000000000 |
+----+-------+------------------------+
1 row in set (0,001 sec)
readyset> CREATE CACHE FROM SELECT * FROM t;
Query OK, 0 rows affected (0,226 sec)
readyset> SELECT * FROM t;
+------+-------+-------+
| id | col_1 | col_2 |
+------+-------+-------+
| 0 | AXw | AXw |
+------+-------+-------+
1 row in set (0,007 sec)
The issue is that MySQL is doing type promotion, CHAR(10) COLLATE binary is being promoted to BINARY field:
mysql> SHOW CREATE TABLE t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int NOT NULL,
`col_1` char(10) NOT NULL DEFAULT 'AXw',
`col_2` binary(10) NOT NULL DEFAULT 'AXw\0\0\0\0\0\0\0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,003 sec)
@gvsg-rs We are producing different results than upstream in the Text protocol:
Upstream: 0x41587700000000000000
Readyset: AXw
