drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
drop table if exists t9;
drop table if exists t1a;
drop table if exists b;
drop table if exists v1;
drop table if exists v2;
drop table if exists v3;
drop table if exists v4;
drop table if exists v5;
drop table if exists v6;

drop view if exists t1;
drop view if exists t2;
drop view if exists t1a;
drop view if exists v;
drop view if exists v1;
drop view if exists v2;
drop view if exists v3;
drop view if exists v4;
drop view if exists v5;
drop view if exists v6;

DROP TABLE if exists `t_1`;
DROP VIEW if exists  `v_1`;

create table t1 (a int, b int) engine=infinidb;
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1 (c) as select b+1 from t1;
select c from v1 order by 1;
select * from v1 order by 1;
select v1.c from v1 order by 1;
create view v2 (d) as select c from v1;
select d from v2 order by 1;
drop view if exists v2;
create view v2 (e) as select c+1 from v1;
select e from v2 order by 1;
create view v3 (f) as select e+1 from v2;
select f from v3 order by 1;
drop view if exists v1;
drop view if exists v2;
drop view if exists v3;


create view v1 (c,d,e,f) as select a,b,
(select a+2 from t1 limit 1) e1, (select a from t1 limit 1) f1 from t1;
create view v2 as select c, d from v1;
select * from v1 order by 1,2,3,4;
select * from v2 order by 1, 2;

create or replace view v1 (c,d,e,f) as select a,b, (select a+2 from t1 limit 1) e1, (select a from t1 limit 1) f1 from t1;
select * from v1 order by 1, 2, 3, 4;

alter view v1 (c,d) as select a,max(b) from t1 group by a;

select * from v1 order by 1, 2;
select * from v2 order by 1, 2;

drop view if exists v1;
drop view if exists v2;
drop table t1;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3);

create view v1 (a) as select a from t1;
create view v2 (b) as select a from t1;

select a.a as a1, b.a as a2 from t1 a left join v1 b on a.a=b.a;
select a.b as a1, b.a as a2 from v2 a left join t1 b on a.b=b.a;
select a.a as a1, b.a as v2 from t1 a left join v1 b on a.a=b.a;

drop view v1, v2;
drop table t1;




create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3), (1), (2), (3);
create view v1 as select distinct a from t1;
select * from v1 order by 1;
select * from t1 order by 1;
drop view v1;
drop table t1;




create table t1 (a int, b int) engine=infinidb;
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1 (c) as select b from t1;
select test1.c from v1 test1;
create view v2 (d) as select b from t1;
select test2.d from v2 test2;
select test1.* from v1 test1, v2 test2 where test1.c=test2.d;
select test2.* from v1 test1, v2 test2 where test1.c=test2.d;
drop table t1;
drop view v1;
drop view v2;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3), (4);
create view v1 as select a+1 from t1 order by 1 desc limit 2;
select * from v1 order by 1;
drop view v1;
drop table t1;


create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3), (4);
create view v1 as select a+1 from t1;
create table t2 select * from v1;
select * from t2;
drop view v1;
drop table t1;
drop table t2 restrict;



create table t1 (a int, b int) engine=infinidb;
insert into t1 values (1,20), (2,30), (3,40), (4,50), (5,100);
create view v1 (c) as select b from t1 where a<=3;
select * from v1;
create view v2 (c) as select b from t1 where a>=2;
select v1.* from v1, v2 where v1.c = v2.c;
drop view v1;
drop view v2;
drop table t1;



create table t1 (a int, b int, c int) engine=infinidb;
insert into t1 values (10,2,-1), (20,3,-2), (30,4,-3), (40,5,-4), (50,10,-5);
create view v1 (x,y) as select a, b from t1;
create view v2 (x,y) as select a, c from t1;
select * from v1;
select * from v2;
select a.y as a1, b.y as b1 from v1 a, v2 b where a.x = b.x;
drop table t1;
drop view v1;
drop view v2;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3);
create view v1 (x) as select a from t1 where a > 1;
select t1.a, v1.x from t1 left join v1 on (t1.a= v1.x);
drop table t1;
drop view v1;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2), (3), (200);
create view v1 (x) as select a from t1 where a > 1;
create view v2 (y) as select x from v1 where x < 100;
select * from v2;
drop table t1;
drop view v1;
drop view v2;



