Debian Codegraph @

mysql-test/suite/innodb/t/alter_multivalue.test

raw
1 SET @@cte_max_recursion_depth := 10000;
2
3 DROP TABLE IF EXISTS test;
4
5 CREATE TABLE test (id INT, json_value JSON);
6
7 # An array of 10 elements with pseudo-random values in each row
8 INSERT INTO test (id, json_value)
9 WITH RECURSIVE cte AS (
10 SELECT 1 id
11 UNION ALL
12 SELECT 1 + id FROM cte WHERE id < @@cte_max_recursion_depth
13 )
14 SELECT id,
15 JSON_ARRAY(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 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED),
24 CAST(@seed := (@seed * 73127 + 36671) % 99053 AS UNSIGNED))
25 FROM cte
26 WHERE (@seed := 0) = 0
27 ORDER BY id;
28
29 SELECT id, CAST(json_value AS CHAR) json_value FROM test ORDER BY id LIMIT 20;
30
31 # Set a variable to a value found in the row with id=10
32 SET @random_value := 37947;
33
34 # Select id of rows in which this value is present
35 # 5 rows will be found, with id values of 10, 2486, 4962, 7438, 9915
36 SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
37
38 ALTER TABLE test ADD INDEX mvi ((CAST(json_value->'$' AS UNSIGNED ARRAY)));
39
40 EXPLAIN SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
41 SELECT id FROM test WHERE @random_value MEMBER OF (json_value->'$');
42
43 # Look at the rows 1311 and 1312 (the values are found experimentally)
44 SELECT id, CAST(json_value AS CHAR) json_value
45 FROM test
46 WHERE id IN (1311, 1312);
47
48 SELECT id FROM test WHERE 48176 MEMBER OF (json_value->'$');
49 SELECT id FROM test WHERE 74452 MEMBER OF (json_value->'$');
50
51 DROP TABLE test;