view.test 35.3 KB
Newer Older
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
1
--disable_warnings
2 3
drop table if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
drop view if exists t1,t2,`t1a``b`,v1,v2,v3,v4,v5,v6;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
4 5 6 7 8 9 10 11 12 13 14 15 16 17
drop database if exists mysqltest;
--enable_warnings
use test;

#
# some basic test of views and its functionality
#

# create view on unexistence table
-- error 1146
create view v1 (c,d) as select a,b from t1;

create temporary table t1 (a int, b int);
#view on temporary table
18
-- error 1352
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
19 20 21 22 23 24 25
create view v1 (c) as select b+1 from t1;
drop table t1;

create table t1 (a int, b int);
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);

#view with variable
26
-- error 1351
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
27 28 29 30 31 32 33 34 35 36 37 38 39 40
create view v1 (c,d) as select a,b+@@global.max_user_connections from t1;

# simple view
create view v1 (c) as select b+1 from t1;
select c from v1;

#tamporary table should not shade (hide) table of view
create temporary table t1 (a int, b int);
# this is empty
select * from t1;
# but this based on normal t1
select c from v1;
show create table v1;
show create view v1;
41
-- error 1347
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
show create view t1;
drop table t1;

# try to use fields from underlaid table
-- error 1054
select a from v1;
-- error 1054
select v1.a from v1;
-- error 1054
select b from v1;
-- error 1054
select v1.b from v1;

# view with different algorithms (explain out put are differ)
explain extended select c from v1;
create algorithm=temptable view v2 (c) as select b+1 from t1;
58
show create view v2;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133
select c from v2;
explain extended select c from v2;

# try to use underlaid table fields in VIEW creation process
-- error 1054
create view v3 (c) as select a+1 from v1;
-- error 1054
create view v3 (c) as select b+1 from v1;


# VIEW on VIEW test with mixing different algorithms on different order
create view v3 (c) as select c+1 from v1;
select c from v3;
explain extended select c from v3;
create algorithm=temptable view v4 (c) as select c+1 from v2;
select c from v4;
explain extended select c from v4;
create view v5 (c) as select c+1 from v2;
select c from v5;
explain extended select c from v5;
create algorithm=temptable view v6 (c) as select c+1 from v1;
select c from v6;
explain extended select c from v6;

# show table/table status test
show tables;
--replace_column 12 # 13 #
show table status;

drop view v1,v2,v3,v4,v5,v6;

#
# alter/create view test
#

# view with subqueries of different types
create view v1 (c,d,e,f) as select a,b,
a in (select a+2 from t1), a = all (select a from t1) from t1;
create view v2 as select c, d from v1;
select * from v1;
select * from v2;

# try to create VIEW with name of existing VIEW
-- error 1050
create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1;

# 'or replace' should work in this case
create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1;

# try to ALTER unexisting VIEW
drop view v2;
-- error 1146
alter view v2 as select c, d from v1;

# 'or replace' on unexisting view
create or replace view v2 as select c, d from v1;

# alter view on existing view
alter view v1 (c,d) as select a,max(b) from t1 group by a;

# check that created view works
select * from v1;
select * from v2;

# simple test of grants
grant create view on test.* to test@localhost;
show grants for test@localhost;
revoke create view on test.* from test@localhost;
show grants for test@localhost;

#try to drop unexisten VIEW
-- error 1051
drop view v100;

#try to drop table with DROP VIEW
134
-- error 1347
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
drop view t1;

#try to drop VIEW with DROP TABLE
-- error 1051
drop table v1;

#try to drop table with DROP VIEW

drop view v1,v2;
drop table t1;

#
# outer left join with merged views
#
create table t1 (a int);
insert into t1 values (1), (2), (3);

create view v1 (a) as select a+1 from t1;
create view v2 (a) as select a-1 from t1;

select * from t1 natural left join v1;
select * from v2 natural left join t1;
select * from v2 natural left join v1;

drop view v1, v2;
drop table t1;


#
# grant create view test
#
connect (root,localhost,root,,test);
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int);
create table mysqltest.t2 (a int, b int);

grant select on mysqltest.t1 to mysqltest_1@localhost;
grant create view,select on test.* to mysqltest_1@localhost;

connect (user1,localhost,mysqltest_1,,test);
connection user1;

create view v1  as select * from mysqltest.t1;
# no CRETE VIEW privilege
-- error 1142
create view mysqltest.v2  as select * from mysqltest.t1;
# no SELECT privilege
-- error 1142
create view v2 as select * from mysqltest.t2;

connection root;
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
revoke all privileges on test.* from mysqltest_1@localhost;

drop database mysqltest;
drop view test.v1;

