view.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

-- error 1146
call p1();
-- error 1146
call p1();
drop procedure p1;

#
# Bug #10624 Views with multiple UNION and UNION ALL produce incorrect results
#
create table t1 (f1 int);
create table t2 (f1 int);
insert into t1 values (1);
insert into t2 values (2);
create view v1 as select * from t1 union select * from t2 union all select * from t2;
select * from v1;
drop view v1;
drop table t1,t2;
#
# Test for bug #10970: view referring a temporary table indirectly
#                     

CREATE TEMPORARY TABLE t1 (a int);
CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1);
-- error 1352
CREATE VIEW v1 AS SELECT f1();

DROP FUNCTION f1;
DROP TABLE t1;

#
# BUG #12533 (crash on DESCRIBE <view> after renaming base table column)
#
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW  IF EXISTS v1;
--enable_warnings

CREATE TABLE t1 (f4 CHAR(5));
CREATE VIEW v1 AS SELECT * FROM t1;
DESCRIBE v1;

ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5);
--error 1356
DESCRIBE v1;
DROP TABLE t1;
DROP VIEW v1;

# 
# Bug #12489 wrongly printed strcmp() function results in creation of broken
#            view
create table t1 (f1 char);
create view v1 as select strcmp(f1,'a') from t1;
select * from v1;
drop view v1;
drop table t1;

#
# Bug #12922 if(sum(),...) with group from view returns wrong results 
#
create table t1 (f1 int, f2 int,f3 int);
insert into t1 values (1,10,20),(2,0,0);
create view v1 as select * from t1;
select if(sum(f1)>1,f2,f3) from v1 group by f1;
drop view v1;
drop table t1;
# BUG#12941
#
--disable_warnings
create table t1 (
  r_object_id char(16) NOT NULL,
  group_name varchar(32) NOT NULL
) engine = InnoDB;

create table t2 (
  r_object_id char(16) NOT NULL,
  i_position int(11) NOT NULL, 
  users_names varchar(32) default NULL
) Engine = InnoDB;
--enable_warnings

create view v1 as select r_object_id, group_name from t1;
create view v2 as select r_object_id, i_position, users_names from t2;

create unique index r_object_id on t1(r_object_id);
create index group_name on t1(group_name);
create unique index r_object_id_i_position on t2(r_object_id,i_position);
create index users_names on t2(users_names);

insert into t1 values('120001a080000542','tstgroup1');
insert into t2 values('120001a080000542',-1, 'guser01');
insert into t2 values('120001a080000542',-2, 'guser02');

select v1.r_object_id, v2.users_names from v1, v2
where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id 
order by users_names;

drop view v1, v2;
drop table t1, t2;

# Bug #6808 - Views: CREATE VIEW v ... FROM t AS v fails
#

create table t1 (s1 int); 
create view abc as select * from t1 as abc;
drop table t1;
drop view abc;

#
# Bug #12993 View column rename broken in subselect
#
create table t1(f1 char(1));
create view v1 as select * from t1;
select * from (select f1 as f2 from v1) v where v.f2='a';
drop view v1;
drop table t1;

#
# Bug #11416 Server crash if using a view that uses function convert_tz
#
create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
select * from v1;
drop view v1;

#
# Bugs #12963, #13000: wrong creation of VIEW with DAYNAME, DAYOFWEEK, and WEEKDAY
#

CREATE TABLE t1 (date DATE NOT NULL);
INSERT INTO  t1 VALUES ('2005-09-06');

CREATE VIEW v1 AS SELECT DAYNAME(date) FROM t1;
SHOW CREATE VIEW v1;

CREATE VIEW v2 AS SELECT DAYOFWEEK(date) FROM t1;
SHOW CREATE VIEW v2;

CREATE VIEW v3 AS SELECT WEEKDAY(date) FROM t1;
SHOW CREATE VIEW v3;

SELECT DAYNAME('2005-09-06');
SELECT DAYNAME(date) FROM t1;
SELECT * FROM v1;

SELECT DAYOFWEEK('2005-09-06');
SELECT DAYOFWEEK(date) FROM t1;
SELECT * FROM v2;

SELECT WEEKDAY('2005-09-06');
SELECT WEEKDAY(date) FROM t1;
SELECT * FROM v3;

DROP TABLE t1;
DROP VIEW  v1, v2, v3;

#
# Bug #13411: crash when using non-qualified view column in HAVING clause 
#

CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
CREATE VIEW v1 AS SELECT a,b FROM t1;
SELECT t1.a FROM t1 GROUP BY t1.a HAVING a > 1;
SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1;

DROP VIEW v1;
DROP TABLE t1;

#
# Bug #13410: failed name resolution for qualified view column in HAVING 
#

CREATE TABLE t1 ( a int, b int );
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
CREATE VIEW v1 AS SELECT a,b FROM t1;
SELECT t1.a FROM t1 GROUP BY t1.a HAVING t1.a > 1;
SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1;
SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3);
SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3);

DROP VIEW v1;
DROP TABLE t1;

#
# Bug #13327 view wasn't using index for const condition
#

CREATE TABLE t1 (a INT, b INT, INDEX(a,b));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 (a INT);
INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
INSERT INTO t3 VALUES (1),(2),(3);
CREATE VIEW v1 AS SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a AND t1.b=t2.b;
CREATE VIEW v2 AS SELECT t3.* FROM t1,t3 WHERE t1.a=t3.a;
EXPLAIN SELECT t1.* FROM t1 JOIN t2 WHERE t1.a=t2.a AND t1.b=t2.b AND t1.a=1;
EXPLAIN SELECT * FROM v1 WHERE a=1;
EXPLAIN SELECT * FROM v2 WHERE a=1;
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3;

#
# Bug #13622 Wrong view .frm created if some field's alias contain \n
#
create table t1 (f1 int);
create view v1 as select t1.f1 as '123
456' from t1;
select * from v1;
drop view v1;
drop table t1;

# Bug #14466 lost sort order in GROUP_CONCAT() in a view
#
create table t1 (f1 int, f2 int);
insert into t1 values(1,1),(1,2),(1,3);
create view v1 as select f1 ,group_concat(f2 order by f2 asc) from t1 group by f1;
create view v2 as select f1 ,group_concat(f2 order by f2 desc) from t1 group by f1;
select * from v1;
select * from v2;
drop view v1,v2;
drop table t1;

#
# BUG#14026 Crash on second PS execution when using views
#
create table t1 (x int, y int);
create table t2 (x int, y int, z int);
create table t3 (x int, y int, z int);
create table t4 (x int, y int, z int);

create view v1 as
select t1.x
from (
  (t1 join t2 on ((t1.y = t2.y))) 
  join 
  (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z))
);

prepare stmt1 from "select count(*) from v1 where x = ?";
set @parm1=1;

execute stmt1 using @parm1;
execute stmt1 using @parm1;
drop view v1;
drop table t1,t2,t3,t4;

#
# Bug #14540: OPTIMIZE, ANALYZE, REPAIR applied to not a view
#

CREATE TABLE t1(id INT);
CREATE VIEW v1 AS SELECT id FROM t1;

OPTIMIZE TABLE v1;
ANALYZE TABLE v1;
REPAIR TABLE v1;

DROP TABLE t1;
OPTIMIZE TABLE v1;
ANALYZE TABLE v1;
REPAIR TABLE v1;

DROP VIEW v1;


#
# BUG#14719: Views DEFINER grammar is incorrect
#

create definer = current_user() sql security invoker view v1 as select 1;
show create view v1;
drop view v1;

create definer = current_user sql security invoker view v1 as select 1;
show create view v1;
drop view v1;

#
# Bug #14816 test_if_order_by_key() expected only Item_fields.
#
create table t1 (id INT, primary key(id));
insert into t1 values (1),(2);
create view v1 as select * from t1;
explain select id from v1 order by id;
drop view v1;
drop table t1;

#
# Bug #14850 Item_ref's values wasn't updated
#
create table t1(f1 int, f2 int);
insert into t1 values (null, 10), (null,2);
select f1, sum(f2) from t1 group by f1;
create view v1 as select * from t1;
select f1, sum(f2) from v1 group by f1;
drop view v1;
drop table t1;

#
# BUG#14885: incorrect SOURCE in view created in a procedure
# TODO: here SOURCE string must be shown when it will be possible
#
--disable_warnings
drop procedure if exists p1;
--enable_warnings
delimiter //;
create procedure p1 () deterministic
begin
create view v1 as select 1;
end;
//
delimiter ;//
call p1();
show create view v1;
drop view v1;
drop procedure p1;

#
# BUG#15096: using function with view for view creation
#
CREATE VIEW v1 AS SELECT 42 AS Meaning;
--disable_warnings
DROP FUNCTION IF EXISTS f1;
--enable_warnings
DELIMITER //;
CREATE FUNCTION f1() RETURNS INTEGER
BEGIN
  DECLARE retn INTEGER;
  SELECT Meaning FROM v1 INTO retn;
  RETURN retn;
