github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/create_table_as_select.result (about) 1 create database test; 2 use test; 3 create table t1(a int default 123, b char(5)); 4 desc t1; 5 Field Type Null Key Default Extra Comment 6 a INT(32) YES 123 7 b CHAR(5) YES null 8 INSERT INTO t1 values (1, '1'); 9 INSERT INTO t1 values (2, '2'); 10 INSERT INTO t1 values (0x7fffffff, 'max'); 11 select * from t1; 12 a b 13 1 1 14 2 2 15 2147483647 max 16 CREATE table t2 (c float) as select b, a from t1; 17 desc t2; 18 Field Type Null Key Default Extra Comment 19 c FLOAT(0) YES null 20 b CHAR(5) YES null 21 a INT(32) YES 123 22 select * from t2; 23 c b a 24 null 1 1 25 null 2 2 26 null max 2147483647 27 CREATE table if not exists t2 (d float) as select b, a from t1; 28 desc t2; 29 Field Type Null Key Default Extra Comment 30 c FLOAT(0) YES null 31 b CHAR(5) YES null 32 a INT(32) YES 123 33 CREATE table t3 (a bigint unsigned not null auto_increment primary key, c float) as select a, b from t1; 34 desc t3; 35 Field Type Null Key Default Extra Comment 36 c FLOAT(0) YES null 37 a BIGINT UNSIGNED(64) NO PRI null 38 b CHAR(5) YES null 39 select * from t3; 40 c a b 41 null 1 1 42 null 2 2 43 null 2147483647 max 44 CREATE table t4 (a tinyint) as select * from t1; 45 Data truncation: data out of range: data type int8, value '2147483647' 46 CREATE table t5 (a char(10)) as select * from t1; 47 desc t5; 48 Field Type Null Key Default Extra Comment 49 a CHAR(10) YES null 50 b CHAR(5) YES null 51 select * from t5; 52 a b 53 1 1 54 2 2 55 2147483647 max 56 insert into t1 values (1, '1_1'); 57 select * from t1; 58 a b 59 1 1 60 2 2 61 2147483647 max 62 1 1_1 63 CREATE table t6 (a int unique) as select * from t1; 64 Duplicate entry '1' for key '__mo_index_idx_col' 65 drop table t6; 66 CREATE table t6 as select max(a) from t1; 67 desc t6; 68 Field Type Null Key Default Extra Comment 69 max(a) INT(32) YES null 70 select * from t6; 71 max(a) 72 2147483647 73 CREATE table t7 as select * from (select * from t1) as t; 74 desc t7; 75 Field Type Null Key Default Extra Comment 76 a INT(32) YES null 77 b CHAR(5) YES null 78 select * from t7; 79 a b 80 1 1 81 2 2 82 2147483647 max 83 1 1_1 84 CREATE table t8 as select a as alias_a, 1 from t1; 85 desc t8; 86 Field Type Null Key Default Extra Comment 87 alias_a INT(32) YES 123 88 1 BIGINT(0) NO null 89 select * from t8; 90 alias_a 1 91 1 1 92 2 1 93 2147483647 1 94 1 1 95 CREATE table t9 (index (a)) as select * from t1; 96 desc t9; 97 Field Type Null Key Default Extra Comment 98 a INT(32) YES MUL 123 99 b CHAR(5) YES null 100 select * from t9; 101 a b 102 1 1 103 2 2 104 2147483647 max 105 1 1_1 106 drop table t1; 107 drop table t2; 108 drop table t3; 109 drop table t5; 110 drop table t6; 111 drop table t7; 112 drop table t8; 113 drop table t9; 114 drop table if exists table01; 115 create table table01(a int default 123, b char(5)); 116 desc table01; 117 Field Type Null Key Default Extra Comment 118 a INT(32) YES 123 119 b CHAR(5) YES null 120 insert into table01 values (1, '1'); 121 insert into table01 values (2, '2'); 122 insert into table01 values (0x7fffffff, 'max'); 123 select * from table01; 124 a b 125 1 1 126 2 2 127 2147483647 max 128 drop table if exists table02; 129 create table table02 (c float) as select b, a from table01; 130 desc table02; 131 Field Type Null Key Default Extra Comment 132 c FLOAT(0) YES null 133 b CHAR(5) YES null 134 a INT(32) YES 123 135 select * from table02; 136 c b a 137 null 1 1 138 null 2 2 139 null max 2147483647 140 drop table if exists table02; 141 create table table02 (d float) as select b, a from table01; 142 desc table02; 143 Field Type Null Key Default Extra Comment 144 d FLOAT(0) YES null 145 b CHAR(5) YES null 146 a INT(32) YES 123 147 select * from table02; 148 d b a 149 null 1 1 150 null 2 2 151 null max 2147483647 152 drop table table01; 153 drop table table02; 154 drop table if exists table03; 155 create table table03(col1 int, col2 char, col3 varchar(10), col4 text, col5 tinyint unsigned, col6 bigint, col7 decimal, col8 float, col9 double); 156 insert into table03 values (1, 'a', 'database', 'cover all data types', 12, 372743927942, 3232.000, -1489.1231, 72392342); 157 insert into table03 values (2, 'b', 'table', 'database management system', 1, 324214, 0.0001, 32932.000, -321342.0); 158 insert into table03 values (null, null, null, null, null, null, null, null, null); 159 select * from table03; 160 col1 col2 col3 col4 col5 col6 col7 col8 col9 161 1 a database cover all data types 12 372743927942 3232 -1489.123 7.2392342E7 162 2 b table database management system 1 324214 0 32932.0 -321342.0 163 null null null null null null null null null 164 drop table if exists table04; 165 create table table04 as select * from table03; 166 show create table table04; 167 168 select * from table04; 169 col1 col2 col3 col4 col5 col6 col7 col8 col9 170 1 a database cover all data types 12 372743927942 3232 -1489.123 7.2392342E7 171 2 b table database management system 1 324214 0 32932.0 -321342.0 172 null null null null null null null null null 173 drop table if exists table05; 174 create table table05 as select col1, col3, col5, col7 from table03; 175 show create table table05; 176 Table Create Table 177 table05 CREATE TABLE `table05` (\n`col1` INT DEFAULT NULL,\n`col3` VARCHAR(10) DEFAULT NULL,\n`col5` TINYINT UNSIGNED DEFAULT NULL,\n`col7` DECIMAL(38,0) DEFAULT NULL\n) 178 select * from table05; 179 col1 col3 col5 col7 180 1 database 12 3232 181 2 table 1 0 182 null null null null 183 drop table if exists table06; 184 create table table06(col10 binary) as select col2, col4, col6, col8, col9 from table03; 185 show create table table06; 186 187 desc table06; 188 Field Type Null Key Default Extra Comment 189 col10 BINARY(1) YES null 190 col2 CHAR(1) YES null 191 col4 TEXT(0) YES null 192 col6 BIGINT(64) YES null 193 col8 FLOAT(0) YES null 194 col9 DOUBLE(0) YES null 195 select * from table06; 196 col10 col2 col4 col6 col8 col9 197 null a cover all data types 372743927942 -1489.123 7.2392342E7 198 null b database management system 324214 32932.0 -321342.0 199 null null null null null null 200 drop table table03; 201 drop table table04; 202 drop table table05; 203 drop table table06; 204 drop table if exists t1; 205 create table t1(a int default 123, b char(5)); 206 desc t1; 207 Field Type Null Key Default Extra Comment 208 a INT(32) YES 123 209 b CHAR(5) YES null 210 INSERT INTO t1 values (1, '1'); 211 INSERT INTO t1 values (2, '2'); 212 INSERT INTO t1 values (0x7fffffff, 'max'); 213 select * from t1; 214 a b 215 1 1 216 2 2 217 2147483647 max 218 create table t2 (c float) as select b, a from t1; 219 desc t2; 220 Field Type Null Key Default Extra Comment 221 c FLOAT(0) YES null 222 b CHAR(5) YES null 223 a INT(32) YES 123 224 select * from t2; 225 c b a 226 null 1 1 227 null 2 2 228 null max 2147483647 229 CREATE table if not exists t2 (d float) as select b, a from t1; 230 231 select * from t2; 232 c b a 233 null 1 1 234 null 2 2 235 null max 2147483647 236 drop table t1; 237 drop table t2; 238 drop table if exists table07; 239 create table table07(col1 date, col2 datetime, col3 timestamp, col4 blob, col5 json); 240 insert into table07 values ('2020-10-11', '2023-11-11 10:00:01', '1997-01-13 12:12:12.000', 'abcdef', '{"x": 17, "x": "red"}'); 241 insert into table07 values ('1919-12-01', '1990-10-10 01:01:01', '2001-12-12 01:01:01.000', 'xxxx', '{"t1": "a"}'); 242 insert into table07 values (null, null, null, null, null); 243 select * from table07; 244 col1 col2 col3 col4 col5 245 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 abcdef {"x": "red"} 246 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 xxxx {"t1": "a"} 247 null null null null null 248 drop table if exists table08; 249 create table table08(col6 int, col7 bigint, col8 char) as select * from table07; 250 show create table table08; 251 252 select * from table08; 253 col6 col7 col8 col1 col2 col3 col4 col5 254 null null null 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 abcdef {"x": "red"} 255 null null null 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 xxxx {"t1": "a"} 256 null null null null null null null null 257 drop table table08; 258 drop table if exists table09; 259 create table table09 as select col1, col2, col4 as newCol4 from table07; 260 show create table table09; 261 262 select * from table09; 263 col1 col2 newcol4 264 2020-10-11 2023-11-11 10:00:01 abcdef 265 1919-12-01 1990-10-10 01:01:01 xxxx 266 null null null 267 drop table table09; 268 drop table if exists table12; 269 create table table12 (col1 date) as select * from table07; 270 show create table table12; 271 272 select * from table12; 273 col1 col2 col3 col4 col5 274 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 abcdef {"x": "red"} 275 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 xxxx {"t1": "a"} 276 null null null null null 277 drop table table12; 278 drop table table07; 279 drop table if exists distinct01; 280 create table distinct01 ( 281 id int, 282 first_name varchar(50), 283 last_name varchar(50), 284 course varchar(100) 285 ); 286 insert into distinct01 (id, first_name, last_name, course) values 287 (1, 'John', 'Doe', 'Computer Science'), 288 (2, 'Jane', 'Smith', 'Mathematics'), 289 (3, 'Alice', 'Johnson', 'Computer Science'), 290 (4, 'Bob', 'Brown', 'Physics'), 291 (5, 'Charlie', 'Doe', 'Computer Science'), 292 (5, 'Charlie', 'Doe', 'Computer Science'); 293 drop table if exists unique_courses; 294 create table unique_courses as select distinct course from distinct01; 295 show create table unique_courses; 296 Table Create Table 297 unique_courses CREATE TABLE `unique_courses` (\n`course` VARCHAR(100) DEFAULT NULL\n) 298 select * from unique_courses; 299 course 300 Computer Science 301 Mathematics 302 Physics 303 drop table if exists unique_courses; 304 create table unique_courses as select distinct * from distinct01; 305 show create table unique_courses; 306 Table Create Table 307 unique_courses CREATE TABLE `unique_courses` (\n`id` INT DEFAULT NULL,\n`first_name` VARCHAR(50) DEFAULT NULL,\n`last_name` VARCHAR(50) DEFAULT NULL,\n`course` VARCHAR(100) DEFAULT NULL\n) 308 select * from unique_courses; 309 id first_name last_name course 310 1 John Doe Computer Science 311 2 Jane Smith Mathematics 312 3 Alice Johnson Computer Science 313 4 Bob Brown Physics 314 5 Charlie Doe Computer Science 315 drop table unique_courses; 316 drop table if exists cast01; 317 create table cast01 (col1 float, col2 double); 318 insert into cast01 values (2617481243.2114, 372534.4353); 319 insert into cast01 values (-3628742.3223252, 0); 320 insert into cast01 values (null, null); 321 select * from cast01; 322 col1 col2 323 2.6174812E9 372534.4353 324 -3628742.2 0.0 325 null null 326 drop table if exists cast02; 327 create table cast02(col1 double, col2 float) select * from cast01; 328 show create table cast02; 329 Table Create Table 330 cast02 CREATE TABLE `cast02` (\n`col1` DOUBLE DEFAULT NULL,\n`col2` FLOAT DEFAULT NULL\n) 331 select * from cast02; 332 col1 col2 333 2.617481216E9 372534.44 334 -3628742.25 0.0 335 null null 336 drop table cast01; 337 drop table cast02; 338 drop table if exists cast03; 339 create table cast03 (col1 int, col2 float, col3 double); 340 insert into cast03 values (321424, 213412.23142, -100.313); 341 insert into cast03 values (-1241, 2314321, 0); 342 insert into cast03 values (0, 0, 0); 343 select * from cast03; 344 col1 col2 col3 345 321424 213412.23 -100.313 346 -1241 2314321.0 0.0 347 0 0.0 0.0 348 drop table if exists cast04; 349 create table cast04(col1 char(10), col2 char(10), col3 char(10)) as select * from cast03; 350 select * from cast04; 351 352 drop table cast03; 353 drop table cast04; 354 drop table if exists cast05; 355 create table cast05 (col1 char, col2 varchar(10)); 356 insert into cast05 values ('9', '-32824'); 357 insert into cast05 values ('0', '32422'); 358 insert into cast05 values (null, null); 359 select * from cast05; 360 col1 col2 361 9 -32824 362 0 32422 363 null null 364 drop table if exists cast06; 365 create table cast06(col1 int, col2 bigint) as select * from cast05; 366 select * from cast06; 367 col1 col2 368 9 -32824 369 0 32422 370 null null 371 show create table cast06; 372 Table Create Table 373 cast06 CREATE TABLE `cast06` (\n`col1` INT DEFAULT NULL,\n`col2` BIGINT DEFAULT NULL\n) 374 drop table cast05; 375 drop table cast06; 376 drop table if exists time01; 377 create table time01 (col1 date, col2 datetime, col3 timestamp, col4 time); 378 insert into time01 values ('2020-01-01', '2020-12-12 00:00:01', '1997-01-01 10:10:10.000', '12:12:12'); 379 insert into time01 values ('1996-12-11', '1989-12-09 00:01:01', '2000-05-06 01:01:01.000', '00:01:01'); 380 insert into time01 values (null, null, null, null); 381 select * from time01; 382 col1 col2 col3 col4 383 2020-01-01 2020-12-12 00:00:01 1997-01-01 10:10:10 12:12:12 384 1996-12-11 1989-12-09 00:01:01 2000-05-06 01:01:01 00:01:01 385 null null null null 386 drop table if exists time02; 387 create table time02 (col1 int, col2 int, col4 int) as select * from time01; 388 show create table time02; 389 Table Create Table 390 time02 CREATE TABLE `time02` (\n`col1` INT DEFAULT NULL,\n`col2` INT DEFAULT NULL,\n`col3` TIMESTAMP DEFAULT NULL,\n`col4` INT DEFAULT NULL\n) 391 select * from time02; 392 col1 col2 col3 col4 393 18262 1607731201 1997-01-01 10:10:10 121212 394 9841 629164861 2000-05-06 01:01:01 101 395 null null null null 396 drop table time02; 397 drop table if exists time03; 398 create table time03 (col2 decimal(38, 0), col4 decimal) as select col2, col3, col4 from time01; 399 show create table time03; 400 Table Create Table 401 time03 CREATE TABLE `time03` (\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` TIMESTAMP DEFAULT NULL,\n`col4` DECIMAL(38,0) DEFAULT NULL\n) 402 select * from time03; 403 col2 col3 col4 404 1607731201 1997-01-01 10:10:10 121212 405 629164861 2000-05-06 01:01:01 101 406 null null null 407 drop table time03; 408 drop table if exists table01; 409 create table table01 (col1 int primary key , col2 char default 'c', col3 decimal not null); 410 insert into table01 values (1, 'a', 3728.424); 411 insert into table01 values (3131, 'b', -32832.43); 412 insert into table01 values (-1, '' , 0); 413 select * from table01; 414 col1 col2 col3 415 1 a 3728 416 3131 b -32832 417 -1 0 418 drop table if exists table02; 419 create table table02 as select * from table01; 420 show create table table02; 421 Table Create Table 422 table02 CREATE TABLE `table02` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT 'c',\n`col3` DECIMAL(38,0) NOT NULL\n) 423 desc table02; 424 Field Type Null Key Default Extra Comment 425 col1 INT(32) NO null 426 col2 CHAR(1) YES 'c' 427 col3 DECIMAL128(38) NO null 428 select * from table02; 429 col1 col2 col3 430 1 a 3728 431 3131 b -32832 432 -1 0 433 drop table table01; 434 drop table table02; 435 drop table if exists table04; 436 drop table if exists table03; 437 create table table03 (a int primary key, b varchar(5) unique key); 438 create table table04 (a int ,b varchar(5), c int, foreign key(c) references table03(a)); 439 insert into table03 values (101,'abc'),(102,'def'); 440 insert into table04 values (1,'zs1',101),(2,'zs2',102); 441 drop table if exists table05; 442 create table table05 as select * from table04; 443 show create table table05; 444 Table Create Table 445 table05 CREATE TABLE `table05` (\n`a` INT DEFAULT NULL,\n`b` VARCHAR(5) DEFAULT NULL,\n`c` INT DEFAULT NULL\n) 446 select * from table05; 447 a b c 448 1 zs1 101 449 2 zs2 102 450 drop table if exists table06; 451 create table table06 (d char not null default 'a') as select a from table03; 452 show create table table06; 453 454 select * from table06; 455 d a 456 a 101 457 a 102 458 drop table table04; 459 drop table table03; 460 drop table table05; 461 drop table table06; 462 drop table if exists math01; 463 create table math01 (col1 int default 0, col2 decimal, col3 float, col4 double not null); 464 insert into math01 values (1, 7382.4324, 432453.3243, -2930.321323); 465 insert into math01 values (-100, 3283.32324, 328932.0, -9392032); 466 insert into math01 values (22813, -241, 932342.4324, -0.1); 467 insert into math01 values (null, null, null, 10); 468 drop table if exists agg01; 469 create table agg01 as select avg(col1) as avgCol, sum(col2) as sumcol, count(col3) as countCol, max(col4) as maxCol, min(col4) as minCol from math01; 470 471 show create table agg01; 472 473 select * from agg01; 474 475 drop table agg01; 476 477 drop table if exists bit01; 478 create table bit01 (col1 char(1), col2 int); 479 insert into bit01 values ('a',111),('a',110),('a',100),('a',000),('b',001),('b',011); 480 select * from bit01; 481 col1 col2 482 a 111 483 a 110 484 a 100 485 a 0 486 b 1 487 b 11 488 drop table if exists bit02; 489 create table bit02 as select bit_and(col2), bit_or(col2), bit_xor(col2), stddev_pop(col2) from bit01; 490 desc bit02; 491 492 show create table bit02; 493 494 select count(*) from bit02; 495 count(*) 496 1 497 select * from bit02; 498 bit_and(col2) bit_or(col2) bit_xor(col2) stddev_pop(col2) 499 0 111 111 51.73892796209317 500 drop table bit02; 501 drop table bit01; 502 drop table if exists math01; 503 create table math01 (col1 int, col2 decimal, col3 bigint, col4 double, col5 float); 504 insert into math01 values (1, 10.50, 1234567890, 123.45, 678.90), 505 (2, 20.75, 9876543210, 234.56, 789.01), 506 (3, 30.10, 1122334455, 345.67, 890.12), 507 (4, 40.25, 2233445566, 456.78, 901.23), 508 (5, 50.40, -3344556677, 567.89, 101.24), 509 (6, 60.55, -4455667788, 678.90, 112.35), 510 (7, 70.70, 5566778899, 789.01, 123.46), 511 (8, 80.85, -6677889900, 890.12, 134.57), 512 (9, 90.00, 7788990011, 901.23, 145.68), 513 (10, 100.00, 8899001122, 101.24, 156.79); 514 drop table if exists math02; 515 create table math02 as select abs(col3), sin(col1), cos(col2), tan(col1), round(col4) from math01; 516 select * from math02; 517 abs(col3) sin(col1) cos(col2) tan(col1) round(col4) 518 1234567890 0.8414709848078965 0.004425697988050786 1.557407724654902 123.0 519 9876543210 0.9092974268256816 -0.5477292602242685 -2.185039863261519 235.0 520 1122334455 0.1411200080598672 0.15425144988758405 -0.1425465430742778 346.0 521 2233445566 -0.7568024953079282 -0.6669380616522619 1.1578212823495775 457.0 522 3344556677 -0.9589242746631385 0.9649660284921132 -3.3805150062465854 568.0 523 4455667788 -0.27941549819892586 -0.25810163593826746 -0.29100619138474915 679.0 524 5566778899 0.6569865987187892 -0.3090227281660707 0.8714479827243188 789.0 525 6677889900 0.9893582466233817 0.7766859820216312 -6.799711455220378 890.0 526 7788990011 0.4121184852417566 -0.4480736161291701 -0.45231565944180985 901.0 527 8899001122 -0.5440211108893699 0.8623188722876839 0.6483608274590867 101.0 528 drop table if exists math03; 529 create table math03 as select cot(col1), atan(col1), sinh(col1), floor(col5) from math01; 530 select * from math02; 531 abs(col3) sin(col1) cos(col2) tan(col1) round(col4) 532 1234567890 0.8414709848078965 0.004425697988050786 1.557407724654902 123.0 533 9876543210 0.9092974268256816 -0.5477292602242685 -2.185039863261519 235.0 534 1122334455 0.1411200080598672 0.15425144988758405 -0.1425465430742778 346.0 535 2233445566 -0.7568024953079282 -0.6669380616522619 1.1578212823495775 457.0 536 3344556677 -0.9589242746631385 0.9649660284921132 -3.3805150062465854 568.0 537 4455667788 -0.27941549819892586 -0.25810163593826746 -0.29100619138474915 679.0 538 5566778899 0.6569865987187892 -0.3090227281660707 0.8714479827243188 789.0 539 6677889900 0.9893582466233817 0.7766859820216312 -6.799711455220378 890.0 540 7788990011 0.4121184852417566 -0.4480736161291701 -0.45231565944180985 901.0 541 8899001122 -0.5440211108893699 0.8623188722876839 0.6483608274590867 101.0 542 drop table if exists math04; 543 create table math04 as select ceil(col4), power(col5, 2), pi() * col1, log(col2), ln(col2), exp(col1) from math01; 544 select * from math04; 545 ceil(col4) power(col5, 2) pi() * col1 log(col2) ln(col2) exp(col1) 546 124.0 460905.24314941466 3.141592653589793 2.3978952727983707 2.3978952727983707 2.718281828459045 547 235.0 622536.7955103517 6.283185307179586 3.044522437723423 3.044522437723423 7.38905609893065 548 346.0 792313.6057074219 9.42477796076938 3.4011973816621555 3.4011973816621555 20.085536923187668 549 457.0 812215.4776957035 12.566370614359172 3.6888794541139363 3.6888794541139363 54.598150033144236 550 568.0 10249.53716745606 15.707963267948966 3.912023005428146 3.912023005428146 148.4131591025766 551 679.0 12622.522157135012 18.84955592153876 4.110873864173311 4.110873864173311 403.4287934927351 552 790.0 15242.37137393799 21.991148575128552 4.2626798770413155 4.2626798770413155 1096.6331584284585 553 891.0 18109.086871240288 25.132741228718345 4.394449154672439 4.394449154672439 2980.9579870417283 554 902.0 21222.66026601568 28.274333882308138 4.499809670330265 4.499809670330265 8103.083927575384 555 102.0 24583.101994665572 31.41592653589793 4.605170185988092 4.605170185988092 22026.465794806718 556 drop table math01; 557 drop table math02; 558 drop table math03; 559 drop table math04; 560 drop table if exists string01; 561 create table string01 (col1 varchar(40), col2 char, col3 text default null); 562 insert into string01 values (' database system', '2', '云原生数据库'); 563 insert into string01 values (' string function ', '1', '字符串函数'); 564 insert into string01 values ('test create table as select', '0', null); 565 drop table if exists string02; 566 create table string02 as select concat_ws(',', col1, 'abcde') from string01; 567 show create table string02; 568 569 select * from string02; 570 concat_ws(,, col1, abcde) 571 database system,abcde 572 string function ,abcde 573 test create table as select,abcde 574 drop table string02; 575 drop table if exists string03; 576 create table string03 as select find_in_set(col2, col1) from string01; 577 show create table string03; 578 579 select * from string03; 580 find_in_set(col2, col1) 581 0 582 0 583 0 584 drop table string03; 585 drop table if exists string04; 586 create table string04 as select oct(col2), empty(col3), length(col1) from string01; 587 show create table string04; 588 589 select * from string04; 590 oct(col2) empty(col3) length(col1) 591 2 false 17 592 1 false 17 593 0 null 27 594 drop table string04; 595 drop table if exists string05; 596 create table string05 as select trim(col1), ltrim(col1), rtrim(col1) from string01; 597 show create table string05; 598 Table Create Table 599 string05 CREATE TABLE `string05` (\n`trim(col1)` VARCHAR(65535) DEFAULT NULL,\n`ltrim(col1)` VARCHAR(65535) DEFAULT NULL,\n`rtrim(col1)` VARCHAR(65535) DEFAULT NULL\n) 600 select * from string05; 601 trim(col1) ltrim(col1) rtrim(col1) 602 database system database system database system 603 string function string function string function 604 test create table as select test create table as select test create table as select 605 drop table string05; 606 drop table if exists string06; 607 create table string06 as select lpad(col1, 5, '-'), rpad(col1, 1, '-') from string01; 608 show create table string06; 609 610 select * from string06; 611 lpad(col1, 5, -) rpad(col1, 1, -) 612 dat 613 stri 614 test t 615 drop table string06; 616 drop table if exists string07; 617 create table string07 as select startswith(col1, ' '), endswith(col1, ' ') from string01; 618 show create table string07; 619 Table Create Table 620 string07 CREATE TABLE `string07` (\n`startswith(col1, )` BOOL DEFAULT NULL,\n`endswith(col1, )` BOOL DEFAULT NULL\n) 621 select * from string07; 622 startswith(col1, ) endswith(col1, ) 623 true false 624 true true 625 false false 626 drop table string07; 627 drop table if exists string08; 628 create table string08 as select hex(col2) from string01; 629 show create table string08; 630 Table Create Table 631 string08 CREATE TABLE `string08` (\n`hex(col2)` VARCHAR(65535) DEFAULT NULL\n) 632 select * from string08; 633 hex(col2) 634 32 635 31 636 30 637 drop table string08; 638 drop table if exists string09; 639 create table string09 as select substring(col1, 3, 4), reverse(col2) from string01; 640 show create table string09; 641 Table Create Table 642 string09 CREATE TABLE `string09` (\n`substring(col1, 3, 4)` VARCHAR(65535) DEFAULT NULL,\n`reverse(col2)` VARCHAR(65535) DEFAULT NULL\n) 643 select * from string09; 644 substring(col1, 3, 4) reverse(col2) 645 data 2 646 trin 1 647 st c 0 648 drop table string09; 649 drop table if exists string10; 650 create table string10 (col1 bigint); 651 insert into string10 values (2319318313), (null); 652 drop table if exists string11; 653 create table string11 as select bin(col1) from string10; 654 show create table string11; 655 Table Create Table 656 string11 CREATE TABLE `string11` (\n`bin(col1)` VARCHAR(65535) DEFAULT NULL\n) 657 select * from string11; 658 bin(col1) 659 10001010001111011111110100101001 660 null 661 drop table string10; 662 drop table string11; 663 drop table if exists string12; 664 drop table string12; 665 no such table test.string12 666 create table string12 (col1 varchar(100) not null, col2 date not null); 667 insert into string12 values (' Deepak Sharma', '2014-12-01' ), (' Ankana Jana', '2018-08-17'),(' Shreya Ghosh', '2020-09-10'); 668 select * from string12; 669 col1 col2 670 Deepak Sharma 2014-12-01 671 Ankana Jana 2018-08-17 672 Shreya Ghosh 2020-09-10 673 drop table if exists string13; 674 create table string13 as select * from string12 where col1 = space(5); 675 show create table string13; 676 Table Create Table 677 string13 CREATE TABLE `string13` (\n`col1` VARCHAR(100) NOT NULL,\n`col2` DATE NOT NULL\n) 678 select * from string13; 679 col1 col2 680 drop table string12; 681 drop table string13; 682 drop table string01; 683 drop table if exists time01; 684 create table time01(col1 date, col2 datetime, col3 timestamp, col4 time); 685 insert into time01 values ('2020-10-11', '2023-11-11 10:00:01', '1997-01-13 12:12:12.000', '12:12:12'); 686 insert into time01 values ('1919-12-01', '1990-10-10 01:01:01', '2001-12-12 01:01:01.000', '10:59:59'); 687 insert into time01 values (null, null, null, null); 688 select * from time01; 689 col1 col2 col3 col4 690 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 12:12:12 691 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 10:59:59 692 null null null null 693 drop table if exists time02; 694 create table time02 as select date_format(col2, '%W %M %Y') from time01; 695 show create table time02; 696 697 desc time02; 698 699 select * from time02; 700 date_format(col2, %w %m %y) 701 Saturday November 2023 702 Wednesday October 1990 703 null 704 drop table time02; 705 drop table if exists time03; 706 create table time03 as select date(col1), date(col2), year(col1), day(col1), weekday(col1), dayofyear(col1) as dya from time01; 707 desc time03; 708 709 show create table time03; 710 711 select * from time03; 712 date(col1) date(col2) year(col1) day(col1) weekday(col1) dya 713 2020-10-11 2023-11-11 2020 11 6 285 714 1919-12-01 1990-10-10 1919 1 0 335 715 null null null null null null 716 drop table time03; 717 drop table if exists time04; 718 create table time04 as select date_add(col2, interval 45 day), date_sub(col2, interval 5 day) from time01; 719 show create table time04; 720 Table Create Table 721 time04 CREATE TABLE `time04` (\n`date_add(col2, interval(45, day))` DATETIME DEFAULT NULL,\n`date_sub(col2, interval(5, day))` DATETIME DEFAULT NULL\n) 722 select * from time04; 723 date_add(col2, interval(45, day)) date_sub(col2, interval(5, day)) 724 2023-12-26 10:00:01 2023-11-06 10:00:01 725 1990-11-24 01:01:01 1990-10-05 01:01:01 726 null null 727 drop table time04; 728 drop table if exists time05; 729 730 create table time05 as select unix_timestamp(col1) from time01; 731 732 show create table time05; 733 734 select * from time05; 735 736 drop table time05; 737 738 drop table if exists time06; 739 create table time06 as select datediff('2007-12-31 23:59:59', col1) as timedifferent from time01; 740 show create table time06; 741 742 select * from time06; 743 timedifferent 744 -4668 745 32172 746 null 747 drop table time06; 748 drop table if exists time07; 749 create table time07 as select timediff("22:22:22", col4) as timedifferent from time01; 750 show create table time07; 751 Table Create Table 752 time07 CREATE TABLE `time07` (\n`timedifferent` TIME DEFAULT NULL\n) 753 select * from time07; 754 timedifferent 755 10:10:10 756 11:22:23 757 null 758 drop table time07; 759 drop table if exists test01; 760 create table test01 as select col1 from time01 order by col1 nulls first; 761 select * from test01; 762 col1 763 null 764 1919-12-01 765 2020-10-11 766 drop table test01; 767 drop table if exists test02; 768 create table test02 as select * from time01 order by col2 desc nulls first; 769 select * from test02; 770 col1 col2 col3 col4 771 null null null null 772 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 12:12:12 773 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 10:59:59 774 drop table test02; 775 drop table if exists test03; 776 create table test03 as select * from time01 order by col2 desc nulls last; 777 select * from test03; 778 col1 col2 col3 col4 779 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 12:12:12 780 1919-12-01 1990-10-10 01:01:01 2001-12-12 01:01:01 10:59:59 781 null null null null 782 drop table test03; 783 drop table if exists test04; 784 create table test04 as select col1 from time01 order by col1 nulls first; 785 select * from test04; 786 col1 787 null 788 1919-12-01 789 2020-10-11 790 drop table test04; 791 insert into time01 values ('2014-10-11', '2021-11-11 10:00:01', '1989-01-13 12:12:12.000', '12:11:12'); 792 insert into time01 values ('2014-12-11', '2021-01-11 10:00:02', '1981-02-13 12:12:12.000', '14:12:12'); 793 insert into time01 values ('2015-10-11', '2021-11-11 10:00:03', '1982-01-13 12:12:12.000', '15:12:12'); 794 insert into time01 values ('2016-10-11', '2021-11-11 10:00:04', '1983-01-13 12:12:12.000', '16:12:12'); 795 insert into time01 values ('2017-10-11', '2021-11-11 10:00:05', '1984-01-13 12:12:12.000', '17:12:12'); 796 insert into time01 values ('2018-10-11', '2021-11-11 10:00:06', '1985-01-13 12:12:12.000', '18:12:12'); 797 insert into time01 values ('2019-10-11', '2021-11-11 10:00:07', '1986-01-13 12:12:12.000', '19:12:12'); 798 insert into time01 values ('2010-10-11', '2021-11-11 10:00:08', '1987-01-13 12:12:12.000', '20:12:12'); 799 insert into time01 values ('2033-10-11', '2021-11-11 10:00:09', '1988-01-13 12:12:12.000', '21:12:12'); 800 insert into time01 values ('2014-10-12', '2021-11-11 10:00:20', '1989-02-13 12:12:12.000', '22:12:12'); 801 drop table if exists new_table; 802 create table new_table as 803 select * 804 from time01 805 order by col1 806 limit 5 807 offset 10; 808 select * from new_table; 809 col1 col2 col3 col4 810 2019-10-11 2021-11-11 10:00:07 1986-01-13 12:12:12 19:12:12 811 2020-10-11 2023-11-11 10:00:01 1997-01-13 12:12:12 12:12:12 812 2033-10-11 2021-11-11 10:00:09 1988-01-13 12:12:12 21:12:12 813 drop table new_table; 814 drop table if exists new_table01; 815 create table new_table01 as 816 select col2, col3 817 from time01 818 order by col1 desc 819 limit 100 820 offset 10; 821 select * from new_table01; 822 col2 col3 823 2021-11-11 10:00:08 1987-01-13 12:12:12 824 1990-10-10 01:01:01 2001-12-12 01:01:01 825 null null 826 drop table new_table01; 827 drop table time01; 828 drop table if exists orders; 829 create table orders (order_id int primary key , customer_id int, order_date date, total_amount decimal); 830 insert into orders values (1, 101, '2023-01-01', 100.00), 831 (2, 101, '2023-01-05', 150.00), 832 (3, 102, '2023-01-02', 200.00), 833 (4, 103, '2023-01-03', 50.00), 834 (5, 101, '2023-01-04', 75.00), 835 (6, 104, '2023-01-06', 300.00), 836 (7, 104, '2023-01-07', 200.00), 837 (8, 105, '2023-01-08', 100.00); 838 select * from orders; 839 order_id customer_id order_date total_amount 840 1 101 2023-01-01 100 841 2 101 2023-01-05 150 842 3 102 2023-01-02 200 843 4 103 2023-01-03 50 844 5 101 2023-01-04 75 845 6 104 2023-01-06 300 846 7 104 2023-01-07 200 847 8 105 2023-01-08 100 848 drop table if exists customer_totals; 849 create table customer_totals as select customer_id, count(order_id) as total_orders, sum(total_amount) as total_amount from orders group by customer_id having count(order_id) > 1 and sum(total_amount) > 150.0; 850 show create table customer_totals; 851 852 select * from customer_totals; 853 customer_id total_orders total_amount 854 101 3 325 855 104 2 500 856 drop table if exists max_totals; 857 create table max_totals as select customer_id, total_orders from customer_totals order by total_orders desc limit 1; 858 desc max_totals; 859 860 select * from max_totals; 861 customer_id total_orders 862 101 3 863 drop table if exists max_customer; 864 create table max_customer as select customer_id, total_amount from customer_totals order by total_amount asc limit 1; 865 show create table max_customer; 866 Table Create Table 867 max_customer CREATE TABLE `max_customer` (\n`customer_id` INT DEFAULT NULL,\n`total_amount` DECIMAL(38,0) DEFAULT NULL\n) 868 select * from max_customer; 869 customer_id total_amount 870 101 325 871 drop table orders; 872 drop table customer_totals; 873 drop table max_totals; 874 drop table max_customer; 875 drop table if exists original_table; 876 create table original_table (id int primary key, name varchar(50), age int, salary decimal, hire_date date); 877 insert into original_table (id, name, age, salary, hire_date) values (1, 'Alice', 30, 5000.00, '2020-01-01'), 878 (2, 'Bob', 35, 6000.00, '2021-05-15'), 879 (3, 'Charlie', 28, 4500.00, '2022-02-20'), 880 (4, 'David', 40, 7000.00, '2021-10-01'), 881 (5, 'Eve', 25, 4000.00, '2020-07-15'); 882 drop table if exists selected_employees; 883 create table selected_employees as select * from original_table where 884 salary >= 5500.00 885 and salary < 7000.00 886 and age > 29 887 and hire_date >= '2021-01-01' 888 and name not like 'A%' 889 and id not in (1, 3) 890 and salary between 5000.00 and 6500.00; 891 892 show create table selected_employees; 893 894 select * from selected_employees; 895 896 drop table selected_employees; 897 898 drop table original_table; 899 drop table if exists view01; 900 drop table if exists view02; 901 drop view if exists v1; 902 create table view01 (a int, b int); 903 insert into view01 values (1,2),(3,4); 904 create table view02 select * from view01; 905 create view v1 as select * from view02; 906 select * from v1; 907 a b 908 1 2 909 3 4 910 drop view v1; 911 drop table view01; 912 drop table view02; 913 drop table if exists table01; 914 create table table01 ( 915 id int auto_increment primary key, 916 col1 varchar(255) not null , 917 col2 int, 918 col3 decimal(10, 2), 919 col4 date, 920 col5 boolean, 921 col6 enum('apple', 'banana', 'orange'), 922 col7 text, 923 col8 timestamp, 924 col9 blob, 925 col10 char, 926 unique index(col8, col10) 927 ); 928 insert into table01 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values 929 ('Value2', 456, 78.90, '2023-10-24', false, 'banana', 'Another text', '2022-01-01 01:01:01.000', 'More binary data', 'D'), 930 ('Value3', 789, 12.34, '2023-10-25', true, 'orange', 'Yet another text', '1979-01-01 01:01:01.123', 'Even more binary data', 'E'); 931 create table test.table02 as select * from table01; 932 show create table table02; 933 Table Create Table 934 table02 CREATE TABLE `table02` (\n`id` INT NOT NULL AUTO_INCREMENT,\n`col1` VARCHAR(255) NOT NULL,\n`col2` INT DEFAULT NULL,\n`col3` DECIMAL(10,2) DEFAULT NULL,\n`col4` DATE DEFAULT NULL,\n`col5` BOOL DEFAULT NULL,\n`col6` ENUM('apple','banana','orange') DEFAULT NULL,\n`col7` TEXT DEFAULT NULL,\n`col8` TIMESTAMP DEFAULT NULL,\n`col9` BLOB DEFAULT NULL,\n`col10` CHAR(1) DEFAULT NULL\n) 935 select * from table02; 936 id col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 937 1 Value2 456 78.90 2023-10-24 false banana Another text 2022-01-01 01:01:01 More binary data D 938 2 Value3 789 12.34 2023-10-25 true orange Yet another text 1979-01-01 01:01:01 Even more binary data E 939 insert into table02 values (12, 'Value1', 123, 45.67, '2023-10-23', TRUE, 'apple', 'This is a text', '2019-01-01 01:01:01.000', 'Some binary data', 'C'); 940 select * from table02; 941 id col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 942 1 Value2 456 78.90 2023-10-24 false banana Another text 2022-01-01 01:01:01 More binary data D 943 2 Value3 789 12.34 2023-10-25 true orange Yet another text 1979-01-01 01:01:01 Even more binary data E 944 12 Value1 123 45.67 2023-10-23 true apple This is a text 2019-01-01 01:01:01 Some binary data C 945 update table02 set col1 = 'newvalue' where col2 = 456; 946 delete from table02 where col10 = 'D'; 947 select * from table02; 948 id col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 949 2 Value3 789 12.34 2023-10-25 true orange Yet another text 1979-01-01 01:01:01 Even more binary data E 950 12 Value1 123 45.67 2023-10-23 true apple This is a text 2019-01-01 01:01:01 Some binary data C 951 alter table table02 add column newcolumn int after col3, drop column col4; 952 show create table table02; 953 Table Create Table 954 table02 CREATE TABLE `table02` (\n`id` INT NOT NULL AUTO_INCREMENT,\n`col1` VARCHAR(255) NOT NULL,\n`col2` INT DEFAULT NULL,\n`col3` DECIMAL(10,2) DEFAULT NULL,\n`newcolumn` INT DEFAULT NULL,\n`col5` BOOL DEFAULT NULL,\n`col6` ENUM('apple','banana','orange') DEFAULT NULL,\n`col7` TEXT DEFAULT NULL,\n`col8` TIMESTAMP DEFAULT NULL,\n`col9` BLOB DEFAULT NULL,\n`col10` CHAR(1) DEFAULT NULL\n) 955 alter table table02 modify column newcolumn bigint; 956 desc table02; 957 Field Type Null Key Default Extra Comment 958 id INT(32) NO null 959 col1 VARCHAR(255) NO null 960 col2 INT(32) YES null 961 col3 DECIMAL64(10) YES null 962 newcolumn BIGINT(64) YES null 963 col5 BOOL(0) YES null 964 col6 ENUM('apple','banana','orange') YES null 965 col7 TEXT(0) YES null 966 col8 TIMESTAMP(0) YES null 967 col9 BLOB(0) YES null 968 col10 CHAR(1) YES null 969 select * from table02; 970 id col1 col2 col3 newcolumn col5 col6 col7 col8 col9 col10 971 2 Value3 789 12.34 null true orange Yet another text 1979-01-01 01:01:01 Even more binary data E 972 12 Value1 123 45.67 null true apple This is a text 2019-01-01 01:01:01 Some binary data C 973 truncate table02; 974 select * from table02; 975 id col1 col2 col3 newcolumn col5 col6 col7 col8 col9 col10 976 drop table table02; 977 drop table table01; 978 drop table if exists students; 979 create table students (student_id int primary key , student_name varchar(20), student_age int); 980 insert into students values (1, 'Alice', 20); 981 insert into students values (2, 'Bob', 22); 982 insert into students values (3, 'Charlie', 21); 983 insert into students values (4, 'Dave', 23); 984 drop table if exists courses; 985 create table courses (course_id int, course_name varchar(10)); 986 insert into courses values (101, 'Math'), (102, 'English'), (103, 'History'), (104, 'Science'); 987 drop table if exists enrollments; 988 create table enrollments (student_id int, course_id int); 989 insert into enrollments values (1, 101), (1, 103), (2, 102), (3, 101), (3, 102), (3, 103), (4, 104); 990 drop table if exists c_enrollments; 991 create table student_course_enrollments as 992 select 993 s.student_id, 994 s.student_name, 995 s.student_age, 996 c.course_name 997 from 998 students s 999 left join 1000 enrollments e ON s.student_id = e.student_id 1001 left join 1002 courses c ON e.course_id = c.course_id; 1003 select * from student_course_enrollments; 1004 student_id student_name student_age course_name 1005 1 Alice 20 Math 1006 1 Alice 20 History 1007 2 Bob 22 English 1008 3 Charlie 21 Math 1009 3 Charlie 21 English 1010 3 Charlie 21 History 1011 4 Dave 23 Science 1012 show create table student_course_enrollments; 1013 1014 drop table if exists student_course_enrollments_inner; 1015 create table student_course_enrollments_inner AS 1016 select 1017 s.student_id, 1018 s.student_name, 1019 s.student_age, 1020 c.course_name 1021 from 1022 students s 1023 inner join 1024 enrollments e on s.student_id = e.student_id 1025 inner join 1026 courses c on e.course_id = c.course_id; 1027 show create table student_course_enrollments_inner; 1028 1029 select * from student_course_enrollments; 1030 student_id student_name student_age course_name 1031 1 Alice 20 Math 1032 1 Alice 20 History 1033 2 Bob 22 English 1034 3 Charlie 21 Math 1035 3 Charlie 21 English 1036 3 Charlie 21 History 1037 4 Dave 23 Science 1038 drop table if exists student_course_enrollments_right; 1039 create table test.student_course_enrollments_right AS 1040 select 1041 s.student_id, 1042 s.student_name, 1043 s.student_age, 1044 c.course_name 1045 from 1046 students s 1047 right join 1048 enrollments e on s.student_id = e.student_id 1049 right join 1050 courses c on e.course_id = c.course_id; 1051 show create table student_course_enrollments_right; 1052 1053 select * from student_course_enrollments_right; 1054 student_id student_name student_age course_name 1055 1 Alice 20 Math 1056 3 Charlie 21 Math 1057 2 Bob 22 English 1058 3 Charlie 21 English 1059 1 Alice 20 History 1060 3 Charlie 21 History 1061 4 Dave 23 Science 1062 drop table if exists student_course_enrollments_full; 1063 create table student_course_enrollments_full AS 1064 select 1065 s.student_id, 1066 s.student_name, 1067 s.student_age, 1068 c.course_name 1069 from 1070 students s 1071 right join 1072 enrollments e on s.student_id = e.student_id 1073 right join 1074 courses c on e.course_id = c.course_id; 1075 show create table student_course_enrollments_full; 1076 1077 select * from student_course_enrollments_full; 1078 student_id student_name student_age course_name 1079 1 Alice 20 Math 1080 3 Charlie 21 Math 1081 2 Bob 22 English 1082 3 Charlie 21 English 1083 1 Alice 20 History 1084 3 Charlie 21 History 1085 4 Dave 23 Science 1086 drop table if exists outerjoin01; 1087 create table outerjoin01 (col1 int, col2 char(3)); 1088 insert into outerjoin01 values(10,'aaa'), (10,null), (10,'bbb'), (20,'zzz'); 1089 drop table if exists outerjoin02; 1090 create table outerjoin02(a1 char(3), a2 int, a3 real); 1091 insert into outerjoin02 values('AAA', 10, 0.5); 1092 insert into outerjoin02 values('BBB', 20, 1.0); 1093 drop table if exists oj01; 1094 create table oj01 as select outerjoin01.col1, outerjoin01.col2, outerjoin02.a2 from outerjoin01 left outer join outerjoin02 on outerjoin01.col1=10 limit 3; 1095 show create table oj01; 1096 Table Create Table 1097 oj01 CREATE TABLE `oj01` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(3) DEFAULT NULL,\n`a2` INT DEFAULT NULL\n) 1098 select * from oj01; 1099 col1 col2 a2 1100 10 aaa 10 1101 10 aaa 20 1102 10 null 10 1103 drop table if exists oj02; 1104 create table oj02 as select outerjoin01.col1, outerjoin01.col2, outerjoin02.a2 from outerjoin01 natural join outerjoin02 order by col1 desc; 1105 show create table oj02; 1106 Table Create Table 1107 oj02 CREATE TABLE `oj02` (\n`col1` INT DEFAULT NULL,\n`col2` CHAR(3) DEFAULT NULL,\n`a2` INT DEFAULT NULL\n) 1108 select * from oj02; 1109 col1 col2 a2 1110 20 zzz 10 1111 20 zzz 20 1112 10 aaa 10 1113 10 null 10 1114 10 bbb 10 1115 10 aaa 20 1116 10 null 20 1117 10 bbb 20 1118 drop table oj01; 1119 drop table oj02; 1120 drop table outerjoin01; 1121 drop table outerjoin02; 1122 drop table student_course_enrollments; 1123 drop table student_course_enrollments_full; 1124 drop table student_course_enrollments_inner; 1125 drop table student_course_enrollments_right; 1126 drop table if exists employees; 1127 create table employees (col1 int, col2 bigint); 1128 insert into employees values (1, 50000), (2, 60000), (3, 55000), (4, 70000); 1129 drop table if exists sal; 1130 create table sal as 1131 select 1132 col1, 1133 col2, 1134 col2 * 0.1 as bonus 1135 from 1136 employees; 1137 select * from sal; 1138 col1 col2 bonus 1139 1 50000 5000.0 1140 2 60000 6000.0 1141 3 55000 5500.0 1142 4 70000 7000.0 1143 drop table if exists sal; 1144 create table test.sal as 1145 select 1146 col1, 1147 col2, 1148 (select col2 * 0.1 from employees e2 where e2.col1 = e1.col1) as bonus 1149 from 1150 employees e1; 1151 select * from sal; 1152 col1 col2 bonus 1153 1 50000 5000.0 1154 2 60000 6000.0 1155 3 55000 5500.0 1156 4 70000 7000.0 1157 drop table if exists sal; 1158 create table sal as 1159 select 1160 col1, 1161 col2, 1162 (select col2 from employees where col2 = 60000) 1163 from employees; 1164 select * from sal; 1165 col1 col2 (select col2 from employees where col2 = 60000) 1166 1 50000 60000 1167 2 60000 60000 1168 3 55000 60000 1169 4 70000 60000 1170 drop table employees; 1171 drop table sal; 1172 drop table if exists test01; 1173 create table test01 (col1 int, col2 decimal, col3 varchar(50)); 1174 insert into test01 values (1, 3242434.423, '3224332r32r'); 1175 insert into test01 values (2, 39304.3424, '343234343213124'); 1176 insert into test01 values (3, 372.324, '00'); 1177 drop table if exists test02; 1178 create table test02 (col1 int primary key ) as select col1 from test01; 1179 show create table test02; 1180 Table Create Table 1181 test02 CREATE TABLE `test02` (\n`col1` INT NOT NULL,\nPRIMARY KEY (`col1`)\n) 1182 desc test02; 1183 Field Type Null Key Default Extra Comment 1184 col1 INT(32) NO PRI null 1185 insert into test02 values (2); 1186 Duplicate entry '2' for key 'col1' 1187 drop table if exists test03; 1188 create table test03 (col2 decimal unique key) as select col2 from test01; 1189 show create table test03; 1190 Table Create Table 1191 test03 CREATE TABLE `test03` (\n`col2` DECIMAL(38,0) DEFAULT NULL,\nUNIQUE KEY `col2` (`col2`)\n) 1192 desc test03; 1193 Field Type Null Key Default Extra Comment 1194 col2 DECIMAL128(38) YES UNI null 1195 insert into test03 values (372.324); 1196 Duplicate entry '372' for key '__mo_index_idx_col' 1197 drop table if exists test04; 1198 create table test04 (col1 int, col2 varchar(50), key(col1, col2)) as select col1, col3 from test01; 1199 show create table test04; 1200 Table Create Table 1201 test04 CREATE TABLE `test04` (\n`col2` VARCHAR(50) DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col3` VARCHAR(50) DEFAULT NULL,\nKEY `col1` (`col1`,`col2`)\n) 1202 select * from test04; 1203 col2 col1 col3 1204 null 1 3224332r32r 1205 null 2 343234343213124 1206 null 3 00 1207 drop table if exists test05; 1208 create table test05 (col1 int, col2 decimal, primary key (col1, col2)) as select col1, col2 from test01; 1209 show create table test05; 1210 Table Create Table 1211 test05 CREATE TABLE `test05` (\n`col1` INT NOT NULL,\n`col2` DECIMAL(38,0) NOT NULL,\nPRIMARY KEY (`col1`,`col2`)\n) 1212 select * from test05; 1213 col1 col2 1214 1 3242434 1215 2 39304 1216 3 372 1217 insert into test05 values (2, 39304.3424); 1218 Duplicate entry ('\d\w\d{38}'|'\(\d\,\d{5}\)') for key '__mo_cpkey_col' 1219 alter table test01 rename column col1 to newCol; 1220 show create table test01; 1221 Table Create Table 1222 test01 CREATE TABLE `test01` (\n`newcol` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL,\n`col3` VARCHAR(50) DEFAULT NULL\n) 1223 drop table if exists test06; 1224 create table test06 (col1 int not null default 100) as select col1 from test01; 1225 invalid input: column col1 does not exist 1226 create table test06 (col1 int not null default 100) as select newcol from test01; 1227 show create table test06; 1228 1229 select * from test06; 1230 col1 newcol 1231 100 1 1232 100 2 1233 100 3 1234 drop table test01; 1235 drop table test02; 1236 drop table test03; 1237 drop table test04; 1238 drop table test05; 1239 drop table test06; 1240 drop table if exists prepare01; 1241 create table prepare01(col1 int primary key , col2 char); 1242 insert into prepare01 values (1,'a'),(2,'b'),(3,'c'); 1243 show create table prepare01; 1244 Table Create Table 1245 prepare01 CREATE TABLE `prepare01` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT NULL,\nPRIMARY KEY (`col1`)\n) 1246 show columns from prepare01; 1247 Field Type Null Key Default Extra Comment 1248 col1 INT(32) NO PRI null 1249 col2 CHAR(1) YES null 1250 drop table if exists prepare02; 1251 prepare s1 from 'create table prepare02 as select * from prepare01'; 1252 execute s1; 1253 show create table prepare02; 1254 Table Create Table 1255 prepare02 CREATE TABLE `prepare02` (\n`col1` INT NOT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 1256 select * from prepare02; 1257 col1 col2 1258 1 a 1259 2 b 1260 3 c 1261 drop table if exists prepare03; 1262 prepare s2 from 'create table prepare03(col1 int, col2 char, col3 char) as select col1, col2 from prepare01'; 1263 execute s2; 1264 select * from prepare03; 1265 col3 col1 col2 1266 null 1 a 1267 null 2 b 1268 null 3 c 1269 show create table prepare03; 1270 Table Create Table 1271 prepare03 CREATE TABLE `prepare03` (\n`col3` CHAR(1) DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col2` CHAR(1) DEFAULT NULL\n) 1272 drop table prepare01; 1273 drop table prepare02; 1274 drop table prepare03; 1275 drop table if exists orders; 1276 create table orders (order_id int primary key , customer_id int, order_date date, total_amount decimal); 1277 insert into orders values (1, 101, '2023-01-01', 100.00), 1278 (2, 101, '2023-01-05', 150.00), 1279 (3, 102, '2023-01-02', 200.00), 1280 (4, 103, '2023-01-03', 50.00), 1281 (5, 101, '2023-01-04', 75.00), 1282 (6, 104, '2023-01-06', 300.00), 1283 (7, 104, '2023-01-07', 200.00), 1284 (8, 105, '2023-01-08', 100.00); 1285 select * from orders; 1286 order_id customer_id order_date total_amount 1287 1 101 2023-01-01 100 1288 2 101 2023-01-05 150 1289 3 102 2023-01-02 200 1290 4 103 2023-01-03 50 1291 5 101 2023-01-04 75 1292 6 104 2023-01-06 300 1293 7 104 2023-01-07 200 1294 8 105 2023-01-08 100 1295 drop table if exists customer_totals; 1296 create temporary table customer_totals as select customer_id, count(order_id) as total_orders, sum(total_amount) as total_amount from orders group by customer_id having count(order_id) > 1 and sum(total_amount) > 150.0; 1297 show create table customer_totals; 1298 1299 select * from customer_totals; 1300 customer_id total_orders total_amount 1301 101 3 325 1302 104 2 500 1303 drop table if exists max_totals; 1304 create temporary table max_totals as select customer_id, total_orders from customer_totals order by total_orders desc limit 1; 1305 desc max_totals; 1306 1307 select * from max_totals; 1308 customer_id total_orders 1309 101 3 1310 drop table if exists max_customer; 1311 create temporary table max_customer as select customer_id, total_amount from customer_totals order by total_amount asc limit 1; 1312 show create table max_customer; 1313 Table Create Table 1314 max_customer CREATE TABLE `max_customer` (\n`customer_id` INT DEFAULT NULL,\n`total_amount` DECIMAL(38,0) DEFAULT NULL\n) 1315 select * from max_customer; 1316 customer_id total_amount 1317 101 325 1318 drop table orders; 1319 drop table customer_totals; 1320 drop table max_totals; 1321 drop table max_customer; 1322 drop table if exists table10; 1323 drop table if exists table11; 1324 create table table10 as select col100 from table07; 1325 SQL parser error: table "table07" does not exist 1326 create table table11 (col20 decimal, col30 char, col40 varchar) as select col100 from table07; 1327 SQL parser error: table "table07" does not exist 1328 drop table table07; 1329 no such table test.table07 1330 drop table table08; 1331 no such table test.table08 1332 drop table table09; 1333 no such table test.table09 1334 drop table table12; 1335 no such table test.table12 1336 drop table if exists abnormal01; 1337 create table abnormal01 (col1 int default null ); 1338 insert into abnormal01 values (1), (null); 1339 drop table if exists abnormal02; 1340 create table test.abnormal02 (col1 int not null) as select col1 from abnormal01; 1341 constraint violation: Column 'col1' cannot be null 1342 drop table abnormal01; 1343 drop table if exists abnormal03; 1344 create table abnormal03 (col1 int, col2 bigint); 1345 insert into abnormal03 values (1, 8324824234); 1346 insert into abnormal03 values (1, 8324824234); 1347 select * from abnormal03; 1348 col1 col2 1349 1 8324824234 1350 1 8324824234 1351 drop table if exists abnormal04; 1352 drop table if exists abnormal05; 1353 create table abnormal04 (col1 int primary key ) as select col1 from abnormal03; 1354 Duplicate entry '1' for key 'col1' 1355 create table abnormal05 (col2 bigint unique key) as select col2 from abnormal03; 1356 Duplicate entry '8324824234' for key '__mo_index_idx_col' 1357 drop table abnormal03; 1358 drop table if exists abnormal06; 1359 create table abnormal06 (col1 bigint, col2 decimal); 1360 insert into abnormal06 values (271928310313092, 32984832.3214214); 1361 drop table if exists abnormal07; 1362 create table abnormal07 (col1 int) as select col1 from abnormal06; 1363 Data truncation: data out of range: data type int32, value '271928310313092' 1364 drop table abnormal06; 1365 drop table if exists abnormal07; 1366 create table abnormal07 (col1 int, col2 bigint, col3 decimal, col4 char); 1367 insert into abnormal07 values (1, 2, 3, 'a'); 1368 insert into abnormal07 values (1, 2, 3, 'b'); 1369 insert into abnormal07 values (1, 2, 3, 'c'); 1370 insert into abnormal07 values (1, 2, 3, 'd'); 1371 insert into abnormal07 values (null, null, null, null); 1372 drop table if exists abnormal08; 1373 create table abnormal08 as select col1, col2, col3, col4, col5 from abnormal07; 1374 invalid input: column col5 does not exist 1375 create table abnormal07 as select * from abnormal07; 1376 table abnormal07 already exists 1377 drop table abnormal07; 1378 drop table if exists abnormal09; 1379 create table abnormal09 (col1 int, col2 decimal); 1380 insert into abnormal09 values (1, 2); 1381 insert into abnormal09 values (1, 2); 1382 drop table if exists abnormal10; 1383 create table abnormal10(col1 int primary key) as select col1 from abnormal09; 1384 Duplicate entry '1' for key 'col1' 1385 create table abnormal10(col2 decimal unique key) as select col2 from abnormal09; 1386 table abnormal10 already exists 1387 drop table abnormal09; 1388 drop table if exists abnormal10; 1389 create table abnormal10 as select * from mo_catalog.mo_columns; 1390 drop table if exists time_window01; 1391 create table time_window01 (ts timestamp primary key , col2 int); 1392 insert into time_window01 values ('2021-01-12 00:00:00.000', 12); 1393 insert into time_window01 values ('2020-01-12 12:00:12.000', 24); 1394 insert into time_window01 values ('2023-01-12 00:00:00.000', 34); 1395 insert into time_window01 values ('2024-01-12 12:00:12.000', 20); 1396 select * from time_window01; 1397 ts col2 1398 2021-01-12 00:00:00 12 1399 2020-01-12 12:00:12 24 1400 2023-01-12 00:00:00 34 1401 2024-01-12 12:00:12 20 1402 drop table if exists time_window02; 1403 create table time_window02 as select _wstart, _wend, max(col2), min(col2) from time_window01 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 1404 select * from time_window02; 1405 _wstart _wend max(col2) min(col2) 1406 2019-12-08 00:00:00 2020-03-17 00:00:00 24 24 1407 2021-01-11 00:00:00 2021-04-21 00:00:00 12 12 1408 drop table time_window01; 1409 drop table time_window02; 1410 drop table if exists time_window03; 1411 create table time_window03 (ts timestamp primary key , col2 bool); 1412 insert into time_window03 values ('2023-10-26 10:00:00.000', false); 1413 insert into time_window03 values ('2023-10-26 10:10:00.000', true); 1414 insert into time_window03 values ('2023-10-26 10:20:00.000', null); 1415 insert into time_window03 values ('2023-10-26 10:30:00.000', true); 1416 select * from time_window03; 1417 ts col2 1418 2023-10-26 10:00:00 false 1419 2023-10-26 10:10:00 true 1420 2023-10-26 10:20:00 null 1421 2023-10-26 10:30:00 true 1422 drop table if exists time_window04; 1423 create table time_window04 as select _wstart, _wend, max(col2), min(col2) from time_window03 where ts > '2020-01-11 12:00:12.000' and ts < '2024-01-13 00:00:00.000' interval(ts, 10, second) fill(prev); 1424 select * from time_window04; 1425 _wstart _wend max(col2) min(col2) 1426 2023-10-26 10:00:00 2023-10-26 10:00:10 false false 1427 2023-10-26 10:10:00 2023-10-26 10:10:10 true true 1428 2023-10-26 10:20:00 2023-10-26 10:20:10 true true 1429 2023-10-26 10:30:00 2023-10-26 10:30:10 true true 1430 select * from time_window03; 1431 ts col2 1432 2023-10-26 10:00:00 false 1433 2023-10-26 10:10:00 true 1434 2023-10-26 10:20:00 null 1435 2023-10-26 10:30:00 true 1436 drop table time_window03; 1437 drop table time_window04; 1438 drop table if exists test.window01; 1439 create table window01 (user_id integer not null, date date); 1440 insert into window01 values (1, '2002-06-09'); 1441 insert into window01 values (2, '2002-06-09'); 1442 insert into window01 values (1, '2002-06-09'); 1443 insert into window01 values (3, '2002-06-09'); 1444 insert into window01 values (4, '2002-06-09'); 1445 insert into window01 values (4, '2002-06-09'); 1446 insert into window01 values (5, '2002-06-09'); 1447 drop table if exists window02; 1448 create table window02 as select rank() over () r from window01; 1449 select * from window02; 1450 r 1451 1 1452 1 1453 1 1454 1 1455 1 1456 1 1457 1 1458 drop table if exists window03; 1459 create table window03 as select dense_rank() over () r from window01; 1460 select * from window03; 1461 r 1462 1 1463 1 1464 1 1465 1 1466 1 1467 1 1468 1 1469 drop table window01; 1470 drop table window02; 1471 drop table window03; 1472 drop table if exists row01; 1473 create table row01(i int,j int); 1474 insert into row01 values(1,1); 1475 insert into row01 values(1,4); 1476 insert into row01 values(1,2); 1477 insert into row01 values(1,4); 1478 drop table if exists row02; 1479 create table row02 as select i, j, sum(i+j) over (order by j rows between 2 preceding and 1 preceding) foo from row01 order by foo desc; 1480 select * from row02; 1481 i j foo 1482 1 4 8 1483 1 4 5 1484 1 2 2 1485 1 1 null 1486 drop table if exists row03; 1487 create table row03 as select i, j, sum(i+j) over (order by j rows between 2 following and 1 following) foo from row01 order by foo desc; 1488 select * from row03; 1489 i j foo 1490 1 1 null 1491 1 2 null 1492 1 4 null 1493 1 4 null 1494 drop table row01; 1495 drop table row02; 1496 drop table row03; 1497 drop table if exists dense_rank01; 1498 create table dense_rank01 (id integer, sex char(1)); 1499 insert into dense_rank01 values (1, 'm'); 1500 insert into dense_rank01 values (2, 'f'); 1501 insert into dense_rank01 values (3, 'f'); 1502 insert into dense_rank01 values (4, 'f'); 1503 insert into dense_rank01 values (5, 'm'); 1504 drop table if exists dense_rank02; 1505 create table dense_rank02 as select sex, id, rank() over (partition by sex order by id desc) from dense_rank01; 1506 select * from dense_rank02; 1507 sex id rank() over (partition by sex order by id desc) 1508 f 4 1 1509 f 3 2 1510 f 2 3 1511 m 5 1 1512 m 1 2 1513 drop table if exists dense_rank03; 1514 create table dense_rank03 as select sex, id, dense_rank() over (partition by sex order by id desc) from dense_rank01; 1515 select * from dense_rank03; 1516 sex id dense_rank() over (partition by sex order by id desc) 1517 f 4 1 1518 f 3 2 1519 f 2 3 1520 m 5 1 1521 m 1 2 1522 drop table dense_rank01; 1523 drop table dense_rank02; 1524 drop table dense_rank03; 1525 drop table if exists test01; 1526 create table test01( 1527 col1 tinyint, 1528 col2 smallint, 1529 col3 int, 1530 col4 bigint, 1531 col5 tinyint unsigned, 1532 col6 smallint unsigned, 1533 col7 int unsigned, 1534 col8 bigint unsigned, 1535 col9 float, 1536 col10 double 1537 ); 1538 insert into test01 values (1,2,3,4,5,6,7,8,10.2131,3824.34324); 1539 insert into test01 values (2,3,4,5,6,7,8,9,2131.3242343,-3824.34324); 1540 show create table test01; 1541 Table Create Table 1542 test01 CREATE TABLE `test01` (\n`col1` TINYINT DEFAULT NULL,\n`col2` SMALLINT DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` BIGINT DEFAULT NULL,\n`col5` TINYINT UNSIGNED DEFAULT NULL,\n`col6` SMALLINT UNSIGNED DEFAULT NULL,\n`col7` INT UNSIGNED DEFAULT NULL,\n`col8` BIGINT UNSIGNED DEFAULT NULL,\n`col9` FLOAT DEFAULT NULL,\n`col10` DOUBLE DEFAULT NULL\n) 1543 create publication publication01 database test; 1544 show publications; 1545 publication database create_time update_time sub_account comments 1546 publication01 test 2024-04-19 10:33:28 null * 1547 drop table if exists test02; 1548 create table test02 as select * from test01; 1549 select * from test02; 1550 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 1551 1 2 3 4 5 6 7 8 10.2131 3824.34324 1552 2 3 4 5 6 7 8 9 2131.3242 -3824.34324 1553 drop publication publication01; 1554 drop table test01; 1555 drop account if exists acc0; 1556 create account acc0 admin_name 'root' identified by '111'; 1557 drop table if exists sys_tbl_1; 1558 create table sys_tbl_1(a int primary key, b decimal, c char, d varchar(20) ); 1559 insert into sys_tbl_1 values(1,2,'a','database'),(2,3,'b','test publication'),(3, 4, 'c','324243243'); 1560 create publication sys_pub_1 database test; 1561 select * from sys_tbl_1; 1562 a b c d 1563 1 2 a database 1564 2 3 b test publication 1565 3 4 c 324243243 1566 show publications; 1567 publication database create_time update_time sub_account comments 1568 sys_pub_1 test 2024-04-19 10:33:29 null * 1569 select pub_name, database_name, account_list from mo_catalog.mo_pubs; 1570 pub_name database_name account_list 1571 sys_pub_1 test all 1572 create database sub1 from sys publication sys_pub_1; 1573 show databases; 1574 Database 1575 information_schema 1576 mo_catalog 1577 mysql 1578 sub1 1579 system 1580 system_metrics 1581 use sub1; 1582 drop table if exists test; 1583 create table test as select * from sys_tbl_1; 1584 internal error: cannot create table in subscription database 1585 drop database sub1; 1586 drop account acc0; 1587 drop publication sys_pub_1; 1588 show variables like 'lower_case_table_names'; 1589 Variable_name Value 1590 lower_case_table_names 1 1591 set @@global.lower_case_table_names = 0; 1592 use test; 1593 drop table if exists alias01; 1594 create table alias01 (col1 int, col2 decimal); 1595 insert into alias01 values (1,2); 1596 insert into alias01 values (2,3); 1597 drop table if exists alias02; 1598 create table alias02 (NewCol int) as select * from alias01; 1599 show create table alias02; 1600 Table Create Table 1601 alias02 CREATE TABLE `alias02` (\n`NewCol` INT DEFAULT NULL,\n`col1` INT DEFAULT NULL,\n`col2` DECIMAL(38,0) DEFAULT NULL\n) 1602 select * from alias02; 1603 NewCol col1 col2 1604 null 1 2 1605 null 2 3 1606 drop table alias01; 1607 drop database test; 1608 drop database if exists db1; 1609 create database db1; 1610 use db1; 1611 drop role if exists role_r1; 1612 drop user if exists role_u1; 1613 create role role_r1; 1614 create user role_u1 identified by '111' default role role_r1; 1615 drop table if exists t1; 1616 create table t1(col1 int); 1617 insert into t1 values(1); 1618 insert into t1 values(2); 1619 grant create database, drop database on account * to role_r1; 1620 grant show databases on account * to role_r1; 1621 grant connect on account * to role_r1; 1622 grant create table, drop table on database *.* to role_r1; 1623 grant show tables on database * to role_r1; 1624 use db1; 1625 drop table if exists t2; 1626 create table t2 as select * from t1; 1627 internal error: do not have privilege to execute the statement 1628 grant select on table * to role_r1; 1629 grant insert on table * to role_r1; 1630 use db1; 1631 drop table if exists t2; 1632 create table t2 as select * from t1; 1633 select * from t2; 1634 col1 1635 1 1636 2 1637 drop table t1; 1638 drop table t2; 1639 drop database db1; 1640 drop database if exists db2; 1641 create database db2; 1642 use db2; 1643 drop role if exists role_r1; 1644 drop role if exists role_r2; 1645 drop user if exists role_u1; 1646 drop user if exists role_u2; 1647 create role role_r1; 1648 create user role_u1 identified by '111' default role role_r1; 1649 create role role_r2; 1650 create user role_u2 identified by '111' default role role_r2; 1651 drop table if exists t1; 1652 create table t1(col1 int); 1653 insert into t1 values(1); 1654 insert into t1 values(2); 1655 grant create database, drop database on account * to role_r1; 1656 grant show databases on account * to role_r1; 1657 grant connect on account * to role_r1; 1658 grant create table, drop table on database *.* to role_r1; 1659 grant show tables on database * to role_r1; 1660 grant select on table * to role_r1; 1661 grant insert on table * to role_r1; 1662 use db2; 1663 drop table if exists t2; 1664 create table t2 as select * from t1; 1665 use db2; 1666 internal error: do not have privilege to execute the statement 1667 drop table if exists t3; 1668 internal error: do not have privilege to execute the statement 1669 create table t3 as select * from t2; 1670 internal error: do not have privilege to execute the statement 1671 select * from t3; 1672 not connect to a database 1673 grant create database, drop database on account * to role_r2; 1674 grant show databases on account * to role_r2; 1675 grant connect on account * to role_r2; 1676 grant create table, drop table on database *.* to role_r2; 1677 grant show tables on database * to role_r2; 1678 grant select on table * to role_r2; 1679 grant insert on table * to role_r2; 1680 use db2; 1681 drop table if exists t3; 1682 create table t3 as select * from t2; 1683 select * from t3; 1684 col1 1685 1 1686 2 1687 drop table t1; 1688 drop table t2; 1689 drop table t3; 1690 drop role role_r1; 1691 drop role role_r2; 1692 drop user role_u1; 1693 drop user role_u2; 1694 drop database db2; 1695 drop role if exists role_r1; 1696 drop user if exists role_u1; 1697 create role role_r1; 1698 create user role_u1 identified by '111' default role role_r1; 1699 grant show databases on account * to role_r1; 1700 grant connect on account * to role_r1; 1701 grant show tables on database * to role_r1; 1702 grant create database, drop database on account * to role_r1; 1703 drop database if exists db3; 1704 create database db3; 1705 drop database if exists db4; 1706 create database db4; 1707 use db3; 1708 grant create table, drop table on database db3 to role_r1; 1709 grant create table, drop table on database db4 to role_r1; 1710 grant select on table * to role_r1; 1711 grant insert on table * to role_r1; 1712 use db4; 1713 grant select on table * to role_r1; 1714 grant insert on table * to role_r1; 1715 use db3; 1716 drop table if exists t1; 1717 create table t1(col1 int); 1718 insert into t1 values(1); 1719 insert into t1 values(2); 1720 drop database if exists db4; 1721 create database db4; 1722 use db4; 1723 drop table if exists t2; 1724 create table t2 as select * from db3.t1; 1725 use db3; 1726 drop table t1; 1727 use db4; 1728 select * from t2; 1729 col1 1730 1 1731 2 1732 drop table t2; 1733 drop role role_r1; 1734 drop user role_u1; 1735 drop database db3; 1736 drop database db4; 1737 drop database if exists db; 1738 create database db; 1739 use db; 1740 drop role if exists role_r1; 1741 drop role if exists role_r2; 1742 drop user if exists role_u1; 1743 drop user if exists role_u2; 1744 create role role_r1; 1745 create user role_u1 identified by '111' default role role_r1; 1746 create role role_r2; 1747 create user role_u2 identified by '111' default role role_r2; 1748 grant create database, drop database on account * to role_r1; 1749 grant show databases on account * to role_r1; 1750 grant connect on account * to role_r1; 1751 grant create table, drop table on database *.* to role_r1; 1752 grant show tables on database * to role_r1; 1753 grant select on table * to role_r1; 1754 grant insert on table * to role_r1; 1755 drop database if exists db5; 1756 create database db5; 1757 use db5; 1758 drop table if exists t1; 1759 create table t1 (col1 int); 1760 insert into t1 values (1); 1761 insert into t1 values (2); 1762 grant role_r1 to role_r2; 1763 drop database if exists db6; 1764 create database db6; 1765 use db6; 1766 create table t2 as select * from db5.t1; 1767 select * from db6.t2; 1768 col1 1769 1 1770 2 1771 drop role role_r1; 1772 drop role role_r2; 1773 drop user role_u1; 1774 drop user role_u2; 1775 drop database db; 1776 drop database db5; 1777 drop database db6; 1778 drop database if exists db7; 1779 create database db7; 1780 use db7; 1781 drop role if exists role_r1; 1782 drop user if exists role_u1; 1783 create role role_r1; 1784 create user role_u1 identified by '111' default role role_r1; 1785 drop table if exists t1; 1786 create table t1(col1 int); 1787 insert into t1 values(1); 1788 insert into t1 values(2); 1789 grant create database, drop database on account * to role_r1; 1790 grant show databases on account * to role_r1; 1791 grant connect on account * to role_r1; 1792 grant create table, drop table on database *.* to role_r1; 1793 grant show tables on database * to role_r1; 1794 create table t2 as select * from t1; 1795 not connect to a database 1796 grant select on table * to role_r1; 1797 grant insert on table * to role_r1; 1798 drop table if exists t2; 1799 create table t2 as select * from t1; 1800 not connect to a database 1801 select * from t2; 1802 not connect to a database 1803 drop table t1; 1804 drop table t2; 1805 no such table db7.t2 1806 drop role role_r1; 1807 drop user role_u1; 1808 drop database db7; 1809 set @@global.lower_case_table_names = 1;