[#MDEV-11815] SP variables of temporal data types do not replicate correctly

SP variables in statement binlog are replicated by replacing them to a special function NAME_CONST().
Substitution is implemented in sp_head.cc in Item_splocal::append_for_log(), which uses a static function sp_get_item_value().

The implementation of sp_get_item_value() is not correct. It replaces values of temporal variables to string literals, so they are interpreted differently on the master and on the slave.

This mtr test demonstrates the problem:

--source include/have_binlog_format_statement.inc

--source include/master-slave.inc

connection master;

CREATE TABLE t1(a INT);

DELIMITER $$;

CREATE PROCEDURE p1()

BEGIN

  DECLARE a TIME DEFAULT '01:01:01';

  INSERT INTO t1 VALUES (a=10101);

END;

$$

DELIMITER ;$$

CALL p1;

SELECT * FROM t1;

sync_slave_with_master;

SELECT * FROM t1;

connection master;

DROP TABLE t1;

DROP PROCEDURE p1;

sync_slave_with_master;

--source include/rpl_end.inc

This tests returns the following results:

include/master-slave.inc

[connection master]

connection master;

CREATE TABLE t1(a INT);

CREATE PROCEDURE p1()

BEGIN

DECLARE a TIME DEFAULT '01:01:01';

INSERT INTO t1 VALUES (a=10101);

END;

$$

CALL p1;

SELECT * FROM t1;

connection slave;

SELECT * FROM t1;

connection master;

DROP TABLE t1;

DROP PROCEDURE p1;

connection slave;

include/rpl_end.inc

Notice, the INSERT statement inserted 1 on the master and 0 on the slave.

If I look inside mysql-test/var/mysqld.1/data/master-bin.000001, I can see that the INSERT query rewritten as follows:

INSERT INTO t1 VALUES ( NAME_CONST('a',_latin1'01:01:01' COLLATE 'latin1_swedish_ci')=10101)

This is wrong. It should be rewritten using the temporal literal syntax instead of the string literal syntax:

INSERT INTO t1 VALUES ( NAME_CONST('a',TIME'01:01:01')=10101)

[MDEV-11815] SP variables of temporal data types do not replicate correctly Created: 2017-01-16 Updated: 2017-09-15 Resolved: 2017-02-01
Status:	Closed
Project:	MariaDB Server
Component/s:	Replication, Stored routines
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s:	10.3.0

[MDEV-11815] SP variables of temporal data types do not replicate correctly Created: 2017-01-16 Updated: 2017-09-15 Resolved: 2017-02-01