Debian Codegraph @

mysql-test/suite/x/t/crud_use_of_index.test

raw
1 ## verify of index usage
2
3 ## 1. Verify usage array index
4 ## 1.1 with "cont_in (JSON_CONTAINS)
5 ## 1.2 with "overlaps" (JSON_OVERLAPS)
6
7 --source ../include/have_performance_schema_threads.inc
8 --source include/xplugin_preamble.inc
9 --source include/xplugin_create_user.inc
10
11 ## Test starts here
12 --write_file $MYSQL_TMP_DIR/array_index.xpl
13 -->import assert_select.macro
14 -->quiet
15
16 -->macro Insert %COLL% %VALUE% %ERROR%
17 Mysqlx.Crud.Insert {
18 collection {
19 name: "%COLL%"
20 schema: "xtest"
21 }
22 data_model: DOCUMENT
23 row {
24 field {
25 type: LITERAL
26 literal {
27 type: V_STRING
28 v_string {
29 value: %VALUE%
30 }
31 }
32 }
33 }
34 }
35 -->expecterror %ERROR%
36 -->recvresult
37 -->endmacro
38
39 -->macro Get_thread_id
40 -->stmtsql SELECT thread_id FROM performance_schema.threads WHERE processlist_id=connection_id()
41 -->recvtovar %THREAD_ID%
42 -->endmacro
43
44
45 -->macro Assert_index_use %TID%
46 callmacro Assert_select
47 Result
48 0
49 SELECT NO_INDEX_USED OR NO_GOOD_INDEX_USED as Result FROM performance_schema.events_statements_history WHERE THREAD_ID = %TID% ORDER BY event_id DESC LIMIT 1;
50 -->endmacro
51
52
53
54
55 -->callmacro Get_thread_id
56 #select %THREAD_ID%;
57
58 -->title =Create_collection
59 -->stmtadmin create_collection {"schema":"xtest", "name":"data"}
60 -->recvresult
61
62 -->title =Insert documents into collection 'data'
63 -->callmacro Insert data '{"_id":"001"}' ER_SUCCESS
64 -->callmacro Insert data '{"_id":"002","integer":[9, 10]}' ER_SUCCESS
65 -->callmacro Insert data '{"_id":"003","integer":[11, 12]}' ER_SUCCESS
66 -->callmacro Insert data '{"_id":"004", "integer":[13, 14, 15, 16]}' ER_SUCCESS
67
68 -->title =Create index on collection 'data'
69 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"data", "name":"idx", "unique":false, "fields":{"field":"$.integer", "type":"SIGNED", "array":true}}
70 -->recvresult
71
72 -->echo ## 1. Verify usage array index
73 -->echo ## 1.1 with "cont_in (JSON_CONTAINS)
74
75 Mysqlx.Crud.Find {
76 collection {
77 name: "data"
78 schema: "xtest"
79 }
80 data_model: DOCUMENT
81 criteria {
82 type: OPERATOR
83 operator {
84 name: "cont_in"
85 param {
86 type: LITERAL
87 literal {
88 type: V_SINT v_signed_int: 11
89 }
90 }
91 param {
92 type: IDENT
93 identifier {
94 document_path{type:MEMBER value:"integer"}
95 }
96 }
97 }
98 }
99 }
100 -->recvresult
101
102 -->callmacro Assert_index_use %THREAD_ID%
103
104
105
106 -->echo ## 1. Verify usage array index
107 -->echo ## 1.2 with "overlaps" (JSON_OVERLAPS)
108
109 Mysqlx.Crud.Find {
110 collection {
111 name: "data"
112 schema: "xtest"
113 }
114 data_model: DOCUMENT
115 criteria {
116 type: OPERATOR
117 operator {
118 name: "overlaps"
119 param {
120 type: LITERAL
121 literal {
122 type: V_OCTETS v_octets {value:'[8,14,15]'content_type:2}
123 }
124 }
125 param {
126 type: IDENT
127 identifier {
128 document_path{type:MEMBER value:"integer"}
129 }
130 }
131 }
132 }
133 }
134 -->recvresult
135
136 -->callmacro Assert_index_use %THREAD_ID%
137
138 EOF
139
140
141 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
142
143 --replace_regex /THREAD_ID = ([0-9]+)/THREAD_ID = XX/
144 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/array_index.xpl 2>&1
145
146 ## Cleanup
147 --source ../include/xplugin_cleanup.inc