Debian Codegraph @

mysql-test/suite/funcs_1/views/views_master.inc

raw
1 #### suite/funcs_1/views/views_master.test
2
3 # See BUG#18404149
4 set optimizer_switch='condition_fanout_filter=off';
5
6 --replace_result $engine_type <storage_engine>
7 eval SET SESSION default_storage_engine = $engine_type;
8
9 let $message= ! Attention: The file with the expected results is not
10 | thoroughly checked.
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
15
16 # As long as
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;
24 if ($have_bug_32285)
25 {
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
29 }
30
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
34
35
36 # 3.3 Views
37 # MySQL views are based on a subset of the view requirements described in
38 # the following standard SQL document:
39 #
40 # * ISO/IEC 9075-2:2003 Information technology -- Database languages --
41 # SQL -- Part 2: Foundation (SQL/Foundation)
42 #
43 # MySQL has also added some vendor-specific enhancements to the standard
44 # SQL requirements.
45
46 # FIXME (mleich)
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
49 # accidently modified
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.
55
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
59 # result sets.
60 # Testcase 3.3.1.1
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);
73 # Testcase 3.3.1.45
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);
78 # Testcase 3.3.1.46
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);
83 # Testcase 3.3.1.47
84 insert into test.tb2 (f59,f60) values(0987,41) ;
85 insert into test.tb2 (f59,f60) values(7876,74) ;
86 # Testcase 3.3.1.52
87 INSERT INTO tb2 (f59,f61) VALUES(321,765 );
88 INSERT INTO tb2 (f59,f61) VALUES(9112,8771);
89 # Testcase 3.3.1.53
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 ) ;
93 # Testcase 3.3.1.A1
94 Insert into tb2 (f59,f60,f61) values (107,105,106) ;
95 Insert into tb2 (f59,f60,f61) values (109,108,104) ;
96 # Testcase 3.3.1.A2
97 Insert into tb2 (f59,f60,f61) values (207,205,206) ;
98 Insert into tb2 (f59,f60,f61) values (209,208,204) ;
99 # Testcase 3.3.1.A3
100 Insert into tb2 (f59,f60,f61) values (27,25,26) ;
101 Insert into tb2 (f59,f60,f61) values (29,28,24) ;
102 # Testcase 3.3.1.63
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);
107 # Testcase 3.3.1.64
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 );
111 if ($have_bug_32285)
112 {
113 --disable_ps_protocol
114 }
115 SELECT * FROM tb2 ORDER BY f59, f60, f61;
116 --enable_ps_protocol
117 #
118 #
119 Use test;
120 #
121 # End of basic preparations.
122 #
123 ##############################################################################
124
125
126
127 #==============================================================================
128 # 3.3.1 Syntax checks for CREATE VIEW, CREATE OR REPLACE VIEW, ALTER VIEW,
129 # and DROP VIEW:
130 #==============================================================================
131
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
136 # are supported.
137 ###############################################################################
138 --disable_warnings
139 Drop table if exists t1;
140 --enable_warnings
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);
148
149 #(01)
150 --disable_warnings
151 Drop view if exists v1 ;
152 --enable_warnings
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;
156
157 #(02)
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;
162
163 #(03)
164 CREATE or REPLACE VIEW v1 AS select f59,f60,f61
165 FROM test.tb2;
166 select * FROM v1 order by f59,f60,f61 limit 4,3;
167
168 #(04)
169 CREATE or REPLACE VIEW v1 AS select distinct f59
170 FROM test.tb2;
171 select * FROM v1 order by f59 limit 4,3;
172
173 #(05)
174 ALTER VIEW v1 AS select f59
175 FROM test.tb2;
176 select * FROM v1 order by f59 limit 6,2;
177
178 #(06)
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;
182
183 #(07)
184 CREATE or REPLACE VIEW v1 AS select f59
185 from tb2 order by f59 asc;
186 select * FROM v1 limit 0,10;
187
188 #(08)
189 CREATE or REPLACE VIEW v1 AS select f59
190 from tb2 order by f59 desc;
191 select * FROM v1 limit 0,10;
192
193 #(09)
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;
197
198 #(10)
199 CREATE or REPLACE VIEW v1 AS select f59
200 from tb2 group by f59 order by f59;
201 select * FROM v1 order by f59 limit 0,10;
202
203 #(11)
204 CREATE or REPLACE VIEW v1 AS select f59
205 from tb2 group by f59 order by f59 desc;
206 select * FROM v1 order by f59 limit 0,10;
207
208 #(12)
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;
212
213 #(13)
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;
217
218 #(14)
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;
222
223 #(15)
224 if ($have_bug_32285)
225 {
226 --disable_ps_protocol
227 }
228 --vertical_results
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;
232
233 #(16)
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;
237 --horizontal_results
238 --enable_ps_protocol
239
240 #(17)
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;
244
245
246 #(18)
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;
250
251 DROP VIEW v1 ;
252 DROP TABLE t1 ;
253
254
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 ###############################################################################
261 --disable_warnings
262 DROP TABLE IF EXISTS t1 ;
263 DROP VIEW IF EXISTS v1 ;
264 DROP VIEW IF EXISTS v2 ;
265 --enable_warnings
266 CREATE TABLE t1 (f1 BIGINT) ;
267
268 # User variables and parameters are not supported in VIEWs -> 3.3.1.40
269
270 # SELECT INTO is illegal
271 SET @x=0;
272 --error ER_VIEW_SELECT_CLAUSE
273 CREATE or REPLACE VIEW v1 AS Select 1 INTO @x;
274 Select @x;
275
276 # Subquery in the FROM clause is legal
277 CREATE or REPLACE VIEW v1 AS Select 1
278 FROM (SELECT 1 FROM t1) my_table;
279 DROP VIEW v1;
280
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 ;
287 SET @a:=0 ;
288 SELECT @a ;
289 INSERT INTO v1 VALUES (1) ;
290 SELECT @a ;
291 SELECT * FROM t1 ORDER BY 1;
292 DROP TRIGGER tr1 ;
293 SET @a:=0 ;
294 --error ER_WRONG_OBJECT
295 CREATE TRIGGER tr1 BEFORE INSERT ON v1 FOR EACH ROW SET @a:=1 ;
296
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;
305
306 # VIEWs cannot contain a PRIMARY KEY or have an Index.
307 --disable_warnings
308 DROP TABLE IF EXISTS t1, t2 ;
309 DROP VIEW IF EXISTS v1 ;
310 DROP VIEW IF EXISTS v2 ;
311 --enable_warnings
312 CREATE TABLE t1 ( f1 DATE, f2 VARCHAR(30), 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);
322 DROP TABLE t1;
323 DROP VIEW v1;
324
325
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
330 # the correct order.
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 ###############################################################################
334 --disable_warnings
335 DROP VIEW IF EXISTS v1 ;
336 --enable_warnings
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;
351 # AS forgotten
352 --error ER_PARSE_ERROR
353 CREATE or REPLACE VIEW v1 Select f59, f60
354 from test.tb2 my_table where f59 = 250 ;
355 # positive case
356 CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
357 FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
358 DROP VIEW v1;
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;
363 # AS after SELECT
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;
390
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;
416
417 --disable_warnings
418 Drop table if exists t1 ;
419 --enable_warnings
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);
427 # by before order
428 --error ER_PARSE_ERROR
429 CREATE or REPLACE view v1 as Select f59, f60
430 from tb2 by order f59;
431 # by before group
432 --error ER_PARSE_ERROR
433 CREATE or REPLACE view v1 as Select f59, f60
434 from tb2 by group f59 ;
435
436
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
442 # and tools.
443 ###############################################################################
444 --disable_warnings
445 DROP VIEW IF EXISTS v1 ;
446 --enable_warnings
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;
453 # positive case
454 CREATE VIEW v1 AS SELECT 1;
455 DROP VIEW v1;
456 --error ER_PARSE_ERROR
457 VIEW v1 AS SELECT 1;
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
465 CREATE VIEW v1 AS ;
466
467
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
473 # and tools.
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
481 --disable_warnings
482 DROP VIEW IF EXISTS v1 ;
483 --enable_warnings
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 ;
517
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 ;
539
540
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,
546 # and drop time.
547 ###############################################################################
548 # Note(mleich): non-qualified view name means a view name without preceding
549 # database name
550 --disable_warnings
551 DROP VIEW IF EXISTS v1 ;
552 --enable_warnings
553 Create view test.v1 AS Select * from test.tb2;
554 Alter view test.v1 AS Select F59 from test. tb2 limit 100 ;
555 Drop view test.v1 ;
556 Create view v1 AS Select * from test.tb2 limit 100 ;
557 Alter view v1 AS Select F59 from test.tb2 limit 100 ;
558 Drop view v1 ;
559
560
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
570 # be deleted.
571 --disable_warnings
572 DROP TABLE IF EXISTS t1 ;
573 DROP VIEW IF EXISTS v1 ;
574 DROP VIEW IF EXISTS V1 ;
575 --enable_warnings
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;
584 SELECT * FROM v1 ORDER BY 1;
585 # SELECT * FROM V1;
586 --disable_warnings
587 DROP TABLE IF EXISTS t1 ;
588 DROP VIEW IF EXISTS v1 ;
589 DROP VIEW IF EXISTS V1 ;
590 --enable_warnings
591
592
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
598 # is rejected.
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 ;
615 --disable_warnings
616 Drop view if exists test.procedure ;
617 --enable_warnings
618 Create view test.procedure as Select * from test.tb2 limit 100 ;
619 Drop view if exists test.procedure ;
620
621
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
630 # be checked in
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.
633 --disable_warnings
634 Drop TABLE IF EXISTS t1 ;
635 Drop VIEW IF EXISTS v1;
636 Drop VIEW IF EXISTS v2;
637 Drop VIEW IF EXISTS v3;
638 --enable_warnings
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;
643
644 # Only negative cases, positive cases will be checked later:
645 DROP TABLE t1;
646 --error ER_VIEW_INVALID
647 SELECT * FROM v1 ORDER BY 1;
648 --error ER_VIEW_INVALID
649 DELETE FROM v1;
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
656 SELECT * FROM v2 ORDER BY 1;
657 --error ER_VIEW_INVALID
658 DELETE FROM v2;
659 --error ER_VIEW_INVALID
660 UPDATE v2 SET f1 = 'aaaaa';
661 --error ER_VIEW_INVALID
662 INSERT INTO v2 SET f1 = "fffff";
663 DROP VIEW v1;
664 # v2 is based on v1, which is now dropped
665 --error ER_VIEW_INVALID
666 SELECT * FROM v2 ORDER BY 1;
667 --error ER_VIEW_INVALID
668 DELETE FROM v2;
669 --error ER_VIEW_INVALID
670 UPDATE v2 SET f1 = 'aaaaa';
671 --error ER_VIEW_INVALID
672 INSERT INTO v2 SET f1 = "fffff";
673
674 DROP VIEW v2;
675
676 # A VIEW based on itself is non sense.
677 --disable_warnings
678 DROP TABLE IF EXISTS t1 ;
679 DROP VIEW IF EXISTS v1 ;
680 --enable_warnings
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;
689
690 DROP VIEW v1;
691 DROP TABLE t1;
692
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 ###############################################################################
699 --disable_warnings
700 Drop view if exists test.v1 ;
701 --enable_warnings
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 ;
707
708
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.
721 --disable_warnings
722 Drop view if exists test.v111 ;
723 --enable_warnings
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 );
729 DROP VIEW test.v111;
730
731
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 ###############################################################################
739 USE test;
740 --disable_warnings
741 Drop database if exists test2 ;
742 --enable_warnings
743 Create database test2 ;
744 # Plan of sub tests
745 # Object name object type in object type in
746 # database test database test2
747 # t1 TABLE TABLE
748 # t2 TABLE VIEW
749 # v1 VIEW TABLE
750 # v2 VIEW VIEW
751 --disable_warnings
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;
755 --enable_warnings
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));
763
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;
768
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.
779 USE test;
780 INSERT INTO t1 VALUES('test.t1 - 2');
781 INSERT INTO t2 VALUES('test.t2 - 2');
782 INSERT INTO t0 VALUES('test.t0 - 2');
783 USE test2;
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
788 SELECT * FROM t1 ORDER BY 1;
789 SELECT * FROM t2;
790 SELECT * FROM v1 ORDER BY 1;
791 SELECT * FROM v2 ORDER BY 1;
792 USE test;
793 SELECT * FROM t1 ORDER BY 1;
794 SELECT * FROM t2;
795 SELECT * FROM v1 ORDER BY 1;
796 SELECT * FROM v2 ORDER BY 1;
797
798
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
805 # the new view.
806 ###############################################################################
807 --disable_warnings
808 DROP TABLE IF EXISTS t1;
809 DROP VIEW IF EXISTS v1;
810 --enable_warnings
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;
815 --sorted_result
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;
821 --sorted_result
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;
826 if ($have_bug_11589)
827 {
828 --disable_ps_protocol
829 }
830 --vertical_results
831 SELECT * FROM test.v1 order by f59,f60,f61,f62,f63,f64,f65;
832 --horizontal_results
833 --enable_ps_protocol
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;
838 Drop table test.t1 ;
839 Drop view test.v1 ;
840
841
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;
853
854
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
861 # creates the view.
862 ###############################################################################
863 --disable_warnings
864 Drop table if exists test.v1 ;
865 --enable_warnings
866 CREATE OR REPLACE view test.v1 as select * from tb2;
867 if ($have_bug_32285)
868 {
869 --disable_ps_protocol
870 }
871 --replace_regex /\x00/#/
872 --sorted_result
873 SELECT * FROM test.v1;
874 --enable_ps_protocol
875 Drop view test.v1 ;
876
877
878 let $message= Testcase 3.3.1.16 + 3.3.1.17 ;
879 --source include/show_msg80.inc
880 ###############################################################################
881 # Testcase 3.3.1.16: Ensure that a view with a definition that does not include
882 # an explicit column-name list takes its column names from
883 # the underlying base table(s).
884 # Testcase 3.3.1.17: Ensure that a view with a definition that does include an
885 # explicit column-name list uses the explicit names and not
886 # the name of the columns from the underlying base tables(s)
887 ###############################################################################
888 --disable_warnings
889 Drop table if exists test.v1 ;
890 --enable_warnings
891 CREATE OR REPLACE VIEW v1 AS SELECT * From tb2;
892 # Note(mleich): The empty result is intended, because I want to compare
893 # column names only.
894 SELECT * FROM tb2 WHERE 1 = 2;
895 SELECT * FROM v1 WHERE 1 = 2;
896 Drop view v1;
897
898 --disable_warnings
899 DROP TABLE IF EXISTS t1;
900 DROP VIEW IF EXISTS v1;
901 --enable_warnings
902 CREATE TABLE t1 (f1 NUMERIC(15,3));
903 INSERT INTO t1 VALUES(8.8);
904 # 1. no explicit column in VIEW definition or SELECT
905 CREATE VIEW v1 AS SELECT * FROM t1;
906 SHOW CREATE VIEW v1;
907 SELECT * FROM v1 ORDER BY 1;
908 # 2. no explicit column in VIEW definition, but in SELECT column_list
909 CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1;
910 SHOW CREATE VIEW v1;
911 SELECT * FROM v1 ORDER BY 1;
912 # 3. no explicit column in VIEW definition, but alias from SELECT column_list
913 CREATE OR REPLACE VIEW v1 AS SELECT f1 As my_column FROM t1;
914 SHOW CREATE VIEW v1;
915 SELECT * FROM v1 ORDER BY 1;
916 # 4. Finally the requirement: explicit column_list in VIEW definition
917 CREATE OR REPLACE VIEW v1(column1,column2)
918 AS SELECT f1 As my_column, f1 FROM t1;
919 SHOW CREATE VIEW v1;
920 SELECT * FROM v1 ORDER BY 1;
921 CREATE OR REPLACE VIEW test.v1(column1,column2)
922 AS SELECT f1 As my_column, f1 FROM test.t1;
923 SHOW CREATE VIEW v1;
924 SELECT * FROM v1 ORDER BY 1;
925
926
927 let $message= Testcase 3.3.1.18 ;
928 --source include/show_msg80.inc
929 ###############################################################################
930 # Testcase 3.3.1.18: Ensure that a reference to a view with a definition that
931 # includes an explicit column-name fails, with an appropriate
932 # error message, if the reference includes columns names
933 # from the underlying base table(s) rather than the view
934 # column names.
935 ###############################################################################
936 # Note(mleich): The goal is to check the merge algorithm.
937 --disable_warnings
938 Drop view if exists v1 ;
939 Drop view if exists v1_1 ;
940 --enable_warnings
941 Create view v1
942 as Select test.tb2.f59 as NewNameF1, test.tb2.f60
943 from test.tb2 limit 0,100 ;
944 Create view v1_1
945 as Select test.tb2.f59 as NewNameF1, test.tb2.f60 as NewNameF2
946 from tb2 limit 0,100 ;
947 --error ER_BAD_FIELD_ERROR
948 SELECT NewNameF1,f60 FROM test.v1_1 ;
949 --error ER_BAD_FIELD_ERROR
950 SELECT NewNameF1, v1_1.f60 FROM test.v1_1 ;
951 --error ER_BAD_FIELD_ERROR
952 SELECT f59, f60 FROM test.v1 ;
953 Use test ;
954 --error ER_BAD_FIELD_ERROR
955 SELECT F59 FROM v1 ;
956
957
958 let $message= Testcase 3.3.1.19 ;
959 --source include/show_msg80.inc
960 ###############################################################################
961 # Testcase 3.3.1.19: Ensure that every column of a view must have a
962 # distinct name
963 ###############################################################################
964 --disable_warnings
965 DROP TABLE IF EXISTS t1, t2;
966 DROP VIEW IF EXISTS v1;
967 --enable_warnings
968 CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
969 INSERT INTO t1 VALUES(7, 7.7);
970 CREATE TABLE t2( f1 BIGINT, f2 DECIMAL(5,2));
971 INSERT INTO t2 VALUES(6, 6.6);
972 # positive testcases
973 CREATE VIEW v1 AS SELECT * FROM t1;
974 SELECT * FROM v1 ORDER BY 1;
975 CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1;
976 SELECT * FROM v1 ORDER BY 1;
977 CREATE OR REPLACE VIEW v1 AS SELECT f1 AS my_f1, f2 AS my_f2 FROM t1;
978 SELECT * FROM v1 ORDER BY 1;
979 CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
980 SELECT * FROM v1 ORDER BY 1;
981 CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT t1.f1, t2.f2 FROM t1, t2;
982 SELECT * FROM v1 ORDER BY 1;
983 # negative testcases (sometimes including the underlying SELECT)
984 # duplicate via alias in SELECT
985 SELECT f1, f2 AS f1 FROM t1;
986 --error ER_DUP_FIELDNAME
987 CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 AS f1 FROM t1;
988 # duplicate via JOIN SELECT
989 SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
990 --error ER_DUP_FIELDNAME
991 CREATE OR REPLACE VIEW v1 AS SELECT t1.f1, t2.f1 AS f1 FROM t1, t2;
992 # duplicate via VIEW definition
993 --error ER_DUP_FIELDNAME
994 CREATE OR REPLACE VIEW v1 (my_col, my_col) AS SELECT * FROM t1;
995
996
997 let $message= Testcase 3.3.1.20 ;
998 --source include/show_msg80.inc
999 ###############################################################################
1000 # Testcase 3.3.1.20: Ensure that, if a column-name list is provided for a
1001 # view definition, the list contains a name for every column
1002 # in the view
1003 ###############################################################################
1004 --disable_warnings
1005 DROP TABLE IF EXISTS t1;
1006 --enable_warnings
1007 CREATE TABLE t1( f1 BIGINT, f2 DECIMAL(5,2));
1008 # positive case
1009 CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT * FROM t1;
1010 CREATE OR REPLACE VIEW v1 (my_f1, my_f2) AS SELECT f1, f2 FROM t1;
1011 # negative cases, where we assign a wrong number of column names
1012 --error ER_VIEW_WRONG_LIST
1013 CREATE OR REPLACE VIEW v1 (my_f1 ) AS SELECT * FROM t1;
1014 --error ER_VIEW_WRONG_LIST
1015 CREATE OR REPLACE VIEW v1 (my_f1 ) AS SELECT f1, f2 FROM t1;
1016 --error ER_VIEW_WRONG_LIST
1017 CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT * FROM t1;
1018 --error ER_VIEW_WRONG_LIST
1019 CREATE OR REPLACE VIEW v1 (my_f1, my_f2, my_f3) AS SELECT f1, f2 FROM t1;
1020
1021
1022 let $message= Testcase 3.3.1.21 ;
1023 --source include/show_msg80.inc
1024 ###############################################################################
1025 # Testcase 3.3.1.21: Ensure that a view column can be a direct copy of a
1026 # column from an underlying table.
1027 ###############################################################################
1028 --disable_warnings
1029 DROP VIEW IF EXISTS v1;
1030 --enable_warnings
1031 CREATE VIEW test.v1( F59, F60 ) AS SELECT F59, F60 From tb2;
1032 SELECT * FROM test.v1 order by F59, F60 desc LIMIT 2;
1033 Drop view if exists test.v1 ;
1034
1035
1036 let $message= Testcase 3.3.1.22 ;
1037 --source include/show_msg80.inc
1038 ###############################################################################
1039 # Testcase 3.3.1.22: Ensure that a view column can be based on any valid
1040 # expression, whether or not the expression includes a
1041 # reference of the column of an underlying table.
1042 ###############################################################################
1043 --disable_warnings
1044 DROP VIEW IF EXISTS v1;
1045 --enable_warnings
1046 CREATE VIEW test.v1( product ) AS SELECT f59*f60 From tb2 WHERE f59 < 3;
1047 --sorted_result
1048 SELECT * FROM test.v1;
1049 CREATE OR REPLACE VIEW test.v1( product ) AS SELECT 1*2;
1050 --sorted_result
1051 SELECT * FROM test.v1;
1052 CREATE OR REPLACE VIEW test.v1( product ) AS SELECT USER();
1053 --sorted_result
1054 SELECT * FROM test.v1;
1055 Drop view if exists test.v1 ;
1056
1057
1058 let $message= Testcase 3.3.1.23 + 3.3.1.24 ;
1059 --source include/show_msg80.inc
1060 ###############################################################################
1061 # Testcase 3.3.1.23: Ensure that a view definition that includes a reference to
1062 # a non-existent table fails, with an appropriate error
1063 # message, at creation time.
1064 # Testcase 3.3.1.24: Ensure that a view definition that includes a reference to
1065 # a non-existent view fails, with an appropriate error
1066 # message, at creation time.
1067 ###############################################################################
1068 # Note(mleich): The SELECT statement syntax does not contain any functionality
1069 # to claim, that the object after FROM must be a VIEW.
1070 # Testcase 3.3.1.24 should be deleted.
1071 USE test;
1072 --disable_warnings
1073 DROP TABLE IF EXISTS t1;
1074 DROP VIEW IF EXISTS v1;
1075 DROP VIEW IF EXISTS v2;
1076 --enable_warnings
1077 --error ER_NO_SUCH_TABLE
1078 CREATE VIEW test.v2 AS SELECT * FROM test.t1;
1079 --error ER_NO_SUCH_TABLE
1080 CREATE VIEW v2 AS Select * from test.v1;
1081 DROP VIEW IF EXISTS v2;
1082
1083
1084 let $message= Testcase 3.3.1.25 ;
1085 --source include/show_msg80.inc
1086 ###############################################################################
1087 # Testcase 3.3.1.25: Ensure that a view cannot be based on one or more
1088 # temporary tables.
1089 ###############################################################################
1090 # Note(mleich): A temporary table hides permanent tables which have the same
1091 # name. So do not forget to drop the temporary table.
1092 --disable_warnings
1093 DROP TABLE IF EXISTS t1_temp;
1094 DROP TABLE IF EXISTS t2_temp;
1095 DROP VIEW IF EXISTS v1;
1096 --enable_warnings
1097 Create table t1_temp(f59 char(10),f60 int) ;
1098 Create temporary table t1_temp(f59 char(10),f60 int) ;
1099 Insert into t1_temp values('FER',90);
1100 Insert into t1_temp values('CAR',27);
1101 --error ER_VIEW_SELECT_TMPTABLE
1102 Create view v1 as select * from t1_temp ;
1103
1104 Create temporary table t2_temp(f59 char(10),f60 int) ;
1105 Insert into t2_temp values('AAA',11);
1106 Insert into t2_temp values('BBB',22);
1107 --error ER_VIEW_SELECT_TMPTABLE
1108 Create or replace view v1
1109 as select t1_temp.f59,t2_temp.f59 from t1_temp,t2_temp ;
1110 DROP temporary table t1_temp;
1111 DROP table t1_temp;
1112 DROP temporary table t2_temp;
1113
1114 --disable_warnings
1115 DROP TABLE IF EXISTS t1;
1116 DROP VIEW IF EXISTS v1;
1117 --enable_warnings
1118 CREATE TABLE t1 (f1 char(10));
1119 CREATE TEMPORARY TABLE t2 (f2 char(10));
1120 INSERT INTO t1 VALUES('t1');
1121 INSERT INTO t1 VALUES('A');
1122 INSERT INTO t2 VALUES('t2');
1123 INSERT INTO t2 VALUES('B');
1124 # simple SELECT
1125 --error ER_VIEW_SELECT_TMPTABLE
1126 CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2;
1127 # JOIN - temporary table first
1128 --error ER_VIEW_SELECT_TMPTABLE
1129 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2, t1;
1130 --error ER_VIEW_SELECT_TMPTABLE
1131 CREATE OR REPLACE VIEW v1 AS SELECT f2, f1 FROM t2, t1;
1132 # JOIN - temporary table last
1133 --error ER_VIEW_SELECT_TMPTABLE
1134 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1, t2;
1135 --error ER_VIEW_SELECT_TMPTABLE
1136 CREATE OR REPLACE VIEW v1 AS SELECT f1, f2 FROM t1, t2;
1137 # UNION - temporary table first
1138 --error ER_VIEW_SELECT_TMPTABLE
1139 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2 UNION SELECT * FROM t1;
1140 --error ER_VIEW_SELECT_TMPTABLE
1141 CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t2 UNION SELECT f1 FROM t1;
1142 # UNION - temporary table last
1143 --error ER_VIEW_SELECT_TMPTABLE
1144 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;
1145 --error ER_VIEW_SELECT_TMPTABLE
1146 CREATE OR REPLACE VIEW v1 AS SELECT f1 FROM t1 UNION SELECT f2 FROM t2;
1147 # SUBQUERY - temporary table first
1148 --error ER_VIEW_SELECT_TMPTABLE
1149 CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
1150 WHERE f2 = ( SELECT f1 FROM t1 );
1151 # SUBQUERY - temporary table last
1152 --error ER_VIEW_SELECT_TMPTABLE
1153 CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t1
1154 WHERE f1 = ( SELECT f2 FROM t2 );
1155 DROP TABLE t1;
1156 DROP TEMPORARY TABLE t2;
1157
1158
1159 let $message= Testcase 3.3.1.26 ;
1160 --source include/show_msg80.inc
1161 ###############################################################################
1162 # Testcase 3.3.1.26: Ensure that a view can be based on an underlying table
1163 # within the same database
1164 ###############################################################################
1165 --disable_warnings
1166 DROP VIEW IF EXISTS v1;
1167 --enable_warnings
1168 Create view test.v1 AS Select * from test.tb2;
1169 if ($have_bug_11589)
1170 {
1171 --disable_ps_protocol
1172 }
1173 --sorted_result
1174 Select * from test.v1;
1175 --enable_ps_protocol
1176 Drop view test.v1 ;
1177
1178
1179 let $message= Testcase 3.3.1.27 ;
1180 --source include/show_msg80.inc
1181 ###############################################################################
1182 # Testcase 3.3.1.27: Ensure that a view can be based on an underlying view
1183 # within the same database.
1184 ###############################################################################
1185 --disable_warnings
1186 DROP VIEW IF EXISTS test.v1;
1187 Drop VIEW IF EXISTS test.v1_1 ;
1188 --enable_warnings
1189 Create view test.v1 AS Select * from test.tb2;
1190 Create view test.v1_1 AS Select F59 from test.v1 ;
1191 Select * from test.v1_1 order by F59 limit 2;
1192 Drop view test.v1 ;
1193 Drop view test.v1_1 ;
1194
1195
1196 let $message= Testcase 3.3.1.28 ;
1197 --source include/show_msg80.inc
1198 ###############################################################################
1199 # Testcase 3.3.1.28: Ensure that a view can be based on an underlying table
1200 # from another database.
1201 ###############################################################################
1202 --disable_warnings
1203 Drop database if exists test2 ;
1204 --enable_warnings
1205 create database test2 ;
1206 Create view test2.v2 AS Select * from test.tb2 limit 50,50;
1207 use test2 ;
1208 Create view v1 AS Select * from test.tb2 limit 50 ;
1209 if ($have_bug_32285)
1210 {
1211 --disable_ps_protocol
1212 }
1213 --vertical_results
1214 Select * from v1 order by f59,f60,f61,f62,f63,f64,f65;
1215 --horizontal_results
1216 --enable_ps_protocol
1217 --sorted_result
1218 Select * from test2.v2 ;
1219 Drop view if exists test2.v1 ;
1220 Drop view if exists test2.v2 ;
1221 Drop database test2 ;
1222
1223
1224 let $message= Testcase 3.3.1.29 ;
1225 --source include/show_msg80.inc
1226 ###############################################################################
1227 # Testcase 3.3.1.29: Ensure that a view can be based on an underlying view from
1228 # another database.
1229 ###############################################################################
1230 --disable_warnings
1231 Drop database if exists test2 ;
1232 Drop view if exists test.v1 ;
1233 --enable_warnings
1234 create database test2 ;
1235
1236 use test2;
1237 Create view test.v1 AS Select * from test.tb2 limit 50 ;
1238 Create view test2.v2 AS Select F59 from test.v1 ;
1239 Drop view if exists test.v1 ;
1240 Drop view if exists test2.v2 ;
1241
1242 # Note(mleich): Testcase 3.3.1.30 (identical requirements like 3.3.1.26)
1243 # --> omitted
1244
1245 let $message= Testcase 3.3.1.31 ;
1246 --source include/show_msg80.inc
1247 ###############################################################################
1248 # Testcase 3.3.1.31: Ensure that a view can be based on a join of multiple
1249 # tables within the same database.
1250 ###############################################################################
1251 --disable_warnings
1252 Drop table if exists test.t1 ;
1253 --enable_warnings
1254 CREATE TABLE test.t1 ( f59 int, f60 int );
1255 INSERT INTO test.t1 VALUES( 34, 654 );
1256 INSERT INTO test.t1 VALUES( 906, 434 );
1257 INSERT INTO test.t1 VALUES( 445, 765 );
1258 Create or replace view test.v1
1259 AS SELECT test.t1.F59, test.tb2.F60
1260 FROM test.tb2 JOIN test.t1 ON test.tb2.F59 = test.t1.F59 ;
1261 --sorted_result
1262 Select * from test.v1;
1263 Drop view test.v1 ;
1264
1265
1266 let $message= Testcase 3.3.1.32 ;
1267 --source include/show_msg80.inc
1268 ###############################################################################
1269 # Testcase 3.3.1.32: Ensure that a view can be based on a join of multiple
1270 # tables from another database.
1271 ###############################################################################
1272 --disable_warnings
1273 Drop table if exists test.t1 ;
1274 Drop database if exists test2 ;
1275 Drop view if exists test.v1 ;
1276 --enable_warnings
1277 create database test2 ;
1278 use test2 ;
1279 CREATE TABLE t1 ( f59 int, f60 int );
1280 INSERT INTO t1 VALUES( 34, 654 );
1281 INSERT INTO t1 VALUES( 906, 434 );
1282 INSERT INTO t1 VALUES( 445, 765 );
1283 CREATE VIEW test2.v1
1284 AS SELECT test.tb2.F59, test.tb2.F60
1285 FROM test.tb2 INNER JOIN test2.t1 ON tb2.f59 = t1.f59;
1286 --sorted_result
1287 Select * from test2.v1;
1288
1289 Use test;
1290
1291
1292 let $message= Testcase 3.3.1.33 ;
1293 --source include/show_msg80.inc
1294 ###############################################################################
1295 # Testcase 3.3.1.33: Ensure that a view can be based on a join of multiple
1296 # views within the same database.
1297 ###############################################################################
1298 --disable_warnings
1299 Drop view if exists test.v1_firstview ;
1300 Drop view if exists test.v1_secondview ;
1301 Drop view if exists test.v1 ;
1302 --enable_warnings
1303 CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;
1304 CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2;
1305 CREATE VIEW test.v1
1306 AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
1307 FROM test.v1_firstview INNER JOIN test.v1_secondview
1308 ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
1309 SELECT * FROM test.v1 order by f59,f60 limit 0,10;
1310 Drop view if exists test.v1_firstview ;
1311 Drop view if exists test.v1_secondview ;
1312 Drop view if exists test.v1 ;
1313
1314
1315 let $message= Testcase 3.3.1.34 ;
1316 --source include/show_msg80.inc
1317 ###############################################################################
1318 # Testcase 3.3.1.34: Ensure that a view can be based on a join of multiple
1319 # views from another database.
1320 ###############################################################################
1321 --disable_warnings
1322 Drop database if exists test2 ;
1323 Drop view if exists test.v1_firstview ;
1324 Drop view if exists test.v1_secondview ;
1325 --enable_warnings
1326
1327 create database test2 ;
1328 use test2 ;
1329 CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2 ;
1330 CREATE VIEW test.v1_secondview AS SELECT * FROM test.tb2 ;
1331
1332 CREATE VIEW v1
1333 AS SELECT test.v1_firstview.F59, test.v1_firstview.F60
1334 FROM test.v1_firstview INNER JOIN test.v1_secondview
1335 ON test.v1_firstview.f59 = test.v1_secondview.f59 ;
1336 SELECT * FROM v1 order by f59,f60 limit 0,10;
1337 Drop view v1 ;
1338 Drop view test.v1_firstview ;
1339 Drop view test.v1_secondview ;
1340
1341
1342 let $message= Testcase 3.3.1.35 ;
1343 --source include/show_msg80.inc
1344 ###############################################################################
1345 # Testcase 3.3.1.35: Ensure that a view can be based on a join of multiple
1346 # tables and/or views within the same database.
1347 ###############################################################################
1348 use test;
1349
1350 --disable_warnings
1351 Drop view if exists test.v1;
1352 Drop view if exists test.v1_firstview;
1353 --enable_warnings
1354
1355 CREATE VIEW test.v1_firstview AS SELECT * FROM test.tb2;
1356
1357 CREATE VIEW test.v1
1358 AS SELECT test.v1_firstview.f59, test.v1_firstview.f60
1359 FROM test.v1_firstview INNER JOIN test.tb2
1360 ON test.v1_firstview.f59 = test.tb2.f59;
1361 SELECT * FROM test.v1 order by f59,f60 limit 0,10;
1362 Drop view test.v1 ;
1363 Drop view test.v1_firstview;
1364
1365
1366 let $message= Testcase 3.3.1.36 ;
1367 --source include/show_msg80.inc
1368 ###############################################################################
1369 # Testcase 3.3.1.36: Ensure that a view can be based on a join of multiple
1370 # tables and/or views from another database.
1371 ###############################################################################
1372 --disable_warnings
1373 Drop database if exists test2 ;
1374 --enable_warnings
1375 create database test2 ;
1376 use test2 ;
1377
1378 CREATE VIEW v1_firstview AS SELECT * FROM test.tb2 ;
1379 CREATE VIEW v1
1380 AS SELECT v1_firstview.f59, v1_firstview.f60
1381 FROM v1_firstview INNER JOIN test.tb2 ON v1_firstview.f59 = test.tb2.f59 ;
1382 SELECT * FROM v1 order by f59,f60 limit 0,10;
1383
1384 Drop database test2 ;
1385
1386
1387 let $message= Testcase 3.3.1.37 ;
1388 --source include/show_msg80.inc
1389 ###############################################################################
1390 # Testcase 3.3.1.37: Ensure that a view can be based on a join of multiple
1391 # tables and/or views, some of which reside in the same
1392 # database and some of which reside in one other database.
1393 ###############################################################################
1394 use test;
1395 --disable_warnings
1396 Drop table if exists t1;
1397 Drop view if exists test.v1 ;
1398 Drop view if exists test.v1_1 ;
1399 Drop view if exists test.v1_1 ;
1400 Drop view if exists test.v1_main ;
1401 --enable_warnings
1402 Create view test.v1 as Select f59, f60 FROM test.tb2;
1403 Select * from test.v1 order by f59,f60 limit 0,10;
1404
1405 Create table t1(f59 int, f60 int);
1406 Insert into t1 values (90,507) ;
1407
1408 Create view v1_1 as Select f59,f60 from t1 ;
1409 Select * from v1_1 ;
1410
1411 Create view v1_main
1412 as SELECT test.tb2.f59 FROM test.tb2 JOIN test.v1
1413 ON test.tb2.f59 = test.v1.f59;
1414 Select * from v1_main order by f59 limit 0,10;
1415
1416 Drop table t1;
1417 Drop view test.v1 ;
1418 Drop view test.v1_1 ;
1419 Drop view test.v1_main ;
1420
1421
1422 let $message= Testcase 3.3.1.31 - 3.3.1.37 New Implementation ;
1423 --source include/show_msg80.inc
1424 ###############################################################################
1425 # mleich: The testcases 3.3.1.31 - 3.3.1.37 should be tested more systematic.
1426 # Ensure that a view can be based on a join of multiple
1427 # Testcase 3.3.1.31: tables within the same database
1428 # Testcase 3.3.1.32: tables from another database.
1429 # Testcase 3.3.1.33: views within the same database
1430 # Testcase 3.3.1.34: views from another database
1431 # Testcase 3.3.1.35: tables and/or views within the same database
1432 # Testcase 3.3.1.36: tables and/or views from another database
1433 # Testcase 3.3.1.37: tables and/or views, some of which reside in
1434 # the same database and some of which reside in
1435 # one other database.
1436 ###############################################################################
1437 USE test;
1438 --disable_warnings
1439 DROP DATABASE IF EXISTS test2;
1440 DROP TABLE IF EXISTS t0,t1;
1441 DROP VIEW IF EXISTS t3,t4;
1442 --enable_warnings
1443 CREATE DATABASE test2;
1444
1445 --disable_warnings
1446 CREATE TABLE test1.t0 (f1 VARCHAR(20));
1447 CREATE TABLE test1.t1 (f1 VARCHAR(20));
1448 --enable_warnings
1449 CREATE TABLE test2.t0 (f1 VARCHAR(20));
1450 CREATE TABLE test2.t1 (f1 VARCHAR(20));
1451 --disable_warnings
1452 CREATE VIEW test1.t2 AS SELECT * FROM test1.t0;
1453 CREATE VIEW test1.t3 AS SELECT * FROM test2.t0;
1454 --enable_warnings
1455 CREATE VIEW test2.t2 AS SELECT * FROM test2.t0;
1456 CREATE VIEW test2.t3 AS SELECT * FROM test1.t0;
1457 INSERT INTO test1.t0 VALUES('test1.t0');
1458 INSERT INTO test1.t1 VALUES('test1.t1');
1459 INSERT INTO test2.t0 VALUES('test2.t0');
1460 INSERT INTO test2.t1 VALUES('test2.t1');
1461
1462 # The extreme simple standard JOIN VIEW is:
1463 # CREATE OR REPLACE VIEW <database>.v1
1464 # AS SELECT * FROM <table or view 1>,<table or view 2>
1465 let $view= test.v1;
1466 let $tab1= test.t0;
1467 let $tab2= test.t1;
1468 # eval CREATE OR REPLACE VIEW $view AS SELECT * FROM $tab1, $tab2;
1469 # Produce at least all testcases via simple combinatorics, because it is better
1470 # to check some useless combinations than to forget an important one.
1471 let $view= test.v1;
1472 let $num_tab1= 3;
1473 while ($num_tab1)
1474 {
1475 let $num_tab2= 3;
1476 while ($num_tab2)
1477 {
1478 let $num_db1= 2;
1479 while ($num_db1)
1480 {
1481 let $num_db2= 2;
1482 while ($num_db2)
1483 {
1484 # Maybe somebody needs to check the generated values
1485 # --disable_query_log
1486 # eval SELECT '$num_db1.$num_tab1,$num_db2.$num_tab2';
1487 # --enable_query_log
1488 eval CREATE OR REPLACE VIEW $view AS
1489 SELECT ta.f1 AS col1,
1490 tb.f1 AS col2
1491 FROM test$num_db1.t$num_tab1 ta, test$num_db2.t$num_tab2 tb;
1492 eval SELECT * FROM $view;
1493
1494 dec $num_db2;
1495 }
1496
1497 dec $num_db1;
1498 }
1499
1500 dec $num_tab2;
1501 }
1502
1503 dec $num_tab1;
1504 }
1505
1506
1507 let $message= Testcase 3.3.1.38 ;
1508 --source include/show_msg80.inc
1509 ###############################################################################
1510 # Testcase 3.3.1.38: Ensure that a view can be based on a join of multiple
1511 # tables and/or views, some of which reside in the same
1512 # database and some of which reside two or more other
1513 # databases.
1514 ###############################################################################
1515 --disable_warnings
1516 Drop table if exists test1.t1 ;
1517 Drop view if exists test.v1 ;
1518 Drop view if exists test.v1_main;
1519 Drop view if exists test1.v1_1 ;
1520 Drop database if exists test3 ;
1521 --enable_warnings
1522 Create view test.v1 as Select f59, f60 FROM test.tb2;
1523 Select * from test.v1 order by f59,f60 limit 20;
1524
1525 Create table test1.t1 (f59 int,f60 int) ;
1526 Insert into test1.t1 values (199,507) ;
1527 Create view test1.v1_1 as Select f59,f60 from test1.t1 ;
1528 Select * from test1.v1_1 ;
1529
1530 --disable_warnings
1531 --enable_warnings
1532 Create database test3 ;
1533
1534 Create table test3.t1(f59 int,f60 int) ;
1535 Insert into test3.t1 values (1023,7670) ;
1536 Create view test3.v1_2 as Select f59,f60 from test3.t1 ;
1537 Select * from test3.v1_2 ;
1538 use test ;
1539
1540 # mleich: FIXME The SELECT should deliver at least one row.
1541 Create view v1_main
1542 as SELECT test.tb2.f59 as f1, test1.v1_1.f59 as f2,
1543 test3.v1_2.f59 as f3
1544 FROM (test.tb2,test1.v1_1,test.v1) JOIN test3.v1_2
1545 ON (test.v1.f59 = test1.v1_1.f59) ;
1546 Select * from v1_main ;
1547
1548 DROP VIEW test.v1 ;
1549 DROP VIEW test1.v1_1 ;
1550 DROP VIEW test.v1_main ;
1551 DROP DATABASE test3;
1552
1553
1554 let $message= Testcase 3.3.1.39 ;
1555 --source include/show_msg80.inc
1556 ###############################################################################
1557 # Testcase 3.3.1.39: Ensure that a view definition that includes a subquery in
1558 # a FROM clause is rejected with an appropriate error
1559 # message at create time.
1560 # Restriction lifted with bug#11865600
1561 ###############################################################################
1562 --disable_warnings
1563 Drop view if exists test.v1 ;
1564 --enable_warnings
1565 CREATE VIEW test.v1
1566 AS Select f59 from (Select * FROM tb2 limit 20) tx ;
1567 SELECT * FROM test.v1 order by f59 ;
1568 DROP VIEW test.v1 ;
1569
1570
1571 let $message= Testcase 3.3.1.40 ;
1572 --source include/show_msg80.inc
1573 ###############################################################################
1574 # Testcase 3.3.1.40: Ensure that a view definition that includes references to
1575 # one or more user variables is rejected with an appropriate
1576 # error message at create time.
1577 ###############################################################################
1578 --disable_warnings
1579 Drop view if exists test.v1 ;
1580 --enable_warnings
1581 Set @var1 = 'ABC' ;
1582 Set @var2 = 'XYZ' ;
1583 --error ER_VIEW_SELECT_VARIABLE
1584 CREATE VIEW test.v1 AS SELECT @var1, @var2 ;
1585 # System variables (name starts with '@@') are also not allowed
1586 --error ER_VIEW_SELECT_VARIABLE
1587 CREATE VIEW test.v1 AS SELECT @@global.sort_buffer_size;
1588 --disable_warnings
1589 Drop view if exists test.v1 ;
1590 --enable_warnings
1591
1592
1593 let $message= Testcase 3.3.1.41 ;
1594 --source include/show_msg80.inc
1595 ###############################################################################
1596 # Testcase 3.3.1.41: Ensure that a view definition within a stored procedure
1597 # definition cannot include references to any of the stored
1598 # procedures parameters.
1599 ###############################################################################
1600 --disable_warnings
1601 Drop view if exists test.v1 ;
1602 Drop procedure if exists sp1 ;
1603 --enable_warnings
1604
1605 delimiter //;
1606 Create procedure sp1() DETERMINISTIC
1607 Begin
1608 DECLARE x char;
1609 Set x = 200 ;
1610 Create view test.v1 as SELECT * FROM tb2 WHERE f59 = x ;
1611 End //
1612 delimiter ;//
1613 --error ER_SP_DOES_NOT_EXIST
1614 Call sp1() ;
1615 Drop view if exists test.v1 ;
1616 Drop procedure sp1 ;
1617
1618
1619 let $message= Testcase 3.3.1.42 ;
1620 --source include/show_msg80.inc
1621 ###############################################################################
1622 # Testcase 3.3.1.42: Ensure that a view definition that attempts to create a
1623 # temporary view (e.g. CREATE TEMPORARY VIEW or CREATE OR
1624 # REPLACE TEMPORARY VIEW) fails, with an appropriate
1625 # error message.
1626 ###############################################################################
1627 #(01)
1628 --disable_warnings
1629 Drop VIEW if exists test.v1 ;
1630 --enable_warnings
1631 --error ER_PARSE_ERROR
1632 CREATE TEMPORARY VIEW test.v1 AS
1633 SELECT * FROM test.tb2 limit 2 ;
1634 #(02)
1635 --error ER_PARSE_ERROR
1636 CREATE OR REPLACE TEMPORARY VIEW test.v1 AS
1637 SELECT * FROM test.tb2 limit 2 ;
1638
1639 --disable_warnings
1640 Drop view if exists test.v1 ;
1641 --enable_warnings
1642
1643 Use test;
1644
1645
1646 let $message= Testcase 3.3.1.43 ;
1647 --source include/show_msg80.inc
1648 ###############################################################################
1649 # Testcase 3.3.1.43: Ensure that all valid changes (i.e. INSERT, UPDATE, DELETE
1650 # statements) to a view are shown in the view and are
1651 # accepted as changes by the underlying table(s).
1652 ###############################################################################
1653 --disable_warnings
1654 Drop view if exists test.v1 ;
1655 --enable_warnings
1656
1657 CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2;
1658
1659 INSERT INTO test.v1 values(122,432);
1660
1661 if ($have_bug_32285)
1662 {
1663 --disable_ps_protocol
1664 }
1665 --vertical_results
1666 SELECT * FROM test.tb2 where f59 = 122 and f60 = 432 limit 0,20;
1667 --horizontal_results
1668 --enable_ps_protocol
1669
1670 UPDATE test.v1 SET f59 = 3000 WHERE test.v1.f59 = 122 ;
1671
1672 if ($have_bug_32285)
1673 {
1674 --disable_ps_protocol
1675 }
1676 --vertical_results
1677 SELECT * FROM test.tb2 where f59 = 3000 limit 0,20;
1678 --horizontal_results
1679 --enable_ps_protocol
1680
1681 DELETE FROM test.v1
1682 where test.v1.f59 = 3000 and test.v1.f60 = 432;
1683
1684 SELECT * FROM test.tb2 where f59 = 3000 and f60 = 432;
1685
1686 drop view test.v1 ;
1687
1688
1689 let $message= Testcase 3.3.1.44 ;
1690 --source include/show_msg80.inc
1691 ###############################################################################
1692 # Testcase 3.3.1.44: Ensure that all invalid changes to a view are rejected
1693 # with an appropriate error message and do not affect the
1694 # data in the underlying tables(s).
1695 ###############################################################################
1696 # mleich: Maybe we need some more tests here.
1697 --disable_warnings
1698 Drop view if exists test.v1 ;
1699 --enable_warnings
1700
1701 # Note(mleich): The modification will fail, because the VIEW contains 'limit'
1702 CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2 limit 100;
1703
1704 --error ER_NON_INSERTABLE_TABLE
1705 INSERT INTO test.v1 values(31, 32, 33) ;
1706
1707 Drop view test.v1 ;
1708
1709
1710 let $message= Testcase 3.3.1.45 ;
1711 --source include/show_msg80.inc
1712 ###############################################################################
1713 # Testcase 3.3.1.45: Ensure that, for a view with a definition that does not
1714 # include WITH CHECK OPTION, all changes to the view which
1715 # violate the view definition do not show in the view but
1716 # are accepted as changes by the underlying table(s) unless
1717 # a constraint on an underlying table also makes the change
1718 # invalid.
1719 ###############################################################################
1720 --disable_warnings
1721 Drop view if exists test.v1 ;
1722 --enable_warnings
1723 CREATE VIEW test.v1 AS SELECT * FROM test.tb2 where f59 = 04;
1724
1725 --enable_info
1726 UPDATE test.v1 SET f59 = 30 where F59 = 04 ;
1727 --disable_info
1728 SELECT * FROM test.v1 where f59 = 30 order by f59;
1729 if ($have_bug_32285)
1730 {
1731 --disable_ps_protocol
1732 }
1733 --vertical_results
1734 SELECT * FROM test.tb2 where f59 = 30 ;
1735 --horizontal_results
1736 --enable_ps_protocol
1737
1738 --enable_info
1739 UPDATE tb2 SET f59 = 100 where f59 = 30 ;
1740 --disable_info
1741 if ($have_bug_32285)
1742 {
1743 --disable_ps_protocol
1744 }
1745 --vertical_results
1746 SELECT * FROM tb2 where f59 = 100 ;
1747 --horizontal_results
1748 --enable_ps_protocol
1749 SELECT * FROM test.v1 order by f59 ;
1750
1751 drop view if exists test.v1 ;
1752
1753 --disable_warnings
1754 Drop TABLE IF EXISTS test.t1 ;
1755 Drop VIEW IF EXISTS test.v1 ;
1756 --enable_warnings
1757 eval CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), PRIMARY KEY(f1))
1758 ENGINE = $engine_type;
1759 INSERT INTO t1 VALUES(1,'one');
1760 INSERT INTO t1 VALUES(2,'two');
1761 INSERT INTO t1 VALUES(3,'three');
1762 INSERT INTO t1 VALUES(5,'five');
1763 CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 BETWEEN 2 AND 4;
1764
1765 ### SELECTs
1766 # 1. Searched record is within the scope of the view
1767 # 1.1 + exists within the base table
1768 SELECT COUNT(*) FROM v1 WHERE f1 = 2;
1769 # 1.2 + does not exists within the base table
1770 SELECT COUNT(*) FROM v1 WHERE f1 = 4;
1771 # 2. Searched record is outside of the scope of the view
1772 # 2.1 + exists within the base table
1773 SELECT COUNT(*) FROM v1 WHERE f1 = 5;
1774 # 2.2 + does not exists within the base table
1775 SELECT COUNT(*) FROM v1 WHERE f1 = 10;
1776
1777 INSERT INTO t1 VALUES(4,'four');
1778
1779 ### DELETEs
1780 --enable_info
1781 # 1. Searched record is within the scope of the view
1782 # + exists within the base table
1783 DELETE FROM v1 WHERE f1 = 3;
1784 # 2. Searched record is outside of the scope of the view
1785 # + exists within the base table
1786 DELETE FROM v1 WHERE f1 = 5;
1787 --disable_info
1788 SELECT * FROM t1 ORDER BY f1;
1789 SELECT * FROM v1 ORDER BY f1;
1790
1791 ### INSERTs
1792 --enable_info
1793 # 1. The record to be inserted will be within the scope of the view.
1794 # But there is already a record with the PRIMARY KEY f1 = 2 .
1795 # OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
1796 --error ER_DUP_ENTRY
1797 INSERT INTO v1 VALUES(2,'two');
1798 # 2. The record to be inserted will be within the scope of the view.
1799 # There is no already existing record with the PRIMARY KEY f1 = 3 .
1800 INSERT INTO v1 VALUES(3,'three');
1801 # 3. The record to be inserted will be outside of the scope of the view.
1802 # There is no already existing record with the PRIMARY KEY f1 = 6 .
1803 INSERT INTO v1 VALUES(6,'six');
1804 --disable_info
1805 SELECT * FROM t1 ORDER BY f1;
1806 SELECT * FROM v1 ORDER BY f1;
1807
1808 ### UPDATEs
1809 --enable_info
1810 # 1. The record to be updated is within the scope of the view
1811 # and will stay inside the scope.
1812 # But there is already a record with the PRIMARY KEY f1 = 2 .
1813 # OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
1814 --error ER_DUP_ENTRY
1815 UPDATE v1 SET f1 = 2 WHERE f1 = 3;
1816 # 2. The record to be updated is within the scope of the view
1817 # and will stay inside the scope.
1818 UPDATE v1 SET f2 = 'number' WHERE f1 = 3;
1819 # 3. The record to be updated is within the scope of the view
1820 # and will leave the scope.
1821 UPDATE v1 SET f1 = 10 WHERE f1 = 3;
1822 # 4. The record to be updated is outside of the scope of the view.
1823 UPDATE v1 SET f2 = 'number' WHERE f1 = 1;
1824 --disable_info
1825
1826
1827 let $message= Testcase 3.3.1.46 ;
1828 --source include/show_msg80.inc
1829 ###############################################################################
1830 # Testcase 3.3.1.46: Ensure that, for a view with a definition that does
1831 # include WITH CHECK OPTION, all changes to the view which
1832 # violate the view definition are rejected with an
1833 # appropriate error message and are not accepted as changes
1834 # by the underlying table(s).
1835 ###############################################################################
1836 --disable_warnings
1837 Drop view if exists test.v1 ;
1838 --enable_warnings
1839 CREATE VIEW test.v1 AS SELECT f59,f60
1840 FROM test.tb2 where f59 = 195 WITH CHECK OPTION ;
1841
1842 --error ER_VIEW_CHECK_FAILED
1843 UPDATE test.v1 SET f59 = 198 where f59=195 ;
1844 SELECT * FROM test.v1 order by f59 ;
1845
1846 drop view if exists test.v1 ;
1847
1848
1849 let $message= Testcase 3.3.1.47 ;
1850 --source include/show_msg80.inc
1851 ###############################################################################
1852 # Testcase 3.3.1.47: Ensure that, for a view with a definition that does
1853 # include WITH LOCAL CHECK OPTION, all changes to the view
1854 # which violate the view definition are rejected with an
1855 # appropriate error message and are not accepted as changes
1856 # by the underlying table(s).
1857 ###############################################################################
1858 --disable_warnings
1859 Drop view if exists test.v1 ;
1860 Drop view if exists test.v2 ;
1861 --enable_warnings
1862 CREATE VIEW test.v1 AS SELECT f59,f60
1863 FROM test.tb2 where F59 = 0987 WITH LOCAL CHECK OPTION ;
1864 CREATE VIEW test.v2 as SELECT * FROM test.v1 ;
1865
1866 # This UPDATE violates the definition of VIEW test.v1.
1867 --error ER_VIEW_CHECK_FAILED
1868 UPDATE test.v1 SET F59 = 919 where f59 = 0987 ;
1869 SELECT * FROM test.v1 order by f59 ;
1870
1871 # mleich: This UPDATE violates the definition of VIEW test.v1, but this
1872 # does not count, because the UPDATE runs on test.v2, which
1873 # is defined without any CHECK OPTION.
1874 # FIXME Does this testcase fit to 3.3.1.47 ?
1875 UPDATE test.v2 SET F59 = 9879 where f59 = 919 ;
1876 SELECT * FROM tb2 where f59 = 9879 ;
1877
1878 drop view if exists v1 ;
1879 drop view if exists v2 ;
1880
1881
1882 let $message= Testcase 3.3.1.48 ;
1883 --source include/show_msg80.inc
1884 ###############################################################################
1885 # Testcase 3.3.1.48: Ensure that, for a view with a definition that does
1886 # include WITH CASCADED CHECK OPTION, all changes to the
1887 # view which violate the view definition are rejected with
1888 # an appropriate error message and are not accepted as
1889 # changes by the underlying table(s).
1890 ###############################################################################
1891 --disable_warnings
1892 DROP TABLE IF EXISTS test.t1;
1893 DROP VIEW IF EXISTS test.v1;
1894 --enable_warnings
1895 eval CREATE TABLE t1 (f1 ENUM('A', 'B', 'C') NOT NULL, f2 INTEGER)
1896 ENGINE = $engine_type;
1897 INSERT INTO t1 VALUES ('A', 1);
1898 SELECT * FROM t1 order by f1, f2;
1899
1900 CREATE VIEW v1 AS SELECT * FROM t1 WHERE f2 BETWEEN 1 AND 2
1901 WITH CASCADED CHECK OPTION ;
1902 SELECT * FROM v1 order by f1, f2;
1903 --enable_info
1904 # positive cases
1905 UPDATE v1 SET f2 = 2 WHERE f2 = 1;
1906 INSERT INTO v1 VALUES('B',2);
1907 --disable_info
1908 # Bug#11771: View over InnoDB table, wrong result SELECT on VIEW,
1909 # field->query_id wrong
1910 SELECT * FROM v1 order by f1, f2;
1911 # negative cases
1912 --enable_info
1913 --error ER_VIEW_CHECK_FAILED
1914 UPDATE v1 SET f2 = 4;
1915 --error ER_VIEW_CHECK_FAILED
1916 INSERT INTO v1 VALUES('B',3);
1917 --disable_info
1918 # Bug#11771: View over InnoDB table, wrong result SELECT on VIEW,
1919 # field->query_id wrong
1920 SELECT * FROM v1 order by f1, f2;
1921
1922
1923 let $message= Testcase 3.3.1.49 ;
1924 --source include/show_msg80.inc
1925 ###############################################################################
1926 # Testcase 3.3.1.49: Ensure that the WITH [LOCAL | CASCADED] CHECK OPTION
1927 # constraint is always correctly performed within the
1928 # correct scope, including in cases where a view is based
1929 # upon multiple other views whose definitions include every
1930 # possible combination of the WITH CHECK OPTION variants.
1931 ###############################################################################
1932 --disable_warnings
1933 Drop table if exists test.t1 ;
1934 Drop view if exists test.v1 ;
1935 Drop view if exists test.v2 ;
1936 Drop view if exists test.v3 ;
1937 --enable_warnings
1938 Create table test.t1 (f59 INT, f60 INT) ;
1939
1940 Insert into test.t1 values (100,4234);
1941 Insert into test.t1 values (290,6624);
1942 Insert into test.t1 values (410,765);
1943 Insert into test.t1 values (300,433334);
1944 Insert into test.t1 values (800,9788);
1945 Insert into test.t1 values (501,9866);
1946
1947 Create view test.v1 as select f59
1948 FROM test.t1 where f59<500 with check option ;
1949
1950 Create view test.v2 as select *
1951 from test.v1 where f59>0 with local check option ;
1952
1953 --disable_warnings
1954 --enable_warnings
1955
1956 Create view test.v3 as select *
1957 from test.v1 where f59>0 with cascaded check option ;
1958
1959 Insert into test.v2 values(23) ;
1960 Insert into test.v3 values(24) ;
1961
1962 drop view if exists test.v1 ;
1963 drop view if exists test.v2 ;
1964 drop view if exists test.v3 ;
1965
1966 let $message= Testcase 3.3.1.49A ;
1967 --source include/show_msg80.inc
1968 # Testplan:
1969 # -----------------------------------------------------------
1970 # VIEW v1 is based on table t1 (*)
1971 # VIEW v2 is based on view v1 (*)
1972 # VIEW v3 is based on view v2 (*)
1973 #
1974 # (*) All variants like
1975 # - without check option
1976 # - WITH CASCADED CHECK OPTION
1977 # - WITH CHECK OPTION (default = CASCADED)
1978 # - WITH LOCAL CHECK OPTION
1979 #
1980 # The rules for updating and inserting column values:
1981 # 1. Top VIEW WITH CASCADED CHECK OPTION
1982 # --> The WHERE qualifications of all nested VIEWs have to be fulfilled.
1983 # The CHECK OPTIONS of underlying VIEWs have no effect.
1984 # 2. Top VIEW WITH LOCAL CHECK OPTION
1985 # --> The WHERE qualification of this VIEW has to be fulfilled.
1986 # The CHECK OPTIONS of underlying VIEWs have effect.
1987 # 3. Top VIEW without any CHECK OPTION
1988 # --> The WHERE qualification of this VIEW need not be fulfilled.
1989 # The CHECK OPTIONS of underlying VIEWs have effect.
1990 #
1991 # Note: The CHECK OPTION does not influence the retrieval of rows
1992 # (SELECT/DELETE/UPDATE). All WHERE qualifications will be applied
1993 # for the retrieval of rows.
1994 #
1995 # The annoying redundant
1996 # eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
1997 # @v3_to_v1_violation,$mysql_errno);
1998 # could not be put into a file to be sourced because of the closed
1999 # Bug#10267 mysqltest, wrong number of loops when a script is sourced
2000 # within a loop
2001 # To be implemented later.
2002
2003 USE test;
2004 --disable_warnings
2005 DROP TABLE IF EXISTS test.t1 ;
2006 DROP TABLE IF EXISTS test.t1_results ;
2007 DROP VIEW IF EXISTS test.v1;
2008 DROP VIEW IF EXISTS test.v2;
2009 DROP VIEW IF EXISTS test.v3;
2010 --enable_warnings
2011 CREATE TABLE t1 (f1 INTEGER, f2 CHAR(20));
2012 CREATE TABLE t1_results (v3_to_v1_options VARCHAR(100), statement VARCHAR(10),
2013 v3_to_v1_violation VARCHAR(20), errno CHAR(10));
2014 --disable_query_log
2015 SET @part1= '';
2016 SET @part2= 'WITH CHECK OPTION';
2017 SET @part3= 'WITH CASCADED CHECK OPTION';
2018 SET @part4= 'WITH LOCAL CHECK OPTION';
2019 --enable_query_log
2020
2021 let $num1= 4;
2022 while ($num1)
2023 {
2024 --disable_query_log
2025 eval SET @v1_part= @part$num1;
2026 let $aux= `SELECT CONCAT('CREATE VIEW v1 AS SELECT f1, f2
2027 FROM t1 WHERE f1 BETWEEN 0 AND 10 ', @v1_part)` ;
2028 --enable_query_log
2029 eval $aux ;
2030
2031 let $num2= 4;
2032 while ($num2)
2033 {
2034 --disable_query_log
2035 eval SET @v2_part= @part$num2;
2036 let $aux= `SELECT CONCAT('CREATE VIEW v2 AS SELECT f1 AS col1, f2 AS col2
2037 FROM v1 WHERE f1 BETWEEN 6 AND 16 ', @v2_part)` ;
2038 --enable_query_log
2039 eval $aux ;
2040
2041 let $num3= 4;
2042 while ($num3)
2043 {
2044 --disable_query_log
2045 eval SET @v3_part= @part$num3;
2046 let $aux= `SELECT CONCAT('CREATE VIEW v3 (my_col1,my_col2) AS SELECT *
2047 FROM v2 WHERE col1 MOD 2 = 0 ', @v3_part)` ;
2048 --enable_query_log
2049 eval $aux ;
2050 --disable_query_log
2051 --vertical_results
2052 # Present CHECK OPTION clauses from v3 to v1:
2053 SELECT CONCAT(IF(@v3_part = '',' <nothing> ',
2054 @v3_part), ' - ',
2055 IF(@v2_part = '',' <nothing> ',
2056 @v2_part), ' - ',
2057 IF(@v1_part = '',' <nothing> ',
2058 @v1_part))
2059 AS "option_variant"
2060 UNION SELECT RPAD('', 80, '-');
2061 SET @v3_to_v1_options = CONCAT(IF(@v3_part = '',' <nothing> ',
2062 @v3_part), ' - ',
2063 IF(@v2_part = '',' <nothing> ',
2064 @v2_part), ' - ',
2065 IF(@v1_part = '',' <nothing> ',
2066 @v1_part));
2067 --horizontal_results
2068 --enable_query_log
2069 # 1. Visibility of records of t1 via SELECT on the VIEWs
2070 # Outside v1 (0 to 10)
2071 INSERT INTO t1 VALUES(16, 'sixteen');
2072 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2073 INSERT INTO t1 VALUES(0, 'zero');
2074 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2075 # Outside v3 ( value MOD 2 = 0 )
2076 INSERT INTO t1 VALUES(7, 'seven');
2077 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2078 # Inside v3 ( value MOD 2 = 0 )
2079 INSERT INTO t1 VALUES(8, 'eight');
2080 SELECT * FROM v1 ORDER BY 1;
2081 SELECT * FROM v2 ORDER BY 1;
2082 SELECT * FROM v3 ORDER BY 1;
2083 SELECT * FROM t1 ORDER BY 1;
2084 DELETE FROM t1;
2085 # 2. DELETEs within v3
2086 # Outside v1 (0 to 10)
2087 INSERT INTO t1 VALUES(16, 'sixteen');
2088 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2089 INSERT INTO t1 VALUES(0, 'zero');
2090 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2091 # Outside v3 ( value MOD 2 = 0 )
2092 INSERT INTO t1 VALUES(7, 'seven');
2093 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2094 # Inside v3 ( value MOD 2 = 0 )
2095 INSERT INTO t1 VALUES(8, 'eight');
2096 --enable_info
2097 # Outside v1 (0 to 10)
2098 DELETE FROM v3 WHERE my_col1 = 16;
2099 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2100 DELETE FROM v3 WHERE my_col1 = 0;
2101 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2102 # Outside v3 ( value MOD 2 = 0 )
2103 DELETE FROM v3 WHERE my_col1 = 7;
2104 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2105 # Inside v3 ( value MOD 2 = 0 )
2106 DELETE FROM v3 WHERE my_col1 = 8;
2107 --disable_info
2108 SELECT * FROM t1 ORDER BY 1;
2109 DELETE FROM t1;
2110 # 3. UPDATEs within v3 (modify my_col2, which is not part of any
2111 # WHERE qualification)
2112 # The behaviour should be similar to 3. DELETE.
2113 # Outside v1 (0 to 10)
2114 INSERT INTO t1 VALUES(16, 'sixteen');
2115 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2116 INSERT INTO t1 VALUES(0, 'zero');
2117 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2118 # Outside v3 ( value MOD 2 = 0 )
2119 INSERT INTO t1 VALUES(7, 'seven');
2120 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2121 # Inside v3 ( value MOD 2 = 0 )
2122 INSERT INTO t1 VALUES(8, 'eight');
2123 --enable_info
2124 # Outside v1 (0 to 10)
2125 UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 16;
2126 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2127 UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 0;
2128 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2129 # Outside v3 ( value MOD 2 = 0 )
2130 UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 7;
2131 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2132 # Inside v3 ( value MOD 2 = 0 )
2133 UPDATE v3 SET my_col2 = 'whatever' WHERE my_col1 = 8;
2134 --disable_info
2135 SELECT * FROM t1 ORDER BY 1;
2136 DELETE FROM t1;
2137 # 4. UPDATEs within v3 (modify my_col1 to values inside and outside
2138 # of the WHERE qualifications)
2139 --disable_query_log
2140 SET @statement = 'UPDATE';
2141 --enable_query_log
2142 INSERT INTO t1 VALUES(8, 'eight');
2143 # Alter to value outside of v3
2144 --disable_query_log
2145 SET @v3_to_v1_violation = 'v3_ _ ';
2146 --enable_query_log
2147 --enable_info
2148 UPDATE v3 SET my_col1 = 7 WHERE my_col1 = 8;
2149 --disable_info
2150 --disable_query_log
2151 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2152 @v3_to_v1_violation,$mysql_errno);
2153 --enable_query_log
2154 SELECT * FROM t1 ORDER BY 1;
2155 DELETE FROM t1;
2156 INSERT INTO t1 VALUES(8, 'eight');
2157 # Alter to value outside of v2
2158 --disable_query_log
2159 SET @v3_to_v1_violation = ' _v2_ ';
2160 --enable_query_log
2161 --enable_info
2162 UPDATE v3 SET my_col1 = 0 WHERE my_col1 = 8;
2163 --disable_info
2164 --disable_query_log
2165 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2166 @v3_to_v1_violation,$mysql_errno);
2167 --enable_query_log
2168 SELECT * FROM t1 ORDER BY 1;
2169 DELETE FROM t1;
2170 INSERT INTO t1 VALUES(8, 'eight');
2171 # Alter to value outside of v1
2172 --disable_query_log
2173 SET @v3_to_v1_violation = ' _ _v1';
2174 --enable_query_log
2175 --enable_info
2176 UPDATE v3 SET my_col1 = 16 WHERE my_col1 = 8;
2177 --disable_info
2178 --disable_query_log
2179 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2180 @v3_to_v1_violation,$mysql_errno);
2181 --enable_query_log
2182 SELECT * FROM t1 ORDER BY 1;
2183 DELETE FROM t1;
2184 INSERT INTO t1 VALUES(8, 'eight');
2185 # Alter to value inside of v1
2186 --disable_query_log
2187 SET @v3_to_v1_violation = ' _ _ ';
2188 --enable_query_log
2189 --enable_info
2190 UPDATE v3 SET my_col1 = 10 WHERE my_col1 = 8;
2191 --disable_info
2192 --disable_query_log
2193 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2194 @v3_to_v1_violation,$mysql_errno);
2195 --enable_query_log
2196 SELECT * FROM t1 ORDER BY 1;
2197 DELETE FROM t1;
2198 # 5. INSERTs into v3
2199 --disable_query_log
2200 SET @statement = 'INSERT';
2201 --enable_query_log
2202 # Outside v1 (0 to 10)
2203 --disable_query_log
2204 SET @v3_to_v1_violation = ' _ _v1';
2205 --enable_query_log
2206 --enable_info
2207 INSERT INTO v3 VALUES(16, 'sixteen');
2208 --disable_info
2209 --disable_query_log
2210 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2211 @v3_to_v1_violation,$mysql_errno);
2212 --enable_query_log
2213 # Inside v1 (0 to 10), Outside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2214 --disable_query_log
2215 SET @v3_to_v1_violation = ' _v2_ ';
2216 --enable_query_log
2217 --enable_info
2218 INSERT INTO v3 VALUES(0, 'zero');
2219 --disable_info
2220 --disable_query_log
2221 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2222 @v3_to_v1_violation,$mysql_errno);
2223 --enable_query_log
2224 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2225 # Outside v3 ( value MOD 2 = 0 )
2226 --disable_query_log
2227 SET @v3_to_v1_violation = 'v3_ _ ';
2228 --enable_query_log
2229 --enable_info
2230 INSERT INTO v3 VALUES(7, 'seven');
2231 --disable_info
2232 # Inside v1 (0 to 10), Inside v2 ((0 to 10) AND (6 to 16) -> (6 to 10))
2233 # Inside v3 ( value MOD 2 = 0 )
2234 --disable_query_log
2235 SET @v3_to_v1_violation = ' _ _ ';
2236 --enable_query_log
2237 --enable_info
2238 INSERT INTO v3 VALUES(8, 'eight');
2239 --disable_info
2240 --disable_query_log
2241 eval INSERT INTO t1_results VALUES (@v3_to_v1_options,@statement,
2242 @v3_to_v1_violation,$mysql_errno);
2243 --enable_query_log
2244 SELECT * FROM t1 ORDER BY 1;
2245 DELETE FROM t1;
2246
2247 DROP VIEW v3;
2248 dec $num3;
2249 }
2250
2251 DROP VIEW v2;
2252 dec $num2;
2253 }
2254
2255 DROP VIEW v1;
2256 dec $num1;
2257 }
2258
2259 SELECT * FROM t1_results ORDER BY v3_to_v1_options, statement, v3_to_v1_violation, errno;
2260
2261 let $message=
2262 Plausibility checks for INSERTs and UPDATEs ( 4. and 5. above). ;
2263 --source include/show_msg80.inc
2264
2265 # Plausibility checks for INSERTs and UPDATEs ( 4. and 5. above):
2266 # 1. There must be NO denied INSERT/UPDATE, when no WHERE qualification
2267 # is violated. Expect ROW NOT FOUND
2268 SELECT COUNT(*) FROM t1_results
2269 WHERE v3_to_v1_violation = ' _ _ ' AND errno <> 0
2270 ORDER BY v3_to_v1_options;
2271 # 2. There must be denied INSERT/UPDATE, if any CHECK OPTION is violated.
2272 SELECT COUNT(*) FROM t1_results
2273 WHERE v3_to_v1_options LIKE ' %' AND errno <> 0
2274 ORDER BY v3_to_v1_options;
2275 # 3. There must be NO successful INSERT/UPDATE, when the toplevel VIEW v3 is
2276 # defined with any CHECK OPTION and the WHERE qualification of this VIEW is
2277 # violated. Expect ROW NOT FOUND
2278 SELECT COUNT(*) FROM t1_results
2279 WHERE v3_to_v1_options LIKE 'WITH %'
2280 AND v3_to_v1_violation LIKE 'v3_%' AND errno = 0
2281 ORDER BY v3_to_v1_options;
2282 # 4. There must be NO successful INSERT/UPDATE, when the toplevel VIEW v3 is
2283 # defined with any CHECK OPTION and the CHECK OPTION of any VIEW is
2284 # violated. Expect ROW NOT FOUND
2285 SELECT COUNT(*) FROM t1_results
2286 WHERE v3_to_v1_options LIKE 'WITH %' AND v3_to_v1_options NOT LIKE 'WITH LOCAL %'
2287 AND v3_to_v1_violation NOT LIKE ' _ _ ' AND errno = 0
2288 ORDER BY v3_to_v1_options;
2289 # 5. There must be NO failing INSERT/UPDATE getting a
2290 # sql_errno <> 1369 (ER_VIEW_CHECK_FAILED).
2291 SELECT COUNT(*) FROM t1_results
2292 WHERE errno <> 0 AND errno <> 1369
2293 ORDER BY v3_to_v1_options;
2294 let $message= End of plausibility checks;
2295 --source include/show_msg80.inc
2296
2297 DROP TABLE t1_results;
2298
2299
2300 let $message= Testcase 3.3.1.50 - 3.3.1.53;
2301 --source include/show_msg80.inc
2302 --disable_warnings
2303 DROP VIEW IF EXISTS test.v1;
2304 --enable_warnings
2305 ###############################################################################
2306 # Testcase 3.3.1.50: Ensure that a view that is a subset of every column and
2307 # every row of a single underlying table, contains the
2308 # correct row-and-column data; such a view has a definition
2309 # that is semantically equivalent to CREATE VIEW <view name>
2310 # AS SELECT * FROM <table name>.
2311 ###############################################################################
2312 CREATE VIEW test.v1 AS SELECT * FROM test.tb2;
2313 if ($have_bug_32285)
2314 {
2315 --disable_ps_protocol
2316 }
2317 --vertical_results
2318 SELECT * FROM test.v1 order by f59,f60,f61 ;
2319 --horizontal_results
2320 --enable_ps_protocol
2321 drop view test.v1 ;
2322 ###############################################################################
2323 # Testcase 3.3.1.51: Ensure that a view that is a subset of only some columns
2324 # and every row of a single underlying table, contains the
2325 # correct row-and-column data; such a view has a definition
2326 # that is semantically equivalent to CREATE VIEW <view name>
2327 # AS SELECT col1, col3 FROM <table name>.
2328 ###############################################################################
2329 CREATE VIEW test.v1 AS SELECT F59,F61 FROM test.tb2;
2330 SELECT * FROM test.v1 order by F59, F61 limit 50;
2331 drop view test.v1 ;
2332 ###############################################################################
2333 # Testcase 3.3.1.52: Ensure that a view that is a subset of every column and
2334 # some rows of a single underlying table, contains the
2335 # correct row-and-column data; such a view has a definition
2336 # that is semantically equivalent to CREATE VIEW <view name>
2337 # AS SELECT * FROM <table name> WHERE ....
2338 ###############################################################################
2339 CREATE VIEW test.v1 AS SELECT * FROM test.tb2 order by f59, f60, f61;
2340 if ($have_bug_11589)
2341 {
2342 --disable_ps_protocol
2343 }
2344 --vertical_results
2345 SELECT * FROM test.v1 order by f59,f60,f61 ;
2346 --horizontal_results
2347 --enable_ps_protocol
2348 drop view test.v1 ;
2349 ###############################################################################
2350 # Testcase 3.3.1.53: Ensure that a view that is a subset of only some columns
2351 # and some rows of a single underlying table, contains
2352 # the correct row-and-column data; such a view has a
2353 # definition that is semantically equivalent to CREATE VIEW
2354 # <view name> AS SELECT col1, col3 FROM <table name> WHERE ..
2355 ###############################################################################
2356 CREATE VIEW test.v1 AS SELECT F59,f61 FROM test.tb2;
2357 SELECT * FROM test.v1 order by f59,f61 desc limit 20;
2358 drop view test.v1 ;
2359
2360
2361 let $message= Testcase 3.3.1.54 ;
2362 --source include/show_msg80.inc
2363 ###############################################################################
2364 # Testcase 3.3.1.54: Ensure that a view that is a subset of some or all columns
2365 # and/or column expressions and some or all rows of a single
2366 # underlying table contains the correct row-and-column data.
2367 ###############################################################################
2368 USE test;
2369 --disable_warnings
2370 drop table if exists test.t1 ;
2371 drop table if exists test.t2 ;
2372 drop view if exists test.v1 ;
2373 --enable_warnings
2374 Create table t1 (f59 int, f60 int) ;
2375 Create table t2 (f59 int, f60 int) ;
2376
2377 Insert into t1 values (1,10) ;
2378 Insert into t1 values (2,20) ;
2379 Insert into t1 values (47,80) ;
2380 Insert into t2 values (1,1000) ;
2381 Insert into t2 values (2,2000) ;
2382 Insert into t2 values (31,97) ;
2383 Create view test.v1 as select t1.f59, t1.f60
2384 from t1,t2 where t1.f59=t2.f59 ;
2385 Select * from test.v1 order by f59 limit 50 ;
2386
2387 drop table test.t1 ;
2388 drop table test.t2 ;
2389 drop view test.v1 ;
2390
2391
2392 # FIXME(mleich): Implement an automatic check for 3.3.1.50 - 3.3.1.54
2393 # CREATE VIEW ... AS <SELECT ... FROM tb2 ...>
2394 # CREATE TEMPORARY TABLE ... AS <SELECT ... FROM tb2 ...>
2395 # Comparison of the VIEW with the temporary table
2396
2397 let $message= Testcase 3.3.1.50 - 3.3.1.54 additional implementation;
2398 --source include/show_msg80.inc
2399 --disable_warnings
2400 DROP TABLE IF EXISTS t1 ;
2401 DROP VIEW IF EXISTS v1 ;
2402 --enable_warnings
2403 #
2404 #
2405 # Testplan
2406 # ------------------------
2407 #
2408 # Testcase | all columns | all rows | column expressions
2409 # ---------------------------------------------------
2410 # 3.3.1.50 | yes | yes | no
2411 # 3.3.1.51 | no | yes | no
2412 # 3.3.1.52 | yes | no | no
2413 # 3.3.1.53 | no | no | no
2414 # 3.3.1.54 | no | no | yes
2415 CREATE TABLE t1 ( f1 BIGINT, f2 char(10), f3 DECIMAL(10,5) );
2416 INSERT INTO t1 VALUES(1, 'one', 1.1);
2417 INSERT INTO t1 VALUES(2, 'two', 2.2);
2418 INSERT INTO t1 VALUES(3, 'three', 3.3);
2419 # 3.3.1.50
2420 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1;
2421 SELECT * FROM v1 ORDER BY 1;
2422 # 3.3.1.51
2423 CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t1;
2424 SELECT * FROM v1 ORDER BY 1;
2425 # 3.3.1.52
2426 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE f3 = 2.2;
2427 SELECT * FROM v1 ORDER BY 1;
2428 # 3.3.1.53
2429 CREATE OR REPLACE VIEW v1 AS SELECT f2 FROM t1 WHERE f3 = 2.2;
2430 SELECT * FROM v1 ORDER BY 1;
2431 # 3.3.1.54
2432 --vertical_results
2433 SET sql_mode = 'traditional,ansi';
2434 # due to bug#32496 "no trailing blanks in identifier".
2435 CREATE OR REPLACE VIEW v1 AS
2436 SELECT f3 AS "pure column f3:", f1 + f3 AS "sum of columns f1 + f3 =",
2437 3 * (- 0.11111E+1) AS "product of constants 3 * (- 0.11111E+1):",
2438 '->' || CAST(f3 AS CHAR) || '<-'
2439 AS "expression with '||'=CONCAT and CAST(DECIMAL column AS CHAR):"
2440 FROM t1 WHERE f1 = 2;
2441 # This error is not conformant with ansi (see bug#32496). hhunger
2442 --error ER_WRONG_COLUMN_NAME
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 SELECT * FROM v1 ORDER BY 1;
2450 SET sql_mode = '';
2451 --horizontal_results
2452
2453
2454 let $message= Testcases 3.3.1.55 - 3.3.1.62 ;
2455 --source include/show_msg80.inc
2456 ###############################################################################
2457 # Testcase: Ensure that a view that is a subset of some or all columns and
2458 # some or all rows of multiple tables joined with an
2459 # 3.3.1.55 INNER JOIN
2460 # 3.3.1.56 CROSS JOIN
2461 # 3.3.1.57 STRAIGHT JOIN
2462 # 3.3.1.58 NATURAL JOIN
2463 # 3.3.1.59 LEFT OUTER JOIN
2464 # 3.3.1.60 NATURAL LEFT OUTER JOIN
2465 # 3.3.1.61 RIGHT OUTER
2466 # 3.3.1.62 NATURAL RIGHT OUTER
2467 # condition contains the correct row-and-column data.
2468 ###############################################################################
2469 --disable_warnings
2470 Drop table if exists t1, t2 ;
2471 Drop view if exists v1 ;
2472 --enable_warnings
2473 Create table t1 (f59 int, f60 char(10), f61 int, a char(1)) ;
2474 Insert into t1 values (1, 'single', 3, '1') ;
2475 Insert into t1 values (2, 'double', 6, '2') ;
2476 Insert into t1 values (3, 'single-f3', 4, '3') ;
2477
2478 Create table t2 (f59 int, f60 char(10), f61 int, b char(1)) ;
2479 Insert into t2 values (2, 'double', 6, '2') ;
2480 Insert into t2 values (3, 'single-f3', 6, '3') ;
2481 Insert into t2 values (4, 'single', 4, '4') ;
2482
2483 -- disable_query_log
2484 -- disable_result_log
2485 analyze table t1;
2486 analyze table t2;
2487 -- enable_result_log
2488 -- enable_query_log
2489
2490 # Testcase 3.3.1.55 ;
2491 create or replace view test.v1 as
2492 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2493 t1.f61 t1_f61, t2.f61 t2_f61
2494 from t1 inner join t2 where t1.f59 = t2.f59 ;
2495 select * from test.v1 order by t1_f59 ;
2496 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2497 t1.f61 t1_f61, t2.f61 t2_f61
2498 from t1 inner join t2 where t1.f59 = t2.f59;
2499
2500 # Testcase 3.3.1.56 ;
2501 Create or replace view test.v1 as
2502 Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2503 FROM t2 cross join t1;
2504 Select * from v1 order by t1_f59,t2_f59;
2505 --sorted_result
2506 Select t1.f59 AS t1_f59, t2.f59 AS t2_f59
2507 FROM t2 cross join t1;
2508
2509 # Testcase 3.3.1.57 ;
2510 Create or replace view test.v1 as
2511 Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2512 FROM t2,t1;
2513 Select * from v1 order by t1_f59,t2_f59;
2514 --sorted_result
2515 Select straight_join t1.f59 AS t1_f59, t2.f59 AS t2_f59
2516 FROM t2,t1;
2517
2518 # Testcase 3.3.1.58 ;
2519 Create or replace view test.v1 as
2520 Select f59, f60, f61, a, b
2521 FROM t2 natural join t1;
2522 Select * from v1 order by f59;
2523 Select f59, f60, f61, a, b
2524 FROM t2 natural join t1;
2525
2526 # Testcase 3.3.1.59 ;
2527 Create or replace view test.v1 as
2528 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2529 t1.f61 t1_f61, t2.f61 t2_f61
2530 FROM t2 left outer join t1 on t2.f59=t1.f59;
2531 Select * from v1 order by t1_f59;
2532 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2533 t1.f61 t1_f61, t2.f61 t2_f61
2534 FROM t2 left outer join t1 on t2.f59=t1.f59;
2535
2536 # Testcase 3.3.1.60 ;
2537 Create or replace view test.v1 as
2538 Select f59, f60, f61, t1.a, t2.b
2539 FROM t2 natural left outer join t1;
2540 Select * from v1 order by f59;
2541 Select f59, f60, f61, t1.a, t2.b
2542 FROM t2 natural left outer join t1;
2543
2544 # Testcase 3.3.1.61 ;
2545 Create or replace view test.v1 as
2546 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2547 t1.f61 t1_f61, t2.f61 t2_f61
2548 FROM t2 right outer join t1 on t2.f59=t1.f59;
2549 Select * from v1 order by t1_f59;
2550 --sorted_result
2551 Select t1.f59 t1_f59, t2.f59 t2_f59, t1.f60 t1_f60, t2.f60 t2_f60,
2552 t1.f61 t1_f61, t2.f61 t2_f61
2553 FROM t2 right outer join t1 on t2.f59=t1.f59;
2554
2555 # Testcase 3.3.1.62 ;
2556 Create or replace view test.v1 as
2557 Select f59, f60, a, b
2558 FROM t2 natural right outer join t1;
2559 Select * from v1 order by f59 desc;
2560 --sorted_result
2561 Select f59, f60, a, b
2562 FROM t2 natural right outer join t1;
2563
2564 drop table t1, t2;
2565 drop view v1 ;
2566
2567 Use test;
2568
2569
2570 let $message= Testcase 3.3.1.A1 - 3.3.1.A3 ;
2571 --source include/show_msg80.inc
2572 ###############################################################################
2573 # Testcase: Ensure that a view that is a subset of some or all columns and/or
2574 # column expressions and some or all rows of multiple tables joined
2575 # with the combination of
2576 # 3.3.1.A1 LEFT JOIN
2577 # 3.3.1.A2 INNER JOIN
2578 # 3.3.1.A3 CROSS JOIN
2579 # condition contains the correct row-and-column data
2580 ###############################################################################
2581 # Testcase 3.3.1.A1 ;
2582 --disable_warnings
2583 Drop table if exists t1 ;
2584 Drop view if exists v1;
2585 --enable_warnings
2586 Create table t1 (f59 int, f60 int, f61 int) ;
2587 Insert into t1 values (101,201,301) ;
2588 Insert into t1 values (107,501,601) ;
2589 Insert into t1 values (901,801,401) ;
2590
2591 Create or replace view test.v1 as
2592 Select tb2.f59 FROM tb2 LEFT JOIN t1 on tb2.f59 = t1.f59 ;
2593 Select * from test.v1 order by f59 limit 0,10;
2594 Drop view if exists test.v1 ;
2595
2596 # Testcase 3.3.1.A2 ;
2597 --disable_warnings
2598 Drop table if exists t1 ;
2599 Drop view if exists v1;
2600 --enable_warnings
2601 Create table t1 (f59 int, f60 int, f61 int) ;
2602 Insert into t1 values (201,201,201) ;
2603 Insert into t1 values (207,201,201) ;
2604 Insert into t1 values (201,201,201) ;
2605
2606 Create or replace view test.v1
2607 as Select tb2.f59 FROM tb2 INNER JOIN t1 on tb2.f59 = t1.f59 ;
2608 Select * from test.v1 order by f59 limit 0,10;
2609 Drop view if exists test.v1 ;
2610
2611 # Testcase 3.3.1.A3 ;
2612 --disable_warnings
2613 Drop table if exists t1 ;
2614 Drop view if exists v1;
2615 --enable_warnings
2616 Create table t1 (f59 int, f60 int, f61 int) ;
2617 Insert into t1 values (21,21,21) ;
2618 Insert into t1 values (27,21,21) ;
2619 Insert into t1 values (21,21,21) ;
2620
2621 Create or replace view test.v1
2622 as Select tb2.f59 FROM tb2 CROSS JOIN t1 on tb2.f59 = t1.f59 ;
2623 Select * from test.v1 order by f59 limit 0,10;
2624
2625 Drop view test.v1 ;
2626
2627
2628 let $message= Testcase 3.3.1.63 ;
2629 --source include/show_msg80.inc
2630 ###############################################################################
2631 # Testcase 3.3.1.63: Ensure that a view that is a subset of some or all columns
2632 # and/or column expressions and some or all rows of multiple
2633 # tables joined with every possible combination of JOIN
2634 # conditions, UNION, UNION ALL and UNION DISTINCT, nested at
2635 # multiple levels, contains the correct row-and-column data.
2636 ###############################################################################
2637 --disable_warnings
2638 Drop table if exists t1 ;
2639 Drop view if exists test.v1 ;
2640 --enable_warnings
2641
2642 Create table t1 (f59 int, f60 int, f61 int) ;
2643 Insert into t1 values (11,21,31) ;
2644 Insert into t1 values (17,51,61) ;
2645 Insert into t1 values (91,81,41) ;
2646
2647 Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2648 Union ALL (Select f59 from t1 where f59=17 );
2649 Select * from test.v1 order by f59 limit 0,10;
2650
2651 Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2652 Union (Select f59 from t1 where f59=17 );
2653 Select * from test.v1 order by f59 limit 0,10;
2654
2655 Create or replace view test.v1 as (Select f59 FROM tb2 where f59=17 )
2656 Union Distinct (Select f59 from t1 where f60=17 );
2657 Select * from test.v1 order by f59 limit 0,10;
2658
2659 Drop view test.v1 ;
2660
2661 --disable_warnings
2662 drop table if exists t1;
2663 drop view if exists test.v1;
2664 --enable_warnings
2665 create table t1 (f59 int, f60 int, f61 int);
2666
2667 insert into t1 values (101,201,301);
2668 insert into t1 values (107,501,601);
2669 insert into t1 values (901,801,401);
2670
2671 create or replace view test.v1 as
2672 select tb2.f59 from tb2 join t1 on tb2.f59 = t1.f59;
2673 select * from test.v1 order by f59 limit 0,10;
2674
2675 create or replace view test.v1 as
2676 (select f59 from tb2 where f59=107 )
2677 union all
2678 (select f59 from t1 where f59=107 );
2679 select * from test.v1 order by f59 limit 0,10;
2680
2681 create or replace view test.v1 as
2682 (select f59 from tb2 where f59=107 )
2683 union
2684 (select f59 from t1 where f59=107 );
2685 select * from test.v1 order by f59 limit 0,10;
2686
2687 create or replace view test.v1 as
2688 (select f59 from tb2 where f59=107 )
2689 union distinct
2690 (select f59 from t1 where f59=107 );
2691 select * from test.v1 order by f59 limit 0,10;
2692
2693 drop view if exists test.v1 ;
2694 drop table t1;
2695
2696
2697 let $message= Testcase 3.3.1.64 ;
2698 --source include/show_msg80.inc
2699 ###############################################################################
2700 # Testcase 3.3.1.64: Ensure that all changes to a view definition, executed by
2701 # the ALTER VIEW statement, are correctly recorded and have
2702 # the correct effect on the data shown by the view.
2703 ###############################################################################
2704 --disable_warnings
2705 Drop view if exists test.v1 ;
2706 --enable_warnings
2707 CREATE VIEW test.v1 AS SELECT F59
2708 FROM test.tb2 where test.tb2.F59 = 109;
2709
2710 SELECT * FROM test.v1 order by f59 limit 0,10;
2711
2712 ALTER VIEW test.v1 AS SELECT *
2713 FROM test.tb2 WHERE test.tb2.f59 = 242 ;
2714 if ($have_bug_32285)
2715 {
2716 --disable_ps_protocol
2717 }
2718 --vertical_results
2719 SELECT * FROM test.v1 order by f59 limit 0,10;
2720 --horizontal_results
2721 --enable_ps_protocol
2722
2723 Drop view test.v1 ;
2724
2725
2726 let $message= Testcase 3.3.1.65, 3.3.1.A4, 3.3.1.66, 3.3.1.67 ;
2727 --source include/show_msg80.inc
2728 ###############################################################################
2729 # Testcase 3.3.1.65: Ensure that the DROP VIEW statement cleanly drops its
2730 # target view.
2731 # Testcase 3.3.1.A4: Ensure that the DROP VIEW IF EXISTS statement cleanly
2732 # drops its target view.
2733 # Testcase 3.3.1.66: Ensure that DROP VIEW <view name> fails, with an appro-
2734 # priate error message, if the view named does not exist.
2735 # Testcase 3.3.1.67: Ensure that DROP VIEW IF EXISTS <view name> does not fail,
2736 # but merely returns an appropriate warning, if the view
2737 # named does not exist.
2738 ###############################################################################
2739 --disable_warnings
2740 DROP TABLE IF EXISTS t1;
2741 DROP VIEW IF EXISTS test.v1 ;
2742 --enable_warnings
2743 eval CREATE TABLE t1 ( f1 VARCHAR(1000) ) ENGINE = $engine_type ;
2744 CREATE VIEW v1 AS SELECT f1 FROM t1;
2745
2746 # DROP VIEW
2747 DROP VIEW v1;
2748 --error ER_BAD_TABLE_ERROR
2749 DROP VIEW v1;
2750
2751 CREATE VIEW v1 AS SELECT f1 FROM t1;
2752 # DROP VIEW IF EXISTS
2753 DROP VIEW IF EXISTS v1;
2754 DROP VIEW IF EXISTS v1;
2755
2756
2757 let $message= Testcase 3.3.1.68 ;
2758 --source include/show_msg80.inc
2759 ###############################################################################
2760 # Testcase 3.3.1.68: Ensure that DROP VIEW <view name>, DROP VIEW <view name>
2761 # RESTRICT, and DROP VIEW <view name> CASCADE all take
2762 # exactly the same action, until such time as the RESTRICT
2763 # and CASCADE keyword actions are implemented by MySQL.
2764 ###############################################################################
2765 --disable_warnings
2766 DROP TABLE IF EXISTS t1;
2767 DROP VIEW IF EXISTS v1_base ;
2768 DROP VIEW IF EXISTS v1_top ;
2769 --enable_warnings
2770 CREATE TABLE t1 ( f1 DOUBLE);
2771
2772 --disable_query_log
2773 SET @part1= '';
2774 SET @part2= 'RESTRICT';
2775 SET @part3= 'CASCADE';
2776 --enable_query_log
2777
2778 let $num1= 3;
2779 while ($num1)
2780 {
2781
2782 CREATE VIEW v1_base AS SELECT * FROM t1;
2783 CREATE VIEW v1_top AS SELECT * FROM v1_base;
2784 --disable_query_log
2785 let $aux1= `SELECT CONCAT('DROP VIEW v1_top ', @v1_part)` ;
2786 let $aux2= `SELECT CONCAT('DROP VIEW v1_base ', @v1_part)` ;
2787 eval SET @v1_part= @part$num1;
2788 --enable_query_log
2789
2790 # 1. more non important sub testcase, where the view (v1_top) is not the base of
2791 # another object
2792 # DROP VIEW v1_top < |RESTRICD|CASCADE> must be successful.
2793 eval $aux1 ;
2794 # Check, that v1_top really no more exists + cleanup for the second sub test
2795 --error ER_BAD_TABLE_ERROR
2796 DROP VIEW v1_top;
2797
2798 CREATE VIEW v1_top AS SELECT * FROM v1_base;
2799 # 2. more important sub testcase, where the view (v1_base) is the base of
2800 # another object (v1_top)
2801 # DROP VIEW v1_base < |RESTRICT|CASCADE>
2802 # If the RESTRICT and CASCADE keyword actions are implemented by MySQL,
2803 # CASCADE will remove v1_base and the dependend view v1_top and
2804 # RESTRICT will fail, because there exists the dependend view v1_top
2805 eval $aux2 ;
2806 # Check, if v1_base and v1_top exist + cleanup for next loop
2807 DROP VIEW v1_base;
2808 DROP VIEW v1_top;
2809
2810 dec $num1;
2811 }
2812
2813
2814 let $message= Testcase 3.3.1.69, 3.3.1.70, 3.3.1.A5 ;
2815 --source include/show_msg80.inc
2816 ###############################################################################
2817 # Testcases : Ensure that, when a view is dropped, its definition no longer
2818 # appears when a
2819 # 3.3.1.69 SHOW CREATE VIEW, SHOW CREATE TABLE, SHOW TABLE STATUS,
2820 # SHOW TABLE
2821 # 3.3.1.70 CHECK TABLE statement is executed
2822 # 3.3.1.A5 SHOW COLUMNS, SHOW FIELDS, DESCRIBE, EXPLAIN
2823 # statement is executed
2824 ###############################################################################
2825 # Note(mleich): There will be no non failing sub testcases with SHOW here.
2826 # They will be done in 3.3.11 ff.
2827 --disable_warnings
2828 DROP TABLE IF EXISTS t1 ;
2829 DROP VIEW IF EXISTS v1 ;
2830 --enable_warnings
2831 eval CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ENGINE = $engine_type;
2832 CREATE VIEW v1 AS SELECT * FROM t1;
2833 DROP VIEW v1 ;
2834
2835 # The negative tests:
2836 # SELECT
2837 --error ER_NO_SUCH_TABLE
2838 SELECT * FROM v1 ;
2839 #
2840 --error ER_NO_SUCH_TABLE
2841 SHOW CREATE VIEW v1 ;
2842 --error ER_NO_SUCH_TABLE
2843 SHOW CREATE TABLE v1 ;
2844 # Attention: Like is a filter. So we will get an empty result set here.
2845 SHOW TABLE STATUS like 'v1' ;
2846 SHOW TABLES LIKE 'v1';
2847 --error ER_NO_SUCH_TABLE
2848 SHOW COLUMNS FROM v1;
2849 --error ER_NO_SUCH_TABLE
2850 SHOW FIELDS FROM v1;
2851 CHECK TABLE v1;
2852 --error ER_NO_SUCH_TABLE
2853 DESCRIBE v1;
2854 --error ER_NO_SUCH_TABLE
2855 EXPLAIN SELECT * FROM v1;
2856
2857 Use test;
2858
2859 let $message= Testcase 3.3.1.A6 ;
2860 --source include/show_msg80.inc
2861 ###############################################################################
2862 # Testcases 3.3.1.A6 : Ensure that nested views up to level @max_level work.
2863 ###############################################################################
2864 # 1. Simple nested VIEWs
2865 # Configurable parameter @max_level = nesting level
2866 # 128 must be good enough, it is already a pathologic value.
2867 # We currently set it to 32, because of performance issues.
2868 --disable_query_log
2869 SET @max_level= 32;
2870 --enable_query_log
2871 --disable_warnings
2872 DROP DATABASE IF EXISTS test3;
2873 --enable_warnings
2874 CREATE DATABASE test3;
2875 eval CREATE TABLE test3.t1 (f1 DECIMAL(5,3)) ENGINE = $engine_type;
2876 INSERT INTO test3.t1 SET f1 = 1.0;
2877 CREATE VIEW test3.v0 AS SELECT * FROM test3.t1;
2878
2879 let $level= 1;
2880 let $run= 1;
2881 while ($run)
2882 {
2883 --disable_query_log
2884 eval SET @aux = $level - 1;
2885 --enable_query_log
2886 let $sublevel= `SELECT @aux`;
2887
2888 eval CREATE VIEW test3.v$level AS SELECT * FROM test3.v$sublevel;
2889
2890 # DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
2891 # (direct after the while loop) show suspicious results.
2892 let $debug= 0;
2893 if ($debug)
2894 {
2895 eval SHOW CREATE VIEW test3.v$level;
2896 eval SELECT * FROM test3.v$level;
2897 eval EXPLAIN SELECT * FROM test3.v$level;
2898 }
2899
2900 --disable_query_log
2901 eval SET @aux = @max_level > $level;
2902 --enable_query_log
2903 inc $level;
2904 # DEBUG
2905 # eval SELECT @aux AS "@aux", $level AS "next level";
2906
2907 let $run= `SELECT @aux`;
2908 }
2909 #--------------------------------------------------------------------------
2910 # Attention: If the following statements get suspicious/unexpected results
2911 # and you assume that something with the non toplevel VIEWs might
2912 # be wrong, please edit the while loop above and set $debug to 1.
2913 #--------------------------------------------------------------------------
2914 # 1.1 Check of top level VIEW
2915 let $toplevel= `SELECT @max_level`;
2916 eval SHOW CREATE VIEW test3.v$toplevel;
2917 eval SELECT * FROM test3.v$toplevel;
2918 eval EXPLAIN SELECT * FROM test3.v$toplevel;
2919
2920 # 1.2 Check the top level view when a base VIEW is dropped
2921 DROP VIEW test3.v0;
2922 eval SHOW CREATE VIEW test3.v$toplevel;
2923 --error ER_VIEW_INVALID
2924 eval SELECT * FROM test3.v$toplevel;
2925 --error ER_VIEW_INVALID
2926 eval EXPLAIN SELECT * FROM test3.v$toplevel;
2927
2928
2929 # 2. Complicated nested VIEWs
2930 # parameter @max_level = nesting level
2931 # There is a limit(@join_limit = 61) for the number of tables which
2932 # could be joined. This limit will be reached, when we set
2933 # @max_level = @join_limit - 1 .
2934 --disable_query_log
2935 #++++++++++++++++++++++++++++++++++++++++++++++
2936 # OBN - Reduced the value of join limit to 30
2937 # Above seems to hang - FIXME
2938 # mleich - Reason unclear why it hangs for OBN on innodb and memory.
2939 # Hypothesis: Maybe the consumption of virtual memory is high
2940 # and OBN's box performs excessive paging.
2941 # (RAM: OBN ~384MB RAM, mleich 1 GB)
2942 #++++++++++++++++++++++++++++++++++++++++++++++
2943 let $message= FIXME - Setting join_limit to 28 - hangs for higher values;
2944 --source include/show_msg.inc
2945 # OBN - Reduced from 30 in 5.1.21 to avoid hitting the ndbcluster limit
2946 # of "ERROR HY000: Got temporary error 4006 'Connect failure
2947 # - out of connection objects (increase MaxNoOfConcurrentTransactions)'
2948 # from NDBCLUSTER " to early;
2949 #SET @join_limit = 61;
2950 SET @join_limit = 28; # OBN - see above
2951 SET @max_level = @join_limit - 1;
2952 --enable_query_log
2953
2954 --disable_warnings
2955 DROP DATABASE IF EXISTS test3;
2956 DROP TABLE IF EXISTS test1.t1;
2957 DROP TABLE IF EXISTS test2.t1;
2958 let $level= `SELECT @max_level + 1`;
2959 while ($level)
2960 {
2961 dec $level;
2962
2963 eval DROP VIEW IF EXISTS test1.v$level;
2964 }
2965 --enable_warnings
2966 CREATE DATABASE test3;
2967
2968 # Testplan for the content of the tables:
2969 # ---------------------------------------------------------
2970 # Records test1.t1 test2.t1 test3.t1
2971 # NULL, 'numeric column is NULL' yes yes yes
2972 # 0 , NULL yes yes yes
2973 # 5 , 'five' yes yes yes
2974 # 1 , 'one' yes yes no
2975 # 2 , 'two' yes no yes
2976 # 3 , 'three' no yes yes
2977
2978 USE test1;
2979 eval CREATE TABLE t1 (f1 BIGINT, f2 CHAR(50)) ENGINE = $engine_type ;
2980 INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2981 INSERT INTO t1 VALUES (0, NULL);
2982 INSERT INTO t1 VALUES (5, 'five');
2983
2984 INSERT INTO t1 VALUES (1, 'one');
2985 INSERT INTO t1 VALUES (2, 'two');
2986
2987 USE test2;
2988 eval CREATE TABLE t1 (f1 DECIMAL(64,30), f2 VARCHAR(50)) ENGINE = $engine_type;
2989 INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2990 INSERT INTO t1 VALUES (0.000000000000000000000000000000, NULL);
2991 INSERT INTO t1 VALUES (5.000000000000000000000000000000, 'five');
2992
2993 INSERT INTO t1 VALUES (+1.000000000000000000000000000000, 'one');
2994 INSERT INTO t1 VALUES (3.000000000000000, 'three');
2995
2996 USE test3;
2997 eval CREATE TABLE t1 (f1 DOUBLE, f2 VARBINARY(50)) ENGINE = $engine_type;
2998 INSERT INTO t1 VALUES (NULL, 'numeric column is NULL');
2999 INSERT INTO t1 VALUES (+0.0E-35, NULL);
3000 INSERT INTO t1 VALUES (+0.5E+1, 'five');
3001
3002 INSERT INTO t1 VALUES (20.0E-1, 'two');
3003 INSERT INTO t1 VALUES (0.0300E2, 'three');
3004
3005 USE test;
3006
3007 CREATE OR REPLACE VIEW test1.v0 AS SELECT * FROM test2.t1;
3008
3009 --disable_query_log
3010 SET @max_level = IFNULL(@limit1,@max_level);
3011 --enable_query_log
3012 let $level= 1;
3013 let $run= 1;
3014 while ($run)
3015 {
3016 --disable_query_log
3017 eval SET @aux = $level - 1;
3018 let $sublevel= `SELECT @aux`;
3019 eval SET @AUX = $level MOD 3 + 1;
3020 let $dbnum= `SELECT @AUX`;
3021 --enable_query_log
3022
3023 eval CREATE OR REPLACE VIEW test1.v$level AS SELECT f1, f2
3024 FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3025
3026 # DEBUG Please set $debug to 1, if the statements on the toplevel VIEW
3027 # (direct after the while loop) show suspicious results.
3028 let $debug= 0;
3029 if ($debug)
3030 {
3031 eval SHOW CREATE VIEW test1.v$level;
3032 eval SELECT * FROM test1.v$level;
3033 eval SELECT f1, f2
3034 FROM test$dbnum.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3035 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3036 CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3037 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3038 CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3039 }
3040
3041 --disable_query_log
3042 eval SET @aux = @max_level > $level;
3043 --enable_query_log
3044 inc $level;
3045 # DEBUG
3046 # eval SELECT @aux AS "@aux", $level AS "next level";
3047
3048 let $run= `SELECT @aux`;
3049 }
3050
3051 #--------------------------------------------------------------------------
3052 # Atention: If the following statements get suspicious/unexpected results
3053 # and you assume that something with the non toplevel VIEWs might
3054 # be wrong, please edit the while loop above and set $debug to 1.
3055 #--------------------------------------------------------------------------
3056 # 2.1 Check of top level VIEW
3057 let $toplevel= `SELECT @max_level`;
3058 # Show should be easy
3059 eval SHOW CREATE VIEW test1.v$toplevel;
3060 # SELECT is much more complicated
3061 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3062 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3063 let $message= The output of following EXPLAIN is deactivated, because the result
3064 differs on some platforms
3065 FIXME Is this a bug ? ;
3066 --source include/show_msg80.inc
3067 if (1)
3068 {
3069 --disable_result_log
3070 }
3071 # EXPLAIN might be the hell
3072 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3073 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3074 if (1)
3075 {
3076 --enable_result_log
3077 }
3078
3079 # 2.2 Check of top level VIEW when join limit is exceeded
3080 # Exceed the limit for the number of tables which could be joined.
3081 let $level= `SELECT @max_level + 1`;
3082 let $sublevel= `SELECT @max_level`;
3083 eval CREATE VIEW test1.v$level AS SELECT f1, f2
3084 FROM test3.t1 tab1 NATURAL JOIN test1.v$sublevel tab2;
3085 eval SHOW CREATE VIEW test1.v$level;
3086 # the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3087 # is successful so assuming no expected error was intended
3088 # --error ER_TOO_MANY_TABLES
3089 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3090 CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3091 let $message= The output of following EXPLAIN is deactivated, because the result
3092 differs on some platforms
3093 FIXME Is this a bug ? ;
3094 --source include/show_msg80.inc
3095 if (1)
3096 {
3097 --disable_result_log
3098 }
3099 # the following line as written as '--eror ER_TOO_MANY_TABLES' and the command
3100 # is successful so assuming no expected error was intended
3101 # --error ER_TOO_MANY_TABLES
3102 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3103 CAST(f2 AS CHAR) AS f2 FROM test1.v$level;
3104 if (1)
3105 {
3106 --enable_result_log
3107 }
3108 eval DROP VIEW IF EXISTS test1.v$level;
3109
3110 # 2.3 Create a logical wrong (data type "garbage") base for the upper views
3111 # and check the behaviour of the top level view.
3112 # 2.3.1 Exchange numeric and string column
3113 --disable_result_log
3114 CREATE OR REPLACE VIEW test1.v0 AS
3115 SELECT f1 as f2, f2 as f1 FROM test2.t1;
3116 # 2.3.2 DATE instead of numeric
3117 CREATE OR REPLACE VIEW test2.v0 AS
3118 SELECT CAST('0001-01-01' AS DATE) as f1, f2 FROM test3.t1;
3119 eval SHOW CREATE VIEW test1.v$toplevel;
3120 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3121 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3122 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3123 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3124 # 2.3.3 UCS2 string instead of common string
3125 CREATE OR REPLACE VIEW test3.v0 AS
3126 SELECT f1 , CONVERT('ßÄäÖöÜü§' USING UCS2) as f2 FROM test1.t1;
3127 eval SHOW CREATE VIEW test1.v$toplevel;
3128 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3129 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3130 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3131 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3132
3133 # 2.3.4 UCS2 string instead of numeric
3134 CREATE OR REPLACE VIEW test3.v0 AS
3135 SELECT CONVERT('ßÄäÖöÜü§' USING UCS2) as f1, f2 FROM test1.t1;
3136 eval SHOW CREATE VIEW test1.v$toplevel;
3137 eval SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3138 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3139 eval EXPLAIN SELECT CAST(f1 AS SIGNED INTEGER) AS f1,
3140 CAST(f2 AS CHAR) AS f2 FROM test1.v$toplevel;
3141 --enable_result_log
3142
3143 # Cleanup
3144 let $level= `SELECT @max_level + 1`;
3145 while ($level)
3146 {
3147 dec $level;
3148
3149 eval DROP VIEW IF EXISTS test1.v$level;
3150
3151 }
3152 DROP DATABASE test3;
3153 DROP TABLE test1.t1;
3154 DROP TABLE test2.t1;
3155
3156 #==============================================================================
3157 # 3.3.2 Updatable and Insertable-into views:
3158 #==============================================================================
3159 Use test;
3160
3161 let $message= Testcase 3.3.2.1;
3162 --source include/show_msg80.inc
3163 ###############################################################################
3164 # Testcase 3.3.2.1: Ensure that every view which may theoretically accept new
3165 # rows via the INSERT statement does, in fact, do so.
3166 ###############################################################################
3167 --disable_warnings
3168 Drop view if exists test.v1 ;
3169 --enable_warnings
3170
3171 Create View test.v1 AS SELECT f59,f60 FROM tb2 where f59 = 1995 ;
3172 --enable_info
3173 INSERT INTO test.v1 (f59,f60) values (879,700) ;
3174 --disable_info
3175 SELECT f59,f60 FROM test.v1 where f59 = 879 and f60 = 700 ;
3176 DELETE FROM tb2 where f59 = 879 and f60 = 700 ;
3177
3178 Drop view test.v1 ;
3179
3180
3181 let $message= Testcase 3.3.2.2;
3182 --source include/show_msg80.inc
3183 ###############################################################################
3184 # Testcase 3.3.2.2: Ensure that, for every row inserted into a view,
3185 # the correct new data also appears in every relevant
3186 # underlying table.
3187 ###############################################################################
3188 --disable_warnings
3189 Drop view if exists test.v1 ;
3190 --enable_warnings
3191
3192 Create view test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3193 --enable_info
3194 INSERT INTO test.v1 ( f59 , f60 ) values (2005,0101) ;
3195 --disable_info
3196
3197 if ($have_bug_32285)
3198 {
3199 --disable_ps_protocol
3200 }
3201 --vertical_results
3202 SELECT * FROM tb2 where f59 = 2005 and f60 = 0101 ;
3203 --horizontal_results
3204 --enable_ps_protocol
3205 SELECT f59,f60 FROM test.v1 where f59 = 2005 and f60 = 0101 ;
3206 DELETE FROM tb2 where f59 = 2005 and f60 = 0101 ;
3207
3208 Drop view test.v1 ;
3209
3210
3211 let $message= Testcase 3.3.2.3;
3212 --source include/show_msg80.inc
3213 ###############################################################################
3214 # Testcase 3.3.2.3: Ensure that every view which may theoretically accept data
3215 # changes via the UPDATE statement does, in fact, do so.
3216 ###############################################################################
3217 Insert into tb2 (f59,f60,f61) values (780,105,106) ;
3218
3219 --disable_warnings
3220 Drop view if exists test.v1 ;
3221 --enable_warnings
3222
3223 CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3224 --enable_info
3225 UPDATE test.v1 SET f59 = 8 WHERE f59 = 780 and f60 = 105;
3226 --disable_info
3227
3228 if ($have_bug_32285)
3229 {
3230 --disable_ps_protocol
3231 }
3232 --vertical_results
3233 SELECT * FROM tb2 where f59 = 8 and f60 = 105;
3234 --horizontal_results
3235 --enable_ps_protocol
3236 SELECT f59,f60 FROM test.v1 where f59 = 8 and f60 = 105 ;
3237
3238 Drop view test.v1 ;
3239
3240
3241 let $message= Testcase 3.3.2.4;
3242 --source include/show_msg80.inc
3243 ###############################################################################
3244 # Testcase 3.3.2.4: Ensure that, for data values updated within a view, the
3245 # correct new data also appears in every relevant
3246 # underlying table.
3247 ###############################################################################
3248 Insert into tb2 (f59,f60,f61) values (781,105,106) ;
3249
3250 --disable_warnings
3251 Drop view if exists test.v1 ;
3252 --enable_warnings
3253 CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 ;
3254 --enable_info
3255 UPDATE test.v1 SET f59 = 891 WHERE f60 = 105 ;
3256 --disable_info
3257
3258 if ($have_bug_32285)
3259 {
3260 --disable_ps_protocol
3261 }
3262 --vertical_results
3263 SELECT * FROM tb2 where f59 = 891 and f60 = 105;
3264 --horizontal_results
3265 --enable_ps_protocol
3266 SELECT f59,f60 FROM test.v1 where f59 = 891 and f60 = 105 ;
3267
3268 Drop view test.v1 ;
3269
3270
3271 let $message= Testcase 3.3.2.5;
3272 --source include/show_msg80.inc
3273 ###############################################################################
3274 # Testcase 3.3.2.5: Ensure that every view which may theoretically accept data
3275 # deletions via the DELETE statement does, in fact, do so.
3276 ###############################################################################
3277 Insert into tb2 (f59,f60,f61) values (789,105,106) ;
3278
3279 --disable_warnings
3280 Drop view if exists test.v1 ;
3281 --enable_warnings
3282 CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 789 ;
3283 --enable_info
3284 DELETE FROM test.v1 where f59 = 789 ;
3285 --disable_info
3286 SELECT * FROM tb2 where f59 = 789 ;
3287 SELECT f59,f60 FROM test.v1 where f59 = 789 order by f60 ;
3288
3289 Drop view test.v1 ;
3290
3291
3292 let $message= Testcase 3.3.2.6;
3293 --source include/show_msg80.inc
3294 ###############################################################################
3295 # Testcase 3.3.2.6: Ensure that, for data rows deleted from a view, the correct
3296 # rows have also been deleted from every relevant
3297 # underlying table.
3298 ###############################################################################
3299 Insert into tb2 (f59,f60,f61) values (711,105,106) ;
3300
3301 --disable_warnings
3302 Drop view if exists test.v1 ;
3303 --enable_warnings
3304 CREATE VIEW test.v1 AS SELECT f59,f60,f61 FROM tb2 where f59 = 711 ;
3305 --enable_info
3306 DELETE FROM test.v1 where f59 = 711 ;
3307 --disable_info
3308
3309 SELECT * FROM tb2 where f59 = 711 ;
3310 SELECT f59,f60 FROM test.v1 where f59 = 711 order by f60 ;
3311
3312 Drop view test.v1 ;
3313
3314 let $message= Testcase 3.3.2.1 - 3.3.2.6 alternative implementation;
3315 --source include/show_msg80.inc
3316
3317 --disable_warnings
3318 DROP TABLE IF EXISTS t1;
3319 DROP VIEW IF EXISTS v1;
3320 --enable_warnings
3321
3322 CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4),
3323 f4 CHAR, PRIMARY KEY(f1));
3324
3325 # VIEW including the base table PRIMARY KEY, but not all base table columns (f4)
3326 # no additional columns
3327 CREATE VIEW v1 AS SELECT f1, f2, f3 FROM t1;
3328
3329 # Incomplete INSERT 1
3330 # - f2 missing
3331 # - PRIMARY KEY f1 included
3332 # f2 gets the default NULL
3333 INSERT INTO v1 SET f1 = 1;
3334 SELECT * FROM t1 ORDER BY 1;
3335 DELETE FROM t1;
3336
3337 # Incomplete INSERT 2
3338 # - f2 included
3339 # - PRIMARY KEY f1 missing
3340 # f1 gets the default 0, because we are in the native sql_mode
3341 INSERT INTO v1 SET f2 = 'ABC';
3342 # f1 gets the default 0, but this value is already exists
3343 # OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3344 --error ER_DUP_ENTRY
3345 INSERT INTO v1 SET f2 = 'ABC';
3346 SELECT * FROM t1 ORDER BY 1;
3347 DELETE FROM t1;
3348
3349 # Testplan for DELETE:
3350 #
3351 # Column within WHERE qualification
3352 # f1 (PK)
3353 # f2 (non PK)
3354 # none
3355 #
3356 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3357 # DELETE f1
3358 DELETE FROM v1 WHERE f1 = 1;
3359 SELECT * FROM t1 ORDER BY 1;
3360 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3361 # DELETE f2
3362 DELETE FROM v1 WHERE f2 = 'ABC';
3363 SELECT * FROM t1 ORDER BY 1;
3364 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3365 # DELETE none
3366 DELETE FROM v1;
3367 SELECT * FROM t1 ORDER BY 1;
3368
3369 # Testplan for UPDATE:
3370 # Column to modify Column within WHERE qualification
3371 # f1 (PK) f1(PK + same column to modify)
3372 # f1 (PK) f2
3373 # f1 (PK) none
3374 # f2 (non PK) f1(PK)
3375 # f2 (non PK) f2(non PK + same column to modify)
3376 # f2 (non PK) f3(non PK)
3377 # f2 (non PK) none
3378 # f1,f2 f1,f2
3379 #
3380 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3381 # UPDATE f1 - f1
3382 UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3383 SELECT * FROM t1 ORDER BY 1;
3384 DELETE FROM t1;
3385 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3386 # UPDATE f1 - f2
3387 UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3388 SELECT * FROM t1 ORDER BY 1;
3389 DELETE FROM t1;
3390 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3391 # UPDATE f1 - none
3392 UPDATE v1 SET f1 = 2;
3393 SELECT * FROM t1 ORDER BY 1;
3394 DELETE FROM t1;
3395 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3396 # UPDATE f2 - f1
3397 UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3398 SELECT * FROM t1 ORDER BY 1;
3399 DELETE FROM t1;
3400 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3401 # UPDATE f2 - f2
3402 UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3403 SELECT * FROM t1 ORDER BY 1;
3404 DELETE FROM t1;
3405 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3406 # UPDATE f2 - f3
3407 UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3408 SELECT * FROM t1 ORDER BY 1;
3409 DELETE FROM t1;
3410 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3411 # UPDATE f2 - none
3412 UPDATE v1 SET f2 = 'NNN';
3413 SELECT * FROM t1 ORDER BY 1;
3414 DELETE FROM t1;
3415 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3416 # UPDATE f1,f2 - f1,f2
3417 UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3418 SELECT * FROM t1 ORDER BY 1;
3419 DELETE FROM t1;
3420
3421
3422 DROP VIEW v1;
3423 # VIEW without the PRIMARY KEY f1 of the base table
3424 # no additional columns
3425 CREATE VIEW v1 AS SELECT f2, f3 FROM t1;
3426
3427 # INSERT
3428 # - PRIMARY KEY f1 missing in VIEW definition
3429 # f1 gets the default 0, because we are in the native sql_mode
3430 INSERT INTO v1 SET f2 = 'ABC';
3431 # f1 gets the default 0 and this value is already exists
3432 # OBN change for 5.1.21 --error ER_DUP_ENTRY_WITH_KEY_NAME
3433 --error ER_DUP_ENTRY
3434 INSERT INTO v1 SET f2 = 'ABC';
3435 SELECT * FROM t1 ORDER BY 1;
3436 DELETE FROM t1;
3437
3438 # Testplan for DELETE:
3439 #
3440 # Column within WHERE qualification
3441 # f2 (non PK)
3442 # none
3443 #
3444 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3445 # DELETE f2
3446 DELETE FROM v1 WHERE f2 = 'ABC';
3447 SELECT * FROM t1 ORDER BY 1;
3448 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3449 # DELETE none
3450 DELETE FROM v1;
3451 SELECT * FROM t1 ORDER BY 1;
3452
3453 # Testplan for UPDATE:
3454 #
3455 # Column to modify Column within WHERE qualification
3456 # f2 (non PK) f2(non PK + same column to modify)
3457 # f2 (non PK) f3(non PK)
3458 # f2 (non PK) none
3459 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3460 # UPDATE f2 - f2
3461 UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3462 SELECT * FROM t1 ORDER BY 1;
3463 DELETE FROM t1;
3464 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3465 # UPDATE f2 - f3
3466 UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3467 SELECT * FROM t1 ORDER BY 1;
3468 DELETE FROM t1;
3469 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3470 # UPDATE f2 - none
3471 UPDATE v1 SET f2 = 'NNN';
3472 SELECT * FROM t1 ORDER BY 1;
3473 DELETE FROM t1;
3474
3475
3476 DROP VIEW v1;
3477 # VIEW with the PRIMARY KEY f1 of the base table
3478 # but additional constant column
3479 CREATE VIEW v1 AS SELECT f1, f2, f3, 'HELLO' AS my_greeting FROM t1;
3480
3481 # Maybe the SQL standard allows the following INSERT.
3482 # But it would be a very sophisticated DBMS.
3483 --error ER_NON_INSERTABLE_TABLE
3484 INSERT INTO v1 SET f1 = 1;
3485 SELECT * FROM t1 ORDER BY 1;
3486 DELETE FROM t1;
3487 # The next INSERTs should never work, because my_greeting is not a column.
3488 --error ER_NONUPDATEABLE_COLUMN
3489 INSERT INTO v1 SET f1 = 1, my_greeting = 'HELLO';
3490 SELECT * FROM t1 ORDER BY 1;
3491 DELETE FROM t1;
3492
3493 # Testplan for DELETE:
3494 #
3495 # Column within WHERE qualification
3496 # f1 (PK)
3497 # f2 (non PK)
3498 # my_greeting(non base table column)
3499 # none
3500 #
3501 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3502 # DELETE f1
3503 DELETE FROM v1 WHERE f1 = 1;
3504 SELECT * FROM t1 ORDER BY 1;
3505 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3506 # DELETE f2
3507 DELETE FROM v1 WHERE f2 = 'ABC';
3508 SELECT * FROM t1 ORDER BY 1;
3509 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3510 # DELETE my_greeting
3511 DELETE FROM v1 WHERE my_greeting = 'HELLO';
3512 SELECT * FROM t1 ORDER BY 1;
3513 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3514 # DELETE none
3515 DELETE FROM v1;
3516 SELECT * FROM t1 ORDER BY 1;
3517
3518
3519 # Testplan for UPDATE:
3520 #
3521 # Column to modify Column within WHERE qualification
3522 # f1 (PK) f1(PK + same column to modify)
3523 # f1 (PK) f2
3524 # f1 (PK) my_greeting(non base table column)
3525 # f1 (PK) none
3526 # f2 (non PK) f1(PK)
3527 # f2 (non PK) f2(non PK + same column to modify)
3528 # f2 (non PK) f3(non PK)
3529 # f2 (non PK) my_greeting(non base table column)
3530 # f2 (non PK) none
3531 # my_greeting(non base table column) f1(PK)
3532 # my_greeting(non base table column) f2(non PK)
3533 # my_greeting(non base table column) my_greeting(same non base table column)
3534 # my_greeting(non base table column) none
3535 # f1,f2 f1,f2
3536 #
3537 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3538 # UPDATE f1 - f1
3539 UPDATE v1 SET f1 = 2 WHERE f1 = 1;
3540 SELECT * FROM t1 ORDER BY 1;
3541 DELETE FROM t1;
3542 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3543 # UPDATE f1 - f2
3544 UPDATE v1 SET f1 = 2 WHERE f2 = 'ABC';
3545 SELECT * FROM t1 ORDER BY 1;
3546 DELETE FROM t1;
3547 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3548 # UPDATE f1 - my_greeting
3549 UPDATE v1 SET f1 = 2 WHERE my_greeting = 'HELLO';
3550 SELECT * FROM t1 ORDER BY 1;
3551 DELETE FROM t1;
3552 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3553 # UPDATE f1 - none
3554 UPDATE v1 SET f1 = 2;
3555 SELECT * FROM t1 ORDER BY 1;
3556 DELETE FROM t1;
3557 #------------------------------------------------
3558 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3559 # UPDATE f2 - f1
3560 UPDATE v1 SET f2 = 'NNN' WHERE f1 = 1;
3561 SELECT * FROM t1 ORDER BY 1;
3562 DELETE FROM t1;
3563 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3564 # UPDATE f2 - f2
3565 UPDATE v1 SET f2 = 'NNN' WHERE f2 = 'ABC';
3566 SELECT * FROM t1 ORDER BY 1;
3567 DELETE FROM t1;
3568 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3569 # UPDATE f2 - f3
3570 UPDATE v1 SET f2 = 'NNN' WHERE f3 = -1.2E-3;
3571 SELECT * FROM t1 ORDER BY 1;
3572 DELETE FROM t1;
3573 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3574 # UPDATE f2 - my_greeting
3575 UPDATE v1 SET f2 = 'NNN' WHERE my_greeting = 'HELLO';
3576 SELECT * FROM t1 ORDER BY 1;
3577 DELETE FROM t1;
3578 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3579 # UPDATE f2 - none
3580 UPDATE v1 SET f2 = 'NNN';
3581 SELECT * FROM t1 ORDER BY 1;
3582 DELETE FROM t1;
3583 #------------------------------------------------
3584 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3585 # UPDATE my_greeting - f1
3586 --error ER_NONUPDATEABLE_COLUMN
3587 UPDATE v1 SET my_greeting = 'Hej' WHERE f1 = 1;
3588 SELECT * FROM t1 ORDER BY 1;
3589 DELETE FROM t1;
3590 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3591 # UPDATE my_greeting - f2
3592 --error ER_NONUPDATEABLE_COLUMN
3593 UPDATE v1 SET my_greeting = 'Hej' WHERE f2 = 'ABC';
3594 SELECT * FROM t1 ORDER BY 1;
3595 DELETE FROM t1;
3596 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3597 # UPDATE my_greeting - my_greeting
3598 --error ER_NONUPDATEABLE_COLUMN
3599 UPDATE v1 SET my_greeting = 'Hej' WHERE my_greeting = 'HELLO';
3600 SELECT * FROM t1 ORDER BY 1;
3601 DELETE FROM t1;
3602 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3603 # UPDATE my_greeting - none
3604 --error ER_NONUPDATEABLE_COLUMN
3605 UPDATE v1 SET my_greeting = 'Hej';
3606 SELECT * FROM t1 ORDER BY 1;
3607 DELETE FROM t1;
3608 #------------------------------------------------
3609 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3610 # UPDATE f1, f2 - f1, f2
3611 UPDATE v1 SET f1 = 2, f2 = 'NNN' WHERE f1 = 1 AND f2 = 'ABC';
3612 SELECT * FROM t1 ORDER BY 1;
3613 DELETE FROM t1;
3614
3615
3616 DROP TABLE t1;
3617 SET sql_mode = 'traditional';
3618 CREATE TABLE t1 ( f1 BIGINT, f2 CHAR(20), f3 NUMERIC(7,4) NOT NULL,
3619 f4 CHAR, PRIMARY KEY(f1));
3620 # VIEW including the base table PRIMARY KEY, but not the NOT NULL
3621 # base table column (f3)
3622 # no additional columns
3623 DROP VIEW v1;
3624 CREATE VIEW v1 AS SELECT f1, f2, f4 FROM t1;
3625
3626 # This INSERT must fail
3627 --error ER_NO_DEFAULT_FOR_VIEW_FIELD
3628 INSERT INTO v1 SET f1 = 1;
3629 SELECT * FROM t1 ORDER BY 1;
3630 DELETE FROM t1;
3631
3632 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3633 # DELETE f1
3634 DELETE FROM v1 WHERE f1 = 1;
3635
3636 INSERT INTO t1 VALUES(1, 'ABC', -1.2E-3, 'X');
3637 # UPDATE f1 - f2
3638 UPDATE v1 SET f4 = 'Y' WHERE f2 = 'ABC';
3639 SELECT * FROM t1 ORDER BY 1;
3640 DELETE FROM t1;
3641
3642 # Switch back to the native SQL mode
3643 SET sql_mode = '';
3644
3645
3646
3647 let $message= Testcases 3.3.2.7 - 3.3.2.9,
3648 3.3.2.10 - 3.3.2.11 omitted because of missing
3649 features EXCEPT and INTERSECT ;
3650 --source include/show_msg80.inc
3651 ###############################################################################
3652 # Testcase 3.3.2.7: Ensure that a view with a definition that includes
3653 # UNION
3654 # rejects all INSERT, UPDATE, or DELETE attempts
3655 # with an appropriate error message.
3656 # Testcase 3.3.2.8: Ensure that a view with a definition that includes
3657 # UNION DISTINCT
3658 # rejects all INSERT, UPDATE, or DELETE attempts
3659 # with an appropriate error message.
3660 # Testcase 3.3.2.9: Ensure that a view with a definition that includes
3661 # UNION ALL
3662 # rejects all INSERT, UPDATE, or DELETE attempts
3663 # with an appropriate error message.
3664 # Testcase 3.3.2.10: Ensure that a view with a definition that includes
3665 # EXCEPT
3666 # rejects all INSERT, UPDATE, or DELETE attempts
3667 # with an appropriate error message.
3668 # (Note: MySQL does not support EXCEPT at this time;
3669 # this test is for the future.)
3670 # Testcase 3.3.2.11: Ensure that a view with a definition that includes
3671 # INTERSECT
3672 # rejects all INSERT, UPDATE, or DELETE attempts
3673 # with an appropriate error message.
3674 # (Note: MySQL does not support INTERSECT at this time;
3675 # this test is for the future.)
3676 #
3677 # Summary of 3.3.2.7 - 3.3.2.11
3678 # Ensure that a view with a definition that includes
3679 # UNION or UNION DISTINCT or UNION ALL or EXCEPT or INTERSECT
3680 # rejects any INSERT or UPDATE or DELETE statement with an
3681 # appropriate error message
3682 #
3683 # mleich: I assume the type of the storage engine does not play any role.
3684 ###############################################################################
3685 INSERT INTO tb2 (f59,f60,f61) VALUES (77,185,126) ;
3686 INSERT INTO tb2 (f59,f60,f61) VALUES (59,58,54) ;
3687
3688 --disable_warnings
3689 DROP TABLE IF EXISTS t1 ;
3690 DROP VIEW IF EXISTS v1 ;
3691 --enable_warnings
3692 CREATE TABLE t1 (f59 INT, f60 INT, f61 INT) ;
3693 INSERT INTO t1 VALUES (19,41,32) ;
3694 INSERT INTO t1 VALUES (59,54,71) ;
3695 INSERT INTO t1 VALUES (21,91,99) ;
3696
3697 SET @variant1 = 'UNION ';
3698 SET @variant2 = 'UNION ALL ';
3699 SET @variant3 = 'UNION DISTINCT ';
3700 SET @variant4 = 'EXCEPT ';
3701 SET @variant5 = 'INTERSECT ';
3702
3703 # Attention: Set $num to 5, when EXCEPT and INTERSECT is supported
3704 let $num= 3;
3705 while ($num)
3706 {
3707 --disable_query_log
3708 eval SET @variant= @variant$num;
3709 let $aux= `SELECT CONCAT('CREATE VIEW v1 AS ',
3710 'SELECT f61 FROM tb2 WHERE f59=59 ',
3711 @variant,
3712 'SELECT f61 FROM t1 WHERE f59=19')`;
3713 --enable_query_log
3714 # $aux contains the CREATE VIEW statement
3715 eval $aux;
3716 --error ER_NON_INSERTABLE_TABLE
3717 INSERT INTO v1 VALUES (3000);
3718 --error ER_NON_UPDATABLE_TABLE
3719 UPDATE v1 SET f61 = 100 WHERE f61 = 32;
3720 --error ER_NON_UPDATABLE_TABLE
3721 DELETE FROM v1;
3722 DROP VIEW v1 ;
3723
3724 dec $num;
3725 }
3726
3727
3728 let $message= Testcases 3.3.2.12 - 3.3.2.20;
3729 --source include/show_msg80.inc
3730 ###############################################################################
3731 # Testcase 3.3.2.12: Ensure that a view with a definition that includes
3732 # DISTINCT
3733 # rejects all INSERT, UPDATE, or DELETE attempts
3734 # with an appropriate error message.
3735 # Testcase 3.3.2.13: Ensure that a view with a definition that includes
3736 # DISTINCTROW
3737 # rejects all INSERT, UPDATE, or DELETE attempts
3738 # with an appropriate error message.
3739 # Testcase 3.3.2.14: Ensure that a view with a definition that includes
3740 # a set function
3741 # rejects all INSERT, UPDATE, or DELETE attempts
3742 # with an appropriate error message.
3743 # Testcase 3.3.2.15: Ensure that a view with a definition that includes
3744 # GROUP BY
3745 # rejects all INSERT, UPDATE, or DELETE attempts
3746 # with an appropriate error message.
3747 # Testcase 3.3.2.16: Ensure that a view with a definition that includes
3748 # HAVING
3749 # rejects all INSERT, UPDATE, or DELETE attempts
3750 # with an appropriate error message.
3751 # Testcase 3.3.2.17: Ensure that a view with a definition that includes
3752 # a subquery in the select list
3753 # rejects all INSERT, UPDATE, or DELETE attempts
3754 # with an appropriate error message.
3755 # Testcase 3.3.2.18: Ensure that a view with a definition that includes
3756 # a reference to a non-updatable view
3757 # rejects all INSERT, UPDATE, or DELETE attempts
3758 # with an appropriate error message.
3759 # Testcase 3.3.2.19: Ensure that a view with a definition that includes
3760 # a WHERE clause subquery that refers to a table also
3761 # referenced in a FROM clause
3762 # rejects all INSERT, UPDATE, or DELETE attempts
3763 # with an appropriate error message.
3764 # Testcase 3.3.2.20: Ensure that a view with a definition that includes
3765 # ALGORITHM = TEMPTABLE
3766 # rejects all INSERT, UPDATE, or DELETE attempts
3767 # with an appropriate error message.
3768 #
3769 # Summary of 3.3.2.12 - 3.3.2.20:
3770 # Ensure that a view with a definition that includes
3771 # DISTINCT 3.3.2.12
3772 # DISTINCTROW 3.3.2.13
3773 # SET 3.3.2.14
3774 # GROUP BY 3.3.2.15
3775 # HAVING 3.3.2.16
3776 # a sub query in the select list 3.3.2.17
3777 # a reference to a non-updateable view 3.3.2.18
3778 # a WHERE clause sub query that refers to a table also referenced in a
3779 # FROM clause 3.3.2.19
3780 # ALGORITHM = TEMPTABLE 3.3.2.20
3781 # rejects
3782 # any INSERT or UPDATE or DELETE statement
3783 # with an appropriate error message.
3784 #
3785 ###############################################################################
3786 --disable_warnings
3787 DROP TABLE IF EXISTS t1, t2 ;
3788 DROP VIEW IF EXISTS test.v1 ;
3789 Drop view if exists v2 ;
3790 --enable_warnings
3791
3792 CREATE TABLE t1 (f59 int, f60 int, f61 int) ;
3793 INSERT INTO t1 VALUES (19,41,32) ;
3794 INSERT INTO t1 VALUES (59,54,71) ;
3795 INSERT INTO t1 VALUES (21,91,99) ;
3796 CREATE TABLE t2 (f59 int, f60 int, f61 int) ;
3797 INSERT INTO t2 VALUES (19,41,32) ;
3798 INSERT INTO t2 VALUES (59,54,71) ;
3799 INSERT INTO t2 VALUES (21,91,99) ;
3800 CREATE VIEW v2 AS SELECT f59, f60, f61 FROM t2 LIMIT 5;
3801
3802 # For DISTINCT 3.3.2.12
3803 SET @variant1= 'CREATE VIEW v1 AS SELECT DISTINCT(f61) FROM t1';
3804 # For DISTINCTROW 3.3.2.13
3805 SET @variant2= 'CREATE VIEW v1 AS SELECT DISTINCTROW(f61) FROM t1';
3806 # For SET 3.3.2.14
3807 SET @variant3= 'CREATE VIEW v1 AS SELECT SUM(f59) AS f61 FROM t1';
3808 # For GROUP BY 3.3.2.15
3809 SET @variant4= 'CREATE VIEW v1 AS SELECT f61 FROM t1 GROUP BY f61';
3810 # For HAVING 3.3.2.16
3811 SET @variant5= 'CREATE VIEW v1 AS SELECT f61 FROM t1 HAVING f61 > 0';
3812 # For a sub query in the select list 3.3.2.17
3813 # This restriction has been lifted
3814 # SET @variant6= 'CREATE VIEW v1 AS SELECT (SELECT f60 FROM t2 WHERE f59=19) AS f61 FROM t1';
3815 # For a WHERE clause sub query that refers to a table also referenced in a
3816 # FROM clause 3.3.2.18
3817 SET @variant6= 'CREATE VIEW v1 AS SELECT f61 FROM v2';
3818 SET @variant7= 'CREATE VIEW v1 AS SELECT f59 AS f61 FROM t1 WHERE f60 IN (SELECT f59 FROM t1)';
3819 # For ALGORITHM = TEMPTABLE 3.3.2.20
3820 SET @variant8= 'CREATE ALGORITHM = TEMPTABLE VIEW v1 (f61) AS select f60 from t1';
3821
3822 let $num= 8;
3823 while ($num)
3824 {
3825 --disable_abort_on_error
3826 --disable_query_log
3827 eval SET @variant= @variant$num;
3828 let $aux= `SELECT @variant`;
3829 --enable_query_log
3830
3831 # CREATE VIEW v1 ...
3832 eval $aux;
3833
3834 --error ER_NON_INSERTABLE_TABLE
3835 INSERT INTO v1 VALUES (1002);
3836 # --error ER_NON_UPDATABLE_TABLE, ER_UPDATE_TABLE_USED
3837 --error ER_NON_UPDATABLE_TABLE
3838 UPDATE v1 SET f61=1007;
3839 --error ER_NON_UPDATABLE_TABLE
3840 DELETE FROM v1;
3841 DROP VIEW v1;
3842 dec $num;
3843 }
3844
3845 Drop TABLE t1, t2 ;
3846 Drop VIEW v2 ;
3847
3848
3849 let $message= Testcases 3.3.A1;
3850 --source include/show_msg80.inc
3851 ###############################################################################
3852 # Testcase 3.3.A1: Check the effects of base table modifications on an already
3853 # existing VIEW
3854 #
3855 # Attention: Many modifications are logical non sense.
3856 # The consequence is in many cases a "garbage in garbage out" effect.
3857 #
3858 # There is no specification of the intended behaviour within
3859 # the MySQL manual. That means I assume the observed effects are
3860 # no bug as long we do not get a crash or obviously non
3861 # reasonable results.
3862 ###############################################################################
3863 --disable_warnings
3864 DROP TABLE IF EXISTS t1;
3865 DROP TABLE IF EXISTS t2;
3866 DROP VIEW IF EXISTS v1;
3867 DROP VIEW IF EXISTS v2;
3868 --enable_warnings
3869
3870 eval CREATE TABLE t1 (f1 BIGINT, f2 DATE DEFAULT NULL, f4 CHAR(5),
3871 report char(10)) ENGINE = $engine_type;
3872 CREATE VIEW v1 AS SELECT * FROM t1;
3873 INSERT INTO t1 SET f1 = -1, f4 = 'ABC', report = 't1 0';
3874 INSERT INTO v1 SET f1 = -1, f4 = 'ABC', report = 'v1 0';
3875
3876 # 0. Initial state
3877 DESCRIBE t1;
3878 DESCRIBE v1;
3879 SELECT * FROM t1 order by f1, report;
3880 SELECT * FROM v1 order by f1, report;
3881 #
3882 # 1. Name of one base table column is altered
3883 ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
3884 INSERT INTO t1 SET f1 = 0, f4x = 'ABC', report = 't1 1';
3885 --error ER_VIEW_INVALID
3886 INSERT INTO v1 SET f1 = 0, f4 = 'ABC', report = 'v1 1';
3887 --error ER_VIEW_INVALID
3888 INSERT INTO v1 SET f1 = 0, f4x = 'ABC', report = 'v1 1a';
3889 --error ER_VIEW_INVALID
3890 INSERT INTO v1 SET f1 = 0, report = 'v1 1b';
3891 DESCRIBE t1;
3892 # Bug#12533 crash on DESCRIBE <view> after renaming base table column;
3893 --error ER_VIEW_INVALID
3894 DESCRIBE v1;
3895 SELECT * FROM t1 order by f1, report;
3896 --error ER_VIEW_INVALID
3897 SELECT * FROM v1 order by f1, report;
3898 ALTER TABLE t1 CHANGE COLUMN f4x f4 CHAR(5);
3899 #
3900 # 2. Length of one base table column is increased
3901 ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(10);
3902 INSERT INTO t1 SET f1 = 2, f4 = '<-- 10 -->', report = 't1 2';
3903 INSERT INTO v1 SET f1 = 2, f4 = '<-- 10 -->', report = 'v1 2';
3904 DESCRIBE t1;
3905 DESCRIBE v1;
3906 SELECT * FROM t1 order by f1, report;
3907 SELECT * FROM v1 order by f1, report;
3908 #
3909 # 3. Length of one base table column is reduced
3910 # We have to mangle within warnings the row numbers, because they are not
3911 # deterministic in case of NDB.
3912 --replace_regex /at row [0-9]/at row <some number>/
3913 ALTER TABLE t1 CHANGE COLUMN f4 f4 CHAR(8);
3914 INSERT INTO t1 SET f1 = 3, f4 = '<-- 10 -->', report = 't1 3';
3915 INSERT INTO v1 SET f1 = 3, f4 = '<-- 10 -->', report = 'v1 3';
3916 DESCRIBE t1;
3917 DESCRIBE v1;
3918 SELECT * FROM t1 order by f1, report;
3919 SELECT * FROM v1 order by f1, report;
3920 #
3921 # 4. Type of one base table column is altered string -> string
3922 ALTER TABLE t1 CHANGE COLUMN f4 f4 VARCHAR(20);
3923 INSERT INTO t1 SET f1 = 4, f4 = '<------ 20 -------->', report = 't1 4';
3924 INSERT INTO v1 SET f1 = 4, f4 = '<------ 20 -------->', report = 'v1 4';
3925 DESCRIBE t1;
3926 DESCRIBE v1;
3927 SELECT * FROM t1 order by f1, report;
3928 SELECT * FROM v1 order by f1, report;
3929 #
3930 # 5. Type of one base table column altered numeric -> string
3931 ALTER TABLE t1 CHANGE COLUMN f1 f1 VARCHAR(30) charset latin1;
3932 INSERT INTO t1 SET f1 = '<------------- 30 ----------->',
3933 f4 = '<------ 20 -------->', report = 't1 5';
3934 INSERT INTO v1 SET f1 = '<------------- 30 ----------->',
3935 f4 = '<------ 20 -------->', report = 'v1 5';
3936 DESCRIBE t1;
3937 DESCRIBE v1;
3938 SELECT * FROM t1 order by f1, report;
3939 SELECT * FROM v1 order by f1, report;
3940 #
3941 # 6. DROP of one base table column
3942 ALTER TABLE t1 DROP COLUMN f2;
3943 INSERT INTO t1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 't1 6';
3944 --error ER_VIEW_INVALID
3945 INSERT INTO v1 SET f1 = 'ABC', f4 = '<------ 20 -------->', report = 'v1 6';
3946 DESCRIBE t1;
3947 --error ER_VIEW_INVALID
3948 DESCRIBE v1;
3949 SELECT * FROM t1 order by f1, report;
3950 --error ER_VIEW_INVALID
3951 SELECT * FROM v1 order by f1, report;
3952 #
3953 # 7. Recreation of dropped base table column with the same data type like before
3954 ALTER TABLE t1 ADD COLUMN f2 DATE DEFAULT NULL;
3955 INSERT INTO t1 SET f1 = 'ABC', f2 = '1500-12-04',
3956 f4 = '<------ 20 -------->', report = 't1 7';
3957 INSERT INTO v1 SET f1 = 'ABC', f2 = '1500-12-04',
3958 f4 = '<------ 20 -------->', report = 'v1 7';
3959 DESCRIBE t1;
3960 DESCRIBE v1;
3961 SELECT * FROM t1 order by f1, report;
3962 SELECT * FROM v1 order by f1, report;
3963 #
3964 # 8. Recreation of dropped base table column with a different data type
3965 # like before
3966 ALTER TABLE t1 DROP COLUMN f2;
3967 ALTER TABLE t1 ADD COLUMN f2 FLOAT;
3968 INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3969 f4 = '<------ 20 -------->', report = 't1 8';
3970 INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3971 f4 = '<------ 20 -------->', report = 'v1 8';
3972 DESCRIBE t1;
3973 DESCRIBE v1;
3974 SELECT * FROM t1 order by f1, report;
3975 SELECT * FROM v1 order by f1, report;
3976 #
3977 # 9. Add a column to the base table
3978 ALTER TABLE t1 ADD COLUMN f3 NUMERIC(7,2);
3979 INSERT INTO t1 SET f1 = 'ABC', f2 = -3.3E-4,
3980 f3 = -2.2, f4 = '<------ 20 -------->', report = 't1 9';
3981 --error ER_BAD_FIELD_ERROR
3982 INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3983 f3 = -2.2, f4 = '<------ 20 -------->', report = 'v1 9';
3984 INSERT INTO v1 SET f1 = 'ABC', f2 = -3.3E-4,
3985 f4 = '<------ 20 -------->', report = 'v1 9a';
3986 DESCRIBE t1;
3987 DESCRIBE v1;
3988 SELECT * FROM t1 order by f1, report;
3989 SELECT * FROM v1 order by f1, report;
3990 #
3991 # 10. VIEW with numeric function is "victim" of data type change
3992 DROP TABLE t1;
3993 DROP VIEW v1;
3994 eval CREATE TABLE t1 (f1 CHAR(10), f2 BIGINT) ENGINE = $engine_type;
3995 INSERT INTO t1 SET f1 = 'ABC', f2 = 3;
3996 CREATE VIEW v1 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
3997 DESCRIBE t1;
3998 DESCRIBE v1;
3999 SELECT * FROM t1 order by f1, f2;
4000 SELECT * FROM v1 order by 2;
4001 ALTER TABLE t1 CHANGE COLUMN f2 f2 VARCHAR(30);
4002 INSERT INTO t1 SET f1 = 'ABC', f2 = 'DEF';
4003 DESCRIBE t1;
4004 DESCRIBE v1;
4005 SELECT * FROM t1 order by f1, f2;
4006 SELECT * FROM v1 order by 2;
4007 # Some statements for comparison
4008 # - the ugly SQRT('DEF') as constant
4009 SELECT SQRT('DEF');
4010 # - Will a VIEW based on the same definition show the same result ?
4011 CREATE VIEW v2 AS SELECT SQRT('DEF');
4012 SELECT * FROM v2 order by 1;
4013 # - Will a VIEW v2 created after the base table column recreation show the same
4014 # result set like v1 ?
4015 CREATE OR REPLACE VIEW v2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4016 DESCRIBE v2;
4017 SELECT * FROM v2 order by 2;
4018 # - What will be the content of base table created with AS SELECT ?
4019 CREATE TABLE t2 AS SELECT f1, SQRT(f2) my_sqrt FROM t1;
4020 if ($have_bug_32285)
4021 {
4022 --disable_ps_protocol
4023 }
4024 --vertical_results
4025 SELECT * FROM t2 order by 2;
4026 --horizontal_results
4027 --enable_ps_protocol
4028 DROP TABLE t2;
4029 CREATE TABLE t2 AS SELECT * FROM v1;
4030 if ($have_bug_32285)
4031 {
4032 --disable_ps_protocol
4033 }
4034 --vertical_results
4035 SELECT * FROM t2 order by 2;
4036 --horizontal_results
4037 --enable_ps_protocol
4038 DROP TABLE t2;
4039 CREATE TABLE t2 AS SELECT * FROM v2;
4040 if ($have_bug_32285)
4041 {
4042 --disable_ps_protocol
4043 }
4044 --vertical_results
4045 SELECT * FROM t2 order by 2;
4046 --horizontal_results
4047 --enable_ps_protocol
4048 #
4049 DROP TABLE t1;
4050 DROP TABLE t2;
4051 DROP VIEW v1;
4052 DROP VIEW v2;
4053
4054
4055
4056 # Clean up
4057 --disable_warnings
4058 DROP TABLE IF EXISTS t1;
4059 DROP TABLE IF EXISTS t2;
4060 DROP VIEW IF EXISTS v1;
4061 DROP VIEW IF EXISTS v1_1;
4062 DROP VIEW IF EXISTS v1_2;
4063 DROP VIEW IF EXISTS v1_firstview;
4064 DROP VIEW IF EXISTS v1_secondview;
4065 DROP VIEW IF EXISTS v2;
4066 DROP DATABASE IF EXISTS test2;
4067 DROP DATABASE IF EXISTS test3;
4068 --enable_warnings
4069
4070 # FIXME sub testcases, which might be included, if they fit good into
4071 # the requirements and the completeness of the tests is increased
4072 # Bug#10970 Views: dependence on temporary table allowed
4073 # Bug#4663 constant function in WHERE clause evaluated in view definition
4074 # Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails
4075 # Bug#10977 Views: no warning if column name is truncated
4076 # Bug#9505: Views: privilege needed on underlying function
4077
4078 # --source suite/funcs_1/Views/Views_403x406.test
4079 # --source suite/funcs_1/Views/Views_407.test
4080 # --source suite/funcs_1/Views/Views_408x411.test
4081
4082
4083