create table t1 (t_column char(1)) engine=infinidb;
insert into t1 values ('a');
create view v1 as select 'a';
select v1.a, t1.t_column from v1, t1 where v1.a = t1.t_column;
drop view v1;
drop table t1;



create table t1 (col1 char(5),col2 char(5)) engine=infinidb;
insert into t1 values ('abc', 'def');
create view v1 as select * from t1;
alter table t1 add column col3 char(5);
insert into t1 values ('ghi', 'jkl', 'mno');
select * from v1;
drop view v1;
drop table t1;



create table t1 (col1 char(5),col2 char(5)) engine=infinidb;
create view v1 (col1,col2) as select col1,col2 from t1;
insert into t1 values('s1','p1'),('s1','p2'),('s1','p3'),('s1','p4'),('s2','p1'),('s3','p2'),('s4','p4');
select distinct first.col2 from t1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1) order by 1;
select distinct first.col2 from v1 first where first.col2 in (select second.col2 from t1 second where second.col1<>first.col1) order by 1;
drop view v1;
drop table t1;



create table t1 (a int) engine=infinidb;
prepare create_view from "create view v1 as select a from t1";
execute create_view;
insert into t1 values (1), (2), (3);
prepare prep_select from "select v_1.a from v1 v_1 where v_1.a < 3";
execute prep_select;
drop view v1;
drop table t1;


CREATE VIEW v1 (f1) AS SELECT pi();
select * from v1;
drop view v1;



create table t1 (s1 int) engine=infinidb;
create table t2 (s2 int) engine=infinidb;
insert into t1 values (1), (2);
insert into t2 values (2), (3);
create view v1 as select * from t1 union all select * from t2;
select * from v1 order by 1;
create view v2 as select * from t1 union select * from t2;
select * from v2 order by 1;
drop view v1;
drop view v2;
drop table t1;
drop table t2;



create table t1 (col1 int) engine=infinidb;
insert into t1 values (1);
create view v1 as select count(*) from t1;
select * from v1;
insert into t1 values (null);
select * from v1;
drop view v1;
drop table t1;



create table t1 (a int) engine=infinidb;
create table t2 (a int) engine=infinidb;
insert into t1 values (1), (2);
insert into t2 values (1), (2), (3);
create view v1 as select a from t1;
create view v2 as select a from t2 where a in (select a from v1);
select * from v1;
select * from v2;
drop view v2;
drop view v1;
drop table t1;
drop table t2;



create table t1 (a int) engine=infinidb;
insert into t1 values (1),(1),(2),(2),(3),(3);
create view v1 as select a from t1 order by a;
select distinct a from v1;
select distinct a from v1 limit 2;
select distinct a from t1 order by a limit 2;
prepare stmt1 from "select distinct a from v1 limit 2";
execute stmt1;
drop view v1;
drop table t1;



create table t1 (tg_column bigint) engine=infinidb;
insert into t1 values (1), (2), (3), (4);
create view v1 as select count(tg_column) as vg_column from t1;
select avg(vg_column) from v1;
drop view v1;
drop table t1;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2);
create view v1 as select 5 from t1 order by 1;
select * from v1;
drop view v1;
drop table t1;



create table t1 (a int, b int) engine=infinidb;
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
create view v1(c) as select a from t1 where b >= 4;
select c from v1 where exists (select * from t1 where a=2 and b=c);
drop view v1;
drop table t1;



create table t1 (a int) engine=infinidb;
insert into t1 values (1), (2);
create view v1 as select a from t1;
create view v3 as select a from t1;
rename table v1 to v2;
select * from v2;
rename table v3 to v1, v2 to v3;
select * from v1;
select * from v3;
drop table t1;
drop view v1;
drop view v3;



create table t1(a int) engine=infinidb;
insert into t1 values (0), (1), (2), (3);
create table t2 (a int) engine=infinidb;
insert into t2 values (2), (3);
create view v1 as select a from t1 where a > 1;
select t1.* as a, t.a as b, v1.a as c from t1 left join t2 as t on t1.a=t .a left join v1 on v1.a=t1.a order by 1, 2, 3;
drop view v1;
drop table t1;
drop table t2;




