Debian Codegraph @

mysql-test/suite/rpl/t/rpl_cast.test

raw
1 #####################################################################
2 # #
3 # The aim of this testcase is a sanity check to test the newly #
4 # added CAST functionalities in WL#529, in a replication set up, #
5 # to ensure that replication does not break due to any reason. #
6 # #
7 # Creation date: 2019-03-14 #
8 # Author: Deepa Dixit #
9 # #
10 #####################################################################
11
12 --source include/master-slave.inc
13
14 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
15
16 --echo # Create table with different datatypes
17
18 --connection master
19
20 CREATE TABLE wl529 (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, bt BLOB,
21 vt VARCHAR(25), tt TEXT, ft FLOAT, it INT, itu INT
22 UNSIGNED, jt JSON, dt DOUBLE, dc DECIMAL(16,8),
23 dat DATE, yt YEAR);
24
25 INSERT INTO wl529(bt, vt, tt, ft, it, itu, jt, dt, dc, dat, yt) VALUES
26 ('string', '837498713.8472812', '8240921.0982421', 09385.82471, 1234, 12984, "3980921", 98453.8324732, 4824.89247, '2011-04-03', '2012'),
27 ('varchar', '-494804.48724', '48744.84783', 1234.1223, -1234, 938432, "9218421", 384734.84732, 589239.948091, '2010-11-11', '2010'),
28 ('text', '7848094.874821', '48973847.8743', 3875328.9824721, 3473, 3984013, "1", -8478.89247, -402984.8742, '1980-01-01', '1980'),
29 ('blob', '908249214.8742', '7634109.9843040', -9824821.274921, -98347, 3814031, "8742.43", -384731.837438, -4824.89247, '1970-01-01', '1970'),
30 ('json', '4827410.7404', '-87484.874897447', -4873284.98438, 5398432, 8549, "8392", -892472.8748931, 49852.53985, '2020-02-20', '2020');
31
32 CREATE TABLE wl529_insert (f1 FLOAT, f2 VARCHAR(15), f3 TEXT, f4 BLOB, f5 FLOAT,
33 f6 FLOAT, f7 DOUBLE, f8 DOUBLE, f9 DECIMAL(65,30),
34 f10 DECIMAL, f11 DECIMAL(16, 10));
35
36 INSERT INTO wl529_insert SELECT CAST(bt AS FLOAT), CAST(vt AS FLOAT), CAST(tt AS FLOAT), CAST(ft AS FLOAT), CAST(it AS FLOAT), CAST(itu AS FLOAT), CAST(jt AS FLOAT), CAST(dt AS FLOAT), CAST(dc AS FLOAT), CAST(dat AS FLOAT), CAST(yt AS FLOAT) FROM wl529;
37 INSERT INTO wl529_insert SELECT CAST(bt AS DOUBLE), CAST(vt AS DOUBLE), CAST(tt AS DOUBLE), CAST(ft AS DOUBLE), CAST(it AS DOUBLE), CAST(itu AS DOUBLE), CAST(jt AS DOUBLE), CAST(dt AS DOUBLE), CAST(dc AS DOUBLE), CAST(dat AS DOUBLE), CAST(yt AS DOUBLE) FROM wl529;
38 INSERT INTO wl529_insert SELECT CAST(bt AS REAL), CAST(vt AS REAL), CAST(tt AS REAL), CAST(ft AS REAL), CAST(it AS REAL), CAST(itu AS REAL), CAST(jt AS REAL), CAST(dt AS REAL), CAST(dc AS REAL), CAST(dat AS REAL), CAST(yt AS REAL) FROM wl529;
39
40 --source include/sync_slave_sql_with_master.inc
41 --echo [Connection Slave]
42
43 --echo # Check whether table was created and values were inserted on slave
44
45 SELECT * FROM wl529;
46 SELECT * FROM wl529_insert;
47
48 --let $diff_tables = master:wl529_insert, slave:wl529_insert
49 --source include/diff_tables.inc
50
51 --echo [Connection Master]
52 --connection master
53
54 --echo # Update values inside the table
55
56 UPDATE wl529 SET dt = CAST(vt AS DOUBLE) WHERE id = 2;
57 UPDATE wl529 SET dt = CAST(vt AS REAL) WHERE id = 3;
58
59
60 --source include/sync_slave_sql_with_master.inc
61 --echo [Connection Slave]
62
63 --echo # Check whether rows are updated
64
65 SELECT * FROM wl529 WHERE id < 4;
66
67
68 --let $diff_tables = master:wl529, slave:wl529
69 --source include/diff_tables.inc
70
71 --echo [Connection Master]
72 --connection master
73
74 DELETE FROM wl529 WHERE CAST(vt AS DOUBLE) = dt;
75
76 --source include/sync_slave_sql_with_master.inc
77 --echo [Connection Slave]
78
79 --echo # Check whether the rows are deleted
80
81 SELECT * FROM wl529;
82
83 --let $diff_tables = master:wl529, slave:wl529
84 --source include/diff_tables.inc
85
86
87 --echo [Connection Master]
88 --connection master
89
90 --echo # Drop tables and clean up
91
92 DROP TABLE wl529, wl529_insert;
93
94 --source include/sync_slave_sql_with_master.inc
95 --echo [Connection Slave]
96
97 --source include/rpl_end.inc
98