END
//
DELIMITER ;//
CREATE VIEW v2 AS SELECT f1();
select * from v2;
drop view v2,v1;
drop function f1;

#
# Bug#14861: aliased column names are not preserved.
#
create table t1 (id numeric, warehouse_id numeric);
create view v1 as select id from t1;
create view v2 as
select t1.warehouse_id, v1.id as receipt_id
from t1, v1 where t1.id = v1.id;

insert into t1 (id, warehouse_id) values(3, 2);
insert into t1 (id, warehouse_id) values(4, 2);
insert into t1 (id, warehouse_id) values(5, 1);

select v2.receipt_id as alias1, v2.receipt_id as alias2 from v2
order by v2.receipt_id;

drop view v2, v1;
drop table t1;

#
# Bug#16016: MIN/MAX optimization for views
#

CREATE TABLE t1 (a int PRIMARY KEY, b int);
INSERT INTO t1 VALUES (2,20), (3,10), (1,10), (0,30), (5,10);

CREATE VIEW v1 AS SELECT * FROM t1;

SELECT MAX(a) FROM t1;
SELECT MAX(a) FROM v1;

EXPLAIN SELECT MAX(a) FROM t1;
EXPLAIN SELECT MAX(a) FROM v1;

SELECT MIN(a) FROM t1;
SELECT MIN(a) FROM v1;

EXPLAIN SELECT MIN(a) FROM t1;
EXPLAIN SELECT MIN(a) FROM v1;

DROP VIEW v1;
DROP TABLE t1;

#
# Bug#16382: grouping name is resolved against a view column name
#            which coincides with a select column name

CREATE TABLE t1 (x varchar(10));
INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null);
CREATE VIEW v1 AS SELECT * FROM t1;

SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x;
SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x;
SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1;
SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y;
SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x;

DROP VIEW v1;
DROP TABLE t1;

#
# BUG#15943: mysql_next_result hangs on invalid SHOW CREATE VIEW
#

delimiter //;
drop table if exists t1; 
drop view if exists v1; 
create table t1 (id int); 
create view v1 as select * from t1; 
drop table t1; 
show create view v1; 
drop view v1;
//
delimiter ;//

#
# Bug#17726 Not checked empty list caused endless loop
#
create table t1(f1 int, f2 int);
create view v1 as select ta.f1 as a, tb.f1 as b from t1 ta, t1 tb where ta.f1=tb
.f1 and ta.f2=tb.f2;
insert into t1 values(1,1),(2,2);
create view v2 as select * from v1 where a > 1 with local check option;
select * from v2;
update v2 set b=3 where a=2;
select * from v2;
drop view v2, v1;
drop table t1;

#
# Bug #18386: select from view over a table with ORDER BY view_col clause 
#             given view_col is not an image of any column from the base table

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1), (2);

CREATE VIEW v1 AS SELECT SQRT(a) my_sqrt FROM t1;

SELECT my_sqrt FROM v1 ORDER BY my_sqrt;

DROP VIEW v1;
DROP TABLE t1;

#
# Bug #18237: invalid count optimization applied to an outer join with a view 
#             

CREATE TABLE t1 (id int PRIMARY KEY);
CREATE TABLE t2 (id int PRIMARY KEY);

INSERT INTO t1 VALUES (1), (3);
INSERT INTO t2 VALUES (1), (2), (3);

CREATE VIEW v2 AS SELECT * FROM t2;

SELECT COUNT(*) FROM t1 LEFT JOIN t2 ON t1.id=t2.id;
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id;

SELECT COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id;

DROP VIEW v2;

DROP TABLE t1, t2;

#
# Bug #16069: VIEW does return the same results as underlying SELECT
#             with WHERE condition containing BETWEEN over dates 

CREATE TABLE t1 (id int NOT NULL PRIMARY KEY,
                 td date DEFAULT NULL, KEY idx(td));

INSERT INTO t1 VALUES 
 (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'),
 (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'),
 (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06');

CREATE VIEW v1 AS SELECT * FROM t1;

SELECT * FROM t1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);
SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE);

DROP VIEW v1;
DROP TABLE t1;

#
# BUG#14308: Recursive view definitions
#
# using view only
create table t1 (a int);
create view v1 as select * from t1;
create view v2 as select * from v1;
drop table t1;
rename table v2 to t1;
-- erro