[MDEV-25353] JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice Created: 2021-04-06  Updated: 2021-04-21  Resolved: 2021-04-17

Status: Closed
Project: MariaDB Server
Component/s: JSON, Prepared Statements
Affects Version/s: N/A
Fix Version/s: 10.6.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed
relates to MDEV-25259 JSON_TABLE: Illegal mix of collations... Closed

 Description    

SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
 
 
PREPARE stmt FROM "
 
SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
";
 
EXECUTE stmt;

The plain SELECT works and returns a value:

MariaDB [test]> SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
+------+
 
| a    |
 
+------+
 
| 2    |
 
+------+
 
1 row in set (0.001 sec)

The prepared statement fails:

bb-10.6-mdev17399-hf 160bd1691

 
MariaDB [test]> PREPARE stmt FROM "
 
    "> SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
    "> ";
 
Query OK, 0 rows affected (0.001 sec)
 
Statement prepared
 
 
 
MariaDB [test]> EXECUTE stmt;
 
ERROR 1267 (HY000): Illegal mix of collations (koi8u_general_ci,IMPLICIT) and (eucjpms_japanese_ci,IMPLICIT) for operation '='

On MySQL both work.

Also reproducible with EXECUTE IMMEDIATE and upon the 2nd execution of a stored procedure/function:

SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
 
 
CREATE OR REPLACE PROCEDURE pr()
 
SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
 
 
 
CALL pr();
 
CALL pr();


MariaDB [test]> CALL pr();
 
+------+
 
| a    |
 
+------+
 
| 2    |
 
+------+
 
1 row in set (0.002 sec)
 
 
 
Query OK, 0 rows affected (0.002 sec)
 
 
 
MariaDB [test]> CALL pr();
 
ERROR 1267 (HY000): Illegal mix of collations (koi8u_general_ci,IMPLICIT) and (eucjpms_japanese_ci,IMPLICIT) for operation '='

(same with a function)

 Comments   
Comment by Sergei Petrunia [ 2021-04-15 ]

Ok this is how the regular SELECT query manages to work:

The query starts
create_table_for_function() is executed. It creates the JSON_TABLE's temporary table

Then, NATURAL JOIN processing is done:

  #0  Item_field::Item_field (this=0x7fff90018e38, thd=0x7fff90000d78, context_arg=0x7fff90013ee0, f=0x7fff90008050)
 
  #1  in Field_iterator_table_ref::get_or_create_column_ref ()
 
  #2  in mark_common_columns ()
 
  #3  in store_top_level_join_columns ()
 
  #4  in setup_natural_join_row_types ()
 
  #5  in setup_tables ()
 
  #6  in setup_tables_and_check_access ()
 
  #7  in JOIN::prepare ()

Note that the Item_field constructor being called is:

Item_field::Item_field(THD *thd, Name_resolution_context *context_arg,
 
                       Field *f)

Field* is passed as parameter, and the created Item_field has fixed=1 from the start.

Then, execution enters Table_function_json_table::setup ().

In particular, this runs this code:

      f->change_charset(
 
                jc->m_explicit_cs ? jc->m_explicit_cs : m_json->collation);

Then, fix_fields is called for the ON expression:

  #2  0x0000555555d4621d in Item::fix_fields_if_needed_for_scalar (this=0x7fff90019228, thd=0x7fff90000d78, ref=0x7fff90016f80) at /home/psergey/dev-git2/10.6-hf-review6/sql/item.h:999
 
  #3  0x0000555555dcd6bf in Item::fix_fields_if_needed_for_bool (this=0x7fff90019228, thd=0x7fff90000d78, ref=0x7fff90016f80) at /home/psergey/dev-git2/10.6-hf-review6/sql/item.h:1003
 
  #4  0x0000555555dc99ea in setup_on_expr (thd=0x7fff90000d78, table=0x7fff90016f20, is_update=false) at /home/psergey/dev-git2/10.6-hf-review6/sql/sql_base.cc:8312
 
  #5  0x0000555555dc9e6a in setup_conds (thd=0x7fff90000d78, tables=0x7fff90015658, leaves=..., conds=0x7fff90018cb0) at /home/psergey/dev-git2/10.6-hf-review6/sql/sql_base.cc:8429
 
  #6  0x0000555555eac1f5 in setup_without_group (thd=0x7fff90000d78, ref_pointer_array=..., tables=0x7fff90015658, leaves=..., fields=..., all_fields=..., conds=0x7fff90018cb0, order=0x0, group=0x0, win_specs=..., win_funcs=..., hidden_group_fields=0x7fff90018b87, reserved=0x7fff9001419c) at /home/psergey/dev-git2/10.6-hf-review6/sql/sql_select.cc:699
 
  #7  0x0000555555eaf0f4 in JOIN::prepare (this=0x7fff90018898, tables_init=0x7fff90015658, conds_init=0x0, og_num=0, order_init=0x0, skip_order_by=false, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7fff90013e88, unit_arg=0x7fff90004f50) at /home/psergey/dev-git2/10.6-hf-review6/sql/sql_select.cc:1262

But the Item_field objects are already fixed, so attributes are not recomputed for them. As a result, we have Item_field object which has UTF8 charset, while the field it refers to is in koi8u:

(gdb) p item
 
  $75 = (Item_field *) 0x7fff90018e38
 
(gdb) p item->collation
 
  $76 = {collation = 0x5555579ca520 <my_charset_utf8mb4_general_ci>, derivation = DERIVATION_IMPLICIT, repertoire = MY_REPERTOIRE_UNICODE30}
 
(gdb) p item->field->m_collation
 
  $77 = {collation = 0x5555578b7718 <compiled_charsets+2392>, derivation = DERIVATION_IMPLICIT, repertoire = MY_REPERTOIRE_UNICODE30}
 
(gdb) p item->field->m_collation.collation->name
 
  $79 = 0x55555711a64e "koi8u_general_ci"

Comment by Alexey Botchkov [ 2021-04-16 ]

https://github.com/MariaDB/server/commit/c66589c99eac887efd28adeb0d9d6bc1b4473a87

Comment by Sergei Petrunia [ 2021-04-16 ]

Ok to push

Generated at Thu Feb 08 09:37:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.