1
#### suite/funcs_1/views/views_master.test
5
# 1. Fix for Bug#31237 Test "ndb_views" fails because of differing order ...
7
# 2007-11-15 hhunger WL#4084: Review and fix all disabled tests ...
9
let $message= ! Attention: The file with the expected results is not
11
! The server return codes are correct, but
12
| most result sets where the table tb2 is
13
! involved are not checked.;
14
--source include/show_msg80.inc
17
# Bug#32285: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
18
# is not fixed, we must switch the ps-protocol for some statements off (formerly bug#11589).
19
# If this bug is fixed, please
20
# 1. set the following variable to 0
21
# 2. check, if the test passes
22
# 3. remove the workarounds
23
let $have_bug_32285= 1;
26
let $message= There are some statements where the ps-protocol is switched off.
27
Bug#32285: mysqltest, --ps-protocol, strange output, float/double/real with zerofill;
28
--source include/show_msg80.inc
31
# The sub testcases are nearly independend. That is the reason why
32
# we do not want to abort after the first error.
33
--disable_abort_on_error
37
# MySQL views are based on a subset of the view requirements described in
38
# the following standard SQL document:
40
# * ISO/IEC 9075-2:2003 Information technology -- Database languages --
41
# SQL -- Part 2: Foundation (SQL/Foundation)
43
# MySQL has also added some vendor-specific enhancements to the standard
47
# - Alter all object names so that they follow the v/t/..<number> scheme or
48
# apply another method which prevents that customer data might be
50
# - Remove any reference to the preloaded tables tb1 - tb4, if they could
51
# be replaced without loss of value.
52
# Example: failing CREATE VIEW statements
53
# The goal is to split this script into two, where the first one does
54
# not need the possibly huge tables.
56
# Load records needed within the testcases.
57
# We load them here and not within the testcases itself, because the
58
# removal of any unneeded testcase during bug analysis should not alter
61
insert into test.tb2 (f59,f60) values (76710,226546);
62
insert into test.tb2 (f59,f60) values(2760,985654);
63
insert into test.tb2 (f59,f60) values(569300,9114376);
64
insert into test.tb2 (f59,f60) values(660,876546);
65
insert into test.tb2 (f59,f60) values(250,87895654);
66
insert into test.tb2 (f59,f60) values(340,9984376);
67
insert into test.tb2 (f59,f60) values(3410,996546);
68
insert into test.tb2 (f59,f60) values(2550,775654);
69
insert into test.tb2 (f59,f60) values(3330,764376);
70
insert into test.tb2 (f59,f60) values(441,16546);
71
insert into test.tb2 (f59,f60) values(24,51654);
72
insert into test.tb2 (f59,f60) values(323,14376);
74
insert into test.tb2 (f59,f60) values(34,41);
75
insert into test.tb2 (f59,f60) values(04,74);
76
insert into test.tb2 (f59,f60) values(15,87);
77
insert into test.tb2 (f59,f60) values(22,93);
79
insert into test.tb2 (f59,f60) values(394,41);
80
insert into test.tb2 (f59,f60) values(094,74);
81
insert into test.tb2 (f59,f60) values(195,87);
82
insert into test.tb2 (f59,f60) values(292,93);
84
insert into test.tb2 (f59,f60) values(0987,41) ;
85
insert into test.tb2 (f59,f60) values(7876,74) ;
87
INSERT INTO tb2 (f59,f61) VALUES(321,765 );
88
INSERT INTO tb2 (f59,f61) VALUES(9112,8771);
90
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
91
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
92
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
94
Insert into tb2 (f59,f60,f61) values (107,105,106) ;
95
Insert into tb2 (f59,f60,f61) values (109,108,104) ;
97
Insert into tb2 (f59,f60,f61) values (207,205,206) ;
98
Insert into tb2 (f59,f60,f61) values (209,208,204) ;
100
Insert into tb2 (f59,f60,f61) values (27,25,26) ;
101
Insert into tb2 (f59,f60,f61) values (29,28,24) ;
103
Insert into tb2 (f59,f60,f61) values (17,15,16) ;
104
Insert into tb2 (f59,f60,f61) values (19,18,14) ;
105
insert into tb2 (f59,f60,f61) values (107,105,106);
106
insert into tb2 (f59,f60,f61) values (109,108,104);
108
INSERT INTO tb2 (f59,f60) VALUES( 299,899 );
109
INSERT INTO tb2 (f59,f60) VALUES( 242,79 );
110
INSERT INTO tb2 (f59,f60) VALUES( 424,89 );
113
--disable_ps_protocol
115
SELECT * FROM tb2 ORDER BY f59, f60, f61;
121
# End of basic preparations.
123
##############################################################################
127
#==============================================================================
128
# 3.3.1 Syntax checks for CREATE VIEW, CREATE OR REPLACE VIEW, ALTER VIEW,
130
#==============================================================================
132
let $message= Testcase 3.3.1.1 ;
133
--source include/show_msg80.inc
134
###############################################################################
135
# Testcase 3.3.1.1: Ensure that all clauses that should be supported
137
###############################################################################
139
Drop table if exists t1;
141
Create table t1 (f59 INT, f60 INT) ;
142
Insert into t1 values (100,4234);
143
Insert into t1 values (990,6624);
144
Insert into t1 values (710,765);
145
Insert into t1 values (300,433334);
146
Insert into t1 values (800,9788);
147
Insert into t1 values (500,9866);
151
Drop view if exists v1 ;
153
CREATE VIEW v1 AS select f59,f60,f61
154
FROM test.tb2 where f59=250;
155
select * FROM v1 order by f60,f61 limit 0,10;
158
Drop view if exists v1 ;
159
CREATE VIEW v1 AS select f59,f60,f61
160
FROM test.tb2 limit 100;
161
select * FROM v1 order by f59,f60,f61 limit 0,10;
164
CREATE or REPLACE VIEW v1 AS select f59,f60,f61
166
select * FROM v1 order by f59,f60,f61 limit 4,3;
169
CREATE or REPLACE VIEW v1 AS select distinct f59
171
select * FROM v1 order by f59 limit 4,3;
174
ALTER VIEW v1 AS select f59
176
select * FROM v1 order by f59 limit 6,2;
179
CREATE or REPLACE VIEW v1 AS select f59
180
from tb2 order by f59;
181
select * FROM v1 order by f59 limit 0,10;
184
CREATE or REPLACE VIEW v1 AS select f59
185
from tb2 order by f59 asc;
186
select * FROM v1 limit 0,10;
189
CREATE or REPLACE VIEW v1 AS select f59
190
from tb2 order by f59 desc;
191
select * FROM v1 limit 0,10;
194
CREATE or REPLACE VIEW v1 AS select f59
195
from tb2 group by f59;
196
select * FROM v1 order by f59 limit 0,10;
199
CREATE or REPLACE VIEW v1 AS select f59
200
from tb2 group by f59 asc;
201
select * FROM v1 order by f59 limit 0,10;
204
CREATE or REPLACE VIEW v1 AS select f59
205
from tb2 group by f59 desc;
206
select * FROM v1 order by f59 limit 0,10;
209
CREATE or REPLACE VIEW v1 AS (select f59 from tb2)
210
union (select f59 from t1);
211
select * FROM v1 order by f59 limit 0,10;
214
CREATE or REPLACE VIEW v1 AS (select f59 FROM tb2)
215
UNION DISTINCT(select f59 FROM t1) ;
216
select * FROM v1 order by f59 limit 0,10;
219
CREATE or REPLACE VIEW v1 AS (select f59 FROM tb2)
220
UNION ALL(select f59 FROM t1) ;
221
select * FROM v1 order by f59 limit 0,10;
226
--disable_ps_protocol
229
CREATE or REPLACE VIEW v1 AS select *
230
FROM test.tb2 WITH LOCAL CHECK OPTION ;
231
select * FROM v1 order by f59,f60,f61,f62,f63,f64 limit 0,50;
234
CREATE or REPLACE VIEW v1 AS select *
235
FROM test.tb2 WITH CASCADED CHECK OPTION ;
236
select * FROM v1 order by f59,f60,f61,f62,f63,f64 limit 0,10;
241
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
242
FROM test.tb2 WITH CASCADED CHECK OPTION;
243
SELECT * FROM v1 order by f59,f60 limit 0,10;
247
CREATE or REPLACE VIEW v1 AS select f59, f60
248
from test.tb2 where f59=3330 ;
249
select * FROM v1 order by f60 limit 0,10;
255
let $message= Testcase 3.3.1.2 ;
256
--source include/show_msg80.inc
257
###############################################################################
258
# Testcase 3.3.1.2: Ensure that all clauses that should not be supported are
259
# disallowed with an appropriate error message.
260
###############################################################################
262
DROP TABLE IF EXISTS t1 ;
263
DROP VIEW IF EXISTS v1 ;
264
DROP VIEW IF EXISTS v2 ;
266
CREATE TABLE t1 (f1 BIGINT) ;
268
# User variables and parameters are not supported in VIEWs -> 3.3.1.40
270
# SELECT INTO is illegal
272
--error ER_VIEW_SELECT_CLAUSE
273
CREATE or REPLACE VIEW v1 AS Select 1 INTO @x;
276
# Subquery in the FROM clause is illegal
277
--error ER_VIEW_SELECT_DERIVED
278
CREATE or REPLACE VIEW v1 AS Select 1
279
FROM (SELECT 1 FROM t1) my_table;
281
# Triggers cannot be associated with VIEWs
282
CREATE VIEW v1 AS SELECT f1 FROM t1;
283
# Show that 1. The trigger code basically works and the VIEW is updatable
284
# 2. The VIEW is updatable
285
# 3. Insert into view causes that the trigger is executed
286
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
289
INSERT INTO v1 VALUES (1) ;
294
--error ER_WRONG_OBJECT
295
CREATE TRIGGER tr1 BEFORE INSERT ON v1 FOR EACH ROW SET @a:=1 ;
297
RENAME TABLE v1 TO v2;
298
# RENAME VIEW is not available even when we try it via rename table.
299
--error ER_PARSE_ERROR
300
RENAME VIEW v2 TO v1;
301
#--error ER_WRONG_OBJECT
302
ALTER TABLE v2 RENAME AS v1;
303
--error ER_PARSE_ERROR
304
ALTER VIEW v1 RENAME AS v2;
306
# VIEWs cannot contain a PRIMARY KEY or have an Index.
308
DROP TABLE IF EXISTS t1, t2 ;
309
DROP VIEW IF EXISTS v1 ;
310
DROP VIEW IF EXISTS v2 ;
312
CREATE TABLE t1 ( f1 DATE, f2 BLOB, f3 DOUBLE );
313
CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
314
ALTER TABLE t1 ADD PRIMARY KEY(f1);
315
--error ER_WRONG_OBJECT
316
ALTER TABLE v1 ADD PRIMARY KEY(f1);
317
--error ER_PARSE_ERROR
318
ALTER VIEW v1 ADD PRIMARY KEY(f1);
319
CREATE INDEX t1_idx ON t1(f3);
320
--error ER_WRONG_OBJECT
321
CREATE INDEX v1_idx ON v1(f3);
326
let $message= Testcase 3.3.1.3 + 3.1.1.4 ;
327
--source include/show_msg80.inc
328
###############################################################################
329
# Testcase 3.1.1.3: Ensure that all supported clauses are supported only in
331
# Testcase 3.1.1.4: Ensure that an appropriate error message is returned if
332
# a clause is out-of-order in an SQL statement.
333
###############################################################################
335
DROP VIEW IF EXISTS v1 ;
337
# REPLACE after VIEW name
338
--error ER_PARSE_ERROR
339
CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table;
340
# CHECK OPTION before AS SELECT
341
--error ER_PARSE_ERROR
342
CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select *
343
from tb2 my_table limit 50;
344
# CHECK OPTION before AS SELECT
345
--error ER_PARSE_ERROR
346
CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select *
347
from tb2 my_table limit 50;
348
# CREATE after SELECT
349
--error ER_PARSE_ERROR
350
SELECT * FROM tb2 my_table CREATE VIEW As v1;
352
--error ER_PARSE_ERROR
353
CREATE or REPLACE VIEW v1 Select f59, f60
354
from test.tb2 my_table where f59 = 250 ;
356
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
357
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
359
# REPLACE OR CREATE instead of CREATE OR REPLACE
360
--error ER_PARSE_ERROR
361
REPLACE OR CREATE VIEW v1 AS SELECT F59, F60
362
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
364
--error ER_PARSE_ERROR
365
CREATE OR REPLACE VIEW v1 SELECT AS F59, F60
366
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
367
--error ER_PARSE_ERROR
368
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
369
FROM test.tb2 my_table CASCADED WITH CHECK OPTION;
370
# OPTION CHECK instead of CHECK OPTION
371
--error ER_PARSE_ERROR
372
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
373
FROM test.tb2 my_table WITH CASCADED OPTION CHECK;
374
# CHECK OPTION before WITH
375
--error ER_PARSE_ERROR
376
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
377
FROM test.tb2 my_table CHECK OPTION WITH CASCADED;
378
# CHECK OPTION before AS SELECT
379
--error ER_PARSE_ERROR
380
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
381
AS SELECT F59, F60 FROM test.tb2 my_table;
382
# VIEW <viewname> after AS SELECT
383
--error ER_PARSE_ERROR
384
CREATE OR REPLACE AS SELECT F59, F60
385
FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION;
386
# VIEW <viewname> after CHECK OPTION
387
--error ER_PARSE_ERROR
388
CREATE OR REPLACE AS SELECT F59, F60
389
FROM test.tb2 my_table WITH CASCADED CHECK OPTION VIEW v1;
391
# Variants with LOCAL CHECK OPTION
392
--error ER_PARSE_ERROR
393
REPLACE OR CREATE VIEW v1 AS SELECT F59, F60
394
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
395
--error ER_PARSE_ERROR
396
CREATE OR REPLACE VIEW v1 SELECT AS F59, F60
397
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
398
--error ER_PARSE_ERROR
399
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
400
FROM test.tb2 my_table LOCAL WITH CHECK OPTION;
401
--error ER_PARSE_ERROR
402
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
403
FROM test.tb2 my_table WITH LOCAL OPTION CHECK;
404
--error ER_PARSE_ERROR
405
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
406
FROM test.tb2 my_table CHECK OPTION WITH LOCAL;
407
--error ER_PARSE_ERROR
408
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
409
AS SELECT F59, F60 FROM test.tb2 my_table;
410
--error ER_PARSE_ERROR
411
CREATE OR REPLACE AS SELECT F59, F60
412
FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION;
413
--error ER_PARSE_ERROR
414
CREATE OR REPLACE AS SELECT F59, F60
415
FROM test.tb2 my_table WITH LOCAL CHECK OPTION VIEW v1;
418
Drop table if exists t1 ;
420
CREATE table t1 (f1 int ,f2 int) ;
421
INSERT INTO t1 values (235, 22);
422
INSERT INTO t1 values (554, 11);
423
# SELECTs of UNION in braces
424
--error ER_PARSE_ERROR
425
CREATE or REPLACE view v1 as (Select from f59 tb2)
426
Union ALL (Select from f1 t1);
428
--error ER_PARSE_ERROR
429
CREATE or REPLACE view v1 as Select f59, f60
430
from tb2 by order f59;
432
--error ER_PARSE_ERROR
433
CREATE or REPLACE view v1 as Select f59, f60
434
from tb2 by group f59 ;
437
let $message= Testcase 3.3.1.5 ;
438
--source include/show_msg80.inc
439
###############################################################################
440
# Testcase 3.3.1.5: Ensure that all clauses that are defined to be mandatory
441
# are indeed required to be mandatory by the MySQL server
443
###############################################################################
445
DROP VIEW IF EXISTS v1 ;
447
--error ER_PARSE_ERROR
448
CREATE VIEW v1 SELECT * FROM tb2;
449
--error ER_PARSE_ERROR
450
CREATE v1 AS SELECT * FROM tb2;
451
--error ER_PARSE_ERROR
452
VIEW v1 AS SELECT * FROM tb2;
454
CREATE VIEW v1 AS SELECT 1;
456
--error ER_PARSE_ERROR
458
--error ER_PARSE_ERROR
459
CREATE v1 AS SELECT 1;
460
--error ER_PARSE_ERROR
461
CREATE VIEW AS SELECT 1;
462
--error ER_PARSE_ERROR
463
CREATE VIEW v1 SELECT 1;
464
--error ER_PARSE_ERROR
468
let $message= Testcase 3.3.1.6 ;
469
--source include/show_msg80.inc
470
###############################################################################
471
# Testcase 3.3.1.6: Ensure that any clauses that are defined to be optional
472
# are indeed treated as optional by the MySQL server
474
###############################################################################
475
# Note: The positive test in 3.3.1.5 shows, that ALGORITHM ..., CHECK OPTION
476
# and any column_list after the VIEW name are optional.
477
# Therefore check here:
478
# - ALGORITHM = <all possible algorithms>
479
# - all possible CHECK OPTIONs
480
# - some incomplete or wrong stuff
482
DROP VIEW IF EXISTS v1 ;
484
CREATE or REPLACE VIEW v1
485
as SELECT * from tb2;
486
CREATE or REPLACE ALGORITHM = UNDEFINED VIEW v1
487
as SELECT * from tb2;
488
CREATE or REPLACE ALGORITHM = MERGE VIEW v1
489
as SELECT * from tb2;
490
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
491
as SELECT * from tb2;
492
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
493
as SELECT * from tb2;
494
# negative test cases
495
--error ER_PARSE_ERROR
496
CREATE or REPLACE = TEMPTABLE VIEW v1
497
as SELECT * from tb2;
498
--error ER_PARSE_ERROR
499
CREATE or REPLACE ALGORITHM TEMPTABLE VIEW v1
500
as SELECT * from tb2;
501
--error ER_PARSE_ERROR
502
CREATE or REPLACE ALGORITHM = VIEW v1
503
as SELECT * from tb2;
504
--error ER_PARSE_ERROR
505
CREATE or REPLACE TEMPTABLE = ALGORITHM VIEW v1
506
as SELECT * from tb2;
507
--error ER_PARSE_ERROR
508
CREATE or REPLACE TEMPTABLE - ALGORITHM VIEW v1
509
as SELECT * from tb2;
510
--error ER_PARSE_ERROR
511
CREATE or REPLACE GARBAGE = TEMPTABLE VIEW v1
512
as SELECT * from tb2;
513
--error ER_PARSE_ERROR
514
CREATE or REPLACE ALGORITHM = GARBAGE VIEW v1
515
as SELECT * from tb2;
516
Drop view if exists v1 ;
518
CREATE or REPLACE VIEW v1
519
AS SELECT * from tb2 where f59 < 1;
520
CREATE or REPLACE VIEW v1
521
AS SELECT * from tb2 where f59 < 1 WITH CHECK OPTION;
522
CREATE or REPLACE VIEW v1
523
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK OPTION;
524
CREATE or REPLACE VIEW v1
525
AS SELECT * from tb2 where f59 < 1 WITH LOCAL CHECK OPTION;
526
# negative test cases
527
--error ER_PARSE_ERROR
528
CREATE or REPLACE VIEW v1
529
AS SELECT * from tb2 where f59 < 1 WITH NO CHECK OPTION;
530
--error ER_PARSE_ERROR
531
CREATE or REPLACE VIEW v1
532
AS SELECT * from tb2 where f59 < 1 CASCADED CHECK OPTION;
533
--error ER_PARSE_ERROR
534
CREATE or REPLACE VIEW v1
535
AS SELECT * from tb2 where f59 < 1 WITH CASCADED OPTION;
536
--error ER_PARSE_ERROR
537
CREATE or REPLACE VIEW v1
538
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK ;
541
let $message= Testcase 3.3.1.7 ;
542
--source include/show_msg80.inc
543
###############################################################################
544
# Testcase 3.3.1.7: Ensure that all valid, fully-qualified, and non-qualified,
545
# view names are accepted, at creation time, alteration time,
547
###############################################################################
548
# Note(mleich): non-qualified view name means a view name without preceeding
551
DROP VIEW IF EXISTS v1 ;
553
Create view test.v1 AS Select * from test.tb2;
554
Alter view test.v1 AS Select F59 from test. tb2 limit 100 ;
556
Create view v1 AS Select * from test.tb2 limit 100 ;
557
Alter view v1 AS Select F59 from test.tb2 limit 100 ;
561
let $message= Testcase 3.3.1.A0 ;
562
--source include/show_msg80.inc
563
###############################################################################
564
# Testcase 3.3.1.A0: Ensure that view names are treated case sensitive.
565
###############################################################################
566
# Note(mleich): Maybe this test produces portability problems on Windows.
567
# FIXME There should be a test outside this one checking the
568
# creation of objects with cases sensitive names.
569
# If we have this test the following sub testcase should
572
DROP TABLE IF EXISTS t1 ;
573
DROP VIEW IF EXISTS v1 ;
574
DROP VIEW IF EXISTS V1 ;
576
eval CREATE TABLE t1 (f1 NUMERIC(4)) ENGINE = $engine_type;
577
INSERT INTO t1 VALUES(1111), (2222);
578
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 = 1111;
579
# We get here the sql code
580
# - 0 on OS with cases sensitive view names (Example: UNIX)
581
# - ER_TABLE_EXISTS_ERROR on OS without cases sensitive view names (Example: WINDOWS)
582
--error 0,ER_TABLE_EXISTS_ERROR
583
CREATE VIEW V1 AS SELECT * FROM t1 WHERE f1 = 2222;
587
DROP TABLE IF EXISTS t1 ;
588
DROP VIEW IF EXISTS v1 ;
589
DROP VIEW IF EXISTS V1 ;
593
let $message= Testcase 3.3.1.8 ;
594
--source include/show_msg80.inc
595
###############################################################################
596
# Testcase 3.3.1.8: Ensure that any invalid view name is never accepted, and
597
# that an appropriate error message is returned when the name
599
###############################################################################
600
# Note(mleich): There could be more negative tests here, but I assume that the
601
# server routines checking if a table or view name is acceptable
602
# are heavily tested in tests checking the creation of tables.
603
--error ER_PARSE_ERROR
604
Create view select AS Select * from test.tb2 limit 100;
605
--error ER_PARSE_ERROR
606
Create view as AS Select * from test.tb2 limit 100;
607
--error ER_PARSE_ERROR
608
Create view where AS Select * from test.tb2 limit 100;
609
--error ER_PARSE_ERROR
610
Create view from AS Select * from test.tb2 limit 100;
611
--error ER_PARSE_ERROR
612
Create view while AS Select * from test.tb2 limit 100;
613
--error ER_PARSE_ERROR
614
Create view asdkj*(&*&&^ as Select * from test.tb2 limit 100 ;
616
Drop view if exists test.procedure ;
618
Create view test.procedure as Select * from test.tb2 limit 100 ;
619
Drop view if exists test.procedure ;
622
let $message= Testcase 3.3.1.9 ;
623
--source include/show_msg80.inc
624
###############################################################################
625
# Testcase 3.3.1.9: Ensure that a reference to a non-existent view is rejected
626
# with an appropriate error message
627
###############################################################################
628
# Note(mleich): The SELECT statement syntax does not contain any functionality
629
# to claim, that the object after FROM must be a VIEW. SHOW's will
631
# 3.3.11 Checks on SHOW, EXPLAIN, and DESCRIBE statements.
632
# Let's check here a view based on a dropped view or table.
634
Drop TABLE IF EXISTS t1 ;
635
Drop VIEW IF EXISTS v1;
636
Drop VIEW IF EXISTS v2;
637
Drop VIEW IF EXISTS v3;
639
CREATE TABLE t1 ( f1 char(5));
640
INSERT INTO t1 SET f1 = 'abcde';
641
CREATE VIEW v1 AS SELECT f1 FROM t1;
642
CREATE VIEW v2 AS SELECT * FROM v1;
644
# Only negative cases, positive cases will be checked later:
646
--error ER_VIEW_INVALID
648
--error ER_VIEW_INVALID
650
--error ER_VIEW_INVALID
651
UPDATE v1 SET f1 = 'aaaaa';
652
--error ER_VIEW_INVALID
653
INSERT INTO v1 SET f1 = "fffff";
654
# v2 is based on v1, which is now invalid
655
--error ER_VIEW_INVALID
657
--error ER_VIEW_INVALID
659
--error ER_VIEW_INVALID
660
UPDATE v2 SET f1 = 'aaaaa';
661
--error ER_VIEW_INVALID
662
INSERT INTO v2 SET f1 = "fffff";
664
# v2 is based on v1, which is now dropped
665
--error ER_VIEW_INVALID
667
--error ER_VIEW_INVALID
669
--error ER_VIEW_INVALID
670
UPDATE v2 SET f1 = 'aaaaa';
671
--error ER_VIEW_INVALID
672
INSERT INTO v2 SET f1 = "fffff";
676
# A VIEW based on itself is non sense.
678
DROP TABLE IF EXISTS t1 ;
679
DROP VIEW IF EXISTS v1 ;
681
CREATE TABLE t1 (f1 FLOAT);
682
# Create a new VIEW based on itself
683
--error ER_NO_SUCH_TABLE
684
CREATE VIEW v1 AS SELECT * FROM v1;
685
# Replace a valid VIEW with one new based on itself
686
CREATE VIEW v1 AS SELECT * FROM t1;
687
--error ER_NO_SUCH_TABLE
688
CREATE or REPLACE VIEW v1 AS SELECT * FROM v1;
693
let $message= Testcase 3.3.1.10 ;
694
--source include/show_msg80.inc
695
###############################################################################
696
# Testcase 3.3.1.10: Ensure that it is not possible to create two views with
697
# the same name in the same database.
698
###############################################################################
700
Drop view if exists test.v1 ;
702
Create view test.v1 AS Select * from test.tb2 ;
703
--error ER_TABLE_EXISTS_ERROR
704
Create view test.v1 AS Select F59 from test.tb2 ;
705
--error ER_TABLE_EXISTS_ERROR
706
Create view v1 AS Select F59 from test.tb2 ;
709
let $message= Testcase 3.3.1.11 ;
710
--source include/show_msg80.inc
711
###############################################################################
712
# Testcase 3.3.1.11: Ensure that it is not possible to create a view and a base
713
# table with the same name in the same database.
714
###############################################################################
715
# The VIEW should get the same name like an already existing TABLE.
716
--error ER_TABLE_EXISTS_ERROR
717
Create view test.tb2 AS Select f59,f60 from test.tb2 limit 100 ;
718
--error ER_TABLE_EXISTS_ERROR
719
Create view tb2 AS Select f59,f60 from test.tb2 limit 100 ;
720
# The TABLE should get the same name like an already existing VIEW.
722
Drop view if exists test.v111 ;
724
Create view test.v111 as select * from tb2 limit 50;
725
--error ER_TABLE_EXISTS_ERROR
726
Create table test.v111(f1 int );
727
--error ER_TABLE_EXISTS_ERROR
728
Create table v111(f1 int );
732
let $message= Testcase 3.3.1.12 ;
733
--source include/show_msg80.inc
734
###############################################################################
735
# Testcase 3.3.1.12: Ensure that it is possible to create two or more views and
736
# base tables with the same name, providing each resides in
737
# a different database.
738
###############################################################################
741
Drop database if exists test2 ;
743
Create database test2 ;
745
# Object name object type in object type in
746
# database test database test2
752
DROP TABLE IF EXISTS test.t0, test.t1, test.t2;
753
DROP VIEW IF EXISTS test.v1;
754
DROP VIEW IF EXISTS test.v2;
756
CREATE TABLE test.t1 ( f1 VARCHAR(20));
757
CREATE TABLE test2.t1 ( f1 VARCHAR(20));
758
CREATE TABLE test.t2 ( f1 VARCHAR(20));
759
CREATE TABLE test2.v1 ( f1 VARCHAR(20));
760
# t0 is an auxiliary table needed for the VIEWs
761
CREATE TABLE test.t0 ( f1 VARCHAR(20));
762
CREATE TABLE test2.t0 ( f1 VARCHAR(20));
764
CREATE VIEW test2.t2 AS SELECT * FROM test2.t0;
765
CREATE VIEW test.v1 AS SELECT * FROM test.t0;
766
CREATE VIEW test.v2 AS SELECT * FROM test.t0;
767
CREATE VIEW test2.v2 AS SELECT * FROM test2.t0;
769
# Some additional tests on the just created objects to show that they are
770
# accessable and do have the expected content.
771
# INSERTs with full qualified table
772
INSERT INTO test.t1 VALUES('test.t1 - 1');
773
INSERT INTO test2.t1 VALUES('test2.t1 - 1');
774
INSERT INTO test.t2 VALUES('test.t2 - 1');
775
INSERT INTO test2.v1 VALUES('test2.v1 - 1');
776
INSERT INTO test.t0 VALUES('test.t0 - 1');
777
INSERT INTO test2.t0 VALUES('test2.t0 - 1');
778
# INSERTs with not full qualified table name.
780
INSERT INTO t1 VALUES('test.t1 - 2');
781
INSERT INTO t2 VALUES('test.t2 - 2');
782
INSERT INTO t0 VALUES('test.t0 - 2');
784
INSERT INTO t1 VALUES('test2.t1 - 2');
785
INSERT INTO v1 VALUES('test2.v1 - 2');
786
INSERT INTO t0 VALUES('test2.t0 - 2');
787
# SELECTs with full qualified table
799
let $message= Testcase 3.3.1.13 ;
800
--source include/show_msg80.inc
801
###############################################################################
802
# Testcase 3.3.1.13: Ensure that, if the CREATE OR REPLACE VIEW statement is
803
# used to create a view using the name of an existing view,
804
# it first cleanly drops the existing view and then creates
806
###############################################################################
808
DROP TABLE IF EXISTS t1;
809
DROP VIEW IF EXISTS v1;
811
CREATE TABLE t1 (f1 BIGINT);
812
INSERT INTO t1 VALUES(1);
813
CREATE VIEW test.v1 AS SELECT * FROM t1 limit 2;
814
SHOW CREATE VIEW test.v1;
816
SELECT * FROM test.v1;
817
# Switch the algorithm
818
CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW test.v1
819
AS SELECT * FROM t1 limit 2;
820
SHOW CREATE VIEW test.v1;
822
SELECT * FROM test.v1;
823
# Switch the base table
824
CREATE OR REPLACE VIEW test.v1 AS SELECT * FROM tb2 order by f59 limit 2;
825
SHOW CREATE VIEW test.v1;
828
--disable_ps_protocol
831
SELECT * FROM test.v1 order by f59,f60,f61,f62,f63,f64,f65;
834
# Switch the SELECT but not the base table
835
CREATE OR REPLACE VIEW test.v1 AS SELECT F59 FROM tb2;
836
SHOW CREATE VIEW test.v1;
837
SELECT * FROM test.v1 order by F59 limit 10,100;
842
let $message= Testcase 3.3.1.14 ;
843
--source include/show_msg80.inc
844
###############################################################################
845
# Testcase 3.3.1.14: Ensure that, if the CREATE OR REPLACE VIEW statement is
846
# used to create a view using the name of an existing base
847
# table, it fails with an appropriate error message.
848
###############################################################################
849
--error ER_WRONG_OBJECT
850
CREATE OR REPLACE VIEW test.tb2 AS SELECT * From tb2 LIMIT 2;
851
--error ER_WRONG_OBJECT
852
CREATE OR REPLACE VIEW tb2 AS SELECT * From tb2 LIMIT 2;
855
let $message= Testcase 3.3.1.15 ;
856
--source include/show_msg80.inc
857
###############################################################################
858
# Testcase 3.3.1.15: Ensure that, if the CREATE OR REPLACE VIEW statement is
859
# used to create a view using a name that does not already
860
# belong to an existing view or base table, it cleanly
862
###############################################################################
864
Drop table if exists test.v1 ;
866
CREATE OR REPLACE view test.v1 as select * from tb2;
869
--disable_ps_protocol
872
SELECT * FROM test.v1;
877
let $message= Testcase 3.3.1.16 + 3.3.1.17 ;
878
--source include/show_msg80.inc
879
###############################################################################
880
# Testcase 3.3.1.16: Ensure that a view with a definition that does not include
881
# an explicit column-name list takes its column names from
882
# the underlying base table(s).
883
# Testcase 3.3.1.17: Ensure that a view with a definition that does include an
884
# explicit column-name list uses the explicit names and not
885
# the name of the columns from the underlying base tables(s)
886
###############################################################################
888
Drop table if exists test.v1 ;
890
CREATE OR REPLACE VIEW v1 AS SELECT * From tb2;
891
# Note(mleich): The empty result is intended, because I want to compare
893
SELECT * FROM tb2 WHERE 1 = 2;
894
SELECT * FROM v1 WHERE 1 = 2;
898
DROP TABLE IF EXISTS t1;
899
DROP VIEW IF EXISTS v1;
901
CREATE TABLE t1 (f1 NUMERIC(15,3));
902
INSERT INTO t1 VALUES(8.8);
903
# 1. no explicit column in VIEW definition or SELECT
904
CREATE VIEW v1 AS SELECT * FROM t1;
907
# 2. no explicit column in VIEW definition, but in SELECT column_list
908
CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1;
911
# 3. no explicit column in VIEW definition, but alias from SELECT column_list
912
CREATE OR REPLACE VIEW v1 AS SELECT f1 As my_column FROM t1;
915
# 4. Finally the requirement: explicit column_list in VIEW definition
916
CREATE OR REPLACE VIEW v1(column1,column2)
917
AS SELECT f1 As my_column, f1 FROM t1;
920
CREATE OR REPLACE VIEW test.v1(column1,column2)
921
AS SELECT f1 As my_column, f1 FROM test.t1;
926
let $message= Testcase 3.3.1.18 ;
927
--source include/show_msg80.inc
928
###############################################################################
929
# Testcase 3.3.1.18: Ensure that a reference to a view with a definition that
930
# includes an explicit column-name fails, with an appropriate
931
# error message, if the reference includes columns names
932
# from the underlying base table(s) rather than the view
934
###############################################################################
935
# Note(mleich): The goal is to check the merge algorithm.
937
Drop view if exists v1 ;
938
Drop view if exists v1_1 ;
941
as Select test.tb2.f59 as NewNameF1, test.tb2.f60
942
from test.tb2 limit 0,100 ;
944
as Select test.tb2.f59 as NewNameF1, test.tb2.f60 as NewNameF2
945
from tb2 limit 0,100 ;
946
--error ER_BAD_FIELD_ERROR
947
SELECT NewNameF1,f60 FROM test.v1_1 ;
948
--error ER_BAD_FIELD_ERROR
949
SELECT NewNameF1, v1_1.f60 FROM test.v1_1 ;
950
--error ER_BAD_FIELD_ERROR
951
SELECT f59, f60 FROM test.v1 ;
953
--error ER_BAD_FIELD_ERROR
957
let $message= Testcase 3.3.1.19 ;
958
--source include/show_msg80.inc
959
###############################################################################
960
# Testcase 3.3.1.19: Ensure that every column of a view must have a
962
###############################################################################
964
DROP TABLE IF EXISTS t1, t2;
965
DROP VIEW IF EXISTS v1;
967
CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
968
INSERT INTO t1 VALUES(7, 7.7);
969
CREATE TABLE t2( f1 BIGINT, f2 DECIMAL(5,2));
970
INSERT INTO t2 VALUES(6, 6.6);
972
CREATE VIEW v1 AS SELECT * FROM t1;
974
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1;
976
CREATE OR REPLACE VIEW v1 AS SELECT f1 AS my_f1, f2 AS my_f2 FROM t1;
978
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
980
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT t1.f1, t2.f2 FROM t1, t2;
982
# negative testcases (sometimes including the underlying SELECT)
983
# duplicate via alias in SELECT
984
SELECT f1, f2 AS f1 FROM t1;
985
--error ER_DUP_FIELDNAME
986
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 AS f1 FROM t1;
987
# duplicate via JOIN SELECT
988
SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
989
--error ER_DUP_FIELDNAME
990
CREATE OR REPLACE VIEW v1 AS SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
991
# duplicate via VIEW definition
992
--error ER_DUP_FIELDNAME
993
CREATE OR REPLACE VIEW v1 (my_col, my_col) AS SELECT * FROM t1;
996
let $message= Testcase 3.3.1.20 ;
997
--source include/show_msg80.inc
998
###############################################################################
999
# Testcase 3.3.1.20: Ensure that, if a column-name list is provided for a
1000
# view definition, the list contains a name for every column
1002
###############################################################################
1004
DROP TABLE IF EXISTS t1;
1006
CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
1008
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT * FROM t1;
1009
CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
1010
# negative cases, where we assign a wrong number of column names
1011
--error ER_VIEW_WRONG_LIST
1012
CREATE OR REPLACE VIEW v1 (my_f1 ) AS SELECT * FROM t1;
1013
--error ER_VIEW_WRONG_LIST
1014
CREATE OR REPLACE VIEW v1 (my_f1 ) AS SELECT f1, f2 FROM t1;
1015
--error ER_VIEW_WRONG_LIST
1016
CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT * FROM t1;
1017
--error ER_VIEW_WRONG_LIST
1018
CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT f1, f2 FROM t1;
1021
let $message= Testcase 3.3.1.21 ;
1022
--source include/show_msg80.inc
1023
###############################################################################
1024
# Testcase 3.3.1.21: Ensure that a view column can be a direct copy of a
1025
# column from an underlying table.
1026
###############################################################################
1028
DROP VIEW IF EXISTS v1;
1030
CREATE VIEW test.v1( F59, F60 ) AS SELECT F59, F60 From tb2;
1031
SELECT * FROM test.v1 order by F59, F60 desc LIMIT 2;
1032
Drop view if exists test.v1 ;
1035
let $message= Testcase 3.3.1.22 ;
1036
--source include/show_msg80.inc
1037
###############################################################################
1038
# Testcase 3.3.1.22: Ensure that a view column can be based on any valid
1039
# expression, whether or not the expression includes a
1040
# reference of the column of an underlying table.
1041
###############################################################################
1043
DROP VIEW IF EXISTS v1;
1045
CREATE VIEW test.v1( product ) AS SELECT f59*f60 From tb2 WHERE f59 < 3;
1047
SELECT * FROM test.v1;
1048
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT 1*2;
1050
SELECT * FROM test.v1;
1051
CREATE OR REPLACE VIEW test.v1( product ) AS SELECT USER();
1053
SELECT * FROM test.v1;
1054
Drop view if exists test.v1 ;
1057
let $message= Testcase 3.3.1.23 + 3.3.1.24 ;
1058
--source include/show_msg80.inc
1059
###############################################################################
1060
# Testcase 3.3.1.23: Ensure that a view definition that includes a reference to
1061
# a non-existent table fails, with an appropriate error
1062
# message, at creation time.
1063
# Testcase 3.3.1.24: Ensure that a view definition that includes a reference to
1064
# a non-existent view fails, with an appropriate error
1065
# message, at creation time.
1066
###############################################################################
1067
# Note(mleich): The SELECT statement syntax does not contain any functionality
1068
# to claim, that the object after FROM must be a VIEW.
1069
# Testcase 3.3.1.24 should be deleted.
1072
DROP TABLE IF EXISTS t1;
1073
DROP VIEW IF EXISTS v1;
1074
DROP VIEW IF EXISTS v2;
1076
--error ER_NO_SUCH_TABLE
1077
CREATE VIEW test.v2 AS SELECT * FROM test.t1;
1078
--error ER_NO_SUCH_TABLE
1079
CREATE VIEW v2 AS Select * from test.v1;
1080
DROP VIEW IF EXISTS v2;
1083
let $message= Testcase 3.3.1.25 ;
1084
--source include/show_msg80.inc
1085
###############################################################################
1086
# Testcase 3.3.1.25: Ensure that a view cannot be based on one or more
1088
###############################################################################
1089
# Note(mleich): A temporary table hides permanent tables which have the same
1090
# name. So do not forget to drop the temporary table.
1092
DROP TABLE IF EXISTS t1_temp;
1093
DROP TABLE IF EXISTS t2_temp;
1094
DROP VIEW IF EXISTS v1;
1096
Create table t1_temp(f59 char(10),f60 int) ;
1097
Create temporary table t1_temp(f59 char(10),f60 int) ;
1098
Insert into t1_temp values('FER',90);
1099
Insert into t1_temp values('CAR',27);
1100
--error ER_VIEW_SELECT_TMPTABLE
1101
Create view v1 as select * from t1_temp ;
1103
Create temporary table t2_temp(f59 char(10),f60 int) ;
1104
Insert into t2_temp values('AAA',11);
1105
Insert into t2_temp values('BBB',22);
1106
--error ER_VIEW_SELECT_TMPTABLE
1107
Create or replace view v1
1108
as select t1_temp.f59,t2_temp.f59 from t1_temp,t2_temp ;
1109
DROP temporary table t1_temp;
1111
DROP temporary table t2_temp;
1114
DROP TABLE IF EXISTS t1;
1115
DROP VIEW IF EXISTS v1;
1117
CREATE TABLE t1 (f1 char(10));
1118
CREATE TEMPORARY TABLE t2 (f2 char(10));
1119
INSERT INTO t1 VALUES('t1');
1120
INSERT INTO t1 VALUES('A');
1121
INSERT INTO t2 VALUES('t2');
1122
INSERT INTO t2 VALUES('B');
1124
--error ER_VIEW_SELECT_TMPTABLE
1125
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2;
1126
# JOIN - temporary table first
1127
--error ER_VIEW_SELECT_TMPTABLE
1128
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2, t1;
1129
--error ER_VIEW_SELECT_TMPTABLE
1130
CREATE OR REPLACE VIEW v1 AS SELECT f2, f1 FROM t2, t1;
1131
# JOIN - temporary table last
1132
--error ER_VIEW_SELECT_TMPTABLE
1133
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1, t2;
1134
--error ER_VIEW_SELECT_TMPTABLE
1135
CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1, t2;
1136
# UNION - temporary table first
1137
--error ER_VIEW_SELECT_TMPTABLE
1138
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2 UNION SELECT * FROM t1;
1139
--error ER_VIEW_SELECT_TMPTABLE
1140
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2 UNION SELECT f1 FROM t1;
1141
# UNION - temporary table last
1142
--error ER_VIEW_SELECT_TMPTABLE
1143
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;
1144
--error ER_VIEW_SELECT_TMPTABLE
1145
CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1 UNION SELECT f2 FROM t2;
1146
# SUBQUERY - temporary table first
1147
--error ER_VIEW_SELECT_TMPTABLE
1148
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
1149
WHERE f2 = ( SELECT f1 FROM t1 );
1150
# SUBQUERY - temporary table last
1151
--error ER_VIEW_SELECT_TMPTABLE
1152
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t1
1153
WHERE f1 = ( SELECT f2 FROM t2 );
1155
DROP TEMPORARY TABLE t2;
1158
let $message= Testcase 3.3.1.26 ;
1159
--source include/show_msg80.inc
1160
###############################################################################
1161
# Testcase 3.3.1.26: Ensure that a view can be based on an underlying table
1162
# within the same database
1163
###############################################################################
1165
DROP VIEW IF EXISTS v1;
1167
Create view test.v1 AS Select * from test.tb2;
1168
if ($have_bug_11589)
1170
--disable_ps_protocol
1173
Select * from test.v1;
1174
--enable_ps_protocol
1178
let $message= Testcase 3.3.1.27 ;
1179
--source include/show_msg80.inc
1180
###############################################################################
1181
# Testcase 3.3.1.27: Ensure that a view can be based on an underlying view
1182
# within the same database.
1183
###############################################################################
1185
DROP VIEW IF EXISTS test.v1;
1186
Drop VIEW IF EXISTS test.v1_1 ;
1188
Create view test.v1 AS Select * from test.tb2;
1189
Create view test.v1_1 AS Select F59 from test.v1 ;
1190
Select * from test.v1_1 order by F59 limit 2;
1192
Drop view test.v1_1 ;
1195
let $message= Testcase 3.3.1.28 ;
1196
--source include/show_msg80.inc
1197
###############################################################################
1198
# Testcase 3.3.1.28: Ensure that a view can be based on an underlying table
1199
# from another database.
1200
###############################################################################
1202
Drop database if exists test2 ;
1204
create database test2 ;
1205
Create view test2.v2 AS Select * from test.tb2 limit 50,50;
1207
Create view v1 AS Select * from test.tb2 limit 50 ;
1208
if ($have_bug_32285)
1210
--disable_ps_protocol
1213
Select * from v1 order by f59,f60,f61,f62,f63,f64,f65;
1214
--horizontal_results
1215
--enable_ps_protocol
1217
Select * from test2.v2 ;
1218
Drop view if exists test2.v1 ;
1219
Drop view if exists test2.v2 ;
1220
Drop database test2 ;
1223
let $message= Testcase 3.3.1.29 ;
1224
--source include/show_msg80.inc
1225
###############################################################################
1226
# Testcase 3.3.1.29: Ensure that a view can be based on an underlying view from
1228
###############################################################################
1230
Drop database if exists test2 ;
1231
Drop view if exists test.v1 ;
1233
create database test2 ;
1236
Create view test.v1 AS Select * from test.tb2 limit 50 ;
1237
Create view test2.v2 AS Select F59 from test.v1 ;
1238
Drop view if exists test.v1 ;
1239
Drop view if exists test2.v2 ;
1241
# Note(mleich): Testcase 3.3.1.30 (identical requirements like 3.3.1.26)
1244
let $message= Testcase 3.3.1.31 ;
1245
--source include/show_msg80.inc
1246
###############################################################################
1247
# Testcase 3.3.1.31: Ensure that a view can be based on a join of multiple
1248
# tables within the same database.
1249
###############################################################################
1251
Drop table if exists test.t1 ;
1253
CREATE TABLE test.t1 ( f59 int, f60 int );
1254
INSERT INTO test.t1 VALUES( 34, 654 );
1255
INSERT INTO test.t1 VALUES( 906, 434 );
1256
INSERT INTO test.t1 VALUES( 445, 765 );
1257
Create or replace view test.v1
1258
AS SELECT test.t1.F59, test.tb2.F60
1259
FROM test.tb2 JOIN test.t1 ON test.tb2.F59 = test.t1.F59 ;
1261
Select * from test.v1;
1265
let $message= Testcase 3.3.1.32 ;
1266
--source include/show_msg80.inc
1267
###############################################################################
1268
# Testcase 3.3.1.32: Ensure that a view can be based on a join of multiple
1269
# tables from another database.
1270
###############################################################################
1272
Drop table if exists test.t1 ;
1273
Drop database if exists test2 ;
1274
Drop view if exists test.v1 ;
1276
create database test2 ;
1278
CREATE TABLE t1 ( f59 int, f60 int );
1279
INSERT INTO t1 VALUES( 34, 654 );
1280
INSERT INTO t1 VALUES( 906, 434 );
1281
INSERT INTO t1 VALUES( 445, 765 );
1282
CREATE VIEW test2.v1
1283
AS SELECT test.tb2.F59, test.tb2.F60
1284
FROM test.tb2 INNER JOIN test2.t1 ON tb2.f59 = t1.f59;
1286
Select * from test2.v1;
1291
let $message= Testcase 3.3.1.33 ;
1292
--source include/show_msg80.inc
1293
###############################################################################
1294
# Testcase 3.3.1.33: Ensure that a view can be based on a join of multiple
1295
# views within the same database.
1296
###############################################################################
1298
Drop view if exists test.v1_firstview ;
1299
Drop view if exists test.v1_secondview ;
1300
Drop view if exists test.v1 ;
1302
CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;
1303
CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2;
1305
AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
1306
FROM test.v1_firstview INNER JOIN test.v1_secondview
1307
ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
1308
SELECT * FROM test.v1 order by f59,f60 limit 0,10;
1309
Drop view if exists test.v1_firstview ;
1310
Drop view if exists test.v1_secondview ;
1311
Drop view if exists test.v1 ;
1314
let $message= Testcase 3.3.1.34 ;
1315
--source include/show_msg80.inc
1316
###############################################################################
1317
# Testcase 3.3.1.34: Ensure that a view can be based on a join of multiple
1318
# views from another database.
1319
###############################################################################
1321
Drop database if exists test2 ;
1322
Drop view if exists test.v1_firstview ;
1323
Drop view if exists test.v1_secondview ;
1326
create database test2 ;
1328
CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2 ;
1329
CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2 ;
1332
AS SELECT test.v1_firstview.F59, test.v1_firstview.F60
1333
FROM test.v1_firstview INNER JOIN test.v1_secondview
1334
ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
1335
SELECT * FROM v1 order by f59,f60 limit 0,10;
1337
Drop view test.v1_firstview ;
1338
Drop view test.v1_secondview ;
1341
let $message= Testcase 3.3.1.35 ;
1342
--source include/show_msg80.inc
1343
###############################################################################
1344
# Testcase 3.3.1.35: Ensure that a view can be based on a join of multiple
1345
# tables and/or views within the same database.
1346
###############################################################################
1350
Drop view if exists test.v1;
1351
Drop view if exists test.v1_firstview;
1354
CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;
1357
AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
1358
FROM test.v1_firstview INNER JOIN test.tb2
1359
ON test.v1_firstview.f59 = test.tb2.f59;
1360
SELECT * FROM test.v1 order by f59,f60 limit 0,10;
1362
Drop view test.v1_firstview;
1365
let $message= Testcase 3.3.1.36 ;
1366
--source include/show_msg80.inc
1367
###############################################################################
1368
# Testcase 3.3.1.36: Ensure that a view can be based on a join of multiple
1369
# tables and/or views from another database.
1370
###############################################################################
1372
Drop database if exists test2 ;
1374
create database test2 ;
1377
CREATE VIEW v1_firstview AS SELECT * FROM test.tb2 ;
1379
AS SELECT v1_firstview.f59, v1_firstview.f60
1380
FROM v1_firstview INNER JOIN test.tb2 ON v1_firstview.f59 = test.tb2.f59 ;
1381
SELECT * FROM v1 order by f59,f60 limit 0,10;
1383
Drop database test2 ;
1386
let $message= Testcase 3.3.1.37 ;
1387
--source include/show_msg80.inc
1388
###############################################################################
1389
# Testcase 3.3.1.37: Ensure that a view can be based on a join of multiple
1390
# tables and/or views, some of which reside in the same
1391
# database and some of which reside in one other database.
1392
###############################################################################
1395
Drop table if exists t1;
1396
Drop view if exists test.v1 ;
1397
Drop view if exists test.v1_1 ;
1398
Drop view if exists test.v1_1 ;
1399
Drop view if exists test.v1_main ;
1401
Create view test.v1 as Select f59, f60 FROM test.tb2;
1402
Select * from test.v1 order by f59,f60 limit 0,10;
1404
Create table t1(f59 int, f60 int);
1405
Insert into t1 values (90,507) ;
1407
Create view v1_1 as Select f59,f60 from t1 ;
1408
Select * from v1_1 ;
1411
as SELECT test.tb2.f59 FROM test.tb2 JOIN test.v1
1412
ON test.tb2.f59 = test.v1.f59;
1413
Select * from v1_main order by f59 limit 0,10;
1417
Drop view test.v1_1 ;
1418
Drop view test.v1_main ;
1421
let $message= Testcase 3.3.1.31 - 3.3.1.37 New Implementation ;
1422
--source include/show_msg80.inc
1423
###############################################################################
1424
# mleich: The testcases 3.3.1.31 - 3.3.1.37 should be tested more systematic.
1425
# Ensure that a view can be based on a join of multiple
1426
# Testcase 3.3.1.31: tables within the same database
1427
# Testcase 3.3.1.32: tables from another database.
1428
# Testcase 3.3.1.33: views within the same database
1429
# Testcase 3.3.1.34: views from another database
1430
# Testcase 3.3.1.35: tables and/or views within the same database
1431
# Testcase 3.3.1.36: tables and/or views from another database
1432
# Testcase 3.3.1.37: tables and/or views, some of which reside in
1433
# the same database and some of which reside in
1434
# one other database.
1435
###############################################################################
1438
DROP DATABASE IF EXISTS test2;
1439
DROP TABLE IF EXISTS t0,t1;
1440
DROP VIEW IF EXISTS t3,t4;
1442
CREATE DATABASE test2;
1445
CREATE TABLE test1.t0 (f1 VARCHAR(20));
1446
CREATE TABLE test1.t1 (f1 VARCHAR(20));
1448
CREATE TABLE test2.t0 (f1 VARCHAR(20));
1449
CREATE TABLE test2.t1 (f1 VARCHAR(20));
1451
CREATE VIEW test1.t2 AS SELECT * FROM test1.t0;
1452
CREATE VIEW test1.t3 AS SELECT * FROM test2.t0;
1454
CREATE VIEW test2.t2 AS SELECT * FROM test2.t0;
1455
CREATE VIEW test2.t3 AS SELECT * FROM test1.t0;
1456
INSERT INTO test1.t0 VALUES('test1.t0');
1457
INSERT INTO test1.t1 VALUES('test1.t1');
1458
INSERT INTO test2.t0 VALUES('test2.t0');
1459
INSERT INTO test2.t1 VALUES('test2.t1');
1461
# The extreme simple standard JOIN VIEW is:
1462
# CREATE OR REPLACE VIEW <database>.v1
1463
# AS SELECT * FROM <table or view 1>,<table or view 2>
1467
# eval CREATE OR REPLACE VIEW $view AS SELECT * FROM $tab1, $tab2;
1468
# Produce at least all testcases via simple combinatorics, because it is better
1469
# to check some useless combinations than to forget an important one.
1483
# Maybe somebody needs to check the generated values
1484
# --disable_query_log
1485
# eval SELECT '$num_db1.$num_tab1,$num_db2.$num_tab2';
1486
# --enable_query_log
1487
eval CREATE OR REPLACE VIEW $view AS
1488
SELECT ta.f1 AS col1,
1490
FROM test$num_db1.t$num_tab1 ta, test$num_db2.t$num_tab2 tb;
1491
eval SELECT * FROM $view;
1506
let $message= Testcase 3.3.1.38 ;
1507
--source include/show_msg80.inc
1508
###############################################################################
1509
# Testcase 3.3.1.38: Ensure that a view can be based on a join of multiple
1510
# tables and/or views, some of which reside in the same
1511
# database and some of which reside two or more other
1513
###############################################################################
1515
Drop table if exists test1.t1 ;
1516
Drop view if exists test.v1 ;
1517
Drop view if exists test.v1_main;
1518
Drop view if exists test1.v1_1 ;
1519
Drop database if exists test3 ;
1521
Create view test.v1 as Select f59, f60 FROM test.tb2;
1522
Select * from test.v1 order by f59,f60 limit 20;
1524
Create table test1.t1 (f59 int,f60 int) ;
1525
Insert into test1.t1 values (199,507) ;
1526
Create view test1.v1_1 as Select f59,f60 from test1.t1 ;
1527
Select * from test1.v1_1 ;
1531
Create database test3 ;
1533
Create table test3.t1(f59 int,f60 int) ;
1534
Insert into test3.t1 values (1023,7670) ;
1535
Create view test3.v1_2 as Select f59,f60 from test3.t1 ;
1536
Select * from test3.v1_2 ;
1539
# mleich: FIXME The SELECT should deliver at least one row.
1541
as SELECT test.tb2.f59 as f1, test1.v1_1.f59 as f2,
1542
test3.v1_2.f59 as f3
1543
FROM (test.tb2,test1.v1_1,test.v1) JOIN test3.v1_2
1544
ON (test.v1.f59 = test1.v1_1.f59) ;
1545
Select * from v1_main ;
1548
DROP VIEW test1.v1_1 ;
1549
DROP VIEW test.v1_main ;
1550
DROP DATABASE test3;
1553
let $message= Testcase 3.3.1.39 ;
1554
--source include/show_msg80.inc
1555
###############################################################################
1556
# Testcase 3.3.1.39: Ensure that a view definition that includes a subquery in
1557
# a FROM clause is rejected with an appropriate error
1558
# message at create time.
1559
###############################################################################
1561
Drop view if exists test.v1 ;
1563
--error ER_VIEW_SELECT_DERIVED
1565
AS Select f59 from (Select * FROM tb2 limit 20) tx ;
1566
--error ER_NO_SUCH_TABLE
1567
SELECT * FROM test.v1 order by f59 ;
1569
Drop view if exists test.v1 ;
1573
let $message= Testcase 3.3.1.40 ;
1574
--source include/show_msg80.inc
1575
###############################################################################
1576
# Testcase 3.3.1.40: Ensure that a view definition that includes references to
1577
# one or more user variables is rejected with an appropriate
1578
# error message at create time.
1579
###############################################################################
1581
Drop view if exists test.v1 ;
1585
--error ER_VIEW_SELECT_VARIABLE
1586
CREATE VIEW test.v1 AS SELECT @var1, @var2 ;
1587
# System variables (name starts with '@@') are also not allowed
1588
--error ER_VIEW_SELECT_VARIABLE
1589
CREATE VIEW test.v1 AS SELECT @@global.sort_buffer_size;
1591
Drop view if exists test.v1 ;
1595
let $message= Testcase 3.3.1.41 ;
1596
--source include/show_msg80.inc
1597
###############################################################################
1598
# Testcase 3.3.1.41: Ensure that a view definition within a stored procedure
1599
# definition cannot include references to any of the stored
1600
# procedures parameters.
1601
###############################################################################
1603
Drop view if exists test.v1 ;
1604
Drop procedure if exists sp1 ;
1608
Create procedure sp1() DETERMINISTIC
1612
Create view test.v1 as SELECT * FROM tb2 WHERE f59 = x ;
1615
--error ER_SP_DOES_NOT_EXIST
1617
Drop view if exists test.v1 ;
1618
Drop procedure sp1 ;
1621
let $message= Testcase 3.3.1.42 ;
1622
--source include/show_msg80.inc
1623
###############################################################################
1624
# Testcase 3.3.1.42: Ensure that a view definition that attempts to create a
1625
# temporary view (e.g. CREATE TEMPORARY VIEW or CREATE OR
1626
# REPLACE TEMPORARY VIEW) fails, with an appropriate
1628
###############################################################################
1631
Drop VIEW if exists test.v1 ;
1633
--error ER_PARSE_ERROR
1634
CREATE TEMPORARY VIEW test.v1 AS
1635
SELECT * FROM test.tb2 limit 2 ;
1637
--error ER_PARSE_ERROR
1638
CREATE OR REPLACE TEMPORARY VIEW test.v1 AS
1639
SELECT * FROM test.tb2 limit 2 ;
1642
Drop view if exists test.v1 ;
1648
let $message= Testcase 3.3.1.43 ;
1649
--source include/show_msg80.inc
1650
###############################################################################
1651
# Testcase 3.3.1.43: Ensure that all valid changes (i.e. INSERT, UPDATE, DELETE
1652
# statements) to a view are shown in the view and are
1653
# accepted as changes by the underlying table(s).
1654
###############################################################################
1656
Drop view if exists test.v1 ;
1659
CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2;
1661
INSERT INTO test.v1 values(122,432);
1663
if ($have_bug_32285)
1665
--disable_ps_protocol
1668
SELECT * FROM test.tb2 where f59 = 122 and f60 = 432 limit 0,20;
1669
--horizontal_results
1670
--enable_ps_protocol
1672
UPDATE test.v1 SET f59 = 3000 WHERE test.v1.f59 = 122 ;
1674
if ($have_bug_32285)
1676
--disable_ps_protocol
1679
SELECT * FROM test.tb2 where f59 = 3000 limit 0,20;
1680
--horizontal_results
1681
--enable_ps_protocol
1684
where test.v1.f59 = 3000 and test.v1.f60 = 432;
1686
SELECT * FROM test.tb2 where f59 = 3000 and f60 = 432;
1691
let $message= Testcase 3.3.1.44 ;
1692
--source include/show_msg80.inc
1693
###############################################################################
1694
# Testcase 3.3.1.44: Ensure that all invalid changes to a view are rejected
1695
# with an appropriate error message and do not affect the
1696
# data in the underlying tables(s).
1697
###############################################################################
1698
# mleich: Maybe we need some more tests here.
1700
Drop view if exists test.v1 ;
1703
# Note(mleich): The modification will fail, because the VIEW contains 'limit'
1704
CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2 limit 100;
1706
--error ER_NON_INSERTABLE_TABLE
1707
INSERT INTO test.v1 values(31, 32, 33) ;
1712
let $message= Testcase 3.3.1.45 ;
1713
--source include/show_msg80.inc
1714
###############################################################################
1715
# Testcase 3.3.1.45: Ensure that, for a view with a definition that does not
1716
# include WITH CHECK OPTION, all changes to the view which
1717
# violate the view definition do not show in the view but
1718
# are accepted as changes by the underlying table(s) unless
1719
# a constraint on an underlying table also makes the change
1721
###############################################################################
1723
Drop view if exists test.v1 ;
1725
CREATE VIEW test.v1 AS SELECT * FROM test.tb2 where f59 = 04;
1728
UPDATE test.v1 SET f59 = 30 where F59 = 04 ;
1730
SELECT * FROM test.v1 where f59 = 30 order by f59;
1731
if ($have_bug_32285)
1733
--disable_ps_protocol
1736
SELECT * FROM test.tb2 where f59 = 30 ;
1737
--horizontal_results
1738
--enable_ps_protocol
1741
UPDATE tb2 SET f59 = 100 where f59 = 30 ;
1743
if ($have_bug_32285)
1745
--disable_ps_protocol
1748
SELECT * FROM tb2 where f59 = 100 ;
1749
--horizontal_results
1750
--enable_ps_protocol
1751
SELECT * FROM test.v1 order by f59 ;
1753
drop view if exists test.v1 ;
1756
Drop TABLE IF EXISTS test.t1 ;
1757
Drop VIEW IF EXISTS test.v1 ;
1759
eval CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), PRIMARY KEY(f1))
1760
ENGINE = $engine_type;
1761
INSERT INTO t1 VALUES(1,'one');
1762
INSERT INTO t1 VALUES(2,'two');
1763
INSERT INTO t1 VALUES(3,'three');
1764
INSERT INTO t1 VALUES(5,'five');
1765
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 BETWEEN 2 AND 4;
1768
# 1. Searched record is within the scope of the view
1769
# 1.1 + exists within the base table
1770
SELECT COUNT(*) FROM v1 WHERE f1 = 2;
1771
# 1.2 + does not exists within the base table
1772
SELECT COUNT(*) FROM v1 WHERE f1 = 4;
1773
# 2. Searched record is outside of the scope of the view
1774
# 2.1 + exists within the base table
1775
SELECT COUNT(*) FROM v1 WHERE f1 = 5;
1776
# 2.2 + does not exists within the base table
1777
SELECT COUNT(*) FROM v1 WHERE f1 = 10;
1779
INSERT INTO t1 VALUES(4,'four');
1783
# 1. Searched record is within the scope of the view
1784
# + exists within the base table
1785
DELETE FROM v1 WHERE f1 = 3;
1786
# 2. Searched record is outside of the scope of the view
1787
# + exists within the base table
1788
DELETE FROM v1 WHERE f1 = 5;
1790
SELECT * FROM t1 ORDER BY f1;
1791
SELECT * FROM v1 ORDER BY f1;
1795
# 1. The record to be inserted will be within the scope of the view.
1796
# But there is already a record with the PRIMARY KEY f1 = 2 .
1797
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
1798
--error ER_DUP_ENTRY
1799
INSERT INTO v1 VALUES(2,'two');
1800
# 2. The record to be inserted will be within the scope of the view.
1801
# There is no already existing record with the PRIMARY KEY f1 = 3 .
1802
INSERT INTO v1 VALUES(3,'three');
1803
# 3. The record to be inserted will be outside of the scope of the view.
1804
# There is no already existing record with the PRIMARY KEY f1 = 6 .
1805
INSERT INTO v1 VALUES(6,'six');
1807
SELECT * FROM t1 ORDER BY f1;
1808
SELECT * FROM v1 ORDER BY f1;
1812
# 1. The record to be updated is within the scope of the view
1813
# and will stay inside the scope.
1814
# But there is already a record with the PRIMARY KEY f1 = 2 .
1815
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
1816
--error ER_DUP_ENTRY
1817
UPDATE v1 SET f1 = 2 WHERE f1 = 3;
1818
# 2. The record to be updated is within the scope of the view
1819
# and will stay inside the scope.
1820
UPDATE v1 SET f2 = 'number' WHERE f1 = 3;
1821
# 3. The record to be updated is within the scope of the view
1822
# and will leave the scope.
1823
UPDATE v1 SET f1 = 10 WHERE f1 = 3;
1824
# 4. The record to be updated is outside of the scope of the view.
1825
UPDATE v1 SET f2 = 'number' WHERE f1 = 1;
1829
let $message= Testcase 3.3.1.46 ;
1830
--source include/show_msg80.inc
1831
###############################################################################
1832
# Testcase 3.3.1.46: Ensure that, for a view with a definition that does
1833
# include WITH CHECK OPTION, all changes to the view which
1834
# violate the view definition are rejected with an
1835
# appropriate error message and are not accepted as changes
1836
# by the underlying table(s).
1837
###############################################################################
1839
Drop view if exists test.v1 ;
1841
CREATE VIEW test.v1 AS SELECT f59,f60
1842
FROM test.tb2 where f59 = 195 WITH CHECK OPTION ;
1844
--error ER_VIEW_CHECK_FAILED
1845
UPDATE test.v1 SET f59 = 198 where f59=195 ;
1846
SELECT * FROM test.v1 order by f59 ;
1848
drop view if exists test.v1 ;
1851
let $message= Testcase 3.3.1.47 ;
1852
--source include/show_msg80.inc
1853
###############################################################################
1854
# Testcase 3.3.1.47: Ensure that, for a view with a definition that does
1855
# include WITH LOCAL CHECK OPTION, all changes to the view
1856
# which violate the view definition are rejected with an
1857
# appropriate error message and are not accepted as changes
1858
# by the underlying table(s).
1859
###############################################################################
1861
Drop view if exists test.v1 ;
1862
Drop view if exists test.v2 ;
1864
CREATE VIEW test.v1 AS SELECT f59,f60
1865
FROM test.tb2 where F59 = 0987 WITH LOCAL CHECK OPTION ;
1866
CREATE VIEW test.v2 as SELECT * FROM test.v1 ;
1868
# This UPDATE violates the definition of VIEW test.v1.
1869
--error ER_VIEW_CHECK_FAILED
1870
UPDATE test.v1 SET F59 = 919 where f59 = 0987 ;
1871
SELECT * FROM test.v1 order by f59 ;
1873
# mleich: This UPDATE violates the definition of VIEW test.v1, but this
1874
# does not count, because the UPDATE runs on test.v2, which
1875
# is defined without any CHECK OPTION.
1876
# FIXME Does this testcase fit to 3.3.1.47 ?
1877
UPDATE test.v2 SET F59 = 9879 where f59 = 919 ;
1878
SELECT * FROM tb2 where f59 = 9879 ;
1880
drop view if exists v1 ;
1881
drop view if exists v2 ;
1884
let $message= Testcase 3.3.1.48 ;
1885
--source include/show_msg80.inc
1886
###############################################################################
1887
# Testcase 3.3.1.48: Ensure that, for a view with a definition that does
1888
# include WITH CASCADED CHECK OPTION, all changes to the
1889
# view which violate the view definition are rejected with
1890
# an appropriate error message and are not accepted as
1891
# changes by the underlying table(s).
1892
###############################################################################
1894
DROP TABLE IF EXISTS test.t1;
1895
DROP VIEW IF EXISTS test.v1;
1897
eval CREATE TABLE t1 (f1 ENUM('A', 'B', 'C') NOT NULL, f2 INTEGER)
1898
ENGINE = $engine_type;
1899
INSERT INTO t1 VALUES ('A', 1);
1900
SELECT * FROM t1 order by f1, f2;
1902
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f2 BETWEEN 1 AND 2
1903
WITH CASCADED CHECK OPTION ;
1904
SELECT * FROM v1 order by f1, f2;
1907
UPDATE v1 SET f2 = 2 WHERE f2 = 1;
1908
INSERT INTO v1 VALUES('B',2);
1910
# Bug#11771: View over InnoDB table, wrong result SELECT on VIEW,
1911
# field->query_id wrong
1912
SELECT * FROM v1 order by f1, f2;
1915
--error ER_VIEW_CHECK_FAILED
1916
UPDATE v1 SET f2 = 4;
1917
--error ER_VIEW_CHECK_FAILED
1918
INSERT INTO v1 VALUES('B',3);
1920
# Bug#11771: View over InnoDB table, wrong result SELECT on VIEW,
1921
# field->query_id wrong
1922
SELECT * FROM v1 order by f1, f2;
1925
let $message= Testcase 3.3.1.49 ;
1926
--source include/show_msg80.inc
1927
###############################################################################
1928
# Testcase 3.3.1.49: Ensure that the WITH [LOCAL | CASCADED] CHECK OPTION
1929
# constraint is always correctly performed within the
1930
# correct scope, including in cases where a view is based
1931
# upon multiple other views whose definitions include every
1932
# possible combination of the WITH CHECK OPTION variants.
1933
###############################################################################
1935
Drop table if exists test.t1 ;
1936
Drop view if exists test.v1 ;
1937
Drop view if exists test.v2 ;
1938
Drop view if exists test.v3 ;
1940
Create table test.t1 (f59 INT, f60 INT) ;
1942
Insert into test.t1 values (100,4234);
1943
Insert into test.t1 values (290,6624);
1944
Insert into test.t1 values (410,765);
1945
Insert into test.t1 values (300,433334);
1946
Insert into test.t1 values (800,9788);
1947
Insert into test.t1 values (501,9866);
1949
Create view test.v1 as select f59
1950
FROM test.t1 where f59<500 with check option ;
1952
Create view test.v2 as select *
1953
from test.v1 where f59>0 with local check option ;
1958
Create view test.v3 as select *
1959
from test.v1 where f59>0 with cascaded check option ;
1961
Insert into test.v2 values(23) ;
1962
Insert into test.v3 values(24) ;
1964
drop view if exists test.v1 ;
1965
drop view if exists test.v2 ;
1966
drop view if exists test.v3 ;
1968
let $message= Testcase 3.3.1.49A ;
1969
--source include/show_msg80.inc
1971
# -----------------------------------------------------------
1972
# VIEW v1 is based on table t1 (*)
1973
# VIEW v2 is based on view v1 (*)
1974
# VIEW v3 is based on view v2 (*)
1976
# (*) All variants like
1977
# - without check option
1978
# - WITH CASCADED CHECK OPTION
1979
# - WITH CHECK OPTION (default = CASCADED)
1980
# - WITH LOCAL CHECK OPTION
1982
# The rules for updating and inserting column values:
1983
# 1. Top VIEW WITH CASCADED CHECK OPTION
1984
# --> The WHERE qualifications of all nested VIEWs have to be fulfilled.
1985
# The CHECK OPTIONS of underlying VIEWs have no effect.
1986
# 2. Top VIEW WITH LOCAL CHECK OPTION
1987
# --> Only the WHERE qualification of this VIEW has to be fulfilled.
1988
# The CHECK OPTIONS of underlying VIEWs have no effect.
1989
# 3. Top VIEW without any CHECK OPTION
1990
# --> The WHERE qualifications of all nested VIEWs need not to be fulfilled.
1991
# The CHECK OPTIONS of underlying VIEWs have no effect.
1993
# v3 | v2 | v1 | Qualifications to be checked
1994
# ------------------------------------------------------------------------
1995
# CASCADED | <any> | <any> | qual_v3 + qual_v2 + qual_v3
1996
# <default> | <any> | <any> | qual_v3 + qual_v2 + qual_v3
1997
# LOCAL | <any> | <any> | qual_v3
1998
# <without> | <any> | <any> |
2000
# Note: The CHECK OPTION does not influence the retrieval of rows
2001
# (SELECT/DELETE/UPDATE). All WHERE qualifications will be applied
2002
# for the retrieval of rows.
2004
# The annoying redundant
2005
# eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2006
# @v3_to_v1_violation,$mysql_errno);
2007
# could not be put into a file to be sourced because of the closed
2008
# Bug#10267 mysqltest, wrong number of loops when a script is sourced
2010
# To be implemented later.
2014
DROP TABLE IF EXISTS test.t1 ;
2015
DROP TABLE IF EXISTS test.t1_results ;
2016
DROP VIEW IF EXISTS test.v1;
2017
DROP VIEW IF EXISTS test.v2;
2018
DROP VIEW IF EXISTS test.v3;
2020
CREATE TABLE t1 (f1 INTEGER, f2 CHAR(20));
2021
CREATE TABLE t1_results (v3_to_v1_options VARCHAR(100), statement VARCHAR(10),
2022
v3_to_v1_violation VARCHAR(20), errno CHAR(10));
2025
SET @part2= 'WITH CHECK OPTION';
2026
SET @part3= 'WITH CASCADED CHECK OPTION';
2027
SET @part4= 'WITH LOCAL CHECK OPTION';
2034
eval SET @v1_part= @part$num1;
2035
let $aux= `SELECT CONCAT('CREATE VIEW v1 AS SELECT f1, f2
2036
FROM t1 WHERE f1 BETWEEN 0 AND 10 ', @v1_part)` ;
2044
eval SET @v2_part= @part$num2;
2045
let $aux= `SELECT CONCAT('CREATE VIEW v2 AS SELECT f1 AS col1, f2 AS col2
2046
FROM v1 WHERE f1 BETWEEN 6 AND 16 ', @v2_part)` ;
2054
eval SET @v3_part= @part$num3;
2055
let $aux= `SELECT CONCAT('CREATE VIEW v3 (my_col1,my_col2) AS SELECT *
2056
FROM v2 WHERE col1 MOD 2 = 0 ', @v3_part)` ;
2059
SELECT CONCAT(IF(@v3_part = '',' <nothing> ',
2061
IF(@v2_part = '',' <nothing> ',
2063
IF(@v1_part = '',' <nothing> ',
2066
UNION SELECT RPAD('', 80, '-');
2067
SET @v3_to_v1_options = CONCAT(IF(@v3_part = '',' <nothing> ',
2069
IF(@v2_part = '',' <nothing> ',
2071
IF(@v1_part = '',' <nothing> ',
2073
--horizontal_results
2075
# 1. Visibility of records of t1 via SELECT on the VIEWs
2076
# Outside v1 (0 to 10)
2077
INSERT INTO t1 VALUES(16, 'sixteen');
2078
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2079
INSERT INTO t1 VALUES(0, 'zero');
2080
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2081
# Outside v3 ( value MOD 2 = 0 )
2082
INSERT INTO t1 VALUES(7, 'seven');
2083
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2084
# Inside v3 ( value MOD 2 = 0 )
2085
INSERT INTO t1 VALUES(8, 'eight');
2091
# 2. DELETEs within v3
2092
# Outside v1 (0 to 10)
2093
INSERT INTO t1 VALUES(16, 'sixteen');
2094
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2095
INSERT INTO t1 VALUES(0, 'zero');
2096
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2097
# Outside v3 ( value MOD 2 = 0 )
2098
INSERT INTO t1 VALUES(7, 'seven');
2099
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2100
# Inside v3 ( value MOD 2 = 0 )
2101
INSERT INTO t1 VALUES(8, 'eight');
2103
# Outside v1 (0 to 10)
2104
DELETE FROM v3 WHERE my_col1 = 16;
2105
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2106
DELETE FROM v3 WHERE my_col1 = 0;
2107
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2108
# Outside v3 ( value MOD 2 = 0 )
2109
DELETE FROM v3 WHERE my_col1 = 7;
2110
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2111
# Inside v3 ( value MOD 2 = 0 )
2112
DELETE FROM v3 WHERE my_col1 = 8;
2116
# 3. UPDATEs within v3 (modify my_col2, which is not part of any
2117
# WHERE qualification)
2118
# The behaviour should be similar to 3. DELETE.
2119
# Outside v1 (0 to 10)
2120
INSERT INTO t1 VALUES(16, 'sixteen');
2121
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2122
INSERT INTO t1 VALUES(0, 'zero');
2123
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2124
# Outside v3 ( value MOD 2 = 0 )
2125
INSERT INTO t1 VALUES(7, 'seven');
2126
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2127
# Inside v3 ( value MOD 2 = 0 )
2128
INSERT INTO t1 VALUES(8, 'eight');
2130
# Outside v1 (0 to 10)
2131
UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 16;
2132
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2133
UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 0;
2134
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2135
# Outside v3 ( value MOD 2 = 0 )
2136
UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 7;
2137
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2138
# Inside v3 ( value MOD 2 = 0 )
2139
UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 8;
2143
# 4. UPDATEs within v3 (modify my_col1 to values inside and outside
2144
# of the WHERE qualifications)
2146
SET @statement = 'UPDATE';
2148
INSERT INTO t1 VALUES(8, 'eight');
2149
# Alter to value outside of v3
2151
SET @v3_to_v1_violation = 'v3_ _ ';
2154
UPDATE v3 SET my_col1 = 7 WHERE my_col1 = 8;
2157
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2158
@v3_to_v1_violation,$mysql_errno);
2162
INSERT INTO t1 VALUES(8, 'eight');
2163
# Alter to value outside of v2
2165
SET @v3_to_v1_violation = ' _v2_ ';
2168
UPDATE v3 SET my_col1 = 0 WHERE my_col1 = 8;
2171
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2172
@v3_to_v1_violation,$mysql_errno);
2176
INSERT INTO t1 VALUES(8, 'eight');
2177
# Alter to value outside of v1
2179
SET @v3_to_v1_violation = ' _ _v1';
2182
UPDATE v3 SET my_col1 = 16 WHERE my_col1 = 8;
2185
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2186
@v3_to_v1_violation,$mysql_errno);
2190
INSERT INTO t1 VALUES(8, 'eight');
2191
# Alter to value inside of v1
2193
SET @v3_to_v1_violation = ' _ _ ';
2196
UPDATE v3 SET my_col1 = 10 WHERE my_col1 = 8;
2199
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2200
@v3_to_v1_violation,$mysql_errno);
2204
# 5. INSERTs into v3
2206
SET @statement = 'INSERT';
2208
# Outside v1 (0 to 10)
2210
SET @v3_to_v1_violation = ' _ _v1';
2213
INSERT INTO v3 VALUES(16, 'sixteen');
2216
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2217
@v3_to_v1_violation,$mysql_errno);
2219
# Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2221
SET @v3_to_v1_violation = ' _v2_ ';
2224
INSERT INTO v3 VALUES(0, 'zero');
2227
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2228
@v3_to_v1_violation,$mysql_errno);
2230
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2231
# Outside v3 ( value MOD 2 = 0 )
2233
SET @v3_to_v1_violation = 'v3_ _ ';
2236
INSERT INTO v3 VALUES(7, 'seven');
2238
# Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2239
# Inside v3 ( value MOD 2 = 0 )
2241
SET @v3_to_v1_violation = ' _ _ ';
2244
INSERT INTO v3 VALUES(8, 'eight');
2247
eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2248
@v3_to_v1_violation,$mysql_errno);
2265
SELECT * FROM t1_results ORDER BY v3_to_v1_options;
2268
Plausibility checks for INSERTs and UPDATEs ( 4. and 5. above).
2269
All following SELECTs must give ROW NOT FOUND ;
2270
--source include/show_msg80.inc
2272
# Plausibility checks for INSERTs and UPDATEs ( 4. and 5. above):
2273
# 1. There must be NO denied INSERT/UPDATE, when no WHERE qualification
2274
# is violated. Expect ROW NOT FUND
2275
SELECT * FROM t1_results
2276
WHERE v3_to_v1_violation = ' _ _ ' AND errno <> 0
2277
ORDER BY v3_to_v1_options;
2278
# 2. There must be NO denied INSERT/UPDATE, when the toplevel VIEW v3 is
2279
# defined without any CHECK OPTION. Expect ROW NOT FUND
2280
SELECT * FROM t1_results
2281
WHERE v3_to_v1_options LIKE ' %' AND errno <> 0
2282
ORDER BY v3_to_v1_options;
2283
# 3. There must be NO successful INSERT/UPDATE, when the toplevel VIEW v3 is
2284
# defined with any CHECK OPTION and the WHERE qualification of this VIEW is
2285
# violated. Expect ROW NOT FUND
2286
SELECT * FROM t1_results
2287
WHERE v3_to_v1_options LIKE 'WITH %'
2288
AND v3_to_v1_violation LIKE 'v3_%' AND errno = 0
2289
ORDER BY v3_to_v1_options;
2290
# 4. There must be NO successful INSERT/UPDATE, when the toplevel VIEW v3 is
2291
# defined with any CHECK OPTION and the CHECK OPTION does not contain LOCAL
2292
# and the WHERE qualification of any VIEW is violated. Expect ROW NOT FUND
2293
SELECT * FROM t1_results
2294
WHERE v3_to_v1_options LIKE 'WITH %' AND v3_to_v1_options NOT LIKE 'WITH LOCAL %'
2295
AND v3_to_v1_violation NOT LIKE ' _ _ ' AND errno = 0
2296
ORDER BY v3_to_v1_options;
2297
# 5. There must be NO failing INSERT/UPDATE getting a
2298
# sql_errno <> 1369 (ER_VIEW_CHECK_FAILED).
2299
SELECT * FROM t1_results
2300
WHERE errno <> 0 AND errno <> 1369
2301
ORDER BY v3_to_v1_options;
2302
let $message= End of plausibility checks;
2303
--source include/show_msg80.inc
2305
DROP TABLE t1_results;
2308
let $message= Testcase 3.3.1.50 - 3.3.1.53;
2309
--source include/show_msg80.inc
2311
DROP VIEW IF EXISTS test.v1;
2313
###############################################################################
2314
# Testcase 3.3.1.50: Ensure that a view that is a subset of every column and
2315
# every row of a single underlying table, contains the
2316
# correct row-and-column data; such a view has a definition
2317
# that is semantically equivalent to CREATE VIEW <view name>
2318
# AS SELECT * FROM <table name>.
2319
###############################################################################
2320
CREATE VIEW test.v1 AS SELECT * FROM test.tb2;
2321
if ($have_bug_32285)
2323
--disable_ps_protocol
2326
SELECT * FROM test.v1 order by f59,f60,f61 ;
2327
--horizontal_results
2328
--enable_ps_protocol
2330
###############################################################################
2331
# Testcase 3.3.1.51: Ensure that a view that is a subset of only some columns
2332
# and every row of a single underlying table, contains the
2333
# correct row-and-column data; such a view has a definition
2334
# that is semantically equivalent to CREATE VIEW <view name>
2335
# AS SELECT col1, col3 FROM <table name>.
2336
###############################################################################
2337
CREATE VIEW test.v1 AS SELECT F59,F61 FROM test.tb2;
2338
SELECT * FROM test.v1 order by F59, F61 limit 50;
2340
###############################################################################
2341
# Testcase 3.3.1.52: Ensure that a view that is a subset of every column and
2342
# some rows of a single underlying table, contains the
2343
# correct row-and-column data; such a view has a definition
2344
# that is semantically equivalent to CREATE VIEW <view name>
2345
# AS SELECT * FROM <table name> WHERE ....
2346
###############################################################################
2347
CREATE VIEW test.v1 AS SELECT * FROM test.tb2 order by f59, f60, f61;
2348
if ($have_bug_11589)
2350
--disable_ps_protocol
2353
SELECT * FROM test.v1 order by f59,f60,f61 ;
2354
--horizontal_results
2355
--enable_ps_protocol
2357
###############################################################################
2358
# Testcase 3.3.1.53: Ensure that a view that is a subset of only some columns
2359
# and some rows of a single underlying table, contains
2360
# the correct row-and-column data; such a view has a
2361
# definition that is semantically equivalent to CREATE VIEW
2362
# <view name> AS SELECT col1, col3 FROM <table name> WHERE ..
2363
###############################################################################
2364
CREATE VIEW test.v1 AS SELECT F59,f61 FROM test.tb2;
2365
SELECT * FROM test.v1 order by f59,f61 desc limit 20;
2369
let $message= Testcase 3.3.1.54 ;
2370
--source include/show_msg80.inc
2371
###############################################################################
2372
# Testcase 3.3.1.54: Ensure that a view that is a subset of some or all columns
2373
# and/or column expressions and some or all rows of a single
2374
# underlying table contains the correct row-and-column data.
2375
###############################################################################
2378
drop table if exists test.t1 ;
2379
drop table if exists test.t2 ;
2380
drop view if exists test.v1 ;
2382
Create table t1 (f59 int, f60 int) ;
2383
Create table t2 (f59 int, f60 int) ;
2385
Insert into t1 values (1,10) ;
2386
Insert into t1 values (2,20) ;
2387
Insert into t1 values (47,80) ;
2388
Insert into t2 values (1,1000) ;
2389
Insert into t2 values (2,2000) ;
2390
Insert into t2 values (31,97) ;
2391
Create view test.v1 as select t1.f59, t1.f60
2392
from t1,t2 where t1.f59=t2.f59 ;
2393
Select * from test.v1 order by f59 limit 50 ;
2395
drop table test.t1 ;
2396
drop table test.t2 ;
2400
# FIXME(mleich): Implement an automatic check for 3.3.1.50 - 3.3.1.54
2401
# CREATE VIEW ... AS <SELECT ... FROM tb2 ...>
2402
# CREATE TEMPORARY TABLE ... AS <SELECT ... FROM tb2 ...>
2403
# Comparison of the VIEW with the temporary table
2405
let $message= Testcase 3.3.1.50 - 3.3.1.54 additional implementation;
2406
--source include/show_msg80.inc
2408
DROP TABLE IF EXISTS t1 ;
2409
DROP VIEW IF EXISTS v1 ;
2414
# ------------------------
2416
# Testcase | all columns | all rows | column expressions
2417
# ---------------------------------------------------
2418
# 3.3.1.50 | yes | yes | no
2419
# 3.3.1.51 | no | yes | no
2420
# 3.3.1.52 | yes | no | no
2421
# 3.3.1.53 | no | no | no
2422
# 3.3.1.54 | no | no | yes
2423
CREATE TABLE t1 ( f1 BIGINT, f2 char(10), f3 DECIMAL(10,5) );
2424
INSERT INTO t1 VALUES(1, 'one', 1.1);
2425
INSERT INTO t1 VALUES(2, 'two', 2.2);
2426
INSERT INTO t1 VALUES(3, 'three', 3.3);
2428
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
2431
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t1;
2434
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE f3 = 2.2;
2437
CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t1 WHERE f3 = 2.2;
2441
SET sql_mode = 'traditional,ansi';
2442
# due to bug#32496 "no trailing blanks in identifier".
2443
CREATE OR REPLACE VIEW v1 AS
2444
SELECT f3 AS "pure column f3:", f1 + f3 AS "sum of columns f1 + f3 =",
2445
3 * (- 0.11111E+1) AS "product of constants 3 * (- 0.11111E+1):",
2446
'->' || CAST(f3 AS CHAR) || '<-'
2447
AS "expression with '||'=CONCAT and CAST(DECIMAL column AS CHAR):"
2448
FROM t1 WHERE f1 = 2;
2449
# This error is not conformant with ansi (see bug#32496). hhunger
2450
--error ER_WRONG_COLUMN_NAME
2451
CREATE OR REPLACE VIEW v1 AS
2452
SELECT f3 AS "pure column f3: ", f1 + f3 AS "sum of columns f1 + f3 = ",
2453
3 * (- 0.11111E+1) AS "product of constants 3 * (- 0.11111E+1): ",
2454
'->' || CAST(f3 AS CHAR) || '<-'
2455
AS "expression with '||'=CONCAT and CAST(DECIMAL column AS CHAR): "
2456
FROM t1 WHERE f1 = 2;
2459
--horizontal_results
2462
let $message= Testcases 3.3.1.55 - 3.3.1.62 ;
2463
--source include/show_msg80.inc
2464
###############################################################################
2465
# Testcase: Ensure that a view that is a subset of some or all columns and
2466
# some or all rows of multiple tables joined with an
2467
# 3.3.1.55 INNER JOIN
2468
# 3.3.1.56 CROSS JOIN
2469
# 3.3.1.57 STRAIGHT JOIN
2470
# 3.3.1.58 NATURAL JOIN
2471
# 3.3.1.59 LEFT OUTER JOIN
2472
# 3.3.1.60 NATURAL LEFT OUTER JOIN
2473
# 3.3.1.61 RIGHT OUTER
2474
# 3.3.1.62 NATURAL RIGHT OUTER
2475
# condition contains the correct row-and-column data.
2476
###############################################################################
2478
Drop table if exists t1, t2 ;
2479
Drop view if exists v1 ;
2481
Create table t1 (f59 int, f60 char(10), f61 int, a char(1)) ;
2482
Insert into t1 values (1, 'single', 3, '1') ;
2483
Insert into t1 values (2, 'double', 6, '2') ;
2484
Insert into t1 values (3, 'single-f3', 4, '3') ;
2486
Create table t2 (f59 int, f60 char(10), f61 int, b char(1)) ;
2487
Insert into t2 values (2, 'double', 6, '2') ;
2488
Insert into t2 values (3, 'single-f3', 6, '3') ;
2489
Insert into t2 values (4, 'single', 4, '4') ;
2491
# Testcase 3.3.1.55 ;
2492
create or replace view test.v1 as
2493
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2494
t1.f61 t1_f61, t2.f61 t2_f61
2495
from t1 inner join t2 where t1.f59 = t2.f59 ;
2496
select * from test.v1 order by t1_f59 ;
2497
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2498
t1.f61 t1_f61, t2.f61 t2_f61
2499
from t1 inner join t2 where t1.f59 = t2.f59;
2501
# Testcase 3.3.1.56 ;
2502
Create or replace view test.v1 as
2503
Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2504
FROM t2 cross join t1;
2505
Select * from v1 order by t1_f59,t2_f59;
2506
Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2507
FROM t2 cross join t1;
2509
# Testcase 3.3.1.57 ;
2510
Create or replace view test.v1 as
2511
Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2513
Select * from v1 order by t1_f59,t2_f59;
2514
Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2517
# Testcase 3.3.1.58 ;
2518
Create or replace view test.v1 as
2519
Select f59, f60, f61, a, b
2520
FROM t2 natural join t1;
2521
Select * from v1 order by f59;
2522
Select f59, f60, f61, a, b
2523
FROM t2 natural join t1;
2525
# Testcase 3.3.1.59 ;
2526
Create or replace view test.v1 as
2527
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2528
t1.f61 t1_f61, t2.f61 t2_f61
2529
FROM t2 left outer join t1 on t2.f59=t1.f59;
2530
Select * from v1 order by t1_f59;
2531
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2532
t1.f61 t1_f61, t2.f61 t2_f61
2533
FROM t2 left outer join t1 on t2.f59=t1.f59;
2535
# Testcase 3.3.1.60 ;
2536
Create or replace view test.v1 as
2537
Select f59, f60, f61, t1.a, t2.b
2538
FROM t2 natural left outer join t1;
2539
Select * from v1 order by f59;
2540
Select f59, f60, f61, t1.a, t2.b
2541
FROM t2 natural left outer join t1;
2543
# Testcase 3.3.1.61 ;
2544
Create or replace view test.v1 as
2545
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2546
t1.f61 t1_f61, t2.f61 t2_f61
2547
FROM t2 right outer join t1 on t2.f59=t1.f59;
2548
Select * from v1 order by t1_f59;
2549
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2550
t1.f61 t1_f61, t2.f61 t2_f61
2551
FROM t2 right outer join t1 on t2.f59=t1.f59;
2553
# Testcase 3.3.1.62 ;
2554
Create or replace view test.v1 as
2555
Select f59, f60, a, b
2556
FROM t2 natural right outer join t1;
2557
Select * from v1 order by f59 desc;
2558
Select f59, f60, a, b
2559
FROM t2 natural right outer join t1;
2567
let $message= Testcase 3.3.1.A1 - 3.3.1.A3 ;
2568
--source include/show_msg80.inc
2569
###############################################################################
2570
# Testcase: Ensure that a view that is a subset of some or all columns and/or
2571
# column expressions and some or all rows of multiple tables joined
2572
# with the combination of
2573
# 3.3.1.A1 LEFT JOIN
2574
# 3.3.1.A2 INNER JOIN
2575
# 3.3.1.A3 CROSS JOIN
2576
# condition contains the correct row-and-column data
2577
###############################################################################
2578
# Testcase 3.3.1.A1 ;
2580
Drop table if exists t1 ;
2581
Drop view if exists v1;
2583
Create table t1 (f59 int, f60 int, f61 int) ;
2584
Insert into t1 values (101,201,301) ;
2585
Insert into t1 values (107,501,601) ;
2586
Insert into t1 values (901,801,401) ;
2588
Create or replace view test.v1 as
2589
Select tb2.f59 FROM tb2 LEFT JOIN t1 on tb2.f59 = t1.f59 ;
2590
Select * from test.v1 order by f59 limit 0,10;
2591
Drop view if exists test.v1 ;
2593
# Testcase 3.3.1.A2 ;
2595
Drop table if exists t1 ;
2596
Drop view if exists v1;
2598
Create table t1 (f59 int, f60 int, f61 int) ;
2599
Insert into t1 values (201,201,201) ;
2600
Insert into t1 values (207,201,201) ;
2601
Insert into t1 values (201,201,201) ;
2603
Create or replace view test.v1
2604
as Select tb2.f59 FROM tb2 INNER JOIN t1 on tb2.f59 = t1.f59 ;
2605
Select * from test.v1 order by f59 limit 0,10;
2606
Drop view if exists test.v1 ;
2608
# Testcase 3.3.1.A3 ;
2610
Drop table if exists t1 ;
2611
Drop view if exists v1;
2613
Create table t1 (f59 int, f60 int, f61 int) ;
2614
Insert into t1 values (21,21,21) ;
2615
Insert into t1 values (27,21,21) ;
2616
Insert into t1 values (21,21,21) ;
2618
Create or replace view test.v1
2619
as Select tb2.f59 FROM tb2 CROSS JOIN t1 on tb2.f59 = t1.f59 ;
2620
Select * from test.v1 order by f59 limit 0,10;
2625
let $message= Testcase 3.3.1.63 ;
2626
--source include/show_msg80.inc
2627
###############################################################################
2628
# Testcase 3.3.1.63: Ensure that a view that is a subset of some or all columns
2629
# and/or column expressions and some or all rows of multiple
2630
# tables joined with every possible combination of JOIN
2631
# conditions, UNION, UNION ALL and UNION DISTINCT, nested at
2632
# multiple levels, contains the correct row-and-column data.
2633
###############################################################################
2635
Drop table if exists t1 ;
2636
Drop view if exists test.v1 ;
2639
Create table t1 (f59 int, f60 int, f61 int) ;
2640
Insert into t1 values (11,21,31) ;
2641
Insert into t1 values (17,51,61) ;
2642
Insert into t1 values (91,81,41) ;
2644
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2645
Union ALL (Select f59 from t1 where f59=17 );
2646
Select * from test.v1 order by f59 limit 0,10;
2648
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2649
Union (Select f59 from t1 where f59=17 );
2650
Select * from test.v1 order by f59 limit 0,10;
2652
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2653
Union Distinct (Select f59 from t1 where f60=17 );
2654
Select * from test.v1 order by f59 limit 0,10;
2659
drop table if exists t1;
2660
drop view if exists test.v1;
2662
create table t1 (f59 int, f60 int, f61 int);
2664
insert into t1 values (101,201,301);
2665
insert into t1 values (107,501,601);
2666
insert into t1 values (901,801,401);
2668
create or replace view test.v1 as
2669
select tb2.f59 from tb2 join t1 on tb2.f59 = t1.f59;
2670
select * from test.v1 order by f59 limit 0,10;
2672
create or replace view test.v1 as
2673
(select f59 from tb2 where f59=107 )
2675
(select f59 from t1 where f59=107 );
2676
select * from test.v1 order by f59 limit 0,10;
2678
create or replace view test.v1 as
2679
(select f59 from tb2 where f59=107 )
2681
(select f59 from t1 where f59=107 );
2682
select * from test.v1 order by f59 limit 0,10;
2684
create or replace view test.v1 as
2685
(select f59 from tb2 where f59=107 )
2687
(select f59 from t1 where f59=107 );
2688
select * from test.v1 order by f59 limit 0,10;
2690
drop view if exists test.v1 ;
2694
let $message= Testcase 3.3.1.64 ;
2695
--source include/show_msg80.inc
2696
###############################################################################
2697
# Testcase 3.3.1.64: Ensure that all changes to a view definition, executed by
2698
# the ALTER VIEW statement, are correctly recorded and have
2699
# the correct effect on the data shown by the view.
2700
###############################################################################
2702
Drop view if exists test.v1 ;
2704
CREATE VIEW test.v1 AS SELECT F59
2705
FROM test.tb2 where test.tb2.F59 = 109;
2707
SELECT * FROM test.v1 order by f59 limit 0,10;
2709
ALTER VIEW test.v1 AS SELECT *
2710
FROM test.tb2 WHERE test.tb2.f59 = 242 ;
2711
if ($have_bug_32285)
2713
--disable_ps_protocol
2716
SELECT * FROM test.v1 order by f59 limit 0,10;
2717
--horizontal_results
2718
--enable_ps_protocol
2723
let $message= Testcase 3.3.1.65, 3.3.1.A4, 3.3.1.66, 3.3.1.67 ;
2724
--source include/show_msg80.inc
2725
###############################################################################
2726
# Testcase 3.3.1.65: Ensure that the DROP VIEW statement cleanly drops its
2728
# Testcase 3.3.1.A4: Ensure that the DROP VIEW IF EXISTS statement cleanly
2729
# drops its target view.
2730
# Testcase 3.3.1.66: Ensure that DROP VIEW <view name> fails, with an appro-
2731
# priate error message, if the view named does not exist.
2732
# Testcase 3.3.1.67: Ensure that DROP VIEW IF EXISTS <view name> does not fail,
2733
# but merely returns an appropriate warning, if the view
2734
# named does not exist.
2735
###############################################################################
2737
DROP TABLE IF EXISTS t1;
2738
DROP VIEW IF EXISTS test.v1 ;
2740
eval CREATE TABLE t1 ( f1 VARCHAR(1000) ) ENGINE = $engine_type ;
2741
CREATE VIEW v1 AS SELECT f1 FROM t1;
2745
--error ER_BAD_TABLE_ERROR
2748
CREATE VIEW v1 AS SELECT f1 FROM t1;
2749
# DROP VIEW IF EXISTS
2750
DROP VIEW IF EXISTS v1;
2751
DROP VIEW IF EXISTS v1;
2754
let $message= Testcase 3.3.1.68 ;
2755
--source include/show_msg80.inc
2756
###############################################################################
2757
# Testcase 3.3.1.68: Ensure that DROP VIEW <view name>, DROP VIEW <view name>
2758
# RESTRICT, and DROP VIEW <view name> CASCADE all take
2759
# exactly the same action, until such time as the RESTRICT
2760
# and CASCADE keyword actions are implemented by MySQL.
2761
###############################################################################
2763
DROP TABLE IF EXISTS t1;
2764
DROP VIEW IF EXISTS v1_base ;
2765
DROP VIEW IF EXISTS v1_top ;
2767
CREATE TABLE t1 ( f1 DOUBLE);
2771
SET @part2= 'RESTRICT';
2772
SET @part3= 'CASCADE';
2779
CREATE VIEW v1_base AS SELECT * FROM t1;
2780
CREATE VIEW v1_top AS SELECT * FROM v1_base;
2782
let $aux1= `SELECT CONCAT('DROP VIEW v1_top ', @v1_part)` ;
2783
let $aux2= `SELECT CONCAT('DROP VIEW v1_base ', @v1_part)` ;
2784
eval SET @v1_part= @part$num1;
2787
# 1. more non important sub testcase, where the view (v1_top) is not the base of
2789
# DROP VIEW v1_top < |RESTRICD|CASCADE> must be successful.
2791
# Check, that v1_top really no more exists + cleanup for the second sub test
2792
--error ER_BAD_TABLE_ERROR
2795
CREATE VIEW v1_top AS SELECT * FROM v1_base;
2796
# 2. more important sub testcase, where the view (v1_base) is the base of
2797
# another object (v1_top)
2798
# DROP VIEW v1_base < |RESTRICT|CASCADE>
2799
# If the RESTRICT and CASCADE keyword actions are implemented by MySQL,
2800
# CASCADE will remove v1_base and the dependend view v1_top and
2801
# RESTRICT will fail, because there exists the dependend view v1_top
2803
# Check, if v1_base and v1_top exist + cleanup for next loop
2811
let $message= Testcase 3.3.1.69, 3.3.1.70, 3.3.1.A5 ;
2812
--source include/show_msg80.inc
2813
###############################################################################
2814
# Testcases : Ensure that, when a view is dropped, its definition no longer
2816
# 3.3.1.69 SHOW CREATE VIEW, SHOW CREATE TABLE, SHOW TABLE STATUS,
2818
# 3.3.1.70 CHECK TABLE statement is executed
2819
# 3.3.1.A5 SHOW COLUMNS, SHOW FIELDS, DESCRIBE, EXPLAIN
2820
# statement is executed
2821
###############################################################################
2822
# Note(mleich): There will be no non failing sub testcases with SHOW here.
2823
# They will be done in 3.3.11 ff.
2825
DROP TABLE IF EXISTS t1 ;
2826
DROP VIEW IF EXISTS v1 ;
2828
eval CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ENGINE = $engine_type;
2829
CREATE VIEW v1 AS SELECT * FROM t1;
2832
# The negative tests:
2834
--error ER_NO_SUCH_TABLE
2837
--error ER_NO_SUCH_TABLE
2838
SHOW CREATE VIEW v1 ;
2839
--error ER_NO_SUCH_TABLE
2840
SHOW CREATE TABLE v1 ;
2841
# Attention: Like is a filter. So we will get an empty result set here.
2842
SHOW TABLE STATUS like 'v1' ;
2843
SHOW TABLES LIKE 'v1';
2844
--error ER_NO_SUCH_TABLE
2845
SHOW COLUMNS FROM v1;
2846
--error ER_NO_SUCH_TABLE
2847
SHOW FIELDS FROM v1;
2849
--error ER_NO_SUCH_TABLE
2851
--error ER_NO_SUCH_TABLE
2852
EXPLAIN SELECT * FROM v1;
2856
let $message= Testcase 3.3.1.A6 ;
2857
--source include/show_msg80.inc
2858
###############################################################################
2859
# Testcases 3.3.1.A6 : Ensure that nested views up to level @max_level work.
2860
###############################################################################
2861
# 1. Simple nested VIEWs
2862
# Configurable parameter @max_level = nesting level
2863
# 128 must be good enough, it is already a pathologic value.
2864
# We currently set it to 32, because of performance issues.
2869
DROP DATABASE IF EXISTS test3;
2871
CREATE DATABASE test3;
2872
eval CREATE TABLE test3.t1 (f1 DECIMAL(5,3)) ENGINE = $engine_type;
2873
INSERT INTO test3.t1 SET f1 = 1.0;
2874
CREATE VIEW test3.v0 AS SELECT * FROM test3.t1;
2881
eval SET @aux = $level - 1;
2883
let $sublevel= `SELECT @aux`;
2885
eval CREATE VIEW test3.v$level AS SELECT * FROM test3.v$sublevel;
2887
# DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
2888
# (direct after the while loop) show suspicious results.
2892
eval SHOW CREATE VIEW test3.v$level;
2893
eval SELECT * FROM test3.v$level;
2894
eval EXPLAIN SELECT * FROM test3.v$level;
2898
eval SET @aux = @max_level > $level;
2902
# eval SELECT @aux AS "@aux", $level AS "next level";
2904
let $run= `SELECT @aux`;
2906
#--------------------------------------------------------------------------
2907
# Attention: If the following statements get suspicious/unexpected results
2908
# and you assume that something with the non toplevel VIEWs might
2909
# be wrong, please edit the while loop above and set $debug to 1.
2910
#--------------------------------------------------------------------------
2911
# 1.1 Check of top level VIEW
2912
let $toplevel= `SELECT @max_level`;
2913
eval SHOW CREATE VIEW test3.v$toplevel;
2914
eval SELECT * FROM test3.v$toplevel;
2915
eval EXPLAIN SELECT * FROM test3.v$toplevel;
2917
# 1.2 Check the top level view when a base VIEW is dropped
2919
eval SHOW CREATE VIEW test3.v$toplevel;
2920
--error ER_VIEW_INVALID
2921
eval SELECT * FROM test3.v$toplevel;
2922
--error ER_VIEW_INVALID
2923
eval EXPLAIN SELECT * FROM test3.v$toplevel;
2926
# 2. Complicated nested VIEWs
2927
# parameter @max_level = nesting level
2928
# There is a limit(@join_limit = 61) for the number of tables which
2929
# could be joined. This limit will be reached, when we set
2930
# @max_level = @join_limit - 1 .
2932
#++++++++++++++++++++++++++++++++++++++++++++++
2933
# OBN - Reduced the value of join limit to 30
2934
# Above seems to hang - FIXME
2935
# mleich - Reason unclear why it hangs for OBN on innodb and memory.
2936
# Hypothesis: Maybe the consumption of virtual memory is high
2937
# and OBN's box performs excessive paging.
2938
# (RAM: OBN ~384MB RAM, mleich 1 GB)
2939
#++++++++++++++++++++++++++++++++++++++++++++++
2940
let $message= FIXME - Setting join_limit to 28 - hangs for higher values;
2941
--source include/show_msg.inc
2942
# OBN - Reduced from 30 in 5.1.21 to avoid hitting the ndbcluster limit
2943
# of "ERROR HY000: Got temporary error 4006 'Connect failure
2944
# - out of connection objects (increase MaxNoOfConcurrentTransactions)'
2945
# from NDBCLUSTER " to early;
2946
#SET @join_limit = 61;
2947
SET @join_limit = 28; # OBN - see above
2948
SET @max_level = @join_limit - 1;
2952
DROP DATABASE IF EXISTS test3;
2953
DROP TABLE IF EXISTS test1.t1;
2954
DROP TABLE IF EXISTS test2.t1;
2955
let $level= `SELECT @max_level + 1`;
2960
eval DROP VIEW IF EXISTS test1.v$level;
2963
CREATE DATABASE test3;
2965
# Testplan for the content of the tables:
2966
# ---------------------------------------------------------
2967
# Records test1.t1 test2.t1 test3.t1
2968
# NULL, 'numeric column is NULL' yes yes yes
2969
# 0 , NULL yes yes yes
2970
# 5 , 'five' yes yes yes
2971
# 1 , 'one' yes yes no
2972
# 2 , 'two' yes no yes
2973
# 3 , 'three' no yes yes
2976
eval CREATE TABLE t1 (f1 BIGINT, f2 CHAR(50)) ENGINE = $engine_type ;
2977
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2978
INSERT INTO t1 VALUES (0, NULL);
2979
INSERT INTO t1 VALUES (5, 'five');
2981
INSERT INTO t1 VALUES (1, 'one');
2982
INSERT INTO t1 VALUES (2, 'two');
2985
eval CREATE TABLE t1 (f1 DECIMAL(64,30), f2 VARCHAR(50)) ENGINE = $engine_type;
2986
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2987
INSERT INTO t1 VALUES (0.000000000000000000000000000000, NULL);
2988
INSERT INTO t1 VALUES (5.000000000000000000000000000000, 'five');
2990
INSERT INTO t1 VALUES (+1.000000000000000000000000000000, 'one');
2991
INSERT INTO t1 VALUES (3.000000000000000, 'three');
2994
eval CREATE TABLE t1 (f1 DOUBLE, f2 VARBINARY(50)) ENGINE = $engine_type;
2995
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2996
INSERT INTO t1 VALUES (+0.0E-35, NULL);
2997
INSERT INTO t1 VALUES (+0.5E+1, 'five');
2999
INSERT INTO t1 VALUES (20.0E-1, 'two');
3000
INSERT INTO t1 VALUES (0.0300E2, 'three');
3004
CREATE OR REPLACE VIEW test1.v0 AS SELECT * FROM test2.t1;
3007
SET @max_level = IFNULL(@limit1,@max_level);
3014
eval SET @aux = $level - 1;
3015
let $sublevel= `SELECT @aux`;
3016
eval SET @AUX = $level MOD 3 + 1;
3017
let $dbnum= `SELECT @AUX`;
3020
eval CREATE OR REPLACE VIEW test1.v$level AS SELECT f1, f2
3021
FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3023
# DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
3024
# (direct after the while loop) show suspicious results.
3028
eval SHOW CREATE VIEW test1.v$level;
3029
eval SELECT * FROM test1.v$level;
3031
FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3032
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3033
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3034
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3035
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3039
eval SET @aux = @max_level > $level;
3043
# eval SELECT @aux AS "@aux", $level AS "next level";
3045
let $run= `SELECT @aux`;
3048
#--------------------------------------------------------------------------
3049
# Atention: If the following statements get suspicious/unexpected results
3050
# and you assume that something with the non toplevel VIEWs might
3051
# be wrong, please edit the while loop above and set $debug to 1.
3052
#--------------------------------------------------------------------------
3053
# 2.1 Check of top level VIEW
3054
let $toplevel= `SELECT @max_level`;
3055
# Show should be easy
3056
eval SHOW CREATE VIEW test1.v$toplevel;
3057
# SELECT is much more complicated
3058
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3059
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3060
let $message= The output of following EXPLAIN is deactivated, because the result
3061
differs on some platforms
3062
FIXME Is this a bug ? ;
3063
--source include/show_msg80.inc
3066
--disable_result_log
3068
# EXPLAIN might be the hell
3069
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3070
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3076
# 2.2 Check of top level VIEW when join limit is exceeded
3077
# Exceed the limit for the number of tables which could be joined.
3078
let $level= `SELECT @max_level + 1`;
3079
let $sublevel= `SELECT @max_level`;
3080
eval CREATE VIEW test1.v$level AS SELECT f1, f2
3081
FROM test3.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3082
eval SHOW CREATE VIEW test1.v$level;
3083
# the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3084
# is successful so assuming no expected error was intended
3085
# --error ER_TOO_MANY_TABLES
3086
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3087
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3088
let $message= The output of following EXPLAIN is deactivated, because the result
3089
differs on some platforms
3090
FIXME Is this a bug ? ;
3091
--source include/show_msg80.inc
3094
--disable_result_log
3096
# the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3097
# is successful so assuming no expected error was intended
3098
# --error ER_TOO_MANY_TABLES
3099
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3100
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3105
eval DROP VIEW IF EXISTS test1.v$level;
3107
# 2.3 Create a logical wrong (data type "garbage") base for the upper views
3108
# and check the behaviour of the top level view.
3109
# 2.3.1 Exchange numeric and string column
3110
--disable_result_log
3111
CREATE OR REPLACE VIEW test1.v0 AS
3112
SELECT f1 as f2, f2 as f1 FROM test2.t1;
3113
# 2.3.2 DATE instead of numeric
3114
CREATE OR REPLACE VIEW test2.v0 AS
3115
SELECT CAST('0001-01-01' AS DATE) as f1, f2 FROM test3.t1;
3116
eval SHOW CREATE VIEW test1.v$toplevel;
3117
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3118
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3119
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3120
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3121
# 2.3.3 UCS2 string instead of common string
3122
CREATE OR REPLACE VIEW test3.v0 AS
3123
SELECT f1 , CONVERT('ßÄäÖöÜü§' USING UCS2) as f2 FROM test1.t1;
3124
eval SHOW CREATE VIEW test1.v$toplevel;
3125
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3126
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3127
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3128
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3130
# 2.3.4 UCS2 string instead of numeric
3131
CREATE OR REPLACE VIEW test3.v0 AS
3132
SELECT CONVERT('ßÄäÖöÜü§' USING UCS2) as f1, f2 FROM test1.t1;
3133
eval SHOW CREATE VIEW test1.v$toplevel;
3134
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3135
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3136
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3137
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3141
let $level= `SELECT @max_level + 1`;
3146
eval DROP VIEW IF EXISTS test1.v$level;
3149
DROP DATABASE test3;
3150
DROP TABLE test1.t1;
3151
DROP TABLE test2.t1;
3153
#==============================================================================
3154
# 3.3.2 Updatable and Insertable-into views:
3155
#==============================================================================
3158
let $message= Testcase 3.3.2.1;
3159
--source include/show_msg80.inc
3160
###############################################################################
3161
# Testcase 3.3.2.1: Ensure that every view which may theoretically accept new
3162
# rows via the INSERT statement does, in fact, do so.
3163
###############################################################################
3165
Drop view if exists test.v1 ;
3168
Create View test.v1 AS SELECT f59,f60 FROM tb2 where f59 = 1995 ;
3170
INSERT INTO test.v1 (f59,f60) values (879,700) ;
3172
SELECT f59,f60 FROM test.v1 where f59 = 879 and f60 = 700 ;
3173
DELETE FROM tb2 where f59 = 879 and f60 = 700 ;
3178
let $message= Testcase 3.3.2.2;
3179
--source include/show_msg80.inc
3180
###############################################################################
3181
# Testcase 3.3.2.2: Ensure that, for every row inserted into a view,
3182
# the correct new data also appears in every relevant
3184
###############################################################################
3186
Drop view if exists test.v1 ;
3189
Create view test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3191
INSERT INTO test.v1 ( f59 , f60 ) values (2005,0101) ;
3194
if ($have_bug_32285)
3196
--disable_ps_protocol
3199
SELECT * FROM tb2 where f59 = 2005 and f60 = 0101 ;
3200
--horizontal_results
3201
--enable_ps_protocol
3202
SELECT f59,f60 FROM test.v1 where f59 = 2005 and f60 = 0101 ;
3203
DELETE FROM tb2 where f59 = 2005 and f60 = 0101 ;
3208
let $message= Testcase 3.3.2.3;
3209
--source include/show_msg80.inc
3210
###############################################################################
3211
# Testcase 3.3.2.3: Ensure that every view which may theoretically accept data
3212
# changes via the UPDATE statement does, in fact, do so.
3213
###############################################################################
3214
Insert into tb2 (f59,f60,f61) values (780,105,106) ;
3217
Drop view if exists test.v1 ;
3220
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3222
UPDATE test.v1 SET f59 = 8 WHERE f59 = 780 and f60 = 105;
3225
if ($have_bug_32285)
3227
--disable_ps_protocol
3230
SELECT * FROM tb2 where f59 = 8 and f60 = 105;
3231
--horizontal_results
3232
--enable_ps_protocol
3233
SELECT f59,f60 FROM test.v1 where f59 = 8 and f60 = 105 ;
3238
let $message= Testcase 3.3.2.4;
3239
--source include/show_msg80.inc
3240
###############################################################################
3241
# Testcase 3.3.2.4: Ensure that, for data values updated within a view, the
3242
# correct new data also appears in every relevant
3244
###############################################################################
3245
Insert into tb2 (f59,f60,f61) values (781,105,106) ;
3248
Drop view if exists test.v1 ;
3250
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3252
UPDATE test.v1 SET f59 = 891 WHERE f60 = 105 ;
3255
if ($have_bug_32285)
3257
--disable_ps_protocol
3260
SELECT * FROM tb2 where f59 = 891 and f60 = 105;
3261
--horizontal_results
3262
--enable_ps_protocol
3263
SELECT f59,f60 FROM test.v1 where f59 = 891 and f60 = 105 ;
3268
let $message= Testcase 3.3.2.5;
3269
--source include/show_msg80.inc
3270
###############################################################################
3271
# Testcase 3.3.2.5: Ensure that every view which may theoretically accept data
3272
# deletions via the DELETE statement does, in fact, do so.
3273
###############################################################################
3274
Insert into tb2 (f59,f60,f61) values (789,105,106) ;
3277
Drop view if exists test.v1 ;
3279
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 789 ;
3281
DELETE FROM test.v1 where f59 = 789 ;
3283
SELECT * FROM tb2 where f59 = 789 ;
3284
SELECT f59,f60 FROM test.v1 where f59 = 789 order by f60 ;
3289
let $message= Testcase 3.3.2.6;
3290
--source include/show_msg80.inc
3291
###############################################################################
3292
# Testcase 3.3.2.6: Ensure that, for data rows deleted from a view, the correct
3293
# rows have also been deleted from every relevant
3295
###############################################################################
3296
Insert into tb2 (f59,f60,f61) values (711,105,106) ;
3299
Drop view if exists test.v1 ;
3301
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 711 ;
3303
DELETE FROM test.v1 where f59 = 711 ;
3306
SELECT * FROM tb2 where f59 = 711 ;
3307
SELECT f59,f60 FROM test.v1 where f59 = 711 order by f60 ;
3311
let $message= Testcase 3.3.2.1 - 3.3.2.6 alternative implementation;
3312
--source include/show_msg80.inc
3315
DROP TABLE IF EXISTS t1;
3316
DROP VIEW IF EXISTS v1;
3319
CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
3320
f4 CHAR, PRIMARY KEY(f1));
3322
# VIEW including the base table PRIMARY KEY, but not all base table columns (f4)
3323
# no additional columns
3324
CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
3326
# Incomplete INSERT 1
3328
# - PRIMARY KEY f1 included
3329
# f2 gets the default NULL
3330
INSERT INTO v1 SET f1 = 1;
3334
# Incomplete INSERT 2
3336
# - PRIMARY KEY f1 missing
3337
# f1 gets the default 0, because we are in the native sql_mode
3338
INSERT INTO v1 SET f2 = 'ABC';
3339
# f1 gets the default 0, but this value is already exists
3340
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3341
--error ER_DUP_ENTRY
3342
INSERT INTO v1 SET f2 = 'ABC';
3346
# Testplan for DELETE:
3348
# Column within WHERE qualification
3353
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3355
DELETE FROM v1 WHERE f1 = 1;
3357
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3359
DELETE FROM v1 WHERE f2 = 'ABC';
3361
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3366
# Testplan for UPDATE:
3367
# Column to modify Column within WHERE qualification
3368
# f1 (PK) f1(PK + same column to modify)
3371
# f2 (non PK) f1(PK)
3372
# f2 (non PK) f2(non PK + same column to modify)
3373
# f2 (non PK) f3(non PK)
3377
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3379
UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3382
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3384
UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3387
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3389
UPDATE v1 SET f1 = 2;
3392
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3394
UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3397
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3399
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3402
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3404
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3407
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3409
UPDATE v1 SET f2 = 'NNN';
3412
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3413
# UPDATE f1,f2 - f1,f2
3414
UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3420
# VIEW without the PRIMARY KEY f1 of the base table
3421
# no additional columns
3422
CREATE VIEW v1 AS SELECT f2, f3 FROM t1;
3425
# - PRIMARY KEY f1 missing in VIEW definition
3426
# f1 gets the default 0, because we are in the native sql_mode
3427
INSERT INTO v1 SET f2 = 'ABC';
3428
# f1 gets the default 0 and this value is already exists
3429
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3430
--error ER_DUP_ENTRY
3431
INSERT INTO v1 SET f2 = 'ABC';
3435
# Testplan for DELETE:
3437
# Column within WHERE qualification
3441
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3443
DELETE FROM v1 WHERE f2 = 'ABC';
3445
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3450
# Testplan for UPDATE:
3452
# Column to modify Column within WHERE qualification
3453
# f2 (non PK) f2(non PK + same column to modify)
3454
# f2 (non PK) f3(non PK)
3456
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3458
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3461
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3463
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3466
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3468
UPDATE v1 SET f2 = 'NNN';
3474
# VIEW with the PRIMARY KEY f1 of the base table
3475
# but additional constant column
3476
CREATE VIEW v1 AS SELECT f1, f2, f3, 'HELLO' AS my_greeting FROM t1;
3478
# Maybe the SQL standard allows the following INSERT.
3479
# But it would be a very sophisticated DBMS.
3480
--error ER_NON_INSERTABLE_TABLE
3481
INSERT INTO v1 SET f1 = 1;
3484
# The next INSERTs should never work, because my_greeting is a constant.
3485
--error ER_NON_INSERTABLE_TABLE
3486
INSERT INTO v1 SET f1 = 1, my_greeting = 'HELLO';
3490
# Testplan for DELETE:
3492
# Column within WHERE qualification
3495
# my_greeting(non base table column)
3498
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3500
DELETE FROM v1 WHERE f1 = 1;
3502
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3504
DELETE FROM v1 WHERE f2 = 'ABC';
3506
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3507
# DELETE my_greeting
3508
DELETE FROM v1 WHERE my_greeting = 'HELLO';
3510
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3516
# Testplan for UPDATE:
3518
# Column to modify Column within WHERE qualification
3519
# f1 (PK) f1(PK + same column to modify)
3521
# f1 (PK) my_greeting(non base table column)
3523
# f2 (non PK) f1(PK)
3524
# f2 (non PK) f2(non PK + same column to modify)
3525
# f2 (non PK) f3(non PK)
3526
# f2 (non PK) my_greeting(non base table column)
3528
# my_greeting(non base table column) f1(PK)
3529
# my_greeting(non base table column) f2(non PK)
3530
# my_greeting(non base table column) my_greeting(same non base table column)
3531
# my_greeting(non base table column) none
3534
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3536
UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3539
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3541
UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3544
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3545
# UPDATE f1 - my_greeting
3546
UPDATE v1 SET f1 = 2 WHERE my_greeting = 'HELLO';
3549
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3551
UPDATE v1 SET f1 = 2;
3554
#------------------------------------------------
3555
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3557
UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3560
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3562
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3565
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3567
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3570
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3571
# UPDATE f2 - my_greeting
3572
UPDATE v1 SET f2 = 'NNN' WHERE my_greeting = 'HELLO';
3575
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3577
UPDATE v1 SET f2 = 'NNN';
3580
#------------------------------------------------
3581
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3582
# UPDATE my_greeting - f1
3583
--error ER_NONUPDATEABLE_COLUMN
3584
UPDATE v1 SET my_greeting = 'Hej' WHERE f1 = 1;
3587
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3588
# UPDATE my_greeting - f2
3589
--error ER_NONUPDATEABLE_COLUMN
3590
UPDATE v1 SET my_greeting = 'Hej' WHERE f2 = 'ABC';
3593
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3594
# UPDATE my_greeting - my_greeting
3595
--error ER_NONUPDATEABLE_COLUMN
3596
UPDATE v1 SET my_greeting = 'Hej' WHERE my_greeting = 'HELLO';
3599
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3600
# UPDATE my_greeting - none
3601
--error ER_NONUPDATEABLE_COLUMN
3602
UPDATE v1 SET my_greeting = 'Hej';
3605
#------------------------------------------------
3606
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3607
# UPDATE f1, f2 - f1, f2
3608
UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3614
SET sql_mode = 'traditional';
3615
CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4) NOT NULL,
3616
f4 CHAR, PRIMARY KEY(f1));
3617
# VIEW including the base table PRIMARY KEY, but not the NOT NULL
3618
# base table column (f3)
3619
# no additional columns
3621
CREATE VIEW v1 AS SELECT f1, f2, f4 FROM t1;
3623
# This INSERT must fail
3624
--error ER_NO_DEFAULT_FOR_VIEW_FIELD
3625
INSERT INTO v1 SET f1 = 1;
3629
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3631
DELETE FROM v1 WHERE f1 = 1;
3633
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3635
UPDATE v1 SET f4 = 'Y' WHERE f2 = 'ABC';
3639
# Switch back to the native SQL mode
3644
let $message= Testcases 3.3.2.7 - 3.3.2.9,
3645
3.3.2.10 - 3.3.2.11 omitted because of missing
3646
features EXCEPT and INTERSECT ;
3647
--source include/show_msg80.inc
3648
###############################################################################
3649
# Testcase 3.3.2.7: Ensure that a view with a definition that includes
3651
# rejects all INSERT, UPDATE, or DELETE attempts
3652
# with an appropriate error message.
3653
# Testcase 3.3.2.8: Ensure that a view with a definition that includes
3655
# rejects all INSERT, UPDATE, or DELETE attempts
3656
# with an appropriate error message.
3657
# Testcase 3.3.2.9: Ensure that a view with a definition that includes
3659
# rejects all INSERT, UPDATE, or DELETE attempts
3660
# with an appropriate error message.
3661
# Testcase 3.3.2.10: Ensure that a view with a definition that includes
3663
# rejects all INSERT, UPDATE, or DELETE attempts
3664
# with an appropriate error message.
3665
# (Note: MySQL does not support EXCEPT at this time;
3666
# this test is for the future.)
3667
# Testcase 3.3.2.11: Ensure that a view with a definition that includes
3669
# rejects all INSERT, UPDATE, or DELETE attempts
3670
# with an appropriate error message.
3671
# (Note: MySQL does not support INTERSECT at this time;
3672
# this test is for the future.)
3674
# Summary of 3.3.2.7 - 3.3.2.11
3675
# Ensure that a view with a definition that includes
3676
# UNION or UNION DISTINCT or UNION ALL or EXCEPT or INTERSECT
3677
# rejects any INSERT or UPDATE or DELETE statement with an
3678
# appropriate error message
3680
# mleich: I assume the type of the storage engine does not play any role.
3681
###############################################################################
3682
INSERT INTO tb2 (f59,f60,f61) VALUES (77,185,126) ;
3683
INSERT INTO tb2 (f59,f60,f61) VALUES (59,58,54) ;
3686
DROP TABLE IF EXISTS t1 ;
3687
DROP VIEW IF EXISTS v1 ;
3689
CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ;
3690
INSERT INTO t1 VALUES (19,41,32) ;
3691
INSERT INTO t1 VALUES (59,54,71) ;
3692
INSERT INTO t1 VALUES (21,91,99) ;
3694
SET @variant1 = 'UNION ';
3695
SET @variant2 = 'UNION ALL ';
3696
SET @variant3 = 'UNION DISTINCT ';
3697
SET @variant4 = 'EXCEPT ';
3698
SET @variant5 = 'INTERSECT ';
3700
# Attention: Set $num to 5, when EXCEPT and INTERSECT is supported
3705
eval SET @variant= @variant$num;
3706
let $aux= `SELECT CONCAT('CREATE VIEW v1 AS ',
3707
'SELECT f61 FROM tb2 WHERE f59=59 ',
3709
'SELECT f61 FROM t1 WHERE f59=19')`;
3711
# $aux contains the CREATE VIEW statement
3713
--error ER_NON_INSERTABLE_TABLE
3714
INSERT INTO v1 VALUES (3000);
3715
--error ER_NON_UPDATABLE_TABLE
3716
UPDATE v1 SET f61 = 100 WHERE f61 = 32;
3717
--error ER_NON_UPDATABLE_TABLE
3725
let $message= Testcases 3.3.2.12 - 3.3.2.20;
3726
--source include/show_msg80.inc
3727
###############################################################################
3728
# Testcase 3.3.2.12: Ensure that a view with a definition that includes
3730
# rejects all INSERT, UPDATE, or DELETE attempts
3731
# with an appropriate error message.
3732
# Testcase 3.3.2.13: Ensure that a view with a definition that includes
3734
# rejects all INSERT, UPDATE, or DELETE attempts
3735
# with an appropriate error message.
3736
# Testcase 3.3.2.14: Ensure that a view with a definition that includes
3738
# rejects all INSERT, UPDATE, or DELETE attempts
3739
# with an appropriate error message.
3740
# Testcase 3.3.2.15: Ensure that a view with a definition that includes
3742
# rejects all INSERT, UPDATE, or DELETE attempts
3743
# with an appropriate error message.
3744
# Testcase 3.3.2.16: Ensure that a view with a definition that includes
3746
# rejects all INSERT, UPDATE, or DELETE attempts
3747
# with an appropriate error message.
3748
# Testcase 3.3.2.17: Ensure that a view with a definition that includes
3749
# a subquery in the select list
3750
# rejects all INSERT, UPDATE, or DELETE attempts
3751
# with an appropriate error message.
3752
# Testcase 3.3.2.18: Ensure that a view with a definition that includes
3753
# a reference to a non-updatable view
3754
# rejects all INSERT, UPDATE, or DELETE attempts
3755
# with an appropriate error message.
3756
# Testcase 3.3.2.19: Ensure that a view with a definition that includes
3757
# a WHERE clause subquery that refers to a table also
3758
# referenced in a FROM clause
3759
# rejects all INSERT, UPDATE, or DELETE attempts
3760
# with an appropriate error message.
3761
# Testcase 3.3.2.20: Ensure that a view with a definition that includes
3762
# ALGORITHM = TEMPTABLE
3763
# rejects all INSERT, UPDATE, or DELETE attempts
3764
# with an appropriate error message.
3766
# Summary of 3.3.2.12 - 3.3.2.20:
3767
# Ensure that a view with a definition that includes
3769
# DISTINCTROW 3.3.2.13
3773
# a sub query in the select list 3.3.2.17
3774
# a reference to a non-updateable view 3.3.2.18
3775
# a WHERE clause sub query that refers to a table also referenced in a
3776
# FROM clause 3.3.2.19
3777
# ALGORITHM = TEMPTABLE 3.3.2.20
3779
# any INSERT or UPDATE or DELETE statement
3780
# with an appropriate error message.
3782
###############################################################################
3784
DROP TABLE IF EXISTS t1, t2 ;
3785
DROP VIEW IF EXISTS test.v1 ;
3786
Drop view if exists v2 ;
3789
CREATE TABLE t1 (f59 int, f60 int, f61 int) ;
3790
INSERT INTO t1 VALUES (19,41,32) ;
3791
INSERT INTO t1 VALUES (59,54,71) ;
3792
INSERT INTO t1 VALUES (21,91,99) ;
3793
CREATE TABLE t2 (f59 int, f60 int, f61 int) ;
3794
INSERT INTO t2 VALUES (19,41,32) ;
3795
INSERT INTO t2 VALUES (59,54,71) ;
3796
INSERT INTO t2 VALUES (21,91,99) ;
3797
CREATE VIEW v2 AS SELECT f59, f60, f61 FROM t2 LIMIT 5;
3799
# For DISTINCT 3.3.2.12
3800
SET @variant1= 'CREATE VIEW v1 AS SELECT DISTINCT(f61) FROM t1';
3801
# For DISTINCTROW 3.3.2.13
3802
SET @variant2= 'CREATE VIEW v1 AS SELECT DISTINCTROW(f61) FROM t1';
3804
SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
3805
# For GROUP BY 3.3.2.15
3806
SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
3807
# For HAVING 3.3.2.16
3808
SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
3809
# For a sub query in the select list 3.3.2.17
3810
SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
3811
# For a WHERE clause sub query that refers to a table also referenced in a
3812
# FROM clause 3.3.2.18
3813
SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
3814
SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
3815
# For ALGORITHM = TEMPTABLE 3.3.2.20
3816
SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
3821
--disable_abort_on_error
3823
eval SET @variant= @variant$num;
3824
let $aux= `SELECT @variant`;
3827
# CREATE VIEW v1 ...
3830
--error ER_NON_INSERTABLE_TABLE
3831
INSERT INTO v1 VALUES (1002);
3832
# --error ER_NON_UPDATABLE_TABLE, ER_UPDATE_TABLE_USED
3833
--error ER_NON_UPDATABLE_TABLE
3834
UPDATE v1 SET f61=1007;
3835
--error ER_NON_UPDATABLE_TABLE
3845
let $message= Testcases 3.3.A1;
3846
--source include/show_msg80.inc
3847
###############################################################################
3848
# Testcase 3.3.A1: Check the effects of base table modifications on an already
3851
# Attention: Many modifications are logical non sense.
3852
# The consequence is in many cases a "garbage in garbage out" effect.
3854
# There is no specification of the intended behaviour within
3855
# the MySQL manual. That means I assume the observed effects are
3856
# no bug as long we do not get a crash or obviously non
3857
# reasonable results.
3858
###############################################################################
3860
DROP TABLE IF EXISTS t1;
3861
DROP TABLE IF EXISTS t2;
3862
DROP VIEW IF EXISTS v1;
3863
DROP VIEW IF EXISTS v2;
3866
eval CREATE TABLE t1 (f1 BIGINT, f2 DATE DEFAULT NULL, f4 CHAR(5),
3867
report char(10)) ENGINE = $engine_type;
3868
CREATE VIEW v1 AS SELECT * FROM t1;
3869
INSERT INTO t1 SET f1 = -1, f4 = 'ABC', report = 't1 0';
3870
INSERT INTO v1 SET f1 = -1, f4 = 'ABC', report = 'v1 0';
3875
SELECT * FROM t1 order by f1, report;
3876
SELECT * FROM v1 order by f1, report;
3878
# 1. Name of one base table column is altered
3879
ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
3880
INSERT INTO t1 SET f1 = 0, f4x = 'ABC', report = 't1 1';
3881
--error ER_VIEW_INVALID
3882
INSERT INTO v1 SET f1 = 0, f4 = 'ABC', report = 'v1 1';
3883
--error ER_BAD_FIELD_ERROR
3884
INSERT INTO v1 SET f1 = 0, f4x = 'ABC', report = 'v1 1a';
3885
--error ER_VIEW_INVALID
3886
INSERT INTO v1 SET f1 = 0, report = 'v1 1b';
3888
# Bug#12533 crash on DESCRIBE <view> after renaming base table column;
3889
--error ER_VIEW_INVALID
3891
SELECT * FROM t1 order by f1, report;
3892
--error ER_VIEW_INVALID
3893
SELECT * FROM v1 order by f1, report;
3894
ALTER TABLE t1 CHANGE COLUMN f4x f4 CHAR(5);
3896
# 2. Length of one base table column is increased
3897
ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(10);
3898
INSERT INTO t1 SET f1 = 2, f4 = '<-- 10 -->', report = 't1 2';
3899
INSERT INTO v1 SET f1 = 2, f4 = '<-- 10 -->', report = 'v1 2';
3902
SELECT * FROM t1 order by f1, report;
3903
SELECT * FROM v1 order by f1, report;
3905
# 3. Length of one base table column is reduced
3906
# As the order of warnings is not deterministic:
3908
ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8);
3910
INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3';
3911
INSERT INTO v1 SET f1 = 3, f4 = '<-- 10 -->', report = 'v1 3';
3914
SELECT * FROM t1 order by f1, report;
3915
SELECT * FROM v1 order by f1, report;
3917
# 4. Type of one base table column is altered string -> string
3918
ALTER TABLE t1 CHANGE COLUMN f4 f4 VARCHAR(20);
3919
INSERT INTO t1 SET f1 = 4, f4 = '<------ 20 -------->', report = 't1 4';
3920
INSERT INTO v1 SET f1 = 4, f4 = '<------ 20 -------->', report = 'v1 4';
3923
SELECT * FROM t1 order by f1, report;
3924
SELECT * FROM v1 order by f1, report;
3926
# 5. Type of one base table column altered numeric -> string
3927
ALTER TABLE t1 CHANGE COLUMN f1 f1 VARCHAR(30);
3928
INSERT INTO t1 SET f1 = '<------------- 30 ----------->',
3929
f4 = '<------ 20 -------->', report = 't1 5';
3930
INSERT INTO v1 SET f1 = '<------------- 30 ----------->',
3931
f4 = '<------ 20 -------->', report = 'v1 5';
3934
SELECT * FROM t1 order by f1, report;
3935
SELECT * FROM v1 order by f1, report;
3937
# 6. DROP of one base table column
3938
ALTER TABLE t1 DROP COLUMN f2;
3939
INSERT INTO t1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 't1 6';
3940
--error ER_VIEW_INVALID
3941
INSERT INTO v1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 'v1 6';
3943
--error ER_VIEW_INVALID
3945
SELECT * FROM t1 order by f1, report;
3946
--error ER_VIEW_INVALID
3947
SELECT * FROM v1 order by f1, report;
3949
# 7. Recreation of dropped base table column with the same data type like before
3950
ALTER TABLE t1 ADD COLUMN f2 DATE DEFAULT NULL;
3951
INSERT INTO t1 SET f1 = 'ABC', f2 = '1500-12-04',
3952
f4 = '<------ 20 -------->', report = 't1 7';
3953
INSERT INTO v1 SET f1 = 'ABC', f2 = '1500-12-04',
3954
f4 = '<------ 20 -------->', report = 'v1 7';
3957
SELECT * FROM t1 order by f1, report;
3958
SELECT * FROM v1 order by f1, report;
3960
# 8. Recreation of dropped base table column with a different data type
3962
ALTER TABLE t1 DROP COLUMN f2;
3963
ALTER TABLE t1 ADD COLUMN f2 FLOAT;
3964
INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3965
f4 = '<------ 20 -------->', report = 't1 8';
3966
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3967
f4 = '<------ 20 -------->', report = 'v1 8';
3970
SELECT * FROM t1 order by f1, report;
3971
SELECT * FROM v1 order by f1, report;
3973
# 9. Add a column to the base table
3974
ALTER TABLE t1 ADD COLUMN f3 NUMERIC(7,2);
3975
INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3976
f3 = -2.2, f4 = '<------ 20 -------->', report = 't1 9';
3977
--error ER_BAD_FIELD_ERROR
3978
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3979
f3 = -2.2, f4 = '<------ 20 -------->', report = 'v1 9';
3980
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3981
f4 = '<------ 20 -------->', report = 'v1 9a';
3984
SELECT * FROM t1 order by f1, report;
3985
SELECT * FROM v1 order by f1, report;
3987
# 10. VIEW with numeric function is "victim" of data type change
3990
eval CREATE TABLE t1 (f1 CHAR(10), f2 BIGINT) ENGINE = $engine_type;
3991
INSERT INTO t1 SET f1 = 'ABC', f2 = 3;
3992
CREATE VIEW v1 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
3995
SELECT * FROM t1 order by f1, f2;
3996
SELECT * FROM v1 order by 2;
3997
ALTER TABLE t1 CHANGE COLUMN f2 f2 VARCHAR(30);
3998
INSERT INTO t1 SET f1 = 'ABC', f2 = 'DEF';
4001
SELECT * FROM t1 order by f1, f2;
4002
SELECT * FROM v1 order by 2;
4003
# Some statements for comparison
4004
# - the ugly SQRT('DEF) as constant
4006
# - Will a VIEW based on the same definition show the same result ?
4007
CREATE VIEW v2 AS SELECT SQRT('DEF');
4008
SELECT * FROM v2 order by 1;
4009
# - Will a VIEW v2 created after the base table column recreation show the same
4010
# result set like v1 ?
4011
CREATE OR REPLACE VIEW v2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4013
SELECT * FROM v2 order by 2;
4014
# - What will be the content of base table created with AS SELECT ?
4015
CREATE TABLE t2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4016
if ($have_bug_32285)
4018
--disable_ps_protocol
4021
SELECT * FROM t2 order by 2;
4022
--horizontal_results
4023
--enable_ps_protocol
4025
CREATE TABLE t2 AS SELECT * FROM v1;
4026
if ($have_bug_32285)
4028
--disable_ps_protocol
4031
SELECT * FROM t2 order by 2;
4032
--horizontal_results
4033
--enable_ps_protocol
4035
CREATE TABLE t2 AS SELECT * FROM v2;
4036
if ($have_bug_32285)
4038
--disable_ps_protocol
4041
SELECT * FROM t2 order by 2;
4042
--horizontal_results
4043
--enable_ps_protocol
4054
DROP TABLE IF EXISTS t1;
4055
DROP TABLE IF EXISTS t2;
4056
DROP VIEW IF EXISTS v1;
4057
DROP VIEW IF EXISTS v1_1;
4058
DROP VIEW IF EXISTS v1_2;
4059
DROP VIEW IF EXISTS v1_firstview;
4060
DROP VIEW IF EXISTS v1_secondview;
4061
DROP VIEW IF EXISTS v2;
4062
DROP DATABASE IF EXISTS test2;
4063
DROP DATABASE IF EXISTS test3;
4066
# FIXME sub testcases, which might be included, if they fit good into
4067
# the requirements and the completeness of the tests is increased
4068
# Bug#10970 Views: dependence on temporary table allowed
4069
# Bug#4663 constant function in WHERE clause evaluated in view definition
4070
# Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails
4071
# Bug#10977 Views: no warning if column name is truncated
4072
# Bug#9505: Views: privilege needed on underlying function
4074
# --source suite/funcs_1/Views/Views_403x406.test
4075
# --source suite/funcs_1/Views/Views_407.test
4076
# --source suite/funcs_1/Views/Views_408x411.test
added
removed
mysql-test/suite/funcs_1/views/views_master.inc