#
# grants per columns
#
# MERGE algorithm
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int);
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
grant select (c) on mysqltest.v1 to mysqltest_1@localhost;

connection user1;
select c from mysqltest.v1;
# there are not privilege ob column 'd'
-- error 1143
select d from mysqltest.v1;

connection root;
revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
drop database mysqltest;

# TEMPORARY TABLE algorithm
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int);
create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
grant select (c) on mysqltest.v1 to mysqltest_1@localhost;

connection user1;
select c from mysqltest.v1;
# there are not privilege ob column 'd'
-- error 1143
select d from mysqltest.v1;

connection root;
revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
drop database mysqltest;

#
# EXPLAIN rights
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings
#prepare views and tables
create table mysqltest.t1 (a int, b int);
create table mysqltest.t2 (a int, b int);
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
grant select on mysqltest.v1 to mysqltest_1@localhost;
grant select on mysqltest.v2 to mysqltest_1@localhost;
grant select on mysqltest.v3 to mysqltest_1@localhost;
grant select on mysqltest.v4 to mysqltest_1@localhost;

connection user1;
# all selects works
select c from mysqltest.v1;
select c from mysqltest.v2;
select c from mysqltest.v3;
select c from mysqltest.v4;
# test of show coluns
show columns from mysqltest.v1;
show columns from mysqltest.v2;
# but explain/show do not
268
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
269
explain select c from mysqltest.v1;
270
-- error 1142
271
show create view mysqltest.v1;
272
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
273
explain select c from mysqltest.v2;
274
-- error 1142
275
show create view mysqltest.v2;
276
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
277
explain select c from mysqltest.v3;
278
-- error 1142
279
show create view mysqltest.v3;
280
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
281
explain select c from mysqltest.v4;
282
-- error 1142
283
show create view mysqltest.v4;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
284 285 286 287 288 289 290

# allow to see one of underlaing table
connection root;
grant select on mysqltest.t1 to mysqltest_1@localhost;
connection user1;
# EXPLAIN of view on above table works
explain select c from mysqltest.v1;
291
-- error 1142
292
show create view mysqltest.v1;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
293
explain select c from mysqltest.v2;
294
-- error 1142
295
show create view mysqltest.v2;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
296
# but other EXPLAINs do not
297
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
298
explain select c from mysqltest.v3;
299
-- error 1142
300
show create view mysqltest.v3;
301
-- error 1345
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
302
explain select c from mysqltest.v4;
303
-- error 1142
304
show create view mysqltest.v4;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
305 306 307 308 309 310

# allow to see any view in mysqltest database
connection root;
grant show view on mysqltest.* to mysqltest_1@localhost;
connection user1;
explain select c from mysqltest.v1;
311
show create view mysqltest.v1;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
312
explain select c from mysqltest.v2;
313
show create view mysqltest.v2;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
314
explain select c from mysqltest.v3;
315
show create view mysqltest.v3;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
316
explain select c from mysqltest.v4;
317
show create view mysqltest.v4;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396

connection root;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
drop database mysqltest;

#
# QUERY CHECHE options for VIEWs
#
set GLOBAL query_cache_size=1355776;
flush status;
create table t1 (a int, b int);

# queries with following views should not be in query cache
create view v1 (c,d) as select sql_no_cache a,b from t1;
create view v2 (c,d) as select a+rand(),b from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
select * from v2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
select * from v2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

drop view v1,v2;

# SQL_CACHE option
set query_cache_type=demand;
flush status;
# query with view will be cached, but direct acess to table will not
create view v1 (c,d) as select sql_cache a,b from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
drop view v1;
set query_cache_type=default;

drop table t1;
set GLOBAL query_cache_size=default;


#
# DISTINCT option for VIEW
#
create table t1 (a int);
insert into t1 values (1), (2), (3), (1), (2), (3);
create view v1 as select distinct a from t1;
select * from v1;
explain select * from v1;
select * from t1;
drop view v1;
drop table t1;

#
# syntax compatibility
#
create table t1 (a int);
monty@mishka.local's avatar
monty@mishka.local committed
397
-- error 1368
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
398
create view v1 as select distinct a from t1 WITH CHECK OPTION;
399 400 401
create view v1 as select a from t1 WITH CHECK OPTION;
create view v2 as select a from t1 WITH CASCADED CHECK OPTION;
create view v3 as select a from t1 WITH LOCAL CHECK OPTION;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451
drop view v3 RESTRICT;
drop view v2 CASCADE;
drop view v1;
drop table t1;

