github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/secondary_index_master.result (about) 1 SET GLOBAL experimental_master_index = 1; 2 drop table if exists t1; 3 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 4 insert into t1 values("Congress","Lane", "1"); 5 insert into t1 values("Juniper","Way", "2"); 6 insert into t1 values("Nightingale","Lane", "3"); 7 select * from t1; 8 a b c 9 Congress Lane 1 10 Juniper Way 2 11 Nightingale Lane 3 12 create index idx1 using master on t1(a,b); 13 insert into t1 values("Changing","Expanse", "4"); 14 update t1 set a = "Altering" where c = "4"; 15 delete from t1 where c = "2"; 16 select * from t1 where a = "Congress" and b="Lane"; 17 a b c 18 Congress Lane 1 19 create table t2(a varchar(30), b bigint, c varchar(30) primary key); 20 insert into t2 values("Congress",1, "1"); 21 insert into t2 values("Juniper",2, "2"); 22 insert into t2 values("Nightingale",3, "3"); 23 create index idx2 using master on t2(a,b); 24 not supported: column 'b' is not varchar type. 25 drop table if exists t1; 26 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 27 insert into t1 values("Congress","Lane", "1"); 28 insert into t1 values("Juniper","Way", "2"); 29 insert into t1 values("Nightingale","Lane", "3"); 30 create index idx1 using master on t1(a,b); 31 insert into t1 values("Alberta","Blvd", "4"); 32 select * from t1 where a = "Alberta" and b="Blvd"; 33 a b c 34 Alberta Blvd 4 35 insert into t1 values("Nightingale","Lane", "5"); 36 select * from t1 where a = "Nightingale" and b="Lane"; 37 a b c 38 Nightingale Lane 3 39 Nightingale Lane 5 40 insert into t1 values(NULL,"Lane", "6"); 41 select * from t1 where b="Lane"; 42 a b c 43 Congress Lane 1 44 Nightingale Lane 3 45 Nightingale Lane 5 46 null Lane 6 47 drop table if exists t2; 48 create table t2(a varchar(30), b varchar(30), c varchar(30)); 49 insert into t2 values("arjun", "sk", "7"); 50 insert into t2 values("albin", "john", "8"); 51 insert into t1 select * from t2; 52 select * from t1 where b="Lane"; 53 a b c 54 Congress Lane 1 55 Nightingale Lane 3 56 Nightingale Lane 5 57 null Lane 6 58 update t1 set a="albin" ,b="john" where c="7"; 59 select * from t1 where a="albin"; 60 a b c 61 albin john 8 62 albin john 7 63 update t1 set a=NULL ,b="john" where c="7"; 64 select * from t1 where b="john"; 65 a b c 66 albin john 8 67 null john 7 68 delete from t1 where c="7"; 69 select * from t1 where a="john"; 70 a b c 71 truncate table t1; 72 select * from t1; 73 a b c 74 show index from t1; 75 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 76 t1 0 PRIMARY 1 c A 0 NULL NULL YES NULL 77 t1 1 idx1 1 a A 0 NULL NULL YES master YES NULL 78 t1 1 idx1 2 b A 0 NULL NULL YES master YES NULL 79 show create table t1; 80 Table Create Table 81 t1 CREATE TABLE `t1` (\n`a` VARCHAR(30) DEFAULT NULL,\n`b` VARCHAR(30) DEFAULT NULL,\n`c` VARCHAR(30) NOT NULL,\nPRIMARY KEY (`c`),\nKEY `idx1` USING master (`a`,`b`)\n) 82 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1"; 83 name type column_name 84 idx1 MULTIPLE a 85 idx1 MULTIPLE b 86 drop table t1; 87 show index from t1; 88 no such table secondary_index_master.t1 89 show create table t1; 90 no such table secondary_index_master.t1 91 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1"; 92 name type column_name 93 drop table if exists t1; 94 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 95 insert into t1 values("Congress","Lane", "1"); 96 insert into t1 values("Juniper","Way", "2"); 97 insert into t1 values("Nightingale","Lane", "3"); 98 create index idx1 using master on t1(a); 99 insert into t1 values("Abi","Ma", "4"); 100 select * from t1 where a = "Abi"; 101 a b c 102 Abi Ma 4 103 drop table if exists t1; 104 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 105 insert into t1 values("Congress","Lane", "1"); 106 insert into t1 values("Juniper","Way", "2"); 107 insert into t1 values("Nightingale","Lane", "3"); 108 create index idx1 using master on t1(a,b,c); 109 insert into t1 values("Abel","John", "4"); 110 insert into t1 values("Amy","Brian", "5"); 111 select * from t1 where a = "Congress" and b="Lane" and c="1"; 112 a b c 113 Congress Lane 1 114 drop table if exists t1; 115 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 116 create index idx1 using master on t1(a,b); 117 insert into t1 values("Congress","Lane", "1"); 118 insert into t1 values("Juniper","Way", "2"); 119 insert into t1 values("Nightingale","Lane", "3"); 120 select * from t1 where a = "Congress" and b="Lane"; 121 a b c 122 Congress Lane 1 123 drop table if exists t1; 124 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key, index idx1 using master (a,b)); 125 insert into t1 values("Congress","Lane", "1"); 126 insert into t1 values("Juniper","Way", "2"); 127 insert into t1 values("Nightingale","Lane", "3"); 128 select * from t1 where a = "Congress" and b="Lane"; 129 a b c 130 Congress Lane 1 131 drop table if exists t1; 132 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 133 insert into t1 values("Congress","Lane", "1"); 134 insert into t1 values("Juniper","Way", "2"); 135 insert into t1 values("Nightingale","Lane", "3"); 136 alter table t1 add index idx1 using master(a,b); 137 insert into t1 values("Congress","Lane", "4"); 138 select * from t1 where a = "Congress" and b="Lane"; 139 a b c 140 Congress Lane 1 141 Congress Lane 4 142 drop table if exists t1; 143 create table t1(a varchar(30), b varchar(30), c varchar(30)); 144 create index idx1 using master on t1(a,b); 145 insert into t1 values("Congress","Lane", "1"); 146 insert into t1 values("Juniper","Way", "2"); 147 insert into t1 values("Nightingale","Lane", "3"); 148 select * from t1 where a="Congress" and b="Lane"; 149 a b c 150 Congress Lane 1 151 drop table if exists t1; 152 create table t1(a varchar(30), b varchar(30), c varchar(30), primary key(a,b)); 153 create index idx1 using master on t1(a,b); 154 insert into t1 values("Congress","Lane", "1"); 155 insert into t1 values("Juniper","Way", "2"); 156 insert into t1 values("Nightingale","Lane", "3"); 157 select * from t1 where a="Congress" and b="Lane"; 158 a b c 159 Congress Lane 1 160 drop table if exists t1; 161 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 162 create index idx1 using master on t1(a,b); 163 insert into t1 values("Congress","Lane", "1"); 164 insert into t1 values("Juniper","Way", "2"); 165 insert into t1 values("Nightingale","Lane", "3"); 166 alter table t1 drop column b; 167 insert into t1 values("Congress", "4"); 168 select * from t1 where a="Congress"; 169 a c 170 Congress 1 171 Congress 4 172 drop table if exists t1; 173 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 174 create index idx1 using master on t1(a); 175 insert into t1 values("Congress","Lane", "1"); 176 insert into t1 values("Juniper","Way", "2"); 177 insert into t1 values("Nightingale","Lane", "3"); 178 alter table t1 rename column a to a1; 179 insert into t1 values("Congress","Lane", "4"); 180 select * from t1 where a1="Congress"; 181 a1 b c 182 Congress Lane 1 183 Congress Lane 4 184 drop table if exists t1; 185 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 186 create index idx1 using master on t1(a); 187 insert into t1 values("Congress","Lane", "1"); 188 insert into t1 values("Juniper","Way", "2"); 189 insert into t1 values("Nightingale","Lane", "3"); 190 alter table t1 modify column c int; 191 insert into t1 values("Congress","Lane", 4); 192 select * from t1 where a="Congress"; 193 a b c 194 Congress Lane 1 195 Congress Lane 4 196 drop table if exists t1; 197 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 198 create index idx1 using master on t1(a); 199 insert into t1 values("Congress","Lane", "1"); 200 insert into t1 values("Juniper","Way", "2"); 201 insert into t1 values("Nightingale","Lane", "3"); 202 alter table t1 drop primary key; 203 alter table t1 add primary key (a,b); 204 insert into t1 values("Congress","Lane2", "4"); 205 select * from t1 where a="Congress"; 206 a b c 207 Congress Lane 1 208 Congress Lane2 4 209 drop table if exists t1; 210 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 211 create index idx1 using master on t1(a); 212 insert into t1 values("Congress","Lane", "4"); 213 insert into t1 values("Juniper","Way", "5"); 214 insert into t1 values("Nightingale","Lane", "6"); 215 alter table t1 drop primary key; 216 insert into t1 values("Congress","Lane", "7"); 217 select * from t1 where a="Congress"; 218 a b c 219 Congress Lane 4 220 Congress Lane 7 221 drop table if exists t1; 222 create table t1(a varchar(30), b bigint, c varchar(30) primary key); 223 create index idx1 using master on t1(a,b); 224 not supported: column 'b' is not varchar type. 225 drop table if exists t1; 226 create table t1(a varchar(30), b varchar(30), c varchar(30)); 227 create index idx1 using master on t1(a,b); 228 insert into t1 values("Congress","Lane", "1"); 229 insert into t1 values("Juniper","Way", "2"); 230 insert into t1 values("Nightingale","Lane", "3"); 231 select * from t1 where a="Congress" and b="Lane"; 232 a b c 233 Congress Lane 1 234 drop table if exists t1; 235 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 236 create index idx1 using master on t1(a,b); 237 insert into t1 values("Congress","Lane", "1"); 238 insert into t1 values("Juniper","Way", "2"); 239 insert into t1 values("Nightingale","Lane", "3"); 240 select * from t1 where a="Nightingale" and b="Lane"; 241 a b c 242 Nightingale Lane 3 243 drop table if exists t1; 244 create table t1(a varchar(30), b0 varchar(30), b1 varchar(30), c varchar(30), d varchar(30), primary key( c, d)); 245 create index idx1 using master on t1(a,b0); 246 insert into t1 values("Congress","Lane", "ALane","1","0"); 247 insert into t1 values("Juniper","Way","AWay", "2","0"); 248 insert into t1 values("Nightingale","Lane","ALane", "3","0"); 249 select * from t1 where a="Nightingale" and b0="Lane"; 250 a b0 b1 c d 251 Nightingale Lane ALane 3 0 252 drop table if exists t1; 253 create table t1(a varchar(30), b varchar(30), c varchar(30) primary key); 254 create index idx1 using master on t1(a,b); 255 insert into t1 values("Congress","Lane", "1"); 256 insert into t1 values("Juniper","Way", "2"); 257 insert into t1 values("Nightingale","Lane", "3"); 258 select * from t1 where b="Lane"; 259 a b c 260 Congress Lane 1 261 Nightingale Lane 3 262 select * from t1 where a="Juniper" and b="Way"; 263 a b c 264 Juniper Way 2 265 drop table if exists t1; 266 create table t1(a varchar(30), b varchar(30), c varchar(30)); 267 create index idx1 using master on t1(a,b,c); 268 insert into t1 values("Congress","Lane", "1"); 269 insert into t1 values("Juniper","Way", "2"); 270 insert into t1 values("Nightingale","Lane", "3"); 271 select * from t1 where a="Congress" and b="Lane" and c="1"; 272 a b c 273 Congress Lane 1 274 select * from t1 where a="Nightingale" and c between "2" and "3"; 275 a b c 276 Nightingale Lane 3 277 drop table if exists t1; 278 create table t1(a varchar(30), b varchar(30), c varchar(30)); 279 create index idx1 using master on t1(a,b,c); 280 insert into t1 values("Congress","Lane", "1"); 281 insert into t1 values("Juniper","Way", "2"); 282 insert into t1 values("Nightingale","Lane", "3"); 283 select * from t1 where a in ("Congress","Nightingale") and b="Lane" and c in("1","2","3"); 284 a b c 285 Congress Lane 1 286 Nightingale Lane 3 287 drop table if exists t1; 288 create table t1(a varchar(30), b varchar(30), c varchar(30)); 289 create index idx1 using master on t1(a,b,c); 290 insert into t1 values("Congress","Lane", "1"); 291 insert into t1 values("Juniper","Way", "2"); 292 insert into t1 values("Nightingale","Lane", "3"); 293 select * from t1 where a between "Congress" and "Nightingale" and b="Lane" and c between "1" and "3"; 294 a b c 295 Congress Lane 1 296 Nightingale Lane 3 297 select * from t1 where a between "Congress" and "Nightingale" and b="Lane" and c between "1" and "3" limit 1; 298 a b c 299 Congress Lane 1 300 SET GLOBAL experimental_master_index = 0;