Debian Codegraph @

mysql-test/suite/rpl/r/rpl_multi_valued_index.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 SET SESSION sql_log_bin= 0;
7 call mtr.add_suppression('.*Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.*');
8 SET SESSION sql_log_bin= 1;
9 include/sync_slave_sql_with_master.inc
10 CALL mtr.add_suppression(".*Column 1 of table .* cannot be converted from type.*");
11 CALL mtr.add_suppression(".*The replica coordinator and worker threads are stopped.*");
12 SET @@global.slave_rows_search_algorithms="TABLE_SCAN,INDEX_SCAN";
13 Warnings:
14 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
15 FLUSH LOGS;
16 [connection master]
17 #
18 # WL#8955: Add multi-valied index support
19 #
20 CREATE TABLE t1(pl INT AUTO_INCREMENT KEY, f1 JSON, KEY i1((CAST(f1->'$[*]' AS UNSIGNED ARRAY))));
21 insert into t1(f1) values
22 (cast('[1,3]' as json)), (cast(2 as json)), (cast('[3,3,4,4,7]' as json)),
23 (cast('[5,7]' as json)),
24 (cast('[8,4,3,5]' as json)), (cast('[5,6,7]' as json)),
25 (cast('[9,2,7]' as json)), (cast('[1,3]' as json)),
26 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)), (cast('[8,4,3,5]' as json)),
27 (cast('[9,2,7]' as json)), (cast('[9,2,7]' as json)),
28 (cast('[1,3]' as json)),
29 (cast('[3,3,4,4,7]' as json)), (cast(4 as json)),
30 (cast(7 as json)), (cast('[8,4,3,5]' as json)), (cast('[9,2,7]' as json)),
31 ('[98,99]');
32 include/sync_slave_sql_with_master.inc
33 [Connection Slave]
34 include/diff_tables.inc [master:t1, slave:t1]
35 analyze table t1;
36 Table Op Msg_type Msg_text
37 test.t1 analyze status OK
38 select * from t1 where 5 member of (f1->"$[*]");
39 pl f1
40 4 [5, 7]
41 5 [8, 4, 3, 5]
42 6 [5, 6, 7]
43 11 [8, 4, 3, 5]
44 18 [8, 4, 3, 5]
45 explain select * from t1 where 5 member of (f1->"$[*]");
46 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
47 1 SIMPLE t1 NULL ref i1 i1 9 const 5 100.00 Using where
48 Warnings:
49 Note 1003 /* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` where json'5' member of (cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array))
50 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
51 pl f1
52 3 [3, 3, 4, 4, 7]
53 5 [8, 4, 3, 5]
54 9 [3, 3, 4, 4, 7]
55 11 [8, 4, 3, 5]
56 15 [3, 3, 4, 4, 7]
57 18 [8, 4, 3, 5]
58 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[4,3]");
59 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
60 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
61 Warnings:
62 Note 1003 /* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[4, 3]')
63 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
64 pl f1
65 4 [5, 7]
66 6 [5, 6, 7]
67 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[5,7]");
68 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
69 1 SIMPLE t1 NULL range i1 i1 9 NULL 2 100.00 Using where
70 Warnings:
71 Note 1003 /* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[5, 7]')
72 [connection master]
73 select * from t1 force index(i1) where 99 member of (f1->"$[*]");
74 pl f1
75 20 [98, 99]
76 update t1 set f1=cast('[100,99]' as json) where 99 member of (f1->"$[*]");
77 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
78 pl f1
79 20 [100, 99]
80 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
81 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
82 1 SIMPLE t1 NULL range i1 i1 9 NULL 1 100.00 Using where
83 Warnings:
84 Note 1003 /* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[100]')
85 include/sync_slave_sql_with_master.inc
86 [Connection Slave]
87 include/diff_tables.inc [master:t1, slave:t1]
88 select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
89 pl f1
90 20 [100, 99]
91 explain select * from t1 force index(i1) where json_contains(f1->"$[*]", "[100]");
92 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
93 1 SIMPLE t1 NULL range i1 i1 9 NULL 1 100.00 Using where
94 Warnings:
95 Note 1003 /* select#1 */ select `test`.`t1`.`pl` AS `pl`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` FORCE INDEX (`i1`) where json_contains(cast(json_extract(`f1`,_utf8mb4'$[*]') as unsigned array),json'[100]')
96 [connection master]
97 DROP TABLE t1;
98 SET @start_row_image_value = @@session.binlog_row_image;
99 SET @@session.binlog_row_image = MINIMAL;
100 SET @save_btdt= @@global.binlog_transaction_dependency_tracking;
101 Warnings:
102 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
103 SET @@global.binlog_transaction_dependency_tracking = 'WRITESET';
104 Warnings:
105 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
106 FLUSH LOGS;
107 include/save_binlog_position.inc
108 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
109 KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
110 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
111 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
112 INSERT INTO t1(data) VALUES ('[33,44]');
113 INSERT INTO t1(data) VALUES ('[0,99]');
114 include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;1 4;1 5]
115 include/sync_slave_sql_with_master.inc
116 [Connection Slave]
117 include/diff_tables.inc [master:t1, slave:t1]
118 [Connection Master]
119 DROP TABLE t1;
120 FLUSH LOGS;
121 include/save_binlog_position.inc
122 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
123 data JSON NOT NULL,
124 UNIQUE KEY zips(id, (CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
125 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
126 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
127 INSERT INTO t1(data) VALUES ('[33,44]');
128 INSERT INTO t1(data) VALUES ('[0,99]');
129 include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;1 4;3 5]
130 include/sync_slave_sql_with_master.inc
131 [Connection Slave]
132 include/diff_tables.inc [master:t1, slave:t1]
133 [Connection Master]
134 DROP TABLE t1;
135 FLUSH LOGS;
136 include/save_binlog_position.inc
137 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, id INT DEFAULT 0,
138 data JSON NOT NULL,
139 UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY)), id));
140 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
141 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
142 INSERT INTO t1(data) VALUES ('[33,44]');
143 INSERT INTO t1(data) VALUES ('[0,99]');
144 include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;1 4;3 5]
145 include/sync_slave_sql_with_master.inc
146 [Connection Slave]
147 include/diff_tables.inc [master:t1, slave:t1]
148 [Connection Master]
149 DROP TABLE t1;
150 FLUSH LOGS;
151 include/save_binlog_position.inc
152 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON NOT NULL,
153 UNIQUE KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
154 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
155 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
156 INSERT INTO t1(data) VALUES ('[]');
157 INSERT INTO t1(data) VALUES ('[0,99]');
158 include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;1 4;3 5]
159 include/sync_slave_sql_with_master.inc
160 [Connection Slave]
161 include/diff_tables.inc [master:t1, slave:t1]
162 [Connection Master]
163 DROP TABLE t1;
164 FLUSH LOGS;
165 include/save_binlog_position.inc
166 CREATE TABLE t1 (pk INT AUTO_INCREMENT KEY, data JSON,
167 KEY zips((CAST(CAST(data AS JSON) AS UNSIGNED ARRAY))));
168 INSERT INTO t1(data) VALUES ('[0,1,2]'), ('[10,11,12]');
169 UPDATE t1 SET data = '[3,4,5]' WHERE JSON_CONTAINS(CAST(data AS JSON),'1');
170 INSERT INTO t1(data) VALUES ('[33,44]');
171 INSERT INTO t1(data) VALUES (NULL);
172 INSERT INTO t1(data) VALUES ('[0,99]');
173 include/include/assert_logical_timestamps.inc [0 1;1 2;2 3;1 4;1 5;1 6]
174 include/sync_slave_sql_with_master.inc
175 [Connection Slave]
176 include/diff_tables.inc [master:t1, slave:t1]
177 [connection master]
178 DROP TABLE t1;
179 CREATE TABLE t1 (
180 id INT KEY AUTO_INCREMENT,
181 j4 JSON ,
182 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS CHAR(10) ARRAY)))));
183 INSERT INTO t1(j4) VALUES('["foobar"]');
184 include/sync_slave_sql_with_master.inc
185 include/diff_tables.inc [master:t1, slave:t1]
186 [connection master]
187 DROP TABLE t1;
188 CREATE TABLE t1 (
189 id INT KEY AUTO_INCREMENT,
190 j4 JSON ,
191 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS BINARY(10) ARRAY)))));
192 INSERT INTO t1(j4) VALUES('["foobar"]');
193 include/sync_slave_sql_with_master.inc
194 include/diff_tables.inc [master:t1, slave:t1]
195 [connection master]
196 DROP TABLE t1;
197 CREATE TABLE t1 (
198 id INT KEY AUTO_INCREMENT,
199 j4 JSON ,
200 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS DECIMAL ARRAY)))));
201 INSERT INTO t1(j4) VALUES('[123]');
202 include/sync_slave_sql_with_master.inc
203 include/diff_tables.inc [master:t1, slave:t1]
204 [connection master]
205 DROP TABLE t1;
206 CREATE TABLE t1 (
207 id INT KEY AUTO_INCREMENT,
208 j4 JSON ,
209 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS DECIMAL(4,2) ARRAY)))));
210 INSERT INTO t1(j4) VALUES('[12.34]');
211 include/sync_slave_sql_with_master.inc
212 include/diff_tables.inc [master:t1, slave:t1]
213 [connection master]
214 DROP TABLE t1;
215 CREATE TABLE t1 (
216 id INT KEY AUTO_INCREMENT,
217 j4 JSON ,
218 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS UNSIGNED ARRAY)))));
219 INSERT INTO t1(j4) VALUES('[12]');
220 include/sync_slave_sql_with_master.inc
221 include/diff_tables.inc [master:t1, slave:t1]
222 [connection master]
223 DROP TABLE t1;
224 CREATE TABLE t1 (
225 id INT KEY AUTO_INCREMENT,
226 j4 JSON ,
227 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS SIGNED ARRAY)))));
228 INSERT INTO t1(j4) VALUES('[-1234]');
229 include/sync_slave_sql_with_master.inc
230 include/diff_tables.inc [master:t1, slave:t1]
231 [connection master]
232 DROP TABLE t1;
233 CREATE TABLE t1 (
234 id INT KEY AUTO_INCREMENT,
235 j4 JSON ,
236 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS DATE ARRAY)))));
237 INSERT INTO t1(j4) VALUES('["01-01-01"]');
238 include/sync_slave_sql_with_master.inc
239 include/diff_tables.inc [master:t1, slave:t1]
240 [connection master]
241 DROP TABLE t1;
242 CREATE TABLE t1 (
243 id INT KEY AUTO_INCREMENT,
244 j4 JSON ,
245 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS TIME(6) ARRAY)))));
246 INSERT INTO t1(j4) VALUES('["01:01:01.123456"]');
247 include/sync_slave_sql_with_master.inc
248 include/diff_tables.inc [master:t1, slave:t1]
249 [connection master]
250 DROP TABLE t1;
251 CREATE TABLE t1 (
252 id INT KEY AUTO_INCREMENT,
253 j4 JSON ,
254 KEY mv_idx_binary ((( CAST(j4->'$[*]' AS DATETIME(6) ARRAY)))));
255 INSERT INTO t1(j4) VALUES('["01-01-01 01:01:01.123456"]');
256 include/sync_slave_sql_with_master.inc
257 include/diff_tables.inc [master:t1, slave:t1]
258 [connection master]
259 DROP TABLE t1;
260 SET @@global.binlog_transaction_dependency_tracking = @save_btdt;
261 Warnings:
262 Warning 1287 '@@binlog_transaction_dependency_tracking' is deprecated and will be removed in a future release.
263 SET @@global.slave_rows_search_algorithms= "INTITAL_ROW_SEARCH_CONF";
264 Warnings:
265 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
266 FLUSH LOGS;
267 include/sync_slave_sql_with_master.inc
268 SET @@global.slave_rows_search_algorithms= "INTITAL_ROW_SEARCH_CONF";
269 Warnings:
270 Warning 1287 '@@slave_rows_search_algorithms' is deprecated and will be removed in a future release.
271 include/rpl_end.inc