#
# aliases
#
create table t1 (a int, b int);
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1 (c) as select b+1 from t1;
select test.c from v1 test;
create algorithm=temptable view v2 (c) as select b+1 from t1;
select test.c from v2 test;
select test1.* from v1 test1, v2 test2 where test1.c=test2.c;
select test2.* from v1 test1, v2 test2 where test1.c=test2.c;
drop table t1;
drop view v1,v2;

#
# LIMIT clasuse test
#
create table t1 (a int);
insert into t1 values (1), (2), (3), (4);
create view v1 as select a+1 from t1 order by 1 desc limit 2;
select * from v1;
explain select * from v1;
drop view v1;
drop table t1;

#
# CREATE ... SELECT view test
#
create table t1 (a int);
insert into t1 values (1), (2), (3), (4);
create view v1 as select a+1 from t1;
create table t2 select * from v1;
show columns from t2;
select * from t2;
drop view v1;
drop table t1,t2;

#
# simple view + simple update
#
create table t1 (a int, b int, primary key(a));
insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
create view v1 (a,c) as select a, b+1 from t1;
create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
# try to update expression
452
-- error 1348
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473
update v1 set c=a+c;
# try to update VIEW with forced TEMPORARY TABLE algorithm
-- error 1288
update v2 set a=a+c;
# updatable field of updateable view
update v1 set a=a+c;
select * from v1;
select * from t1;
drop table t1;
drop view v1,v2;

#
# simple view + simple multi-update
#
create table t1 (a int, b int, primary key(a));
insert into t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
create table t2 (x int);
insert into t2 values (10), (20);
create view v1 (a,c) as select a, b+1 from t1;
create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
# try to update expression
474
-- error 1348
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630
update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a;
# try to update VIEW with forced TEMPORARY TABLE algorithm
-- error 1288
update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a;
# updatable field of updateable view
update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a;
select * from v1;
select * from t1;
drop table t1,t2;
drop view v1,v2;

#
# UPDATE privileges on VIEW columns and whole VIEW
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int, primary key(a));
insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
create table mysqltest.t2 (x int);
insert into mysqltest.t2 values (3), (4), (5), (6);
create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;

grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
grant update on mysqltest.v1 to mysqltest_1@localhost;
grant select on mysqltest.* to mysqltest_1@localhost;

connection user1;
use mysqltest;
# update with rights on VIEW column
update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c;
select * from t1;
update v1 set a=a+c;
select * from t1;
# update with rights on whole VIEW
update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c;
select * from t1;
update v2 set a=a+c;
select * from t1;
# no rights on column
-- error 1143
update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
-- error 1143
update v2 set c=a+c;
# no rights for view
-- error 1143
update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
-- error 1142
update v3 set a=a+c;

use test;
connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# MEREGE VIEW with WHERE clause
#
create table t1 (a int, b int, primary key(b));
insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100);
create view v1 (c) as select b from t1 where a<3;
# simple select and explaint to be sure that it is MERGE
select * from v1;
explain extended select * from v1;
# update test
update v1 set c=c+1;
select * from t1;
# join of such VIEWs test
create view v2 (c) as select b from t1 where a>=3;
select * from v1, v2;
drop view v1, v2;
drop table t1;

#
# simple view + simple delete
#
create table t1 (a int, b int, primary key(a));
insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
create view v1 (a,c) as select a, b+1 from t1;
create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
# try to update VIEW with forced TEMPORARY TABLE algorithm
-- error 1288
delete from v2 where c < 4;
# updatable field of updateable view
delete from v1 where c < 4;
select * from v1;
select * from t1;
drop table t1;
drop view v1,v2;

#
# simple view + simple multi-delete
#
create table t1 (a int, b int, primary key(a));
insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
create table t2 (x int);
insert into t2 values (1), (2), (3), (4);
create view v1 (a,c) as select a, b+1 from t1;
create algorithm=temptable view v2 (a,c) as select a, b+1 from t1;
# try to update VIEW with forced TEMPORARY TABLE algorithm
-- error 1288
delete v2 from t2,v2 where t2.x=v2.a;
# updatable field of updateable view
delete v1 from t2,v1 where t2.x=v1.a;
select * from v1;
select * from t1;
drop table t1,t2;
drop view v1,v2;

#
# DELETE privileges on VIEW
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int, primary key(a));
insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
create table mysqltest.t2 (x int);
insert into mysqltest.t2 values (3), (4), (5), (6);
create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;

grant delete on mysqltest.v1 to mysqltest_1@localhost;
grant select on mysqltest.* to mysqltest_1@localhost;

connection user1;
use mysqltest;
# update with rights on VIEW column
delete from v1 where c < 4;
select * from t1;
delete v1 from t2,v1 where t2.x=v1.c;
select * from t1;
# no rights for view
-- error 1142
delete v2 from t2,v2 where t2.x=v2.c;
-- error 1142
delete from v2 where c < 4;