create table t1 (a int) engine=infinidb;
create table t2 (a int) engine=infinidb;
create table t3 (a int) engine=infinidb;
insert into t1 values (1), (2), (3);
insert into t2 values (1), (3);
insert into t3 values (1), (2), (4);
create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a);
select * from t3 left join v3 on (t3.a = v3.a);
create view v1 (a) as select a from t1;
create view v2 (a) as select a from t2;
create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a);
select * from t3 left join v4 on (t3.a = v4.a);
prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a)";
execute stmt1;
drop view v4;
drop view v3;
drop view v2;
drop view v1;
drop table t1;
drop table t2;
drop table t3;



create table t1(f1 int) engine=infinidb;
insert into t1 values (1), (2);
create view v1 as select f1 from t1;
select * from v1 where F1 = 1;
drop view v1;
drop table t1;



create table t1(c1 int) engine=infinidb;
create table t2(c2 int) engine=infinidb;
insert into t1 values (1),(2),(3);
insert into t2 values (1);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
select * from v1;
select * from v2;
select * from (select c1 from v2) X;
drop view v2;
drop view v1;
drop table t1;
drop table t2;



CREATE TABLE t1 (C1 INT, C2 INT) engine=infinidb;
CREATE TABLE t2 (C2 INT) engine=infinidb;
insert into t1 values (1,1), (2,2), (3,3), (4,4);
insert into t2 values (2), (4);
CREATE VIEW v1 AS SELECT C2 FROM t2;
CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2);
SELECT * FROM v2;
drop view v2;
drop view v1;
drop table t1;
drop table t2;




CREATE TABLE t1 (a1 int) engine=infinidb;
CREATE TABLE t2 (a2 int) engine=infinidb;
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1), (2), (3);
CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;
drop table t2;



CREATE TABLE t1 (col1 int, col2 varchar(10)) engine=infinidb; 
INSERT INTO t1 VALUES(1,'trudy'); 
INSERT INTO t1 VALUES(2,'peter'); 
INSERT INTO t1 VALUES(3,'sanja'); 
INSERT INTO t1 VALUES(4,'monty'); 
INSERT INTO t1 VALUES(5,'david'); 
INSERT INTO t1 VALUES(6,'kent'); 
INSERT INTO t1 VALUES(7,'carsten'); 
INSERT INTO t1 VALUES(8,'ranger'); 
INSERT INTO t1 VALUES(10,'matt'); 
CREATE TABLE t2 (col1 int, col2 int, col3 char(1)) engine=infinidb; 
INSERT INTO t2 VALUES (1,1,'y'); 
INSERT INTO t2 VALUES (1,2,'y'); 
INSERT INTO t2 VALUES (2,1,'n'); 
INSERT INTO t2 VALUES (3,1,'n'); 
INSERT INTO t2 VALUES (4,1,'y'); 
INSERT INTO t2 VALUES (4,2,'n'); 
INSERT INTO t2 VALUES (4,3,'n'); 
INSERT INTO t2 VALUES (6,1,'n'); 
INSERT INTO t2 VALUES (8,1,'y');
 
CREATE VIEW v1 AS SELECT * FROM t1; 

SELECT a.col1,a.col2,b.col2,b.col3 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 WHERE b.col2=(SELECT MAX(col2) FROM t2 b2 WHERE b2.col1=b.col1) order by 1, 2;
SELECT a.col1,a.col2,b.col2,b.col3 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 WHERE b.col2=(SELECT MAX(col2) FROM t2 b2 WHERE b2.col1=b.col1) order by 1, 2;

CREATE VIEW v2 AS SELECT * FROM t2; 

SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE b.col2=(SELECT MAX(col2) FROM v2 b2 WHERE b2.col1=b.col1) order by 1, 2;
SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE a.col1 IN (1,5,9) AND b.col2=(SELECT MAX(col2) FROM v2 b2 WHERE b2.col1=b.col1) order by 1, 2;
SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE b.col1 IN (1,5,9) AND b.col2=(SELECT MAX(col2) FROM v2 b2 WHERE b2.col1=b.col1) order by 1, 2;

CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9);

SELECT a.col1,a.col2,b.col2,b.col3 FROM t2 b RIGHT JOIN t1 a ON a.col1=b.col1 WHERE a.col1 in (1,5,9) AND b.col2=(SELECT MAX(col2) FROM t2 b2 WHERE b2.col1=b.col1) order by 1, 2;
SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 WHERE b.col2=(SELECT MAX(col2) FROM v2 b2 WHERE b2.col1=b.col1) order by 1, 2;

DROP VIEW v1;
drop view v2;
drop view v3;
DROP TABLE t1;
drop table t2;



CREATE TABLE t1 (a int) engine=infinidb;
CREATE TABLE t2 (b int) engine=infinidb;
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (4), (2);
CREATE VIEW v1 AS SELECT * FROM t1,t2 WHERE t1.a=t2.b;
SELECT * FROM v1;
CREATE VIEW v2 AS SELECT * FROM v1;
SELECT * FROM v2;
DROP VIEW v2;
drop view v1;
DROP TABLE t1;
drop table t2;




CREATE TABLE t1(a char(2), b char(2)) engine=infinidb;
CREATE TABLE t2(a char(2), b char(2)) engine=infinidb;
INSERT INTO t1 VALUES ('a','1'), ('b','2');
INSERT INTO t2 VALUES ('a','5'), ('a','6'), ('b','5'), ('b','6');
CREATE VIEW v1 AS
  SELECT t1.b as c, t2.b as d FROM t1,t2 WHERE t1.a=t2.a;
SELECT d, c FROM v1 ORDER BY d,c;
DROP VIEW v1;
DROP TABLE t1;
drop table t2;



create table t1 (s1 int) engine=infinidb;
insert into t1 values (1), (10), (50), (100);
create view  v1 as select sum(distinct s1) from t1;
select * from v1;
drop view v1;
create view  v1 as select avg(distinct s1) from t1;
select * from v1;
drop view v1;
drop table t1;


create table t1 (f1 date) engine=infinidb;
insert into t1 values ('2005-01-01'),('2005-02-02');
create view v1 as select * from t1;
select * from v1 where f1='2005-02-02';
select * from v1 where '2005-02-02'=f1;
drop view v1;
drop table t1;


create table t1 (f1 int) engine=infinidb;
insert into t1 values (1);
create view v1 as select f1 from t1;
select f1 as alias_1 from v1;
drop view v1;
drop table t1;





create table t1 (f1 int, f2 int) engine=infinidb;
create view v1 as select f1 as f3, f2 as f1 from t1;
insert into t1 values (1,3),(2,1),(3,2);
select * from v1 order by f1;
drop view v1;
drop table t1;



CREATE TABLE t1 (f1 char) engine=infinidb;
INSERT INTO t1 VALUES ('A');
CREATE VIEW  v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES('B');
SELECT * FROM v1;
SELECT * FROM t1;
DROP VIEW v1;
DROP TABLE t1;



CREATE TABLE t1 (id int, f varchar(255)) engine=infinidb;
CREATE VIEW v1 AS SELECT id, f FROM t1 WHERE id <= 2;
INSERT INTO t1 VALUES (2, 'foo2');
INSERT INTO t1 VALUES (1, 'foo1');
SELECT * FROM v1;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;



create table t1 (f1 int) engine=infinidb;
create table t2 (f1 int) engine=infinidb;
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 order by 1;
drop view v1;
drop table t1;
drop table t2;


create table t1 (f1 int, f2 int,f3 int) engine=infinidb;
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, f2, f3 order by 1;
select if(sum(f1)>1,f2,f3) from v1 group by f2, f3 order by 1;
drop view v1;
drop table t1;


create table t1 (r_object_id char(16), group_name varchar(32)) engine=infinidb;
create table t2 (r_object_id char(16), i_position int(11), users_names varchar(32)) engine=infinidb;
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;
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;
drop view v2;
drop table t1;
drop table t2;



