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(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');

-- The following two queries contain unsupported subquery items. Limit clause in correlated subquery.
SELECT DISTINCT t2.gid AS lgid,
                (SELECT t1.name FROM t1, t2
                   WHERE t1.lid  = t2.lid AND t2.gid = lgid
                     ORDER BY t2.dt DESC LIMIT 1
                ) as clid
  FROM t2;

CREATE VIEW v1 AS
SELECT DISTINCT t2.gid AS lgid,
                (SELECT t1.name FROM t1, t2
                   WHERE t1.lid  = t2.lid AND t2.gid = lgid
                     ORDER BY t2.dt DESC LIMIT 1
                ) as clid
  FROM t2;
SELECT * FROM v1;

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 order by 1;
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 order by 1, 2;
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;


drop table if exists t_1;
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 order by 1;
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1 order by 1;
ALTER TABLE t1 ADD COLUMN c3 INT;
SELECT * FROM t1 order by 1;
SELECT * FROM v1 order by 1;
DROP VIEW v1;
DROP TABLE t1;