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, statement, v3_to_v1_violation, errno;
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
-- disable_query_log
2492
-- disable_result_log
2495
-- enable_result_log
2498
# Testcase 3.3.1.55 ;
2499
create or replace view test.v1 as
2500
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2501
t1.f61 t1_f61, t2.f61 t2_f61
2502
from t1 inner join t2 where t1.f59 = t2.f59 ;
2503
select * from test.v1 order by t1_f59 ;
2504
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2505
t1.f61 t1_f61, t2.f61 t2_f61
2506
from t1 inner join t2 where t1.f59 = t2.f59;
2508
# Testcase 3.3.1.56 ;
2509
Create or replace view test.v1 as
2510
Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2511
FROM t2 cross join t1;
2512
Select * from v1 order by t1_f59,t2_f59;
2513
Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2514
FROM t2 cross join t1;
2516
# Testcase 3.3.1.57 ;
2517
Create or replace view test.v1 as
2518
Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2520
Select * from v1 order by t1_f59,t2_f59;
2521
Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2524
# Testcase 3.3.1.58 ;
2525
Create or replace view test.v1 as
2526
Select f59, f60, f61, a, b
2527
FROM t2 natural join t1;
2528
Select * from v1 order by f59;
2529
Select f59, f60, f61, a, b
2530
FROM t2 natural join t1;
2532
# Testcase 3.3.1.59 ;
2533
Create or replace view test.v1 as
2534
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2535
t1.f61 t1_f61, t2.f61 t2_f61
2536
FROM t2 left outer join t1 on t2.f59=t1.f59;
2537
Select * from v1 order by t1_f59;
2538
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2539
t1.f61 t1_f61, t2.f61 t2_f61
2540
FROM t2 left outer join t1 on t2.f59=t1.f59;
2542
# Testcase 3.3.1.60 ;
2543
Create or replace view test.v1 as
2544
Select f59, f60, f61, t1.a, t2.b
2545
FROM t2 natural left outer join t1;
2546
Select * from v1 order by f59;
2547
Select f59, f60, f61, t1.a, t2.b
2548
FROM t2 natural left outer join t1;
2550
# Testcase 3.3.1.61 ;
2551
Create or replace view test.v1 as
2552
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2553
t1.f61 t1_f61, t2.f61 t2_f61
2554
FROM t2 right outer join t1 on t2.f59=t1.f59;
2555
Select * from v1 order by t1_f59;
2557
Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2558
t1.f61 t1_f61, t2.f61 t2_f61
2559
FROM t2 right outer join t1 on t2.f59=t1.f59;
2561
# Testcase 3.3.1.62 ;
2562
Create or replace view test.v1 as
2563
Select f59, f60, a, b
2564
FROM t2 natural right outer join t1;
2565
Select * from v1 order by f59 desc;
2567
Select f59, f60, a, b
2568
FROM t2 natural right outer join t1;
2576
let $message= Testcase 3.3.1.A1 - 3.3.1.A3 ;
2577
--source include/show_msg80.inc
2578
###############################################################################
2579
# Testcase: Ensure that a view that is a subset of some or all columns and/or
2580
# column expressions and some or all rows of multiple tables joined
2581
# with the combination of
2582
# 3.3.1.A1 LEFT JOIN
2583
# 3.3.1.A2 INNER JOIN
2584
# 3.3.1.A3 CROSS JOIN
2585
# condition contains the correct row-and-column data
2586
###############################################################################
2587
# Testcase 3.3.1.A1 ;
2589
Drop table if exists t1 ;
2590
Drop view if exists v1;
2592
Create table t1 (f59 int, f60 int, f61 int) ;
2593
Insert into t1 values (101,201,301) ;
2594
Insert into t1 values (107,501,601) ;
2595
Insert into t1 values (901,801,401) ;
2597
Create or replace view test.v1 as
2598
Select tb2.f59 FROM tb2 LEFT JOIN t1 on tb2.f59 = t1.f59 ;
2599
Select * from test.v1 order by f59 limit 0,10;
2600
Drop view if exists test.v1 ;
2602
# Testcase 3.3.1.A2 ;
2604
Drop table if exists t1 ;
2605
Drop view if exists v1;
2607
Create table t1 (f59 int, f60 int, f61 int) ;
2608
Insert into t1 values (201,201,201) ;
2609
Insert into t1 values (207,201,201) ;
2610
Insert into t1 values (201,201,201) ;
2612
Create or replace view test.v1
2613
as Select tb2.f59 FROM tb2 INNER JOIN t1 on tb2.f59 = t1.f59 ;
2614
Select * from test.v1 order by f59 limit 0,10;
2615
Drop view if exists test.v1 ;
2617
# Testcase 3.3.1.A3 ;
2619
Drop table if exists t1 ;
2620
Drop view if exists v1;
2622
Create table t1 (f59 int, f60 int, f61 int) ;
2623
Insert into t1 values (21,21,21) ;
2624
Insert into t1 values (27,21,21) ;
2625
Insert into t1 values (21,21,21) ;
2627
Create or replace view test.v1
2628
as Select tb2.f59 FROM tb2 CROSS JOIN t1 on tb2.f59 = t1.f59 ;
2629
Select * from test.v1 order by f59 limit 0,10;
2634
let $message= Testcase 3.3.1.63 ;
2635
--source include/show_msg80.inc
2636
###############################################################################
2637
# Testcase 3.3.1.63: Ensure that a view that is a subset of some or all columns
2638
# and/or column expressions and some or all rows of multiple
2639
# tables joined with every possible combination of JOIN
2640
# conditions, UNION, UNION ALL and UNION DISTINCT, nested at
2641
# multiple levels, contains the correct row-and-column data.
2642
###############################################################################
2644
Drop table if exists t1 ;
2645
Drop view if exists test.v1 ;
2648
Create table t1 (f59 int, f60 int, f61 int) ;
2649
Insert into t1 values (11,21,31) ;
2650
Insert into t1 values (17,51,61) ;
2651
Insert into t1 values (91,81,41) ;
2653
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2654
Union ALL (Select f59 from t1 where f59=17 );
2655
Select * from test.v1 order by f59 limit 0,10;
2657
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2658
Union (Select f59 from t1 where f59=17 );
2659
Select * from test.v1 order by f59 limit 0,10;
2661
Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2662
Union Distinct (Select f59 from t1 where f60=17 );
2663
Select * from test.v1 order by f59 limit 0,10;
2668
drop table if exists t1;
2669
drop view if exists test.v1;
2671
create table t1 (f59 int, f60 int, f61 int);
2673
insert into t1 values (101,201,301);
2674
insert into t1 values (107,501,601);
2675
insert into t1 values (901,801,401);
2677
create or replace view test.v1 as
2678
select tb2.f59 from tb2 join t1 on tb2.f59 = t1.f59;
2679
select * from test.v1 order by f59 limit 0,10;
2681
create or replace view test.v1 as
2682
(select f59 from tb2 where f59=107 )
2684
(select f59 from t1 where f59=107 );
2685
select * from test.v1 order by f59 limit 0,10;
2687
create or replace view test.v1 as
2688
(select f59 from tb2 where f59=107 )
2690
(select f59 from t1 where f59=107 );
2691
select * from test.v1 order by f59 limit 0,10;
2693
create or replace view test.v1 as
2694
(select f59 from tb2 where f59=107 )
2696
(select f59 from t1 where f59=107 );
2697
select * from test.v1 order by f59 limit 0,10;
2699
drop view if exists test.v1 ;
2703
let $message= Testcase 3.3.1.64 ;
2704
--source include/show_msg80.inc
2705
###############################################################################
2706
# Testcase 3.3.1.64: Ensure that all changes to a view definition, executed by
2707
# the ALTER VIEW statement, are correctly recorded and have
2708
# the correct effect on the data shown by the view.
2709
###############################################################################
2711
Drop view if exists test.v1 ;
2713
CREATE VIEW test.v1 AS SELECT F59
2714
FROM test.tb2 where test.tb2.F59 = 109;
2716
SELECT * FROM test.v1 order by f59 limit 0,10;
2718
ALTER VIEW test.v1 AS SELECT *
2719
FROM test.tb2 WHERE test.tb2.f59 = 242 ;
2720
if ($have_bug_32285)
2722
--disable_ps_protocol
2725
SELECT * FROM test.v1 order by f59 limit 0,10;
2726
--horizontal_results
2727
--enable_ps_protocol
2732
let $message= Testcase 3.3.1.65, 3.3.1.A4, 3.3.1.66, 3.3.1.67 ;
2733
--source include/show_msg80.inc
2734
###############################################################################
2735
# Testcase 3.3.1.65: Ensure that the DROP VIEW statement cleanly drops its
2737
# Testcase 3.3.1.A4: Ensure that the DROP VIEW IF EXISTS statement cleanly
2738
# drops its target view.
2739
# Testcase 3.3.1.66: Ensure that DROP VIEW <view name> fails, with an appro-
2740
# priate error message, if the view named does not exist.
2741
# Testcase 3.3.1.67: Ensure that DROP VIEW IF EXISTS <view name> does not fail,
2742
# but merely returns an appropriate warning, if the view
2743
# named does not exist.
2744
###############################################################################
2746
DROP TABLE IF EXISTS t1;
2747
DROP VIEW IF EXISTS test.v1 ;
2749
eval CREATE TABLE t1 ( f1 VARCHAR(1000) ) ENGINE = $engine_type ;
2750
CREATE VIEW v1 AS SELECT f1 FROM t1;
2754
--error ER_BAD_TABLE_ERROR
2757
CREATE VIEW v1 AS SELECT f1 FROM t1;
2758
# DROP VIEW IF EXISTS
2759
DROP VIEW IF EXISTS v1;
2760
DROP VIEW IF EXISTS v1;
2763
let $message= Testcase 3.3.1.68 ;
2764
--source include/show_msg80.inc
2765
###############################################################################
2766
# Testcase 3.3.1.68: Ensure that DROP VIEW <view name>, DROP VIEW <view name>
2767
# RESTRICT, and DROP VIEW <view name> CASCADE all take
2768
# exactly the same action, until such time as the RESTRICT
2769
# and CASCADE keyword actions are implemented by MySQL.
2770
###############################################################################
2772
DROP TABLE IF EXISTS t1;
2773
DROP VIEW IF EXISTS v1_base ;
2774
DROP VIEW IF EXISTS v1_top ;
2776
CREATE TABLE t1 ( f1 DOUBLE);
2780
SET @part2= 'RESTRICT';
2781
SET @part3= 'CASCADE';
2788
CREATE VIEW v1_base AS SELECT * FROM t1;
2789
CREATE VIEW v1_top AS SELECT * FROM v1_base;
2791
let $aux1= `SELECT CONCAT('DROP VIEW v1_top ', @v1_part)` ;
2792
let $aux2= `SELECT CONCAT('DROP VIEW v1_base ', @v1_part)` ;
2793
eval SET @v1_part= @part$num1;
2796
# 1. more non important sub testcase, where the view (v1_top) is not the base of
2798
# DROP VIEW v1_top < |RESTRICD|CASCADE> must be successful.
2800
# Check, that v1_top really no more exists + cleanup for the second sub test
2801
--error ER_BAD_TABLE_ERROR
2804
CREATE VIEW v1_top AS SELECT * FROM v1_base;
2805
# 2. more important sub testcase, where the view (v1_base) is the base of
2806
# another object (v1_top)
2807
# DROP VIEW v1_base < |RESTRICT|CASCADE>
2808
# If the RESTRICT and CASCADE keyword actions are implemented by MySQL,
2809
# CASCADE will remove v1_base and the dependend view v1_top and
2810
# RESTRICT will fail, because there exists the dependend view v1_top
2812
# Check, if v1_base and v1_top exist + cleanup for next loop
2820
let $message= Testcase 3.3.1.69, 3.3.1.70, 3.3.1.A5 ;
2821
--source include/show_msg80.inc
2822
###############################################################################
2823
# Testcases : Ensure that, when a view is dropped, its definition no longer
2825
# 3.3.1.69 SHOW CREATE VIEW, SHOW CREATE TABLE, SHOW TABLE STATUS,
2827
# 3.3.1.70 CHECK TABLE statement is executed
2828
# 3.3.1.A5 SHOW COLUMNS, SHOW FIELDS, DESCRIBE, EXPLAIN
2829
# statement is executed
2830
###############################################################################
2831
# Note(mleich): There will be no non failing sub testcases with SHOW here.
2832
# They will be done in 3.3.11 ff.
2834
DROP TABLE IF EXISTS t1 ;
2835
DROP VIEW IF EXISTS v1 ;
2837
eval CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ENGINE = $engine_type;
2838
CREATE VIEW v1 AS SELECT * FROM t1;
2841
# The negative tests:
2843
--error ER_NO_SUCH_TABLE
2846
--error ER_NO_SUCH_TABLE
2847
SHOW CREATE VIEW v1 ;
2848
--error ER_NO_SUCH_TABLE
2849
SHOW CREATE TABLE v1 ;
2850
# Attention: Like is a filter. So we will get an empty result set here.
2851
SHOW TABLE STATUS like 'v1' ;
2852
SHOW TABLES LIKE 'v1';
2853
--error ER_NO_SUCH_TABLE
2854
SHOW COLUMNS FROM v1;
2855
--error ER_NO_SUCH_TABLE
2856
SHOW FIELDS FROM v1;
2858
--error ER_NO_SUCH_TABLE
2860
--error ER_NO_SUCH_TABLE
2861
EXPLAIN SELECT * FROM v1;
2865
let $message= Testcase 3.3.1.A6 ;
2866
--source include/show_msg80.inc
2867
###############################################################################
2868
# Testcases 3.3.1.A6 : Ensure that nested views up to level @max_level work.
2869
###############################################################################
2870
# 1. Simple nested VIEWs
2871
# Configurable parameter @max_level = nesting level
2872
# 128 must be good enough, it is already a pathologic value.
2873
# We currently set it to 32, because of performance issues.
2878
DROP DATABASE IF EXISTS test3;
2880
CREATE DATABASE test3;
2881
eval CREATE TABLE test3.t1 (f1 DECIMAL(5,3)) ENGINE = $engine_type;
2882
INSERT INTO test3.t1 SET f1 = 1.0;
2883
CREATE VIEW test3.v0 AS SELECT * FROM test3.t1;
2890
eval SET @aux = $level - 1;
2892
let $sublevel= `SELECT @aux`;
2894
eval CREATE VIEW test3.v$level AS SELECT * FROM test3.v$sublevel;
2896
# DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
2897
# (direct after the while loop) show suspicious results.
2901
eval SHOW CREATE VIEW test3.v$level;
2902
eval SELECT * FROM test3.v$level;
2903
eval EXPLAIN SELECT * FROM test3.v$level;
2907
eval SET @aux = @max_level > $level;
2911
# eval SELECT @aux AS "@aux", $level AS "next level";
2913
let $run= `SELECT @aux`;
2915
#--------------------------------------------------------------------------
2916
# Attention: If the following statements get suspicious/unexpected results
2917
# and you assume that something with the non toplevel VIEWs might
2918
# be wrong, please edit the while loop above and set $debug to 1.
2919
#--------------------------------------------------------------------------
2920
# 1.1 Check of top level VIEW
2921
let $toplevel= `SELECT @max_level`;
2922
eval SHOW CREATE VIEW test3.v$toplevel;
2923
eval SELECT * FROM test3.v$toplevel;
2924
eval EXPLAIN SELECT * FROM test3.v$toplevel;
2926
# 1.2 Check the top level view when a base VIEW is dropped
2928
eval SHOW CREATE VIEW test3.v$toplevel;
2929
--error ER_VIEW_INVALID
2930
eval SELECT * FROM test3.v$toplevel;
2931
--error ER_VIEW_INVALID
2932
eval EXPLAIN SELECT * FROM test3.v$toplevel;
2935
# 2. Complicated nested VIEWs
2936
# parameter @max_level = nesting level
2937
# There is a limit(@join_limit = 61) for the number of tables which
2938
# could be joined. This limit will be reached, when we set
2939
# @max_level = @join_limit - 1 .
2941
#++++++++++++++++++++++++++++++++++++++++++++++
2942
# OBN - Reduced the value of join limit to 30
2943
# Above seems to hang - FIXME
2944
# mleich - Reason unclear why it hangs for OBN on innodb and memory.
2945
# Hypothesis: Maybe the consumption of virtual memory is high
2946
# and OBN's box performs excessive paging.
2947
# (RAM: OBN ~384MB RAM, mleich 1 GB)
2948
#++++++++++++++++++++++++++++++++++++++++++++++
2949
let $message= FIXME - Setting join_limit to 28 - hangs for higher values;
2950
--source include/show_msg.inc
2951
# OBN - Reduced from 30 in 5.1.21 to avoid hitting the ndbcluster limit
2952
# of "ERROR HY000: Got temporary error 4006 'Connect failure
2953
# - out of connection objects (increase MaxNoOfConcurrentTransactions)'
2954
# from NDBCLUSTER " to early;
2955
#SET @join_limit = 61;
2956
SET @join_limit = 28; # OBN - see above
2957
SET @max_level = @join_limit - 1;
2961
DROP DATABASE IF EXISTS test3;
2962
DROP TABLE IF EXISTS test1.t1;
2963
DROP TABLE IF EXISTS test2.t1;
2964
let $level= `SELECT @max_level + 1`;
2969
eval DROP VIEW IF EXISTS test1.v$level;
2972
CREATE DATABASE test3;
2974
# Testplan for the content of the tables:
2975
# ---------------------------------------------------------
2976
# Records test1.t1 test2.t1 test3.t1
2977
# NULL, 'numeric column is NULL' yes yes yes
2978
# 0 , NULL yes yes yes
2979
# 5 , 'five' yes yes yes
2980
# 1 , 'one' yes yes no
2981
# 2 , 'two' yes no yes
2982
# 3 , 'three' no yes yes
2985
eval CREATE TABLE t1 (f1 BIGINT, f2 CHAR(50)) ENGINE = $engine_type ;
2986
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2987
INSERT INTO t1 VALUES (0, NULL);
2988
INSERT INTO t1 VALUES (5, 'five');
2990
INSERT INTO t1 VALUES (1, 'one');
2991
INSERT INTO t1 VALUES (2, 'two');
2994
eval CREATE TABLE t1 (f1 DECIMAL(64,30), f2 VARCHAR(50)) ENGINE = $engine_type;
2995
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2996
INSERT INTO t1 VALUES (0.000000000000000000000000000000, NULL);
2997
INSERT INTO t1 VALUES (5.000000000000000000000000000000, 'five');
2999
INSERT INTO t1 VALUES (+1.000000000000000000000000000000, 'one');
3000
INSERT INTO t1 VALUES (3.000000000000000, 'three');
3003
eval CREATE TABLE t1 (f1 DOUBLE, f2 VARBINARY(50)) ENGINE = $engine_type;
3004
INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
3005
INSERT INTO t1 VALUES (+0.0E-35, NULL);
3006
INSERT INTO t1 VALUES (+0.5E+1, 'five');
3008
INSERT INTO t1 VALUES (20.0E-1, 'two');
3009
INSERT INTO t1 VALUES (0.0300E2, 'three');
3013
CREATE OR REPLACE VIEW test1.v0 AS SELECT * FROM test2.t1;
3016
SET @max_level = IFNULL(@limit1,@max_level);
3023
eval SET @aux = $level - 1;
3024
let $sublevel= `SELECT @aux`;
3025
eval SET @AUX = $level MOD 3 + 1;
3026
let $dbnum= `SELECT @AUX`;
3029
eval CREATE OR REPLACE VIEW test1.v$level AS SELECT f1, f2
3030
FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3032
# DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
3033
# (direct after the while loop) show suspicious results.
3037
eval SHOW CREATE VIEW test1.v$level;
3038
eval SELECT * FROM test1.v$level;
3040
FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3041
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3042
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3043
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3044
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3048
eval SET @aux = @max_level > $level;
3052
# eval SELECT @aux AS "@aux", $level AS "next level";
3054
let $run= `SELECT @aux`;
3057
#--------------------------------------------------------------------------
3058
# Atention: If the following statements get suspicious/unexpected results
3059
# and you assume that something with the non toplevel VIEWs might
3060
# be wrong, please edit the while loop above and set $debug to 1.
3061
#--------------------------------------------------------------------------
3062
# 2.1 Check of top level VIEW
3063
let $toplevel= `SELECT @max_level`;
3064
# Show should be easy
3065
eval SHOW CREATE VIEW test1.v$toplevel;
3066
# SELECT is much more complicated
3067
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3068
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3069
let $message= The output of following EXPLAIN is deactivated, because the result
3070
differs on some platforms
3071
FIXME Is this a bug ? ;
3072
--source include/show_msg80.inc
3075
--disable_result_log
3077
# EXPLAIN might be the hell
3078
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3079
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3085
# 2.2 Check of top level VIEW when join limit is exceeded
3086
# Exceed the limit for the number of tables which could be joined.
3087
let $level= `SELECT @max_level + 1`;
3088
let $sublevel= `SELECT @max_level`;
3089
eval CREATE VIEW test1.v$level AS SELECT f1, f2
3090
FROM test3.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3091
eval SHOW CREATE VIEW test1.v$level;
3092
# the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3093
# is successful so assuming no expected error was intended
3094
# --error ER_TOO_MANY_TABLES
3095
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3096
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3097
let $message= The output of following EXPLAIN is deactivated, because the result
3098
differs on some platforms
3099
FIXME Is this a bug ? ;
3100
--source include/show_msg80.inc
3103
--disable_result_log
3105
# the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3106
# is successful so assuming no expected error was intended
3107
# --error ER_TOO_MANY_TABLES
3108
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3109
CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3114
eval DROP VIEW IF EXISTS test1.v$level;
3116
# 2.3 Create a logical wrong (data type "garbage") base for the upper views
3117
# and check the behaviour of the top level view.
3118
# 2.3.1 Exchange numeric and string column
3119
--disable_result_log
3120
CREATE OR REPLACE VIEW test1.v0 AS
3121
SELECT f1 as f2, f2 as f1 FROM test2.t1;
3122
# 2.3.2 DATE instead of numeric
3123
CREATE OR REPLACE VIEW test2.v0 AS
3124
SELECT CAST('0001-01-01' AS DATE) as f1, f2 FROM test3.t1;
3125
eval SHOW CREATE VIEW test1.v$toplevel;
3126
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3127
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3128
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3129
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3130
# 2.3.3 UCS2 string instead of common string
3131
CREATE OR REPLACE VIEW test3.v0 AS
3132
SELECT f1 , CONVERT('ßÄäÖöÜü§' USING UCS2) as 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;
3139
# 2.3.4 UCS2 string instead of numeric
3140
CREATE OR REPLACE VIEW test3.v0 AS
3141
SELECT CONVERT('ßÄäÖöÜü§' USING UCS2) as f1, f2 FROM test1.t1;
3142
eval SHOW CREATE VIEW test1.v$toplevel;
3143
eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3144
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3145
eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3146
CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3150
let $level= `SELECT @max_level + 1`;
3155
eval DROP VIEW IF EXISTS test1.v$level;
3158
DROP DATABASE test3;
3159
DROP TABLE test1.t1;
3160
DROP TABLE test2.t1;
3162
#==============================================================================
3163
# 3.3.2 Updatable and Insertable-into views:
3164
#==============================================================================
3167
let $message= Testcase 3.3.2.1;
3168
--source include/show_msg80.inc
3169
###############################################################################
3170
# Testcase 3.3.2.1: Ensure that every view which may theoretically accept new
3171
# rows via the INSERT statement does, in fact, do so.
3172
###############################################################################
3174
Drop view if exists test.v1 ;
3177
Create View test.v1 AS SELECT f59,f60 FROM tb2 where f59 = 1995 ;
3179
INSERT INTO test.v1 (f59,f60) values (879,700) ;
3181
SELECT f59,f60 FROM test.v1 where f59 = 879 and f60 = 700 ;
3182
DELETE FROM tb2 where f59 = 879 and f60 = 700 ;
3187
let $message= Testcase 3.3.2.2;
3188
--source include/show_msg80.inc
3189
###############################################################################
3190
# Testcase 3.3.2.2: Ensure that, for every row inserted into a view,
3191
# the correct new data also appears in every relevant
3193
###############################################################################
3195
Drop view if exists test.v1 ;
3198
Create view test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3200
INSERT INTO test.v1 ( f59 , f60 ) values (2005,0101) ;
3203
if ($have_bug_32285)
3205
--disable_ps_protocol
3208
SELECT * FROM tb2 where f59 = 2005 and f60 = 0101 ;
3209
--horizontal_results
3210
--enable_ps_protocol
3211
SELECT f59,f60 FROM test.v1 where f59 = 2005 and f60 = 0101 ;
3212
DELETE FROM tb2 where f59 = 2005 and f60 = 0101 ;
3217
let $message= Testcase 3.3.2.3;
3218
--source include/show_msg80.inc
3219
###############################################################################
3220
# Testcase 3.3.2.3: Ensure that every view which may theoretically accept data
3221
# changes via the UPDATE statement does, in fact, do so.
3222
###############################################################################
3223
Insert into tb2 (f59,f60,f61) values (780,105,106) ;
3226
Drop view if exists test.v1 ;
3229
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3231
UPDATE test.v1 SET f59 = 8 WHERE f59 = 780 and f60 = 105;
3234
if ($have_bug_32285)
3236
--disable_ps_protocol
3239
SELECT * FROM tb2 where f59 = 8 and f60 = 105;
3240
--horizontal_results
3241
--enable_ps_protocol
3242
SELECT f59,f60 FROM test.v1 where f59 = 8 and f60 = 105 ;
3247
let $message= Testcase 3.3.2.4;
3248
--source include/show_msg80.inc
3249
###############################################################################
3250
# Testcase 3.3.2.4: Ensure that, for data values updated within a view, the
3251
# correct new data also appears in every relevant
3253
###############################################################################
3254
Insert into tb2 (f59,f60,f61) values (781,105,106) ;
3257
Drop view if exists test.v1 ;
3259
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3261
UPDATE test.v1 SET f59 = 891 WHERE f60 = 105 ;
3264
if ($have_bug_32285)
3266
--disable_ps_protocol
3269
SELECT * FROM tb2 where f59 = 891 and f60 = 105;
3270
--horizontal_results
3271
--enable_ps_protocol
3272
SELECT f59,f60 FROM test.v1 where f59 = 891 and f60 = 105 ;
3277
let $message= Testcase 3.3.2.5;
3278
--source include/show_msg80.inc
3279
###############################################################################
3280
# Testcase 3.3.2.5: Ensure that every view which may theoretically accept data
3281
# deletions via the DELETE statement does, in fact, do so.
3282
###############################################################################
3283
Insert into tb2 (f59,f60,f61) values (789,105,106) ;
3286
Drop view if exists test.v1 ;
3288
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 789 ;
3290
DELETE FROM test.v1 where f59 = 789 ;
3292
SELECT * FROM tb2 where f59 = 789 ;
3293
SELECT f59,f60 FROM test.v1 where f59 = 789 order by f60 ;
3298
let $message= Testcase 3.3.2.6;
3299
--source include/show_msg80.inc
3300
###############################################################################
3301
# Testcase 3.3.2.6: Ensure that, for data rows deleted from a view, the correct
3302
# rows have also been deleted from every relevant
3304
###############################################################################
3305
Insert into tb2 (f59,f60,f61) values (711,105,106) ;
3308
Drop view if exists test.v1 ;
3310
CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 711 ;
3312
DELETE FROM test.v1 where f59 = 711 ;
3315
SELECT * FROM tb2 where f59 = 711 ;
3316
SELECT f59,f60 FROM test.v1 where f59 = 711 order by f60 ;
3320
let $message= Testcase 3.3.2.1 - 3.3.2.6 alternative implementation;
3321
--source include/show_msg80.inc
3324
DROP TABLE IF EXISTS t1;
3325
DROP VIEW IF EXISTS v1;
3328
CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
3329
f4 CHAR, PRIMARY KEY(f1));
3331
# VIEW including the base table PRIMARY KEY, but not all base table columns (f4)
3332
# no additional columns
3333
CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
3335
# Incomplete INSERT 1
3337
# - PRIMARY KEY f1 included
3338
# f2 gets the default NULL
3339
INSERT INTO v1 SET f1 = 1;
3343
# Incomplete INSERT 2
3345
# - PRIMARY KEY f1 missing
3346
# f1 gets the default 0, because we are in the native sql_mode
3347
INSERT INTO v1 SET f2 = 'ABC';
3348
# f1 gets the default 0, but this value is already exists
3349
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3350
--error ER_DUP_ENTRY
3351
INSERT INTO v1 SET f2 = 'ABC';
3355
# Testplan for DELETE:
3357
# Column within WHERE qualification
3362
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3364
DELETE FROM v1 WHERE f1 = 1;
3366
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3368
DELETE FROM v1 WHERE f2 = 'ABC';
3370
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3375
# Testplan for UPDATE:
3376
# Column to modify Column within WHERE qualification
3377
# f1 (PK) f1(PK + same column to modify)
3380
# f2 (non PK) f1(PK)
3381
# f2 (non PK) f2(non PK + same column to modify)
3382
# f2 (non PK) f3(non PK)
3386
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3388
UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3391
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3393
UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3396
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3398
UPDATE v1 SET f1 = 2;
3401
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3403
UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3406
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3408
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3411
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3413
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3416
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3418
UPDATE v1 SET f2 = 'NNN';
3421
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3422
# UPDATE f1,f2 - f1,f2
3423
UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3429
# VIEW without the PRIMARY KEY f1 of the base table
3430
# no additional columns
3431
CREATE VIEW v1 AS SELECT f2, f3 FROM t1;
3434
# - PRIMARY KEY f1 missing in VIEW definition
3435
# f1 gets the default 0, because we are in the native sql_mode
3436
INSERT INTO v1 SET f2 = 'ABC';
3437
# f1 gets the default 0 and this value is already exists
3438
# OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3439
--error ER_DUP_ENTRY
3440
INSERT INTO v1 SET f2 = 'ABC';
3444
# Testplan for DELETE:
3446
# Column within WHERE qualification
3450
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3452
DELETE FROM v1 WHERE f2 = 'ABC';
3454
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3459
# Testplan for UPDATE:
3461
# Column to modify Column within WHERE qualification
3462
# f2 (non PK) f2(non PK + same column to modify)
3463
# f2 (non PK) f3(non PK)
3465
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3467
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3470
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3472
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3475
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3477
UPDATE v1 SET f2 = 'NNN';
3483
# VIEW with the PRIMARY KEY f1 of the base table
3484
# but additional constant column
3485
CREATE VIEW v1 AS SELECT f1, f2, f3, 'HELLO' AS my_greeting FROM t1;
3487
# Maybe the SQL standard allows the following INSERT.
3488
# But it would be a very sophisticated DBMS.
3489
--error ER_NON_INSERTABLE_TABLE
3490
INSERT INTO v1 SET f1 = 1;
3493
# The next INSERTs should never work, because my_greeting is a constant.
3494
--error ER_NON_INSERTABLE_TABLE
3495
INSERT INTO v1 SET f1 = 1, my_greeting = 'HELLO';
3499
# Testplan for DELETE:
3501
# Column within WHERE qualification
3504
# my_greeting(non base table column)
3507
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3509
DELETE FROM v1 WHERE f1 = 1;
3511
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3513
DELETE FROM v1 WHERE f2 = 'ABC';
3515
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3516
# DELETE my_greeting
3517
DELETE FROM v1 WHERE my_greeting = 'HELLO';
3519
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3525
# Testplan for UPDATE:
3527
# Column to modify Column within WHERE qualification
3528
# f1 (PK) f1(PK + same column to modify)
3530
# f1 (PK) my_greeting(non base table column)
3532
# f2 (non PK) f1(PK)
3533
# f2 (non PK) f2(non PK + same column to modify)
3534
# f2 (non PK) f3(non PK)
3535
# f2 (non PK) my_greeting(non base table column)
3537
# my_greeting(non base table column) f1(PK)
3538
# my_greeting(non base table column) f2(non PK)
3539
# my_greeting(non base table column) my_greeting(same non base table column)
3540
# my_greeting(non base table column) none
3543
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3545
UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3548
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3550
UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3553
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3554
# UPDATE f1 - my_greeting
3555
UPDATE v1 SET f1 = 2 WHERE my_greeting = 'HELLO';
3558
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3560
UPDATE v1 SET f1 = 2;
3563
#------------------------------------------------
3564
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3566
UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3569
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3571
UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3574
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3576
UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3579
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3580
# UPDATE f2 - my_greeting
3581
UPDATE v1 SET f2 = 'NNN' WHERE my_greeting = 'HELLO';
3584
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3586
UPDATE v1 SET f2 = 'NNN';
3589
#------------------------------------------------
3590
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3591
# UPDATE my_greeting - f1
3592
--error ER_NONUPDATEABLE_COLUMN
3593
UPDATE v1 SET my_greeting = 'Hej' WHERE f1 = 1;
3596
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3597
# UPDATE my_greeting - f2
3598
--error ER_NONUPDATEABLE_COLUMN
3599
UPDATE v1 SET my_greeting = 'Hej' WHERE f2 = 'ABC';
3602
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3603
# UPDATE my_greeting - my_greeting
3604
--error ER_NONUPDATEABLE_COLUMN
3605
UPDATE v1 SET my_greeting = 'Hej' WHERE my_greeting = 'HELLO';
3608
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3609
# UPDATE my_greeting - none
3610
--error ER_NONUPDATEABLE_COLUMN
3611
UPDATE v1 SET my_greeting = 'Hej';
3614
#------------------------------------------------
3615
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3616
# UPDATE f1, f2 - f1, f2
3617
UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3623
SET sql_mode = 'traditional';
3624
CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4) NOT NULL,
3625
f4 CHAR, PRIMARY KEY(f1));
3626
# VIEW including the base table PRIMARY KEY, but not the NOT NULL
3627
# base table column (f3)
3628
# no additional columns
3630
CREATE VIEW v1 AS SELECT f1, f2, f4 FROM t1;
3632
# This INSERT must fail
3633
--error ER_NO_DEFAULT_FOR_VIEW_FIELD
3634
INSERT INTO v1 SET f1 = 1;
3638
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3640
DELETE FROM v1 WHERE f1 = 1;
3642
INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3644
UPDATE v1 SET f4 = 'Y' WHERE f2 = 'ABC';
3648
# Switch back to the native SQL mode
3653
let $message= Testcases 3.3.2.7 - 3.3.2.9,
3654
3.3.2.10 - 3.3.2.11 omitted because of missing
3655
features EXCEPT and INTERSECT ;
3656
--source include/show_msg80.inc
3657
###############################################################################
3658
# Testcase 3.3.2.7: Ensure that a view with a definition that includes
3660
# rejects all INSERT, UPDATE, or DELETE attempts
3661
# with an appropriate error message.
3662
# Testcase 3.3.2.8: Ensure that a view with a definition that includes
3664
# rejects all INSERT, UPDATE, or DELETE attempts
3665
# with an appropriate error message.
3666
# Testcase 3.3.2.9: Ensure that a view with a definition that includes
3668
# rejects all INSERT, UPDATE, or DELETE attempts
3669
# with an appropriate error message.
3670
# Testcase 3.3.2.10: Ensure that a view with a definition that includes
3672
# rejects all INSERT, UPDATE, or DELETE attempts
3673
# with an appropriate error message.
3674
# (Note: MySQL does not support EXCEPT at this time;
3675
# this test is for the future.)
3676
# Testcase 3.3.2.11: Ensure that a view with a definition that includes
3678
# rejects all INSERT, UPDATE, or DELETE attempts
3679
# with an appropriate error message.
3680
# (Note: MySQL does not support INTERSECT at this time;
3681
# this test is for the future.)
3683
# Summary of 3.3.2.7 - 3.3.2.11
3684
# Ensure that a view with a definition that includes
3685
# UNION or UNION DISTINCT or UNION ALL or EXCEPT or INTERSECT
3686
# rejects any INSERT or UPDATE or DELETE statement with an
3687
# appropriate error message
3689
# mleich: I assume the type of the storage engine does not play any role.
3690
###############################################################################
3691
INSERT INTO tb2 (f59,f60,f61) VALUES (77,185,126) ;
3692
INSERT INTO tb2 (f59,f60,f61) VALUES (59,58,54) ;
3695
DROP TABLE IF EXISTS t1 ;
3696
DROP VIEW IF EXISTS v1 ;
3698
CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ;
3699
INSERT INTO t1 VALUES (19,41,32) ;
3700
INSERT INTO t1 VALUES (59,54,71) ;
3701
INSERT INTO t1 VALUES (21,91,99) ;
3703
SET @variant1 = 'UNION ';
3704
SET @variant2 = 'UNION ALL ';
3705
SET @variant3 = 'UNION DISTINCT ';
3706
SET @variant4 = 'EXCEPT ';
3707
SET @variant5 = 'INTERSECT ';
3709
# Attention: Set $num to 5, when EXCEPT and INTERSECT is supported
3714
eval SET @variant= @variant$num;
3715
let $aux= `SELECT CONCAT('CREATE VIEW v1 AS ',
3716
'SELECT f61 FROM tb2 WHERE f59=59 ',
3718
'SELECT f61 FROM t1 WHERE f59=19')`;
3720
# $aux contains the CREATE VIEW statement
3722
--error ER_NON_INSERTABLE_TABLE
3723
INSERT INTO v1 VALUES (3000);
3724
--error ER_NON_UPDATABLE_TABLE
3725
UPDATE v1 SET f61 = 100 WHERE f61 = 32;
3726
--error ER_NON_UPDATABLE_TABLE
3734
let $message= Testcases 3.3.2.12 - 3.3.2.20;
3735
--source include/show_msg80.inc
3736
###############################################################################
3737
# Testcase 3.3.2.12: Ensure that a view with a definition that includes
3739
# rejects all INSERT, UPDATE, or DELETE attempts
3740
# with an appropriate error message.
3741
# Testcase 3.3.2.13: Ensure that a view with a definition that includes
3743
# rejects all INSERT, UPDATE, or DELETE attempts
3744
# with an appropriate error message.
3745
# Testcase 3.3.2.14: Ensure that a view with a definition that includes
3747
# rejects all INSERT, UPDATE, or DELETE attempts
3748
# with an appropriate error message.
3749
# Testcase 3.3.2.15: Ensure that a view with a definition that includes
3751
# rejects all INSERT, UPDATE, or DELETE attempts
3752
# with an appropriate error message.
3753
# Testcase 3.3.2.16: Ensure that a view with a definition that includes
3755
# rejects all INSERT, UPDATE, or DELETE attempts
3756
# with an appropriate error message.
3757
# Testcase 3.3.2.17: Ensure that a view with a definition that includes
3758
# a subquery in the select list
3759
# rejects all INSERT, UPDATE, or DELETE attempts
3760
# with an appropriate error message.
3761
# Testcase 3.3.2.18: Ensure that a view with a definition that includes
3762
# a reference to a non-updatable view
3763
# rejects all INSERT, UPDATE, or DELETE attempts
3764
# with an appropriate error message.
3765
# Testcase 3.3.2.19: Ensure that a view with a definition that includes
3766
# a WHERE clause subquery that refers to a table also
3767
# referenced in a FROM clause
3768
# rejects all INSERT, UPDATE, or DELETE attempts
3769
# with an appropriate error message.
3770
# Testcase 3.3.2.20: Ensure that a view with a definition that includes
3771
# ALGORITHM = TEMPTABLE
3772
# rejects all INSERT, UPDATE, or DELETE attempts
3773
# with an appropriate error message.
3775
# Summary of 3.3.2.12 - 3.3.2.20:
3776
# Ensure that a view with a definition that includes
3778
# DISTINCTROW 3.3.2.13
3782
# a sub query in the select list 3.3.2.17
3783
# a reference to a non-updateable view 3.3.2.18
3784
# a WHERE clause sub query that refers to a table also referenced in a
3785
# FROM clause 3.3.2.19
3786
# ALGORITHM = TEMPTABLE 3.3.2.20
3788
# any INSERT or UPDATE or DELETE statement
3789
# with an appropriate error message.
3791
###############################################################################
3793
DROP TABLE IF EXISTS t1, t2 ;
3794
DROP VIEW IF EXISTS test.v1 ;
3795
Drop view if exists v2 ;
3798
CREATE TABLE t1 (f59 int, f60 int, f61 int) ;
3799
INSERT INTO t1 VALUES (19,41,32) ;
3800
INSERT INTO t1 VALUES (59,54,71) ;
3801
INSERT INTO t1 VALUES (21,91,99) ;
3802
CREATE TABLE t2 (f59 int, f60 int, f61 int) ;
3803
INSERT INTO t2 VALUES (19,41,32) ;
3804
INSERT INTO t2 VALUES (59,54,71) ;
3805
INSERT INTO t2 VALUES (21,91,99) ;
3806
CREATE VIEW v2 AS SELECT f59, f60, f61 FROM t2 LIMIT 5;
3808
# For DISTINCT 3.3.2.12
3809
SET @variant1= 'CREATE VIEW v1 AS SELECT DISTINCT(f61) FROM t1';
3810
# For DISTINCTROW 3.3.2.13
3811
SET @variant2= 'CREATE VIEW v1 AS SELECT DISTINCTROW(f61) FROM t1';
3813
SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
3814
# For GROUP BY 3.3.2.15
3815
SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
3816
# For HAVING 3.3.2.16
3817
SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
3818
# For a sub query in the select list 3.3.2.17
3819
SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
3820
# For a WHERE clause sub query that refers to a table also referenced in a
3821
# FROM clause 3.3.2.18
3822
SET @variant7= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
3823
SET @variant8= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
3824
# For ALGORITHM = TEMPTABLE 3.3.2.20
3825
SET @variant9= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
3830
--disable_abort_on_error
3832
eval SET @variant= @variant$num;
3833
let $aux= `SELECT @variant`;
3836
# CREATE VIEW v1 ...
3839
--error ER_NON_INSERTABLE_TABLE
3840
INSERT INTO v1 VALUES (1002);
3841
# --error ER_NON_UPDATABLE_TABLE, ER_UPDATE_TABLE_USED
3842
--error ER_NON_UPDATABLE_TABLE
3843
UPDATE v1 SET f61=1007;
3844
--error ER_NON_UPDATABLE_TABLE
3854
let $message= Testcases 3.3.A1;
3855
--source include/show_msg80.inc
3856
###############################################################################
3857
# Testcase 3.3.A1: Check the effects of base table modifications on an already
3860
# Attention: Many modifications are logical non sense.
3861
# The consequence is in many cases a "garbage in garbage out" effect.
3863
# There is no specification of the intended behaviour within
3864
# the MySQL manual. That means I assume the observed effects are
3865
# no bug as long we do not get a crash or obviously non
3866
# reasonable results.
3867
###############################################################################
3869
DROP TABLE IF EXISTS t1;
3870
DROP TABLE IF EXISTS t2;
3871
DROP VIEW IF EXISTS v1;
3872
DROP VIEW IF EXISTS v2;
3875
eval CREATE TABLE t1 (f1 BIGINT, f2 DATE DEFAULT NULL, f4 CHAR(5),
3876
report char(10)) ENGINE = $engine_type;
3877
CREATE VIEW v1 AS SELECT * FROM t1;
3878
INSERT INTO t1 SET f1 = -1, f4 = 'ABC', report = 't1 0';
3879
INSERT INTO v1 SET f1 = -1, f4 = 'ABC', report = 'v1 0';
3884
SELECT * FROM t1 order by f1, report;
3885
SELECT * FROM v1 order by f1, report;
3887
# 1. Name of one base table column is altered
3888
ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
3889
INSERT INTO t1 SET f1 = 0, f4x = 'ABC', report = 't1 1';
3890
--error ER_VIEW_INVALID
3891
INSERT INTO v1 SET f1 = 0, f4 = 'ABC', report = 'v1 1';
3892
--error ER_BAD_FIELD_ERROR
3893
INSERT INTO v1 SET f1 = 0, f4x = 'ABC', report = 'v1 1a';
3894
--error ER_VIEW_INVALID
3895
INSERT INTO v1 SET f1 = 0, report = 'v1 1b';
3897
# Bug#12533 crash on DESCRIBE <view> after renaming base table column;
3898
--error ER_VIEW_INVALID
3900
SELECT * FROM t1 order by f1, report;
3901
--error ER_VIEW_INVALID
3902
SELECT * FROM v1 order by f1, report;
3903
ALTER TABLE t1 CHANGE COLUMN f4x f4 CHAR(5);
3905
# 2. Length of one base table column is increased
3906
ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(10);
3907
INSERT INTO t1 SET f1 = 2, f4 = '<-- 10 -->', report = 't1 2';
3908
INSERT INTO v1 SET f1 = 2, f4 = '<-- 10 -->', report = 'v1 2';
3911
SELECT * FROM t1 order by f1, report;
3912
SELECT * FROM v1 order by f1, report;
3914
# 3. Length of one base table column is reduced
3915
# We have to mangle within warnings the row numbers, because they are not
3916
# deterministic in case of NDB.
3917
--replace_regex /at row [0-9]/at row <some number>/
3918
ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8);
3919
INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3';
3920
INSERT INTO v1 SET f1 = 3, f4 = '<-- 10 -->', report = 'v1 3';
3923
SELECT * FROM t1 order by f1, report;
3924
SELECT * FROM v1 order by f1, report;
3926
# 4. Type of one base table column is altered string -> string
3927
ALTER TABLE t1 CHANGE COLUMN f4 f4 VARCHAR(20);
3928
INSERT INTO t1 SET f1 = 4, f4 = '<------ 20 -------->', report = 't1 4';
3929
INSERT INTO v1 SET f1 = 4, f4 = '<------ 20 -------->', report = 'v1 4';
3932
SELECT * FROM t1 order by f1, report;
3933
SELECT * FROM v1 order by f1, report;
3935
# 5. Type of one base table column altered numeric -> string
3936
ALTER TABLE t1 CHANGE COLUMN f1 f1 VARCHAR(30);
3937
INSERT INTO t1 SET f1 = '<------------- 30 ----------->',
3938
f4 = '<------ 20 -------->', report = 't1 5';
3939
INSERT INTO v1 SET f1 = '<------------- 30 ----------->',
3940
f4 = '<------ 20 -------->', report = 'v1 5';
3943
SELECT * FROM t1 order by f1, report;
3944
SELECT * FROM v1 order by f1, report;
3946
# 6. DROP of one base table column
3947
ALTER TABLE t1 DROP COLUMN f2;
3948
INSERT INTO t1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 't1 6';
3949
--error ER_VIEW_INVALID
3950
INSERT INTO v1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 'v1 6';
3952
--error ER_VIEW_INVALID
3954
SELECT * FROM t1 order by f1, report;
3955
--error ER_VIEW_INVALID
3956
SELECT * FROM v1 order by f1, report;
3958
# 7. Recreation of dropped base table column with the same data type like before
3959
ALTER TABLE t1 ADD COLUMN f2 DATE DEFAULT NULL;
3960
INSERT INTO t1 SET f1 = 'ABC', f2 = '1500-12-04',
3961
f4 = '<------ 20 -------->', report = 't1 7';
3962
INSERT INTO v1 SET f1 = 'ABC', f2 = '1500-12-04',
3963
f4 = '<------ 20 -------->', report = 'v1 7';
3966
SELECT * FROM t1 order by f1, report;
3967
SELECT * FROM v1 order by f1, report;
3969
# 8. Recreation of dropped base table column with a different data type
3971
ALTER TABLE t1 DROP COLUMN f2;
3972
ALTER TABLE t1 ADD COLUMN f2 FLOAT;
3973
INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3974
f4 = '<------ 20 -------->', report = 't1 8';
3975
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3976
f4 = '<------ 20 -------->', report = 'v1 8';
3979
SELECT * FROM t1 order by f1, report;
3980
SELECT * FROM v1 order by f1, report;
3982
# 9. Add a column to the base table
3983
ALTER TABLE t1 ADD COLUMN f3 NUMERIC(7,2);
3984
INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3985
f3 = -2.2, f4 = '<------ 20 -------->', report = 't1 9';
3986
--error ER_BAD_FIELD_ERROR
3987
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3988
f3 = -2.2, f4 = '<------ 20 -------->', report = 'v1 9';
3989
INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3990
f4 = '<------ 20 -------->', report = 'v1 9a';
3993
SELECT * FROM t1 order by f1, report;
3994
SELECT * FROM v1 order by f1, report;
3996
# 10. VIEW with numeric function is "victim" of data type change
3999
eval CREATE TABLE t1 (f1 CHAR(10), f2 BIGINT) ENGINE = $engine_type;
4000
INSERT INTO t1 SET f1 = 'ABC', f2 = 3;
4001
CREATE VIEW v1 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4004
SELECT * FROM t1 order by f1, f2;
4005
SELECT * FROM v1 order by 2;
4006
ALTER TABLE t1 CHANGE COLUMN f2 f2 VARCHAR(30);
4007
INSERT INTO t1 SET f1 = 'ABC', f2 = 'DEF';
4010
SELECT * FROM t1 order by f1, f2;
4011
SELECT * FROM v1 order by 2;
4012
# Some statements for comparison
4013
# - the ugly SQRT('DEF') as constant
4015
# - Will a VIEW based on the same definition show the same result ?
4016
CREATE VIEW v2 AS SELECT SQRT('DEF');
4017
SELECT * FROM v2 order by 1;
4018
# - Will a VIEW v2 created after the base table column recreation show the same
4019
# result set like v1 ?
4020
CREATE OR REPLACE VIEW v2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4022
SELECT * FROM v2 order by 2;
4023
# - What will be the content of base table created with AS SELECT ?
4024
CREATE TABLE t2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4025
if ($have_bug_32285)
4027
--disable_ps_protocol
4030
SELECT * FROM t2 order by 2;
4031
--horizontal_results
4032
--enable_ps_protocol
4034
CREATE TABLE t2 AS SELECT * FROM v1;
4035
if ($have_bug_32285)
4037
--disable_ps_protocol
4040
SELECT * FROM t2 order by 2;
4041
--horizontal_results
4042
--enable_ps_protocol
4044
CREATE TABLE t2 AS SELECT * FROM v2;
4045
if ($have_bug_32285)
4047
--disable_ps_protocol
4050
SELECT * FROM t2 order by 2;
4051
--horizontal_results
4052
--enable_ps_protocol
4063
DROP TABLE IF EXISTS t1;
4064
DROP TABLE IF EXISTS t2;
4065
DROP VIEW IF EXISTS v1;
4066
DROP VIEW IF EXISTS v1_1;
4067
DROP VIEW IF EXISTS v1_2;
4068
DROP VIEW IF EXISTS v1_firstview;
4069
DROP VIEW IF EXISTS v1_secondview;
4070
DROP VIEW IF EXISTS v2;
4071
DROP DATABASE IF EXISTS test2;
4072
DROP DATABASE IF EXISTS test3;
4075
# FIXME sub testcases, which might be included, if they fit good into
4076
# the requirements and the completeness of the tests is increased
4077
# Bug#10970 Views: dependence on temporary table allowed
4078
# Bug#4663 constant function in WHERE clause evaluated in view definition
4079
# Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails
4080
# Bug#10977 Views: no warning if column name is truncated
4081
# Bug#9505: Views: privilege needed on underlying function
4083
# --source suite/funcs_1/Views/Views_403x406.test
4084
# --source suite/funcs_1/Views/Views_407.test
4085
# --source suite/funcs_1/Views/Views_408x411.test