Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.4, 11.7
-
None
Description
In the test case below, the query
DELETE HISTORY FROM t BEFORE SYSTEM_TIME @@timestamp |
when it is executed as a plain statement, causes an error 4079 ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION; but if it is executed as a prepared statement where @@timestamp comes as a parameter, then instead of an error it only produces a warning 1292 ER_TRUNCATED_WRONG_VALUE.
The discrepancy may seem minor, but it can cause a trouble when it happens in a replication context: being executed (with a warning), it gets written into the binary log – of course, not as a prepared statement with a parameter, but as a resulting statement – and thus it causes an error on the replica.
Note: Use of @@timestamp is not important here, any constant or variable with a fractional part leads to the same problem; I'm using @@timestamp to make the test case more realistic: while BEFORE SYSTEM_TIME 12.3 looks artificial, using @@timestamp there is a fair mistake which is easy to make, at least I myself did it a number of times.
create table t (a int) with system versioning; |
insert into t values (1),(2); |
|
|
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
|
delete history from t before system_time @@timestamp; |
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
|
execute immediate "delete history from t before system_time @@timestamp"; |
|
|
execute immediate "delete history from t before system_time ?" using @@timestamp; |
|
10.5 7f55c610608d45a0958f502a8b428c6d37f86692 |
delete history from t before system_time @@timestamp; |
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' |
execute immediate "delete history from t before system_time @@timestamp"; |
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' |
execute immediate "delete history from t before system_time ?" using @@timestamp; |
Warnings:
|
Warning 1292 Truncated incorrect datetime value: '1731785506.669454' |
Test case demonstrating the effect on replication:
--source include/have_binlog_format_mixed.inc
|
--source include/master-slave.inc
|
|
|
create table t (a int) with system versioning; |
insert into t values (1),(2); |
|
|
execute immediate "delete history from t before system_time ?" using @@timestamp; |
|
|
--sync_slave_with_master
|
|
|
# Cleanup
|
--connection master
|
drop table t; |
--source include/rpl_end.inc |
Last_Errno 4079
|
Last_Error Error 'Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'' on query. Default database: 'test'. Query: 'delete history from t before system_time 1731785600.849373'
|
SHOW BINLOG EVENTS IN 'master-bin.000001';
|
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 256 Server ver: 10.5.28-MariaDB-debug-log, Binlog ver: 4
|
master-bin.000001 256 Gtid_list 1 285 []
|
master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001
|
master-bin.000001 329 Gtid 1 371 GTID 0-1-1
|
master-bin.000001 371 Query 1 483 use `test`; create table t (a int) with system versioning
|
master-bin.000001 483 Gtid 1 525 BEGIN GTID 0-1-2
|
master-bin.000001 525 Query 1 624 use `test`; insert into t values (1),(2)
|
master-bin.000001 624 Query 1 701 COMMIT
|
master-bin.000001 701 Gtid 1 743 BEGIN GTID 0-1-3
|
master-bin.000001 743 Query 1 876 use `test`; delete history from t before system_time 1731785600.849373
|
master-bin.000001 876 Query 1 957 COMMIT
|