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;
insert into t1 values (1),(1),(2),(2),(3),(3);
create view v1 as select a from t1;
select distinct a from v1 order by a;
select distinct a from v1 order by a limit 2;
select distinct a from t1 order by a limit 2;
prepare stmt1 from "select distinct a from v1 order by a 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+1 from t1 where b >= 4;

select c from v1 v where exists (select * from t1 t where a=2 and t.b=v.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 view v1 as select a from t1 where a > 1;
select t1.a as bbb, v1.a from t1 left join v1 on v1.a=t1.a order by 1, 2;
drop view v1;
drop table t1;

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 as bbb, v3.b as ccc 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 as bbb, v4.a as ccc from t3 left join v4 on (t3.a = v4.a);
prepare stmt1 from "select t3.a as bbb, v4.a as ccc 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;
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; 

-- the following queries contain unsupported subquery items. OR operator with subquery
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 c WHERE c.col1=b.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 c WHERE b.col1=b.col1);

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

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

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 c WHERE c.col1=b.col1); 

# Added for Bug 5764. 
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;