Debian Codegraph @

mysql-test/suite/innodb/r/alter_multivalue.result

raw
1 SET @@cte_max_recursion_depth := 10000;
2 DROP TABLE IF EXISTS test;
3 Warnings:
4 Note 1051 Unknown table 'test.test'
5 CREATE TABLE test (id INT, json_value JSON);
6 INSERT INTO test (id, json_value)
7 WITH RECURSIVE cte AS (
8 SELECT 1 id
9 UNION ALL
10 SELECT 1 + id FROM cte WHERE id < @@cte_max_recursion_depth
11 )
12 SELECT id,
13 JSON_ARRAY(CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
14 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
15 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
16 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
17 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
18 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
19 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
20 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
21 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
22 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED))
23 FROM cte
24 WHERE (@seed := 0) = 0
25 ORDER BY id;
26 Warnings:
27 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
28 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
29 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
30 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
31 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
32 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
33 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
34 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
35 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
36 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
37 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
38 SELECT id, CAST(json_value AS CHAR) json_value FROM test ORDER BY id LIMIT 20;
39 id json_value
40 1 [36671, 15019, 31420, 53623, 15628, 90966, 5032, 29840, 8761, 27514]
41 2 [88413, 26706, 37385, 26766, 66673, 46376, 96862, 83168, 8807, 23554]
42 3 [37412, 20135, 25971, 74819, 34176, 18780, 90939, 11663, 70542, 79371]
43 4 [90200, 53748, 43627, 49276, 92689, 7437, 81200, 18880, 73717, 77364]
44 5 [21804, 41638, 9477, 86462, 91302, 10560, 40603, 98577, 94875, 90570]
45 6 [69269, 98520, 86862, 22414, 75258, 43757, 46698, 69142, 23320, 61863]
46 7 [42709, 76624, 89815, 30905, 33358, 28906, 54713, 85446, 84020, 8674]
47 8 [4857, 10452, 67127, 63279, 80156, 44155, 29662, 67151, 35373, 88000]
48 9 [36420, 84000, 31929, 31338, 389, 54863, 59613, 33992, 34620, 96837]
49 10 [37947, 17145, 85265, 22082, 65079, 67319, 38137, 43855, 81328, 68154]
50 11 [82534, 3093, 80483, 84911, 87010, 48433, 57594, 78602, 18588, 17028]
51 12 [47964, 33369, 40879, 74817, 86028, 51144, 785, 89679, 89986, 54944]
52 13 [39720, 10939, 20896, 7832, 42889, 65435, 49592, 22419, 44681, 61900]
53 14 [73977, 72208, 73763, 73404, 69856, 35067, 97116, 35462, 58805, 82017]
54 15 [34680, 27072, 57557, 47334, 23004, 33080, 5465, 95924, 34718, 32414]
55 16 [36959, 76359, 26495, 59856, 73366, 64514, 55665, 68091, 31971, 32029]
56 17 [14116, 66090, 6125, 21880, 52322, 67334, 45459, 98284, 64112, 77352]
57 18 [35757, 37716, 62871, 59293, 9860, 62104, 34882, 39829, 57542, 40012]
58 19 [67628, 50296, 95320, 43648, 95, 50026, 62577, 54456, 12824, 82568]
59 20 [13086, 25560, 32681, 48427, 15044, 76641, 45285, 52970, 7243, 59141]
60 SET @random_value := 37947;
61 SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
62 id
63 10
64 2486
65 4962
66 7438
67 9915
68 ALTER TABLE test ADD INDEX mvi ((CAST(json_value->'$' AS UNSIGNED ARRAY)));
69 EXPLAIN SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
70 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
71 1 SIMPLE test NULL ref mvi mvi 9 const 5 100.00 Using where
72 Warnings:
73 Note 1003 /* select#1 */ select `test`.`test`.`id` AS `id` from `test`.`test` where json'37947' member of (cast(json_extract(`json_value`,_utf8mb4'$') as unsigned array))
74 SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
75 id
76 10
77 2486
78 4962
79 7438
80 9915
81 SELECT id, CAST(json_value AS CHAR) json_value
82 FROM test
83 WHERE id IN (1311, 1312);
84 id json_value
85 1311 [48176, 84025, 77150, 23000, 37731, 70193, 14669, 91697, 71302, 87158]
86 1312 [74452, 39930, 14394, 89531, 63967, 72608, 4875, 39049, 73010, 82241]
87 SELECT id FROM test WHERE 48176 MEMBER OF (json_value->'$');
88 id
89 1311
90 3787
91 6263
92 8739
93 SELECT id FROM test WHERE 74452 MEMBER OF (json_value->'$');
94 id
95 1312
96 3788
97 6264
98 8740
99 DROP TABLE test;