use test;
connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# key presence check
#
create table t1 (a int, b int, c int, primary key(a,b));
insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5);
create view v1 (x,y) as select a, b from t1;
create view v2 (x,y) as select a, c from t1;
631
set updatable_views_with_limit=NO;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
632 633 634 635 636
update v1 set x=x+1;
update v2 set x=x+1;
update v1 set x=x+1 limit 1;
-- error 1288
update v2 set x=x+1 limit 1;
637
set updatable_views_with_limit=YES;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
638 639
update v1 set x=x+1 limit 1;
update v2 set x=x+1 limit 1;
640 641
set updatable_views_with_limit=DEFAULT;
show variables like "updatable_views_with_limit";
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696
select * from t1;
drop table t1;
drop view v1,v2;

#
# simple insert
#
create table t1 (a int, b int, c int, primary key(a,b));
insert into t1 values (10,2,-1), (20,3,-2);
create view v1 (x,y,z) as select c, b, a from t1;
create view v2 (x,y) as select b, a from t1;
create view v3 (x,y,z) as select b, a, b from t1;
create view v4 (x,y,z) as select c+1, b, a from t1;
create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1;
# try insert to VIEW with fields duplicate
-- error 1288
insert into v3 values (-60,4,30);
# try insert to VIEW with expression in SELECT list
-- error 1288
insert into v4 values (-60,4,30);
# try insert to VIEW using temporary table algorithm
-- error 1288
insert into v5 values (-60,4,30);
insert into v1 values (-60,4,30);
insert into v1 (z,y,x) values (50,6,-100);
insert into v2 values (5,40);
select * from t1;
drop table t1;
drop view v1,v2,v3,v4,v5;

#
# insert ... select
#
create table t1 (a int, b int, c int, primary key(a,b));
insert into t1 values (10,2,-1), (20,3,-2);
create table t2 (a int, b int, c int, primary key(a,b));
insert into t2 values (30,4,-60);
create view v1 (x,y,z) as select c, b, a from t1;
create view v2 (x,y) as select b, a from t1;
create view v3 (x,y,z) as select b, a, b from t1;
create view v4 (x,y,z) as select c+1, b, a from t1;
create algorithm=temptable view v5 (x,y,z) as select c, b, a from t1;
# try insert to VIEW with fields duplicate
-- error 1288
insert into v3 select c, b, a from t2;
# try insert to VIEW with expression in SELECT list
-- error 1288
insert into v4 select c, b, a from t2;
# try insert to VIEW using temporary table algorithm
-- error 1288
insert into v5 select c, b, a from t2;
insert into v1 select c, b, a from t2;
insert into v1 (z,y,x) select a+20,b+2,-100 from t2;
insert into v2 select b+1, a+10 from t2;
select * from t1;
697
drop table t1, t2;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801
drop view v1,v2,v3,v4,v5;

#
# insert privileges on VIEW
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int, primary key(a));
insert into mysqltest.t1 values (1,2), (2,3);
create table mysqltest.t2 (x int, y int);
insert into mysqltest.t2 values (3,4);
create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;

grant insert on mysqltest.v1 to mysqltest_1@localhost;
grant select on mysqltest.* to mysqltest_1@localhost;

connection user1;
use mysqltest;
# update with rights on VIEW column
insert into v1 values (5,6);
select * from t1;
insert into v1 select x,y from t2;
select * from t1;
# no rights for view
-- error 1142
insert into v2 values (5,6);
-- error 1142
insert into v2 select x,y from t2;

use test;
connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# outer join based on VIEW with WHERE clause
#
create table t1 (a int, primary key(a));
insert into t1 values (1), (2), (3);
create view v1 (x) as select a from t1 where a > 1;
select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x);
drop table t1;
drop view v1;

#
# merging WHERE condition on VIEW on VIEW
#
create table t1 (a int, primary key(a));
insert into t1 values (1), (2), (3), (200);
create view v1 (x) as select a from t1 where a > 1;
create view v2 (y) as select x from v1 where x < 100;
select * from v2;
drop table t1;
drop view v1,v2;

#
# VIEW on non-updatable view
#
create table t1 (a int, primary key(a));
insert into t1 values (1), (2), (3), (200);
create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1;
create view v2 (y) as select x from v1;
-- error 1288
update v2 set y=10 where y=2;
drop table t1;
drop view v1,v2;

#
# auto_increment field out of VIEW
#
create table t1 (a int not null auto_increment, b int not null, primary key(a), unique(b));
create view v1 (x) as select b from t1;
insert into v1 values (1);
select last_insert_id();
insert into t1 (b) values (2);
select last_insert_id();
select * from t1;
drop view v1;
drop table t1;

