MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe ...bb-10.6-mdev25154

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.

4 files changed, 69 insertions, 0 deletions

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 @@ public:
   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;