########### ../t/create_index_crud.test ############# ## # ## This test runs aims index creation using crud calls # ## Test covers # ## - Create index with crud calls # ## - create with different data type(TEXT/INT) for doc field # ####################################################################### # create_collection_index # Creates an index on a given document field. Indexes on documents are # created by first creating a virtual generated column that extracts # the field from the document to an indexable column and then creating # the index on that column. JSN_EXTRACT() is used to extract the field # value and JSN_UNQUOTE() to remove the quoting from strings. # User must specify the datatype that the field must be extracted as. # The default is TEXT(64). # If a virtual column for the requested column already exists, it # will be reused for the newly created index. If an index of the same name # exists for the table, an error will be returned. # Indexes are created as follows: # ALTER TABLE table ADD COLUMN (v_field_name GENERATED ALWAYS AS # (JSN_EXTRACT(document, '$.field_name ')) VIRTUAL, ADD INDEX # table_field ON table.v_field_name # Arguments for create_collection_index # - schema name of table on which table to be created # - table name - # - name of the index( optional) # - unique - whether the index should be a unique index # - field_path : the document path to the field to be indexed # of doc column # - datatype - datatype of the index # - not_null - whether the generated column will be created as NOT NULL # Pre Conditions # User must have Index privileges on the schema # User must have Alter privileges on the table # The indexed field must be of a simple type. Multi-value indexes are currently not supported. # Errors # XXXX - column already exists # XXXX - invalid field name (if _id or document are specified) # XXXX - field name too long # XXXX - other normal index errors --source include/not_valgrind.inc --source include/xplugin_preamble.inc --source include/xplugin_create_user.inc call mtr.add_suppression("Error parsing message of type 12: args"); call mtr.add_suppression("Error handling message: args"); call mtr.add_suppression("Compute virtual column values failed"); ## Test starts here --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl -->sql #-- "Creating database for testcase" DROP DATABASE IF EXISTS xplugintest; DROP DATABASE IF EXISTS xplugintest_1; CREATE DATABASE xplugintest; CREATE DATABASE xplugintest_1; USE xplugintest; CREATE TABLE table1 ( _id INT , doc JSON ) ; -->endsql #-- "Create index on table2 in xplugintest " -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table2"} -->recvresult #-- "Index with unique(true), index column NOT NULL (true)" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col1_index", "unique":true, "fields":{"field":"$.col1", "type":"TEXT(13)", "required":true}} -->recv #-- Insert the data exceeding index length -->sql INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"01234567890123456789","_id":0}'); -->endsql #-- "Index with unique(false), index column NOT NULL (false) , datatype (INT)" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col2_index", "unique":false, "fields":{"field":"$.col2", "type":"INT", "required":false}} -->recv -->sql SHOW CREATE TABLE xplugintest.table2; INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val1","col2":101,"_id":1}'); #-- "non unique (duplicate) NOT allowed for col1" -->endsql -->expecterror ER_DUP_ENTRY -->sql INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val1","col2":102,"_id":2}'); #-- " null value NOT allowed for col1" -->endsql -->expecterror ER_BAD_NULL_ERROR -->sql INSERT INTO xplugintest.table2(doc) VALUES('{"col2":103,"_id":3}'); SELECT * FROM xplugintest.table2; #-- "non unique (duplicate) allowed for col2" INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val4","col2":102,"_id":4}'); #-- " null value allowed for col2" INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val5","_id":5}'); SELECT * FROM xplugintest.table2; SELECT _id,doc, CONCAT("Prefix",`$ix_t13_r_5E11219941D8F091BF6F252A48E0EEAA9D734FF9`) , `$ix_i_1C70785A292B9A2762EFA186DFCFB4F03C660E7F` FROM xplugintest.table2; #-- "Error as virual column data type mismatch while inserting into JSON doc" -->endsql -->expecterror ER_INVALID_JSON_VALUE_FOR_CAST -->sql INSERT INTO xplugintest.table2(doc) VALUES('{"col1":"val6","col2":"non_int_val","_id":6}'); -->endsql #-- "Index name already exists" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col2_index", "unique":false, "fields":{"field":"$.col3", "type":"INT", "required":false}} -->recv -->sql ALTER TABLE xplugintest.table2 ADD COLUMN `$ix_t13_bf29f74d5ecec695774cb5b3f1ff979e7643bf36` TEXT; -->endsql #-- "Column name exits - reuse" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col3_index", "unique":false, "fields":{"field":"$.col3", "type":"TEXT(13)", "required":false}} -->recv #-- "Invalid field name , if we include _id for index" #-- "No error now - Fix result when behavior is fixed" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"_id_index", "unique":false, "fields":{"field":"$._id", "type":"INT", "required":false}} -->recv #-- "Invalid path expression (.....col5) for document " -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"col5_index", "unique":false, "fields":{"field":"$.....col5", "type":"INT", "required":false}} -->recv -->sql SHOW CREATE TABLE xplugintest.table2; ALTER TABLE xplugintest.table2 DROP INDEX `col1_index`; ALTER TABLE xplugintest.table2 DROP COLUMN `$ix_t13_r_5E11219941D8F091BF6F252A48E0EEAA9D734FF9`; SHOW CREATE TABLE xplugintest.table2; SELECT * FROM xplugintest.table2; ALTER TABLE xplugintest.table2 ADD COLUMN `$col1_ix_t13` text GENERATED ALWAYS AS (LEFT(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.col1')), 13)) STORED ; SHOW CREATE TABLE xplugintest.table2; SELECT * FROM xplugintest.table2; -->endsql # #-- "Not allowed to create index called _id " -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"_id", "unique":false, "fields":{"field":"$.col9", "type":"INT", "required":false}} -->recv -->sql SHOW CREATE TABLE xplugintest.table2; -->endsql # vinay #-- "Missing args - index name" -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "unique":false, "fields":{"field":"$.col5", "type":"INT", "required":false}} -->recv # -->sql #DELETE FROM xplugintest.table2; TRUNCATE xplugintest.table2; INSERT INTO xplugintest.table2(doc) VALUES('{"F1":1, "F2":"abcd", "F3":1000, "_id":0}'); -->endsql -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"iX1", "unique":false, "fields":{"field":"$.F2", "type":"BLOB(767)", "required":false}} -->recv -->echo Create index on two fields #-- should create second virtual column for F2 as required is true now! (JIRA issue MYP-325) -->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}]} -->recv #-- testcase for JIRA issue MYP-290 -->sql #DELETE FROM xplugintest.table2; TRUNCATE xplugintest.table2; INSERT INTO xplugintest.table2(doc) VALUES('{"F2":"REQUIRED", "F3":"1", "KeyName_VeryLongKeyNameWithKeyNameLength_FiFtyFourAndEvenABitLonger": "abcdefgh", "_id":1}'); -->endsql -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table2", "name":"iXLong", "unique":false, "fields":{"field":"$.KeyName_VeryLongKeyNameWithKeyNameLength_FiFtyFourAndEvenABitLonger", "type":"BLOB(3000)", "required":false}} -->recv -->sql SHOW CREATE TABLE xplugintest.table2; SELECT * FROM xplugintest.table2; -->endsql #-- "Test different doc_path names " -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table3"} -->recv -->macro Create_index %INDEX_NAME% %DOC_PATH% -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table3", "name":"%INDEX_NAME%", "unique":true, "fields":{"field":"%DOC_PATH%", "type":"INT", "required":true}} -->recv -->endmacro -->callmacro Create_index IX -->callmacro Create_index IX0 $ -->callmacro Create_index IX1 $.`a#1` -->callmacro Create_index IX2 $.a'; drop datavase xplugintest; ' -->callmacro Create_index IX3 $.`a'1` -->callmacro Create_index IX4 $.`a\\'1` -->callmacro Create_index IX5 $.`a\\\\'1` -->callmacro Create_index IX6 $.a' drop database xplugintest; ' -->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 -->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 -->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 -->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 -->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` #-- Testing very long document path cases JIRA issue MYP-204 -->echo Document path 2k long -->callmacro Create_index IXLong1 $.a0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678 -->sql SHOW CREATE TABLE xplugintest.table3; -->endsql #-- Testing many multi-field indexes JIRA issue MYP-337 -->stmtadmin create_collection {"schema":"xplugintest","name":"col_myp337"} -->recvresult -->macro Create_3_field_index %NUM% -->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}]} -->recv -->endmacro -->quiet -->varlet %VALUE% 11 -->repeat 30 -->callmacro Create_3_field_index %VALUE% -->varinc %VALUE% 1 -->endrepeat -->noquiet -->sql ANALYZE TABLE xplugintest.col_myp337; SHOW INDEX from xplugintest.col_myp337 where Key_name rlike '^index_[12][0-9]*'; SHOW INDEX from xplugintest.col_myp337 where Key_name rlike '^index_[34][0-9]*'; -->endsql EOF --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1 --remove_file $MYSQL_TMP_DIR/mysqlx-in.xpl --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl ## Cleanup -->sql DROP DATABASE IF EXISTS xplugintest; DROP DATABASE IF EXISTS xplugintest_1; -->endsql EOF --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1 ## Cleanup --source ../include/xplugin_cleanup.inc