#
# test of CREATE VIEW privileges if we have limited privileges
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int);
create table mysqltest.t2 (a int, b int);

grant update on mysqltest.t1 to mysqltest_1@localhost;
grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
grant create view,update on test.* to mysqltest_1@localhost;

connection user1;

create view v1 as select * from mysqltest.t1;
create view v2 as select b from mysqltest.t2;
# There are not rights on mysqltest.v1
802
-- error 1142
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
803 804 805 806 807
create view mysqltest.v1 as select * from mysqltest.t1;
# There are not any rights on mysqltest.t2.a
-- error 1143
create view v3 as select a from mysqltest.t2;

808
# give CRETEA VIEW privileges (without any privileges for result colemn)
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
809 810 811 812 813 814 815
connection root;
create table mysqltest.v3 (b int);
grant create view on mysqltest.v3 to mysqltest_1@localhost;
drop table mysqltest.v3;
connection user1;
create view mysqltest.v3 as select b from mysqltest.t2;

816
# give UPDATE privileges
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
817 818
connection root;
grant create view, update on mysqltest.v3 to mysqltest_1@localhost;
819
drop view mysqltest.v3;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
820 821 822
connection user1;
create view mysqltest.v3 as select b from mysqltest.t2;

823 824 825 826 827 828 829 830 831
# give UPDATE and INSERT privilege (to get more privileges then anderlying
# table)
connection root;
grant create view, update, insert on mysqltest.v3 to mysqltest_1@localhost;
drop view mysqltest.v3;
connection user1;
-- error 1143
create view mysqltest.v3 as select b from mysqltest.t2;

bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
832 833 834 835

# If give other privileges for VIEW then underlaying table have =>
# creation prohibited
connection root;
836
create table mysqltest.v3 (b int);
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
837
grant select(b) on mysqltest.v3 to mysqltest_1@localhost;
838
drop table mysqltest.v3;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860
connection user1;
-- error 1142
create view mysqltest.v3 as select b from mysqltest.t2;

# Expression need select privileges
-- error 1143
create view v4 as select b+1 from mysqltest.t2;

connection root;
grant create view,update,select on test.* to mysqltest_1@localhost;
connection user1;
-- error 1143
create view v4 as select b+1 from mysqltest.t2;

connection root;
grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
connection user1;
create view v4 as select b+1 from mysqltest.t2;

connection root;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;
861
drop view v1,v2,v4;
bell@sanja.is.com.ua's avatar
VIEW  
bell@sanja.is.com.ua committed
862

863 864 865 866 867 868 869 870 871 872
#
# VIEW fields quoting
#
set sql_mode='ansi';
create table t1 ("a*b" int);
create view v1 as select "a*b" from t1;
show create view v1;
drop view v1;
drop table t1;
set sql_mode=default;
873 874 875 876 877 878 879 880 881

#
# VIEW without tables
#
create table t1 (t_column int);
create view v1 as select 'a';
select * from v1, t1;
drop view v1;
drop table t1;
882 883 884 885 886 887 888 889 890 891

#
# quote mark inside table name
#
create table `t1a``b` (col1 char(2));
create view v1 as select * from `t1a``b`;
select * from v1;
describe v1;
drop view v1;
drop table `t1a``b`;
892 893 894 895 896 897 898 899

#
# Changing of underlaying table
#
create table t1 (col1 char(5),col2 char(5));
create view v1 as select * from t1;
drop table t1;
create table t1 (col1 char(5),newcol2 char(5));
900
-- error 1356
901 902
insert into v1 values('a','aa');
drop table t1;
903
-- error 1356
904 905
select * from v1;
drop view v1;
906 907 908 909 910 911

#
# check of duplication of column names
#
-- error 1060
create view v1 (a,a) as select 'a','a';
912

913 914 915 916
#
# SP variables inside view test
#
delimiter //;
917
create procedure p1 () begin declare v int; create view v1 as select v; end;//
918
delimiter ;//
919
-- error 1351
920 921 922 923 924 925 926 927 928 929 930 931 932 933
call p1();
drop procedure p1;

#
# updateablity should be transitive
#
create table t1 (col1 int,col2 char(22));
insert into t1 values(5,'Hello, world of views');
create view v1 as select * from t1;
create view v2 as select * from v1;
update v2 set col2='Hello, view world';
select * from t1;
drop view v2, v1;
drop table t1;
bell@sanja.is.com.ua's avatar
Merge  
bell@sanja.is.com.ua committed
934

