## Inserting with upsert ## Preamble --source include/xplugin_preamble.inc --source include/xplugin_create_user.inc ## Test starts here --write_file $MYSQL_TMP_DIR/crud_insert_upsert_with_not_null.xpl ## Test data -->quiet -->import crud_insert.macro -->import crud_find.macro -->varlet %DOC1_FOOBARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "foo@bar.com", "name":"foo"}' content_type:2}} -->varlet %DOC2_BLABARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "email": "bla@bar.com", "name":"bla"}' content_type:2}} -->varlet %DOC2_FOOORACOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "email": "foo@ora.com", "name":"bla"}' content_type:2}} -->varlet %DOC2_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "name":"foo"}' content_type:2}} -->varlet %DOC3_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": null, "name":"bla"}' content_type:2}} -->varlet %DOC3_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "name":"bla"}' content_type:2}} -->varlet %DOC3_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "foo@ora.com", "name":"foo"}' content_type:2}} -->varlet %DOC1_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "foo@ora.com", "name":"foo"}' content_type:2}} -->varlet %DOC1_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "foo@bar.com", "name":"bla"}' content_type:2}} -->varlet %DOC3_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "foo@bar.com", "name":"bla"}' content_type:2}} -->varlet %DOC1_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla"}' content_type:2}} -->varlet %DOC1_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla", "email": null}' content_type:2}} -->varlet %DOC1_BLABARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "bla@bar.com", "name":"foo"}' content_type:2}} -->varlet %DOC4_BLABLACOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "email": "bla@bla.com", "name":"bla"}' content_type:2}} -->varlet %DOC4_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "name":"foo"}' content_type:2}} -->title =Data model: TABLE -->sql CREATE TABLE xtest.xtable (doc JSON); -->endsql -->title -Insert two docs -->callmacro Insert_two TABLE xtable %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS -->callmacro Show xtable -->title -Upsert one doc -->callmacro Upsert_one TABLE xtable %DOC3_FOOORACOM_FOO% ER_X_BAD_INSERT_DATA -->title =Data model: DOCUMENT -->macro Setup %NAME% -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"} -->recvresult -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":true}} -->recvresult -->callmacro Insert_two DOCUMENT %NAME% %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS -->callmacro Show %NAME% -->endmacro -->macro Setup_without_data %NAME% -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"} -->recvresult -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":true}} -->recvresult -->endmacro -->macro Setup_drop_id %NAME% -->callmacro Setup %NAME% -->stmtsql ALTER TABLE xtest.%NAME% DROP COLUMN `_id`; -->recvresult -->endmacro -->title -Test0 - No matches -->callmacro Setup col0 -->callmacro Upsert_one DOCUMENT col0 %DOC3_FOOORACOM_FOO% ER_SUCCESS -->callmacro Show col0 -->title -Test1 - Match on _id -->callmacro Setup col1 -->callmacro Upsert_one DOCUMENT col1 %DOC1_FOOORACOM_FOO% ER_SUCCESS -->callmacro Show col1 -->title -Test2 - Match on secondary key only -->callmacro Setup col2 -->callmacro Upsert_one DOCUMENT col2 %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY -->callmacro Show col2 -->title -Test3 - Match on _id only, missing secondary index -->callmacro Setup col3 -->callmacro Upsert_one DOCUMENT col3 %DOC1_BLA% ER_X_DOC_ID_MISSING -->callmacro Show col3 -->title -Test4 - Match on _id and secondary, but from different rows -->callmacro Setup col4 -->callmacro Upsert_one DOCUMENT col4 %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY -->callmacro Show col4 -->title -Test5 - Match on _id and secondary key (other field value updated) -->callmacro Setup col5 -->callmacro Upsert_one DOCUMENT col5 %DOC1_FOOBARCOM_BLA% ER_SUCCESS -->callmacro Show col5 #BUG#27198820 #-->title -Test6 - New unique document, with null on secondary key #-->callmacro Setup col6 #-->callmacro Upsert_one DOCUMENT col5 %DOC3_NULL_BLA% ER_X_DOC_ID_MISSING #-->callmacro Show col5 -->title -Test7 - New unique document, with missing field on secondary key -->callmacro Setup col7 -->callmacro Upsert_one DOCUMENT col7 %DOC3_BLA% ER_X_DOC_ID_MISSING -->callmacro Show col7 #BUG#27198820 #-->title -Test8 - Match on _id only, null at secondary index #-->callmacro Setup col8 #-->callmacro Upsert_one DOCUMENT col8 %DOC1_NULL_BLA% ER_X_DOC_ID_MISSING #-->callmacro Show col8 -->title -Test8 - Match on _id only, missing field at secondary index -->callmacro Setup col8 -->callmacro Upsert_one DOCUMENT col8 %DOC1_BLA% ER_X_DOC_ID_MISSING -->callmacro Show col8 -->title -Test9 - Match on _id and null value at secondary index -->callmacro Setup_without_data col9 -->callmacro Insert_one DOCUMENT col9 %DOC1_FOOBARCOM_FOO% ER_SUCCESS -->callmacro Insert_one DOCUMENT col9 %DOC2_BLABARCOM_BLA% ER_SUCCESS -->callmacro Insert_one DOCUMENT col9 %DOC3_FOOORACOM_FOO% ER_SUCCESS -->callmacro Show col9 -->callmacro Upsert_one DOCUMENT col9 %DOC2_FOOORACOM_BLA% ER_X_DUPLICATE_ENTRY -->callmacro Show col9 EOF CREATE SCHEMA xtest; exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_insert_upsert_with_not_null.xpl 2>&1; ## Cleanup --source ../include/xplugin_cleanup.inc