create table t1 (s1 int) engine=infinidb;
insert into t1 values (1), (2);
drop view if exists abc;
drop table if exists abc;
create view abc as select abc.* from t1 abc;
drop table t1;
drop view abc;



create table t1 (f1 char(1)) engine=infinidb;
insert into t1 values (1), (2);
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;



CREATE TABLE t1 (datea DATE) engine=infinidb;
INSERT INTO  t1 VALUES ('2005-09-06');
CREATE VIEW v1 AS SELECT DAYNAME(datea) FROM t1;
CREATE VIEW v2 AS SELECT DAYOFWEEK(datea) FROM t1;
CREATE VIEW v3 AS SELECT WEEKDAY(datea) FROM t1;
SELECT DAYNAME('2005-09-06');
SELECT DAYNAME(datea) FROM t1;
SELECT * FROM v1;
SELECT DAYOFWEEK('2005-09-06');
SELECT DAYOFWEEK(datea) FROM t1;
SELECT * FROM v2;
SELECT WEEKDAY('2005-09-06');
SELECT WEEKDAY(datea) FROM t1;
SELECT * FROM v3;
DROP TABLE t1;
DROP VIEW v1;
drop view v2;
drop view v3;



CREATE TABLE t1 (a int, b int) engine=infinidb;
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 order by 1;
SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1 order by 1;
DROP VIEW v1;
DROP TABLE t1;



CREATE TABLE t1 ( a int, b int ) engine=infinidb;
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 order by 1;
SELECT v1.a FROM v1 GROUP BY v1.a HAVING v1.a > 1 order by 1;
SELECT t_1.a FROM t1 AS t_1 GROUP BY t_1.a HAVING t_1.a IN (1,2,3) order by 1;
SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3) order by 1;
DROP VIEW v1;
DROP TABLE t1;



create table t1 (x int, y int) engine=infinidb;
create table t2 (x int, y int, z int) engine=infinidb;
create table t3 (x int, y int, z int) engine=infinidb;
create table t4 (x int, y int, z int) engine=infinidb;
insert into t1 values (1, 1), (2, 2), (3, null);
insert into t2 values (1, 1, 1), (2, 2, null), (3, null, null);
insert into t3 values (1, 1, 1), (2, 2, null), (3, null, null);
insert into t4 values (1, 1, 1), (2, 2, null), (3, null, null);
select t1.x from ((t1 join t2 on ((t1.y = t2.y))) join t3 on t3.y=t2.x left join t4 on (t3.y = t4.y) and (t3.z = t4.z));
create view v1 as select t1.x from ((t1 join t2 on ((t1.y = t2.y))) join t3 on t3.y=t2.x 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;
drop table t2;
drop table t3;
drop table t4;



create table t1 (id numeric, warehouse_id numeric) engine=infinidb;
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;
drop view v1;
drop table t1;



CREATE TABLE t1 (a int, b int) engine=infinidb;
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;
SELECT MIN(a) FROM t1;
SELECT MIN(a) FROM v1;
DROP VIEW v1;
DROP TABLE t1;



CREATE TABLE t1 (x varchar(10)) engine=infinidb;
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 1 order by 1;
SELECT IF(x IS NULL, 'blank', 'not blank') AS x2 FROM t1 GROUP BY x2 order by 1;
SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y order by 1;
DROP VIEW v1;
DROP TABLE t1;



CREATE TABLE t1 (id int) engine=infinidb;
CREATE TABLE t2 (id int) engine=infinidb;
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 COUNT(*) FROM t1 LEFT JOIN v2 ON t1.id=v2.id;
SELECT * FROM t1 LEFT JOIN v2 ON t1.id=v2.id;
DROP VIEW v2;
DROP TABLE t1;
drop table t2;



CREATE TABLE t1 (id int, td date) engine=infinidb;
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;


CREATE TABLE t1 (fName varchar(25), lName varchar(25), DOB date, test_date date, uID int) engine=infinidb;
INSERT INTO t1(fName, lName, DOB, test_date) VALUES
  ('Hank', 'Hill', '1964-09-29', '2007-01-01'),
  ('Tom', 'Adams', '1908-02-14', '2007-01-01'),
  ('Homer', 'Simpson', '1968-03-05', '2007-01-01');
CREATE VIEW v1 AS SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; 
SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75;
SELECT * FROM v1;
select age from v1;
create view v2 as SELECT (year(test_date)-year(DOB)) AS Age FROM t1 WHERE (year(test_date)-year(DOB)) < 75; 
select * from v2;
select Age from v2;
DROP VIEW v1;
drop view v2;
DROP TABLE t1;



CREATE TABLE t1 (s varchar(10)) engine=infinidb;
INSERT INTO t1 VALUES ('yadda'), ('yady');

SELECT TRIM(BOTH 'y' FROM s) FROM t1;
CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1;
SELECT * FROM v1;
DROP VIEW v1;

SELECT TRIM(LEADING 'y' FROM s) FROM t1; 
CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1;
SELECT * FROM v1;
DROP VIEW v1;

SELECT TRIM(TRAILING 'y' FROM s) FROM t1; 
CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
SELECT * FROM v1;
DROP VIEW v1;

DROP TABLE t1;




CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL;
SELECT !0 * 5 AS x FROM DUAL;
SELECT * FROM v;
DROP VIEW v;



CREATE TABLE t1 (mydate DATETIME) engine=infinidb;
INSERT INTO t1 VALUES ('2007-01-01'), ('2007-01-02'), ('2007-01-30'), ('2007-01-31');
CREATE VIEW v1 AS SELECT mydate from t1;
SELECT * FROM t1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31';
DROP VIEW v1;
DROP TABLE t1;



create table t1(f1 int, f2 int) engine=infinidb;
insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2);
select * from t1;
create view v1 as select * from t1 order by f2;
select * from v1;
select * from v1 order by f1;
drop view v1;
drop table t1;



