Debian Codegraph @

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

raw
1 ########### ../t/create_index_crud.test #############
2 ## #
3 ## This test runs aims index creation using crud calls #
4 ## Test covers #
5 ## - Create index with crud calls #
6 ## - create with different data type(TEXT/INT) for doc field #
7 #######################################################################
8
9
10
11 # create_collection_index
12
13 # Creates an index on a given document field. Indexes on documents are
14 # created by first creating a virtual generated column that extracts
15 # the field from the document to an indexable column and then creating
16 # the index on that column. JSN_EXTRACT() is used to extract the field
17 # value and JSN_UNQUOTE() to remove the quoting from strings.
18 # User must specify the datatype that the field must be extracted as.
19 # The default is TEXT(64).
20
21 # If a virtual column for the requested column already exists, it
22 # will be reused for the newly created index. If an index of the same name
23 # exists for the table, an error will be returned.
24
25 # Indexes are created as follows:
26
27 # ALTER TABLE table ADD COLUMN (v_field_name GENERATED ALWAYS AS
28 # (JSN_EXTRACT(document, '$.field_name ')) VIRTUAL, ADD INDEX
29 # table_field ON table.v_field_name
30
31 # Arguments for create_collection_index
32 # - schema name of table on which table to be created
33 # - table name -
34 # - name of the index( optional)
35 # - unique - whether the index should be a unique index
36 # - field_path : the document path to the field to be indexed
37 # of doc column
38 # - datatype - datatype of the index
39 # - not_null - whether the generated column will be created as NOT NULL
40
41 # Pre Conditions
42 # User must have Index privileges on the schema
43 # User must have Alter privileges on the table
44 # The indexed field must be of a simple type. Multi-value indexes are currently not supported.
45
46 # Errors
47 # XXXX - column already exists
48 # XXXX - invalid field name (if _id or document are specified)
49 # XXXX - field name too long
50 # XXXX - other normal index errors
51
52 --source include/not_valgrind.inc
53 --source include/xplugin_preamble.inc
54 --source include/xplugin_create_user.inc
55 call mtr.add_suppression("Error parsing message of type 12: args");
56 call mtr.add_suppression("Error handling message: args");
57 call mtr.add_suppression("Compute virtual column values failed");
58
59 ## Test starts here
60 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
61 -->sql
62 #-- "Creating database for testcase"
63 DROP DATABASE IF EXISTS xplugintest;
64 DROP DATABASE IF EXISTS xplugintest_1;
65 CREATE DATABASE xplugintest;
66 CREATE DATABASE xplugintest_1;
67 USE xplugintest;
68 CREATE TABLE table1 ( _id INT , doc JSON ) ;
69 -->endsql
70
71 #-- "Create index on table2 in xplugintest "
72
73 -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table2"}
74 -->recvresult
75
76 #-- "Index with unique(true), index column NOT NULL (true)"
77 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col1_index", "unique":true, "fields":{"field":"$.col1", "type":"TEXT(13)", "required":true}}
78 -->recv
79
80 #-- Insert the data exceeding index length
81 -->sql
82 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"01234567890123456789","_id":0}');
83 -->endsql
84
85 #-- "Index with unique(false), index column NOT NULL (false) , datatype (INT)"
86 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col2_index", "unique":false, "fields":{"field":"$.col2", "type":"INT", "required":false}}
87 -->recv
88
89
90
91 -->sql
92 SHOW CREATE TABLE xplugintest.table2;
93 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val1","col2":101,"_id":1}');
94 #-- "non unique (duplicate) NOT allowed for col1"
95 -->endsql
96 -->expecterror ER_DUP_ENTRY
97 -->sql
98 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val1","col2":102,"_id":2}');
99 #-- " null value NOT allowed for col1"
100 -->endsql
101 -->expecterror ER_BAD_NULL_ERROR
102 -->sql
103 INSERT INTO xplugintest.table2(doc) VALUES('{"col2":103,"_id":3}');
104 SELECT * FROM xplugintest.table2;
105 #-- "non unique (duplicate) allowed for col2"
106 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val4","col2":102,"_id":4}');
107 #-- " null value allowed for col2"
108 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val5","_id":5}');
109 SELECT * FROM xplugintest.table2;
110 SELECT _id,doc, CONCAT("Prefix",`$ix_t13_r_5E11219941D8F091BF6F252A48E0EEAA9D734FF9`) , `$ix_i_1C70785A292B9A2762EFA186DFCFB4F03C660E7F`
111 FROM xplugintest.table2;
112 #-- "Error as virual column data type mismatch while inserting into JSON doc"
113 -->endsql
114 -->expecterror ER_INVALID_JSON_VALUE_FOR_CAST
115 -->sql
116 INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val6","col2":"non_int_val","_id":6}');
117 -->endsql
118
119 #-- "Index name already exists"
120 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col2_index", "unique":false, "fields":{"field":"$.col3", "type":"INT", "required":false}}
121 -->recv
122
123 -->sql
124 ALTER TABLE xplugintest.table2 ADD COLUMN `$ix_t13_bf29f74d5ecec695774cb5b3f1ff979e7643bf36` TEXT;
125 -->endsql
126 #-- "Column name exits - reuse"
127 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col3_index", "unique":false, "fields":{"field":"$.col3", "type":"TEXT(13)", "required":false}}
128 -->recv
129
130 #-- "Invalid field name , if we include _id for index"
131 #-- "No error now - Fix result when behavior is fixed"
132 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"_id_index", "unique":false, "fields":{"field":"$._id", "type":"INT", "required":false}}
133 -->recv
134
135 #-- "Invalid path expression (.....col5) for document "
136 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col5_index", "unique":false, "fields":{"field":"$.....col5", "type":"INT", "required":false}}
137 -->recv
138
139 -->sql
140 SHOW CREATE TABLE xplugintest.table2;
141 ALTER TABLE xplugintest.table2 DROP INDEX `col1_index`;
142 ALTER TABLE xplugintest.table2 DROP COLUMN `$ix_t13_r_5E11219941D8F091BF6F252A48E0EEAA9D734FF9`;
143 SHOW CREATE TABLE xplugintest.table2;
144 SELECT * FROM xplugintest.table2;
145 ALTER TABLE xplugintest.table2 ADD COLUMN `$col1_ix_t13` text
146 GENERATED ALWAYS AS (LEFT(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.col1')), 13)) STORED ;
147 SHOW CREATE TABLE xplugintest.table2;
148 SELECT * FROM xplugintest.table2;
149 -->endsql
150
151 #
152 #-- "Not allowed to create index called _id "
153 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"_id", "unique":false, "fields":{"field":"$.col9", "type":"INT", "required":false}}
154 -->recv
155 -->sql
156 SHOW CREATE TABLE xplugintest.table2;
157 -->endsql
158 # vinay
159 #-- "Missing args - index name"
160 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "unique":false, "fields":{"field":"$.col5", "type":"INT", "required":false}}
161 -->recv
162
163 #
164 -->sql
165 #DELETE FROM xplugintest.table2;
166 TRUNCATE xplugintest.table2;
167 INSERT INTO xplugintest.table2(doc) VALUES('{"F1":1, "F2":"abcd", "F3":1000, "_id":0}');
168 -->endsql
169 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"iX1", "unique":false, "fields":{"field":"$.F2", "type":"BLOB(767)", "required":false}}
170 -->recv
171
172 -->echo Create index on two fields
173 #-- should create second virtual column for F2 as required is true now! (JIRA issue MYP-325)
174 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"iX3", "unique":true, "fields":[{"field":"$.F3", "type":"BIGINT", "required":true}, {"field":"$.F2", "type":"BLOB(767)", "required":true}]}
175 -->recv
176
177 #-- testcase for JIRA issue MYP-290
178 -->sql
179 #DELETE FROM xplugintest.table2;
180 TRUNCATE xplugintest.table2;
181 INSERT INTO xplugintest.table2(doc) VALUES('{"F2":"REQUIRED", "F3":"1", "KeyName_VeryLongKeyNameWithKeyNameLength_FiFtyFourAndEvenABitLonger": "abcdefgh", "_id":1}');
182 -->endsql
183 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"iXLong", "unique":false, "fields":{"field":"$.KeyName_VeryLongKeyNameWithKeyNameLength_FiFtyFourAndEvenABitLonger", "type":"BLOB(3000)", "required":false}}
184 -->recv
185
186 -->sql
187 SHOW CREATE TABLE xplugintest.table2;
188 SELECT * FROM xplugintest.table2;
189 -->endsql
190
191
192 #-- "Test different doc_path names "
193
194 -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table3"}
195 -->recv
196
197 -->macro Create_index %INDEX_NAME% %DOC_PATH%
198 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table3", "name":"%INDEX_NAME%", "unique":true, "fields":{"field":"%DOC_PATH%", "type":"INT", "required":true}}
199
200 -->recv
201
202 -->endmacro
203
204 -->callmacro Create_index IX
205 -->callmacro Create_index IX0 $
206 -->callmacro Create_index IX1 $.`a#1`
207 -->callmacro Create_index IX2 $.a'; drop datavase xplugintest; '
208 -->callmacro Create_index IX3 $.`a'1`
209 -->callmacro Create_index IX4 $.`a\\'1`
210 -->callmacro Create_index IX5 $.`a\\\\'1`
211 -->callmacro Create_index IX6 $.a' drop database xplugintest; '
212 -->callmacro Create_index IX7 $.b)) virtual not null; drop database xplugintest; alter table table3 add column x int GENERATED ALWAYS AS (JSON_EXTRACT(doc, $.c
213 -->callmacro Create_index IX8 $.b')) virtual not null; drop database xplugintest; alter table table3 add column x int GENERATED ALWAYS AS (JSON_EXTRACT(doc, '$.c
214 -->callmacro Create_index IX9 $.b\\')) virtual not null; drop database xplugintest; alter table table3 add column x int GENERATED ALWAYS AS (JSON_EXTRACT(doc, \\'$.c
215 -->callmacro Create_index IX10 $.b\\\\')) virtual not null; drop database xplugintest; alter table table3 add column x int GENERATED ALWAYS AS (JSON_EXTRACT(doc, \\\\'$.c
216 -->callmacro Create_index IX11 $.`b))` virtual not null; drop database xplugintest; alter table table3 add column x int GENERATED ALWAYS AS (JSON_EXTRACT(doc, $.`c`
217
218 #-- Testing very long document path cases JIRA issue MYP-204
219
220 -->echo Document path 2k long
221 -->callmacro Create_index IXLong1 $.a0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678
222
223 -->sql
224 SHOW CREATE TABLE xplugintest.table3;
225 -->endsql
226
227 #-- Testing many multi-field indexes JIRA issue MYP-337
228
229 -->stmtadmin create_collection {"schema":"xplugintest","name":"col_myp337"}
230 -->recvresult
231
232
233 -->macro Create_3_field_index %NUM%
234 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"col_myp337", "name":"index_%NUM%", "unique":true, "fields":[{"field":"$.F1", "type":"Text(%NUM%)", "required":true},{"field":"$.F1", "type":"Text(%NUM%)", "required":false}, {"field":"$.F2", "type":"INT(%NUM%)", "required":true},{"field":"$.F2", "type":"INT(%NUM%)", "required":false}, {"field":"$.F1", "type":"BLOB(%NUM%)", "required":true}, {"field":"$.F1", "type":"BLOB(%NUM%)", "required":false}]}
235 -->recv
236 -->endmacro
237 -->quiet
238
239 -->varlet %VALUE% 11
240 -->repeat 30
241 -->callmacro Create_3_field_index %VALUE%
242 -->varinc %VALUE% 1
243 -->endrepeat
244
245 -->noquiet
246
247 -->sql
248 ANALYZE TABLE xplugintest.col_myp337;
249 SHOW INDEX from xplugintest.col_myp337 where Key_name rlike '^index_[12][0-9]*';
250 SHOW INDEX from xplugintest.col_myp337 where Key_name rlike '^index_[34][0-9]*';
251 -->endsql
252
253 EOF
254
255 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
256 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl
257
258
259 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
260 ## Cleanup
261 -->sql
262 DROP DATABASE IF EXISTS xplugintest;
263 DROP DATABASE IF EXISTS xplugintest_1;
264 -->endsql
265 EOF
266
267 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
268
269 ## Cleanup
270 --source ../include/xplugin_cleanup.inc