935 936 937 938 939 940 941 942 943
#
# check 'use index' on view with temporary table
#
create table t1 (a int, b int);
create view v1 as select a, sum(b) from t1 group by a;
-- error 1072
select b from v1 use index (some_index) where b=1;
drop view v1;
drop table t1;
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
944

945 946 947 948 949 950 951 952 953 954
#
# using VIEW fields several times in query resolved via temporary tables
#
create table t1 (col1 char(5),col2 char(5));
create view v1 (col1,col2) as select col1,col2 from t1;
insert into v1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4');
select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1);
select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1);
drop view v1;
drop table t1;
955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984

#
# Test of view updatebility in prepared statement
#
create table t1 (a int);
create view v1 as select a from t1;
insert into t1 values (1);

#update
SET @v0 = '2';
PREPARE stmt FROM 'UPDATE v1 SET a = ?';
EXECUTE stmt USING @v0;
DEALLOCATE PREPARE stmt;

#insert without field list
SET @v0 = '3';
PREPARE stmt FROM 'insert into v1 values (?)';
EXECUTE stmt USING @v0;
DEALLOCATE PREPARE stmt;

#insert with field list
SET @v0 = '4';
PREPARE stmt FROM 'insert into v1 (a) values (?)';
EXECUTE stmt USING @v0;
DEALLOCATE PREPARE stmt;

select * from t1;

drop view v1;
drop table t1;
985 986 987 988 989 990 991

#
# error on preparation
#
-- error 1096
CREATE VIEW v02 AS SELECT * FROM DUAL;
SHOW TABLES;
992 993 994 995 996 997 998

#
# EXISTS with UNION VIEW
#
CREATE VIEW v1 AS SELECT EXISTS (SELECT 1 UNION SELECT 2);
select * from v1;
drop view v1;
999 1000 1001 1002 1003 1004

#
# using VIEW where table is required
#
create table t1 (col1 int,col2 char(22));
create view v1 as select * from t1;
1005
-- error 1347
1006 1007 1008
create index i1 on v1 (col1);
drop view v1;
drop table t1;
1009 1010 1011 1012 1013 1014 1015

#
# connection_id(), pi(), current_user(), version() representation test
#
CREATE VIEW v1 (f1,f2,f3,f4) AS SELECT connection_id(), pi(), current_user(), version();
SHOW CREATE VIEW v1;
drop view v1;
1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027

#
# VIEW built over UNION
#
create table t1 (s1 int);
create table t2 (s2 int);
insert into t1 values (1), (2);
insert into t2 values (2), (3);
create view v1 as select * from t1,t2 union all select * from t1,t2;
select * from v1;
drop view v1;
drop tables t1, t2;
1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038

#
# Aggregate functions in view list
#
create table t1 (col1 int);
insert into t1 values (1);
create view v1 as select count(*) from t1;
insert into t1 values (null);
select * from v1;
drop view v1;
drop table t1;
1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049

#
# Showing VIEW with VIEWs in subquery
#
create table t1 (a int);
create table t2 (a int);
create view v1 as select a from t1;
create view v2 as select a from t2 where a in (select a from v1);
show create view v2;
drop view v2, v1;
drop table t1, t2;
1050 1051 1052 1053 1054 1055 1056

#
# SHOW VIEW view with name with spaces
#
CREATE VIEW `v 1` AS select 5 AS `5`;
show create view `v 1`;
drop view `v 1`;
1057 1058 1059 1060 1061 1062 1063 1064 1065 1066

#
# Removing database with .frm archives
#
create database mysqltest;
create table mysqltest.t1 (a int, b int);
create view mysqltest.v1 as select a from mysqltest.t1;
alter view mysqltest.v1 as select b from mysqltest.t1;
alter view mysqltest.v1 as select a from mysqltest.t1;
drop database mysqltest;
1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077

#
# VIEW with full text
#
CREATE TABLE t1 (c1 int not null auto_increment primary key, c2 varchar(20), fulltext(c2));
insert into t1 (c2) VALUES ('real Beer'),('Water'),('Kossu'),('Coca-Cola'),('Vodka'),('Wine'),('almost real Beer');
select * from t1 WHERE match (c2) against ('Beer');
CREATE VIEW v1 AS SELECT  * from t1 WHERE match (c2) against ('Beer');
select * from v1;
drop view v1;
drop table t1;
1078 1079 1080 1081 1082 1083 1084 1085 1086 1087

#
# distinct in temporary table with a VIEW
#
create table t1 (a int);
insert into t1 values (1),(1),(2),(2),(3),(3);
create view v1 as select a from t1;
select distinct a from v1;
select distinct a from v1 limit 2;
select distinct a from t1 limit 2;
1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100
prepare stmt1 from "select distinct a from v1 limit 2";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;
drop view v1;
drop table t1;

