Repository navigation
fix(mysql): give a json_table text column the database's collation - #77
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
Important
The added CHARACTER SET … COLLATE … text cannot be parsed
8000
by the engine's own sqlparser. Any MySQL statement with a json_table text column now loses its parse-tree analysis.
Reviewed changes
Reviewed both commits: the MySQL json_table collation rewrite, the new Rewritten.inserted offset bookkeeping, the oracle case, and the docs.
mysql_collation.rs: scans the text for eachjson_tablecolumn of a char or text type that names no character set or collation, and writes the database's character set and collation before itspath. The rewrite is skipped on MariaDB and on thereylo.tables therows(…)expansion writes.Rewritten.inserted: records the added text so thatto_original, therows(…)list offsets instructured.rsandvectors_as_textstill map back to the author's text.- Oracle
mysql_json_table_collation: covers theIN, join,LIKE,NESTED,_bin, connection-collation and named-charset forms. - Docs: the casts table says
::textis sent as the bare?, a new paragraph explains the collation rule, and the nested insert no longer hard-codescollate utf8mb4_0900_ai_ci.
ℹ️ The compiled text uses the character set and collation of the database the type check ran against
The clause is written into the module's sql from the type check's @@character_set_database and @@collation_database. It applies at run time, whatever database the app connects to. The rows(…) expansion already works this way, but this PR extends it to every hand-written json_table. The character set is the new part. Take a database whose default is latin1 but whose tables are utf8mb4: before, a json_table text column was utf8mb4 (the connection's). Now it is latin1, so JSON strings outside latin1 are converted on the way into an insert … select … from json_table(…). Consider whether to write only COLLATE when the character set already matches the connection's, or to skip the rewrite when the database's character set is not utf8mb4. This is a design choice, so no change is required.
claude-opus-5-5 ｜ 𝕏
JSON_TABLE gives a character column the connection's collation with a column's coercibility, so with mysql2 (utf8mb4_unicode_ci) on a MySQL 8 database (utf8mb4_0900_ai_ci) comparing it with a table's text column failed with Illegal mix of collations. The compiled statement now names the database's character set and collation in each such column's definition, as the expansion of a rows parameter already does; the rewrite records the text it adds so every offset still maps back.
The engine's parser refused CHARACTER SET and COLLATE after a JSON_TABLE column's type, so a statement that named one, or that the collation pass wrote one into, lost its tree: its row count and JSON shapes. It now reads and prints them. The pass writes only COLLATE, and only where the database's character set is utf8mb4, JSON's own.
8d2e369 to
34ff5d8
Compare
|
On the character set (the review's note): agreed, writing the database's character set would convert the JSON's text where the database isn't |
There was a problem hiding this comment.
✅ No new issues found. Both points from the prior review are addressed.
Reviewed changes
Reviewed the two commits since 8d2e369: the parser fix and the narrowing of the rewrite to utf8mb4 databases.
- Vendored
sqlparserreads ajson_tablecolumn's charset and collation:parse_json_table_column_defnow takes an optionalCHARACTER SET/CHARSETandCOLLATEafter the type.JsonTableNamedColumnkeeps them ascharacter_set/collationand prints them back, so the rewritten statement and the probes made from it keep their tree. The crate's README records the change. - New oracle statements:
key_and_list.sqlstaysmaybeOneandshape.sqlgives{ o: { n: text? } }. Both would have regressed tomanyandunknownif the parse had failed. - Rewrite writes only
COLLATEand only on autf8mb4database:mysql.rs:266now requires@@character_set_databaseto beutf8mb4.writtenwritesCOLLATE <collation>, so a database with another default character set no longer converts JSON text into that character set. The module doc, the oraclesetup.sqlcomment and the MySQL docs paragraph now say this. - Unit test parses the written statement and one that names a character set, then checks that the printed form parses back unchanged.
- Rebuilt artifacts and recaptured docs:
tests/fingerprint.test.tspasses against the committedengine.wasmfiles.
claude-opus-5-5 ｜ 𝕏
JSON_TABLEgives a column of a character type the connection's character set and collation, with the coercibility of a table's column. mysql2 connects withutf8mb4_unicode_ci, and MySQL 8 makes its databasesutf8mb4_0900_ai_ci, so out of the box everyjson_tabletext column compared with a table's text column failed withIllegal mix of collations (1267), in the type check and at run time:json_tableis how the docs read every list, bulk insert, nested insert and JSON object on MySQL, and the docs' nested insert worked around it by hard-codingcollate utf8mb4_0900_ai_ci, which is wrong on a database with another default.What changes
@@collation_database, asked once a session) in eachjson_tablecolumn of a character type that names neither:name varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci path '$'. The expansion of arows(…)parameter already did this for its ownJSON_TABLE, which is left as it was. MariaDB is left alone._bincolumn still wins as MySQL decides. One case changes: a column of the connection's collation that is not the database's (utf8mb4_unicode_ciin autf8mb4_0900_ai_cidatabase) compared before and is now refused, until thejson_tablecolumn names that collation. The oracle pins both (mysql_json_table_collation).CAST(… AS CHAR)is not rewritten: a trailingCOLLATEhas explicit coercibility and would change what an equality on a_bincolumn matches. Reylo's own::textis already the bare placeholder, which takes the column's collation; the docs say to write that.Rewritten.inserted), so error offsets, placeholder spans and arows(…)insert's lists, which the run time writes again by offset, all still map back to the author's text.::text,::varchar(n)and::uuidare sent as the bare?; the nested insert drops its hard-coded collation.