github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/update/update_index.result (about) 1 drop database if exists db1; 2 create database db1; 3 use db1; 4 drop table if exists t1; 5 create table t1(a int, b int, c int, unique key(a)); 6 insert into t1 values(1, 1, 1); 7 insert into t1 values(2, 2, 2); 8 insert into t1 values(3, 3, 3); 9 insert into t1 values(4, 4, 4); 10 insert into t1 values(5, 5, 5); 11 insert into t1 values(null, 7, 7); 12 update t1 set a = 6 where a = 1; 13 insert into t1 values(1, 1, 1); 14 update t1 set a = 7 where a is null; 15 insert into t1 values(7, 7, 7); 16 Duplicate entry '7' for key '__mo_index_idx_col' 17 update t1 set a = null where a = 7; 18 insert into t1 values(7, 7, 7); 19 select * from t1; 20 a b c 21 2 2 2 22 3 3 3 23 4 4 4 24 5 5 5 25 6 1 1 26 1 1 1 27 null 7 7 28 7 7 7 29 drop table if exists t1; 30 create table t1(a int, b int, c int, unique key(a, b)); 31 insert into t1 values(1, 1, 1); 32 insert into t1 values(2, 2, 2); 33 insert into t1 values(3, 3, 3); 34 insert into t1 values(4, 4, 4); 35 insert into t1 values(5, 5, 5); 36 insert into t1 values(null, 7, 7); 37 update t1 set a = 6 where a = 1; 38 insert into t1 values(1, 1, 1); 39 update t1 set a = 7 where a is null; 40 insert into t1 values(7, 7, 7); 41 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_index_idx_col' 42 update t1 set a = null where a = 7; 43 insert into t1 values(7, 7, 7); 44 insert into t1 values(null, 8, 8); 45 update t1 set a = 8, b = null where b = 8; 46 insert into t1 values(8, 8, 8); 47 select * from t1; 48 a b c 49 2 2 2 50 3 3 3 51 4 4 4 52 5 5 5 53 6 1 1 54 1 1 1 55 null 7 7 56 7 7 7 57 8 null 8 58 8 8 8 59 drop table if exists t1; 60 drop table if exists t2; 61 create table t1(a int, b int, c int, unique key(a, b)); 62 create table t2(a int, b int, c int, unique key(a)); 63 insert into t1 values(1, 1, 1); 64 insert into t1 values(2, 2, 2); 65 insert into t1 values(3, 3, 3); 66 insert into t1 values(4, 4, 4); 67 insert into t1 values(5, 5, 5); 68 insert into t1 values(null, 7, 7); 69 insert into t2 values(1, 1, 1); 70 insert into t2 values(2, 2, 2); 71 insert into t2 values(3, 3, 3); 72 insert into t2 values(4, 4, 4); 73 insert into t2 values(5, 5, 5); 74 insert into t2 values(null, 7, 7); 75 update t1, t2 set t1.a = 6, t2.a = 6 where t1.a = 1 and t1.a = t2.a; 76 insert into t1 values(1, 1, 1); 77 insert into t2 values(1, 1, 1); 78 update t1, t2 set t1.a = 7, t2.a = 7 where t1.a is null and t2.a is null; 79 insert into t1 values(7, 7, 7); 80 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_index_idx_col' 81 insert into t2 values(7, 7, 7); 82 Duplicate entry '7' for key '__mo_index_idx_col' 83 update t1, t2 set t1.a = null, t2.a = null where t1.a = 7 and t1.a = t2.a; 84 insert into t1 values(7, 7, 7); 85 insert into t1 values(null, 8, 8); 86 insert into t2 values(7, 7, 7); 87 insert into t2 values(null, 8, 8); 88 update t1, t2 set t1.a = 8, t1.b = null, t2.a = 8, t2.b = null where t1.b = 8 and t1.b = t2.b; 89 insert into t1 values(8, 8, 8); 90 insert into t2 values(8, 8, 8); 91 Duplicate entry '8' for key '__mo_index_idx_col' 92 select * from t1; 93 a b c 94 2 2 2 95 3 3 3 96 4 4 4 97 5 5 5 98 6 1 1 99 1 1 1 100 null 7 7 101 7 7 7 102 8 null 8 103 8 8 8 104 select * from t2; 105 a b c 106 2 2 2 107 3 3 3 108 4 4 4 109 5 5 5 110 6 1 1 111 1 1 1 112 null 7 7 113 7 7 7 114 8 null 8 115 drop table if exists t1; 116 create table t1(a int, b int, c int, unique key(a), primary key(c)); 117 insert into t1 values(1, 1, 1); 118 insert into t1 values(2, 2, 2); 119 insert into t1 values(3, 3, 3); 120 insert into t1 values(4, 4, 4); 121 insert into t1 values(5, 5, 5); 122 insert into t1 values(null, 7, 7); 123 update t1 set a = 6 where a = 1; 124 update t1 set a = 7 where a is null; 125 insert into t1 values(7, 7, 8); 126 Duplicate entry '7' for key '__mo_index_idx_col' 127 update t1 set a = null where a = 7; 128 insert into t1 values(7, 7, 9); 129 select * from t1; 130 a b c 131 2 2 2 132 3 3 3 133 4 4 4 134 5 5 5 135 6 1 1 136 null 7 7 137 7 7 9 138 drop table if exists t1; 139 create table t1(a int, b int, c int, unique key(a, b), primary key(b, c)); 140 insert into t1 values(1, 1, 1); 141 insert into t1 values(2, 2, 2); 142 insert into t1 values(3, 3, 3); 143 insert into t1 values(4, 4, 4); 144 insert into t1 values(5, 5, 5); 145 insert into t1 values(null, 7, 7); 146 update t1 set a = 6 where a = 1; 147 update t1 set a = 7 where a is null; 148 insert into t1 values(7, 7, 8); 149 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_index_idx_col' 150 update t1 set a = null where a = 7; 151 insert into t1 values(7, 7, 9); 152 insert into t1 values(null, 8, 10); 153 update t1 set a = 8, b = null where b = 8; 154 constraint violation: Column 'b' cannot be null 155 insert into t1 values(8, 8, 11); 156 select * from t1; 157 a b c 158 2 2 2 159 3 3 3 160 4 4 4 161 5 5 5 162 6 1 1 163 null 7 7 164 7 7 9 165 null 8 10 166 8 8 11 167 drop table if exists t1; 168 create table t1(a int unique, b int, c int); 169 insert into t1 values(1, 1, 1); 170 insert into t1 values(2, 2, 2); 171 insert into t1 values(3, 3, 3); 172 insert into t1 values(4, 4, 4); 173 insert into t1 values(5, 5, 5); 174 insert into t1 values(null, 7, 7); 175 update t1 set a = 6 where a = 1; 176 insert into t1 values(1, 1, 1); 177 update t1 set a = 7 where a is null; 178 insert into t1 values(7, 7, 7); 179 Duplicate entry '7' for key '__mo_index_idx_col' 180 update t1 set a = null where a = 7; 181 insert into t1 values(7, 7, 7); 182 select * from t1; 183 a b c 184 2 2 2 185 3 3 3 186 4 4 4 187 5 5 5 188 6 1 1 189 1 1 1 190 null 7 7 191 7 7 7 192 drop table if exists t1; 193 create table t1(a int unique key, b int, c int); 194 insert into t1 values(1, 1, 1); 195 insert into t1 values(2, 2, 2); 196 insert into t1 values(3, 3, 3); 197 insert into t1 values(4, 4, 4); 198 insert into t1 values(5, 5, 5); 199 insert into t1 values(null, 7, 7); 200 update t1 set a = 6 where a = 1; 201 insert into t1 values(1, 1, 1); 202 update t1 set a = 7 where a is null; 203 insert into t1 values(7, 7, 7); 204 Duplicate entry '7' for key '__mo_index_idx_col' 205 update t1 set a = null where a = 7; 206 insert into t1 values(7, 7, 7); 207 select * from t1; 208 a b c 209 2 2 2 210 3 3 3 211 4 4 4 212 5 5 5 213 6 1 1 214 1 1 1 215 null 7 7 216 7 7 7 217 drop table t1; 218 drop table if exists t2; 219 create table t2(a int primary key, b int unique, c int); 220 insert into t2 values(1, 1, 1); 221 insert into t2 values(2, 2, 2); 222 insert into t2 values(3, 3, 3); 223 insert into t2 values(4, 4, 4); 224 insert into t2 values(5, 5, 5); 225 update t2 set a = 6 where b = 5; 226 select * from t2; 227 a b c 228 1 1 1 229 2 2 2 230 3 3 3 231 4 4 4 232 6 5 5 233 drop table t2; 234 235 create table t3(col1 int primary key, col2 int); 236 create unique index key1 on t3(col2,col1); 237 insert into t3 values(1 , 100); 238 insert into t3 values(2, 200); 239 insert into t3 values(3 , 300); 240 update t3 set col2 = 300 where col1 = 3; 241 update t3 set col2 = 100 where col1 = 1; 242 update t3 set col2 = 200 where col1 = 2;