Debian Codegraph @

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

raw
1 ## Inserting with upsert
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6 ## Test starts here
7
8 --write_file $MYSQL_TMP_DIR/crud_insert_upsert_with_not_null.xpl
9 ## Test data
10 -->quiet
11 -->import crud_insert.macro
12 -->import crud_find.macro
13
14 -->varlet %DOC1_FOOBARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
15 -->varlet %DOC2_BLABARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "email": "[email protected]", "name":"bla"}' content_type:2}}
16 -->varlet %DOC2_FOOORACOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "email": "[email protected]", "name":"bla"}' content_type:2}}
17 -->varlet %DOC2_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "2", "name":"foo"}' content_type:2}}
18 -->varlet %DOC3_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": null, "name":"bla"}' content_type:2}}
19 -->varlet %DOC3_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "name":"bla"}' content_type:2}}
20 -->varlet %DOC3_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "[email protected]", "name":"foo"}' content_type:2}}
21 -->varlet %DOC1_FOOORACOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
22 -->varlet %DOC1_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"bla"}' content_type:2}}
23 -->varlet %DOC3_FOOBARCOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "3", "email": "[email protected]", "name":"bla"}' content_type:2}}
24 -->varlet %DOC1_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla"}' content_type:2}}
25 -->varlet %DOC1_NULL_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "name":"bla", "email": null}' content_type:2}}
26 -->varlet %DOC1_BLABARCOM_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "1", "email": "[email protected]", "name":"foo"}' content_type:2}}
27 -->varlet %DOC4_BLABLACOM_BLA% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "email": "[email protected]", "name":"bla"}' content_type:2}}
28 -->varlet %DOC4_FOO% type: LITERAL literal{type: V_OCTETS v_octets{value:'{"_id": "4", "name":"foo"}' content_type:2}}
29
30
31 -->title =Data model: TABLE
32
33 -->sql
34 CREATE TABLE xtest.xtable (doc JSON);
35 -->endsql
36 -->title -Insert two docs
37 -->callmacro Insert_two TABLE xtable %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS
38 -->callmacro Show xtable
39 -->title -Upsert one doc
40 -->callmacro Upsert_one TABLE xtable %DOC3_FOOORACOM_FOO% ER_X_BAD_INSERT_DATA
41
42
43
44 -->title =Data model: DOCUMENT
45
46 -->macro Setup %NAME%
47 -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"}
48 -->recvresult
49 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":true}}
50 -->recvresult
51 -->callmacro Insert_two DOCUMENT %NAME% %DOC1_FOOBARCOM_FOO% %DOC2_BLABARCOM_BLA% ER_SUCCESS
52 -->callmacro Show %NAME%
53 -->endmacro
54
55 -->macro Setup_without_data %NAME%
56 -->stmtadmin create_collection {"schema":"xtest","name":"%NAME%"}
57 -->recvresult
58 -->stmtadmin create_collection_index {"schema":"xtest", "collection":"%NAME%", "name":"secondary", "unique":true, "fields":{"field":"$.email", "type":"TEXT(20)","required":true}}
59 -->recvresult
60 -->endmacro
61
62
63 -->macro Setup_drop_id %NAME%
64 -->callmacro Setup %NAME%
65 -->stmtsql ALTER TABLE xtest.%NAME% DROP COLUMN `_id`;
66 -->recvresult
67 -->endmacro
68
69
70 -->title -Test0 - No matches
71 -->callmacro Setup col0
72 -->callmacro Upsert_one DOCUMENT col0 %DOC3_FOOORACOM_FOO% ER_SUCCESS
73 -->callmacro Show col0
74
75 -->title -Test1 - Match on _id
76 -->callmacro Setup col1
77 -->callmacro Upsert_one DOCUMENT col1 %DOC1_FOOORACOM_FOO% ER_SUCCESS
78 -->callmacro Show col1
79
80 -->title -Test2 - Match on secondary key only
81 -->callmacro Setup col2
82 -->callmacro Upsert_one DOCUMENT col2 %DOC3_FOOBARCOM_BLA% ER_X_DUPLICATE_ENTRY
83 -->callmacro Show col2
84
85 -->title -Test3 - Match on _id only, missing secondary index
86 -->callmacro Setup col3
87 -->callmacro Upsert_one DOCUMENT col3 %DOC1_BLA% ER_X_DOC_ID_MISSING
88 -->callmacro Show col3
89
90 -->title -Test4 - Match on _id and secondary, but from different rows
91 -->callmacro Setup col4
92 -->callmacro Upsert_one DOCUMENT col4 %DOC1_BLABARCOM_FOO% ER_X_DUPLICATE_ENTRY
93 -->callmacro Show col4
94
95 -->title -Test5 - Match on _id and secondary key (other field value updated)
96 -->callmacro Setup col5
97 -->callmacro Upsert_one DOCUMENT col5 %DOC1_FOOBARCOM_BLA% ER_SUCCESS
98 -->callmacro Show col5
99
100 #BUG#27198820
101 #-->title -Test6 - New unique document, with null on secondary key
102 #-->callmacro Setup col6
103 #-->callmacro Upsert_one DOCUMENT col5 %DOC3_NULL_BLA% ER_X_DOC_ID_MISSING
104 #-->callmacro Show col5
105
106 -->title -Test7 - New unique document, with missing field on secondary key
107 -->callmacro Setup col7
108 -->callmacro Upsert_one DOCUMENT col7 %DOC3_BLA% ER_X_DOC_ID_MISSING
109 -->callmacro Show col7
110
111 #BUG#27198820
112 #-->title -Test8 - Match on _id only, null at secondary index
113 #-->callmacro Setup col8
114 #-->callmacro Upsert_one DOCUMENT col8 %DOC1_NULL_BLA% ER_X_DOC_ID_MISSING
115 #-->callmacro Show col8
116
117 -->title -Test8 - Match on _id only, missing field at secondary index
118 -->callmacro Setup col8
119 -->callmacro Upsert_one DOCUMENT col8 %DOC1_BLA% ER_X_DOC_ID_MISSING
120 -->callmacro Show col8
121
122 -->title -Test9 - Match on _id and null value at secondary index
123 -->callmacro Setup_without_data col9
124 -->callmacro Insert_one DOCUMENT col9 %DOC1_FOOBARCOM_FOO% ER_SUCCESS
125 -->callmacro Insert_one DOCUMENT col9 %DOC2_BLABARCOM_BLA% ER_SUCCESS
126 -->callmacro Insert_one DOCUMENT col9 %DOC3_FOOORACOM_FOO% ER_SUCCESS
127 -->callmacro Show col9
128 -->callmacro Upsert_one DOCUMENT col9 %DOC2_FOOORACOM_BLA% ER_X_DUPLICATE_ENTRY
129 -->callmacro Show col9
130
131 EOF
132
133 CREATE SCHEMA xtest;
134
135 exec $MYSQLXTEST
136 -ux_root --password=''
137 --file=$MYSQL_TMP_DIR/crud_insert_upsert_with_not_null.xpl 2>&1;
138
139 ## Cleanup
140 --source ../include/xplugin_cleanup.inc