JSON_TABLE BINARY Column Collation Drifts Through a View (latin1_bin → utf8mb4_bin)

CREATE DATABASE d DEFAULT CHARACTER SET latin1;

USE d;

SET NAMES latin1;

-- Direct execution: latin1_bin (correct, follows the db default)

SELECT collation(name) FROM json_table('[{"name":"Jeans"}]', '$[*]'

  COLUMNS(name VARCHAR(10) BINARY PATH '$.name')) AS jt;

-- => latin1_bin

-- Through a view: drifts to utf8mb4_bin

CREATE VIEW v1 AS SELECT * FROM json_table('[{"name":"Jeans"}]', '$[*]'

  COLUMNS(name VARCHAR(10) BINARY PATH '$.name')) AS jt;

SELECT collation(name) FROM v1;

-- => utf8mb4_bin

SHOW CREATE VIEW v1;

-- The view definition is rewritten as: name varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_bin

-- (hardcoded utf8mb4, while direct execution semantics follow the database default latin1)