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 (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 order by 1;
SELECT * FROM v1 order by 1;
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;

-- f2, f3 is missing from the GROUP BY list.
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' order by 1;
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 order by 1;
SELECT DAYOFWEEK('2005-09-06');
SELECT DAYOFWEEK(datea) FROM t1;
SELECT * FROM v2 order by 1;
SELECT WEEKDAY('2005-09-06');
SELECT WEEKDAY(datea) FROM t1;
SELECT * FROM v3 order by 1;
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;
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);
create view v1 as select t1.x from t1 join t2 on t1.y = t2.y left join t3 on (t2.y = t3.y) and (t2.z = t3.z);
prepare stmt1 from "select count(*) from v1 where x = ?";
set @parm1=1;
execute stmt1 using @parm1;
drop view v1;
drop table t1;
drop table t2;
drop table t3;



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 IF(x IS NULL, 'blank', 'not blank') order by 1 ;
SELECT IF(x IS NULL, 'blank', 'not blank') AS y FROM v1 GROUP BY y order by 1;
SELECT IF(x IS NULL, 'blank', 'not blank') AS a FROM v1 GROUP BY a 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 t1.id id1, v2.id id2 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; 

-- The following three queries has a separate bug: bug3391
-- SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75;
-- SELECT * FROM v1;
-- select age from v1;
DROP VIEW v1;
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;