CREATE TABLE t1 (id int(11), country varchar(32), code int(11)) engine=infinidb;
INSERT INTO t1 VALUES (1,'ITALY',100),(2,'ITALY',200),(3,'FRANCE',100), (4,'ITALY',100);
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT code, COUNT(DISTINCT country) FROM t1 GROUP BY code ORDER BY code;
SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY code;
DROP VIEW v1;
DROP TABLE t1;



create table table_24532 (
  a int,
  b bigint,
  c int(4),
  d bigint(48)
) engine=infinidb;

create view view_24532_a as
select
  a IS TRUE,
  a IS NOT TRUE,
  a IS FALSE,
  a IS NOT FALSE,
  a IS UNKNOWN,
  a IS NOT UNKNOWN,
  a is NULL,
  a IS NOT NULL,
  ISNULL(a),
  b IS TRUE,
  b IS NOT TRUE,
  b IS FALSE,
  b IS NOT FALSE,
  b IS UNKNOWN,
  b IS NOT UNKNOWN,
  b is NULL,
  b IS NOT NULL,
  ISNULL(b),
  c IS TRUE,
  c IS NOT TRUE,
  c IS FALSE,
  c IS NOT FALSE,
  c IS UNKNOWN,
  c IS NOT UNKNOWN,
  c is NULL,
  c IS NOT NULL,
  ISNULL(c),
  d IS TRUE,
  d IS NOT TRUE,
  d IS FALSE,
  d IS NOT FALSE,
  d IS UNKNOWN,
  d IS NOT UNKNOWN,
  d is NULL,
  d IS NOT NULL,
  ISNULL(d)
from table_24532;

create view view_24532_b as
select
  a IS TRUE,
  if(ifnull(a, 0), 1, 0) as old_istrue,
  a IS NOT TRUE,
  if(ifnull(a, 0), 0, 1) as old_isnottrue,
  a IS FALSE,
  if(ifnull(a, 1), 0, 1) as old_isfalse,
  a IS NOT FALSE,
  if(ifnull(a, 1), 1, 0) as old_isnotfalse
from table_24532;

insert into table_24532 values (0, 0, 0, 0);
select * from view_24532_a;
select * from view_24532_b;
update table_24532 set a=1;
select * from view_24532_a;
select * from view_24532_b;
update table_24532 set a=NULL;
select * from view_24532_a;
select * from view_24532_b;

drop view view_24532_a;
drop view view_24532_b;
drop table table_24532;


