revision-id: c67d69abb9b6d05a1c837dc92e1faad770741f55 (mariadb-10.6.0-42-gc67d69abb9b) parent(s): 370b310b1d67ad42df96b75c3876fdcf67a8694f author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-05-13 12:25:01 +0300 message: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe ... Mark the JSON_TABLE function as SBR-unsafe. It is not unsafe for the current implementation. But we still mark it as such in order to be future-proof and keep it possible to change JSON data representation in the future. --- mysql-test/suite/json/r/json_table_binlog.result | 26 ++++++++++++++++++++++++ mysql-test/suite/json/t/json_table_binlog.test | 25 +++++++++++++++++++++++ sql/json_table.h | 16 +++++++++++++++ sql/sql_yacc.yy | 2 ++ 4 files changed, 69 insertions(+) diff --git a/mysql-test/suite/json/r/json_table_binlog.result b/mysql-test/suite/json/r/json_table_binlog.result new file mode 100644 index 00000000000..472f7395648 --- /dev/null +++ b/mysql-test/suite/json/r/json_table_binlog.result @@ -0,0 +1,26 @@ +# +# MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +# +create table t1 (a int); +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave +set binlog_format='mixed'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +# This must show Annotate_rows, Write_rows_v1 events. Not the statement event +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +drop table t1; diff --git a/mysql-test/suite/json/t/json_table_binlog.test b/mysql-test/suite/json/t/json_table_binlog.test new file mode 100644 index 00000000000..dcc05fb855d --- /dev/null +++ b/mysql-test/suite/json/t/json_table_binlog.test @@ -0,0 +1,25 @@ +--source include/have_binlog_format_mixed.inc + +--echo # +--echo # MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +--echo # + +create table t1 (a int); + +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +set binlog_format='mixed'; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +let $binlog_file= LAST; + +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +--echo # This must show Annotate_rows, Write_rows_v1 events. Not the statement event +--source include/show_binlog_events.inc +drop table t1; diff --git a/sql/json_table.h b/sql/json_table.h index beae5405d25..3560b4ca137 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -183,6 +183,22 @@ class Json_table_column : public Sql_alloc into the TABLE_LIST::table_function. Then the ha_json_table instance is created based on it in the create_table_for_function(). + + == Replication: whether JSON_TABLE is deterministic == + + In sql_yacc.yy, we set BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION whenever + JSON_TABLE is used. The reasoning behind this is as follows: + + In the current MariaDB code, evaluation of JSON_TABLE is deterministic, + that is, for a given input string JSON_TABLE will always produce the same + set of rows in the same order. However one can think of JSON documents + that one can consider indentical which will produce different output. + In order to be feature-proof and withstand changes like: + - sorting JSON object members by name (like MySQL does) + - changing the way duplicate object members are handled + we mark the function as SBR-unsafe. + (If there is ever an issue with this, marking the function as SBR-safe + is a non-intrusive change we will always be able to make) */ class Table_function_json_table : public Sql_alloc diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index fe6112e9592..92769dc01f1 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11675,6 +11675,8 @@ table_function: new (thd->mem_root) Table_function_json_table($4); if (unlikely(!jt)) MYSQL_YYABORT; + /* See comment for class Table_function_json_table: */ + Lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION); Lex->json_table= jt; Select->parsing_place= NO_MATTER;