[Commits] a97b533aebb: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe ...

psergey sergey at mariadb.com
Thu May 13 15:33:57 EEST 2021


revision-id: a97b533aebbdbdae72c1ada4161c894de01ec549 (mariadb-10.6.0-42-ga97b533aebb)
parent(s): 370b310b1d67ad42df96b75c3876fdcf67a8694f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2021-05-13 15:33:57 +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;


More information about the commits mailing list