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, 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);
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;
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 order by 1;
SELECT MAX(a) FROM v1 order by 1;
SELECT MIN(a) FROM t1 order by 1;
SELECT MIN(a) FROM v1 order by 1;
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;

-- Different from ref. IDB throws error. The whole if function should be on the Group By list.
SELECT IF(x IS NULL, 'blank', 'not blank') FROM v1 GROUP BY x order by 1;

-- Alias override column name in InfiniDB. Different from MySQL ref
SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM t1 GROUP BY x 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 x FROM v1 GROUP BY x 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 order by 1;
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) order by 1;
SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.04' AS DATE) order by 1;
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; 
-- @bug 3391
-- SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75;
-- SELECT * FROM v1 order by 1;
-- select age from v1 order by 1;
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 order by 1;
DROP VIEW v1;

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

SELECT TRIM(TRAILING 'y' FROM s) FROM t1 order by 1; 
CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1;
SELECT * FROM v1 order by 1;
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 order by 1;
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 order by 1;
select * from v1 order by f1;
drop view v1;
drop table t1;