#
# aggregate function of aggregate function
#
create table t1 (tg_column bigint);
create view v1 as select count(tg_column) as vg_column from t1;
select avg(vg_column) from v1;
1101 1102
drop view v1;
drop table t1;
1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117

#
# VIEW of VIEW with column renaming
#
create table t1 (col1 bigint not null, primary key (col1));
create table t2 (col1 bigint not null, key (col1));
create view v1 as select * from t1;
create view v2 as select * from t2;
insert into v1 values (1);
insert into v2 values (1);
create view v3 (a,b) as select v1.col1 as a, v2.col1 as b from v1, v2 where v1.col1 = v2.col1;
select * from v3;
show create view v3;
drop view v3, v2, v1;
drop table t2, t1;
1118 1119 1120 1121 1122 1123 1124 1125 1126 1127

#
# VIEW based on functions with  complex names
#
create function `f``1` () returns int return 5;
create view v1 as select test.`f``1` ();
show create view v1;
select * from v1;
drop view v1;
drop function `f``1`;
1128 1129 1130 1131 1132 1133 1134 1135 1136

#
# tested problem when function name length close to ALIGN_SIZE
#
create function x () returns int return 5;
create view v1 as select x ();
select * from v1;
drop view v1;
drop function x;
1137 1138 1139 1140 1141 1142 1143 1144 1145 1146

#
# VIEW with collation
#
create table t2 (col1 char collate latin1_german2_ci);
create view v2 as select col1 collate latin1_german1_ci from t2;
show create view v2;
show create view v2;
drop view v2;
drop table t2;
1147 1148 1149 1150 1151 1152 1153 1154 1155 1156

#
# order by refers on integer field
#
create table t1 (a int);
insert into t1 values (1), (2);
create view v1 as select 5 from t1 order by 1;
select * from v1;
drop view v1;
drop table t1;
1157 1158 1159 1160 1161 1162 1163 1164

#
# VIEW over droped function
#
create function x1 () returns int return 5;
create table t1 (s1 int);
create view v1 as select x1() from t1;
drop function x1;
monty@mysql.com's avatar
monty@mysql.com committed
1165
-- error 1305
1166 1167 1168 1169 1170 1171 1172
select * from v1;
--replace_column 12 # 13 #
show table status;
--replace_column 12 # 13 #
show table status;
drop view v1;
drop table t1;
1173 1174 1175 1176 1177 1178 1179

#
# VIEW with floating point (long bumber) as column
#
create view v1 as select 99999999999999999999999999999999999999999999999999999 as col1;
show create view v1;
drop view v1;
1180 1181 1182 1183 1184 1185 1186 1187 1188 1189

#
# VIEWs with national characters
#
create table t (c char);
create view v as select c from t;
insert into v values ('');
select * from v;
drop view v;
drop table t;
1190 1191 1192 1193 1194 1195 1196 1197 1198 1199

#
# problem with used_tables() of outer reference resolved in VIEW
#
create table t1 (a int, b int);
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1(c) as select a+1 from t1 where b >= 4;
select c from v1 where exists (select * from t1 where a=2 and b=c);
drop view v1;
drop table t1;
1200 1201 1202 1203 1204 1205 1206 1207

#
# view with cast operation
#
create view v1 as select cast(1 as char(3));
show create view v1;
select * from v1;
drop view v1;
1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218

#
# bug handlimg from VIEWs
#
create view v1 as select 'a',1;
create view v2 as select * from v1 union all select * from v1;
create view v3 as select * from v2 where 1 = (select `1` from v2);
create view v4 as select * from v3;
-- error 1242
select * from v4;
drop view v4, v3, v2, v1;
1219 1220 1221 1222

#
# VIEW over SELECT with prohibited clauses
#
monty@mysql.com's avatar
monty@mysql.com committed
1223
-- error 1350
1224
create view v1 as select 5 into @w;
monty@mysql.com's avatar
monty@mysql.com committed
1225
-- error 1350
1226 1227
create view v1 as select 5 into outfile 'ttt';
create table t1 (a int);
monty@mysql.com's avatar
monty@mysql.com committed
1228
-- error 1350
1229 1230
create view v1 as select a from t1 procedure analyse();
drop table t1;
1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241

#
# INSERT into VIEW with ON DUPLICATE
#
create table t1 (s1 int, primary key (s1));
create view v1 as select * from t1;
insert into v1 values (1) on duplicate key update s1 = 7;
insert into v1 values (1) on duplicate key update s1 = 7;
select * from t1;
drop view v1;
drop table t1;
1242