CREATE TABLE t1 (lid int, name char(10)) engine=infinidb;
INSERT INTO t1 (lid, name) VALUES (1, 'YES'), (2, 'NO');

CREATE TABLE t2 (  id int, gid int, lid int, dt date) engine=infinidb;
INSERT INTO t2 (id, gid, lid, dt) VALUES
 (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'),
 (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02');

SELECT DISTINCT t2.gid AS lgid,(SELECT t1.name FROM t1, t2 WHERE t1.lid  = t2.lid AND t2.gid = t1.lid and t1.lid =1 ) as clid   FROM t2 order by 1, 2;


CREATE VIEW v1 AS SELECT DISTINCT t2.gid AS lgid,(SELECT t1.name FROM t1, t2 WHERE t1.lid  = t2.lid AND t2.gid = t1.lid and t1.lid =1 ) as clid   FROM t2;
SELECT * FROM v1 order by 1, 2;

DROP VIEW v1;
DROP table t1;
drop table t2;



CREATE TABLE t1 (a INT) engine=infinidb; 
INSERT INTO t1 VALUES (1),(2),(3);
CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a;

SELECT * FROM t1 UNION SELECT * FROM v1;
SELECT * FROM v1 UNION SELECT * FROM t1;
SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a;

DROP VIEW v1;
DROP TABLE t1;




CREATE TABLE t1 (a INT) engine=infinidb;
CREATE TABLE t2 (b INT, c INT) engine=infinidb;
INSERT INTO t1 (a) VALUES (1), (2);
INSERT INTO t2 (b) VALUES (1), (2);
CREATE VIEW v1 AS SELECT t2.b,t2.c FROM t1, t2
  WHERE t1.a=t2.b AND t2.b < 3;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;
drop table t2;



CREATE TABLE t1 (a int, b int) engine=infinidb;
INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2);
CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1;
SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b ORDER BY 1;
SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a ORDER BY 1;
SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a ORDER BY 1;
DROP VIEW v1;
DROP TABLE t1;



CREATE TABLE t1 (person_id int, username varchar(40), status_flg char(1)) engine=infinidb;
CREATE TABLE t2 (person_role_id int, role_id int, person_id int) engine=infinidb;
CREATE TABLE t3 (role_id int, role_name varchar(100), app_name varchar(40)) engine=infinidb;

CREATE VIEW v1 AS 
SELECT profile.person_id AS person_id
  FROM t1 profile, t2 userrole, t3 role
    WHERE userrole.person_id = profile.person_id AND
          role.role_id = userrole.role_id AND
          profile.status_flg = 'A'
  ORDER BY profile.person_id,role.app_name,role.role_name;

INSERT INTO  t1 VALUES
 (6,'Sw','A'), (-1136332546,'ols','e'), (0,'    *\n','0'),
 (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0');
INSERT INTO t2 VALUES
  (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10);

INSERT INTO t3 VALUES 
  (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'),
  (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'),
  (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'),
  (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'),
  (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS');
 
SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6;
DROP VIEW v1;
DROP TABLE t1;
drop table t2;
drop table t3;



create table t1 (i int) engine=infinidb;
insert into t1 values (1), (2), (1), (3), (2), (4);
create view v1 as select distinct i from t1;
select * from v1 order by 1;
drop view v1;
drop table t1;



CREATE TABLE `t_1` (c1 INT) engine=infinidb;
CREATE VIEW  `v_1` AS SELECT c1 FROM `t_1`;
insert into `t_1` values (1), (2);
select * from `v_1`;
RENAME TABLE `t_1` TO `t_2`;
RENAME TABLE `v_1` TO `v_2`;
ALTER VIEW  `v_2` AS SELECT c1 FROM `t_2`;
select * from `v_2`;
DROP TABLE `t_2`;
DROP VIEW  `v_2`;



CREATE TABLE t1(c1 INT, c2 INT) engine=infinidb;
INSERT INTO t1 VALUES (1, 2), (3, 4);
SELECT * FROM t1;
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1;
ALTER TABLE t1 ADD COLUMN c3 INT;
SELECT * FROM t1;
SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;