Debian Codegraph @

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

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