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;


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 t3.a a1, v3.a a2, v3.b a3 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 t3.a a1, v4.a a2, v4.b a3 from t3 left join v4 on (t3.a = v4.a);
prepare stmt1 from "select t3.a a1, v4.a a2, v4.b a3 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 order by 1;
select * from v2 order by 1;
select * from (select c1 from v2) X order by 1;
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 order by 1;
CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2) order by 1;
SELECT * FROM v2 order by 1;
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; 

-- The following two queries contain subquery that are currently not supported by IDB.
-- Scalar filter and semi join are not from the same pair of tables.
SELECT a.col1,a.col2,b.col2,b.col3 
  FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1
    WHERE b.col2 IS NULL OR 
          b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);

SELECT a.col1,a.col2,b.col2,b.col3 
  FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1
    WHERE b.col2 IS NULL OR 
          b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1);

CREATE VIEW v2 AS SELECT * FROM t2; 

-- The following two queries contain subquery that are currently not supported by IDB.
-- Scalar filter and semi join are not from the same pair of tables.
SELECT a.col1,a.col2,b.col2,b.col3
  FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1
    WHERE b.col2 IS NULL OR
          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 

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 IS NULL OR
          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); 

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

-- The following querie contain subquery that are currently not supported by IDB.
-- Scalar filter and semi join are not from the same pair of tables.

SELECT a.col1,a.col2,b.col2,b.col3
  FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1
    WHERE b.col2 IS NULL OR
          b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); 
 
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 desc;
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);
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;
-- comment out show create view. won't compare with ref.
-- SHOW CREATE VIEW v1;
CREATE VIEW v2 AS SELECT DAYOFWEEK(datea) FROM t1;
-- SHOW CREATE VIEW v2;
CREATE VIEW v3 AS SELECT WEEKDAY(datea) FROM t1;
-- SHOW CREATE VIEW v3;
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;