1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259
#
# test of updating and fetching from the same table check
#
create table t1 (col1 int);
create table t2 (col1 int);
create view v1 as select * from t1;
create view v2 as select * from v1;
-- error 1093
update v2 set col1 = (select max(col1) from v1);
#update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1;
-- error 1093
delete from v2 where col1 = (select max(col1) from v1);
#delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1;
-- error 1093
insert into v2 values ((select max(col1) from v1));
drop view v2,v1;
drop table t1,t2;
bell@sanja.is.com.ua's avatar
Merge  
bell@sanja.is.com.ua committed
1260

1261 1262 1263 1264 1265
#
# HANDLER with VIEW
#
create table t1 (s1 int);
create view v1 as select * from t1;
bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
1266
-- error 1347
1267 1268 1269
handler v1 open as xx;
drop view v1;
drop table t1;
1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281

#
# view with WHERE in nested join
#
create table t1(a int);
insert into t1 values (0), (1), (2), (3);
create table t2 (a int);
insert into t2 select a from t1 where a > 1;
create view v1 as select a from t1 where a > 1;
select * from t1 left join (t2 as t, v1) on v1.a=t1.a;
select * from t1 left join (t2 as t, t2) on t2.a=t1.a;
drop view v1;
1282
drop table t1, t2;
1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296

#
# Collation with view update
#
create table t1 (s1 char);
create view v1 as select s1 collate latin1_german1_ci as s1 from t1;
insert into v1 values ('a');
select * from v1;
update v1 set s1='b';
select * from v1;
update v1,t1 set v1.s1='c' where t1.s1=v1.s1;
select * from v1;
drop view v1;
drop table t1;
1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309

#
# test view with LOCK TABLES (work around)
#
create table t1 (a int);
create table t2 (a int);
create view v1 as select * from t1;
lock tables t1 read, v1 read;
select * from v1;
-- error 1100
select * from t2;
drop view v1;
drop table t1, t2;
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
1310

1311 1312 1313 1314 1315 1316 1317
#
# WITH CHECK OPTION insert/update test
#
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
# simple insert
insert into v1 values(1);
monty@mishka.local's avatar
monty@mishka.local committed
1318
-- error 1369
1319 1320 1321 1322 1323 1324 1325 1326
insert into v1 values(3);
# simple insert with ignore
insert ignore into v1 values (2),(3),(0);
select * from t1;
# prepare data for next check
delete from t1;
# INSERT SELECT test
insert into v1 SELECT 1;
monty@mishka.local's avatar
monty@mishka.local committed
1327
-- error 1369
1328 1329 1330 1331 1332 1333 1334 1335 1336
insert into v1 SELECT 3;
# prepare data for next check
create table t2 (a int);
insert into t2 values (2),(3),(0);
# INSERT SELECT with ignore test
insert ignore into v1 SELECT a from t2;
select * from t1;
#simple UPDATE test
update v1 set a=-1 where a=0;
monty@mishka.local's avatar
monty@mishka.local committed
1337
-- error 1369
1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363
update v1 set a=2 where a=1;
select * from t1;
# prepare data for next check
update v1 set a=0 where a=0;
insert into t2 values (1);
# multiupdate test
update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a;
select * from t1;
# prepare data for next check
update v1 set a=a+1;
# multiupdate with ignore test
update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a;
select * from t1;

drop view v1;
drop table t1, t2;

#
# CASCADED/LOCAL CHECK OPTION test
#
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
create view v2 as select * from v1 where a > 0 with local check option;
create view v3 as select * from v1 where a > 0 with cascaded check option;
insert into v2 values (1);
insert into v3 values (1);
monty@mishka.local's avatar
monty@mishka.local committed
1364
-- error 1369
1365
insert into v2 values (0);
monty@mishka.local's avatar
monty@mishka.local committed
1366
-- error 1369
1367 1368
insert into v3 values (0);
insert into v2 values (2);
monty@mishka.local's avatar
monty@mishka.local committed
1369
-- error 1369
1370 1371 1372 1373
insert into v3 values (2);
select * from t1;
drop view v3,v2,v1;
drop table t1;
1374 1375 1376 1377 1378 1379 1380

#
# CHECK OPTION with INSERT ... ON DUPLICATE KEY UPDATE
#
create table t1 (a int, primary key (a));
create view v1 as select * from t1 where a < 2 with check option;
insert into v1 values (1) on duplicate key update a=2;
monty@mishka.local's avatar
monty@mishka.local committed
1381
-- error 1369
1382 1383 1384 1385 1386
insert into v1 values (1) on duplicate key update a=2;
insert ignore into v1 values (1) on duplicate key update a=2;
select * from t1;
drop view v1;
drop table t1;