github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view-subquery-with-exists.sql (about)

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for  subquery with  exists
     5  -- @label:bvt
     6  SELECT EXISTS(SELECT 1+1);
     7  create view v1 as SELECT EXISTS(SELECT 1+1);
     8  select * from v1;
     9  drop view v1;
    10  
    11  drop table if exists t1;
    12  drop table if exists t2;
    13  drop table if exists t3;
    14  drop table if exists t4;
    15  drop table if exists t5;
    16  drop table if exists t6;
    17  drop table if exists t7;
    18  create table t1 (a int);
    19  create table t2 (a int, b int);
    20  create table t3 (a int);
    21  create table t4 (a int not null, b int not null);
    22  insert into t1 values (2);
    23  insert into t2 values (1,7),(2,7);
    24  insert into t4 values (4,8),(3,8),(5,9);
    25  insert into t3 values (6),(7),(3);
    26  select * from t3 where exists (select * from t2 where t2.b=t3.a);
    27  select * from t3 where not exists (select * from t2 where t2.b=t3.a);
    28  create view v1 as select * from t3 where exists (select * from t2 where t2.b=t3.a);
    29  create view v2 as select * from t3 where not exists (select * from t2 where t2.b=t3.a);
    30  select * from v1;
    31  select * from v2;
    32  drop view v1;
    33  drop view v2;
    34  
    35  insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
    36  insert into t2 values (2,10);
    37  create table t5 (a int);
    38  insert into t5 values (5);
    39  insert into t5 values (2);
    40  create table t6 (patient_uq int, clinic_uq int);
    41  create table t7( uq int primary key, name char(25));
    42  insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
    43  insert into t6 values (1,1),(1,2),(2,2),(1,3);
    44  select * from t6 where exists (select * from t7 where uq = clinic_uq);
    45  create view v1 as select * from t6 where exists (select * from t7 where uq = clinic_uq);
    46  select * from v1;
    47  drop view v1;
    48  
    49  
    50  drop table if exists t1;
    51  drop table if exists t2;
    52  drop table if exists t3;
    53  drop table if exists t4;
    54  drop table if exists t5;
    55  drop table if exists t6;
    56  drop table if exists t7;
    57  CREATE TABLE `t1` (
    58    `numeropost` int(8) unsigned NOT NULL,
    59    `maxnumrep` int(10) unsigned NOT NULL default 0,
    60    PRIMARY KEY  (`numeropost`)
    61  );
    62  
    63  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
    64  
    65  CREATE TABLE `t2` (
    66        `mot` varchar(30) NOT NULL default '',
    67        `topic` int(8) unsigned NOT NULL default 0,
    68        `dt` date,
    69        `pseudo` varchar(35) NOT NULL default ''
    70      );
    71  
    72  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
    73  SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND dt >= date'2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
    74  
    75  create view v1 as SELECT numeropost,maxnumrep FROM t1 WHERE exists (SELECT 1 FROM t2 WHERE (mot='joce') AND dt >= date'2002-10-21' AND t1.numeropost = t2.topic) ORDER BY maxnumrep DESC LIMIT 0, 20;
    76  
    77  select * from v1;
    78  drop view v1;
    79  
    80  drop table if exists t1;
    81  drop table if exists t2;
    82  drop table if exists t3;
    83  CREATE TABLE `t1` (
    84    `mot` varchar(30) NOT NULL default '',
    85    `topic` int(8) unsigned NOT NULL default 0,
    86    `dt` date ,
    87    `pseudo` varchar(35) NOT NULL default ''
    88  );
    89  CREATE TABLE `t2` (
    90    `mot` varchar(30) NOT NULL default '',
    91    `topic` int(8) unsigned NOT NULL default 0,
    92    `dt` date,
    93    `pseudo` varchar(35) NOT NULL default ''
    94  ) ;
    95  CREATE TABLE `t3` (
    96    `numeropost` int(8) unsigned NOT NULL,
    97    `maxnumrep` int(10) unsigned NOT NULL default 0,
    98    PRIMARY KEY  (`numeropost`)
    99  );
   100  INSERT INTO t1 VALUES ('joce','1',null,'joce'),('test','2',null,'test');
   101  INSERT INTO t2 VALUES ('joce','1',null,'joce'),('test','2',null,'test');
   102  INSERT INTO t3 VALUES (1,1);
   103  SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic);
   104  DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
   105  select * from t1;
   106  create view v1 as select * from t1;
   107  select * from v1;
   108  drop view v1;
   109  drop table if exists t1;
   110  drop table if exists t2;
   111  drop table if exists t3;
   112  
   113  create table t1 (a int, b int);
   114  insert into t1 values (1,2),(3,4);
   115  select * from t1 up where exists (select * from t1 where t1.a=up.a);
   116  create view v1 as select * from t1 up where exists (select * from t1 where t1.a=up.a);
   117  select * from v1;
   118  drop view v1;
   119  
   120  
   121  drop table if exists t1;
   122  drop table if exists t2;
   123  drop table if exists t3;
   124  CREATE TABLE t1 (a INT, b INT);
   125  INSERT INTO t1 VALUES (1,1),(2,2);
   126  CREATE TABLE t2 (a INT, b INT);
   127  INSERT INTO t2 VALUES (1,1),(2,2);
   128  CREATE TABLE t3 (a INT, b INT);
   129  -- @bvt:issue#3307
   130  SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   131  SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   132  create view v1 as SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   133  create view v2 as SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   134  select * from v1;
   135  select * from v2;
   136  drop view v1;
   137  drop view v2;
   138  -- @bvt:issue
   139  
   140  drop table if exists t1;
   141  drop table if exists t2;
   142  drop table if exists t3;
   143  CREATE TABLE t1 (f1 varchar(1));
   144  INSERT INTO t1 VALUES ('v'),('s');
   145  CREATE TABLE t2 (f1_key varchar(1));
   146  INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),('d'),('y'),('t'),('d'),('s');
   147  SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
   148  WHERE EXISTS
   149  (
   150  SELECT DISTINCT f1_key
   151  FROM t2
   152  WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
   153  
   154  
   155  create view v1 as SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
   156  WHERE EXISTS
   157  (
   158  SELECT DISTINCT f1_key
   159  FROM t2
   160  WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
   161  select * from v1;
   162  drop view v1;
   163  
   164  drop table if exists t1;
   165  drop table if exists t2;
   166  drop table if exists t3;
   167  CREATE TABLE t1( pk int PRIMARY KEY,uk int,ukn int NOT NULL,ik int,d int);
   168  INSERT INTO t1 VALUES (0, NULL, 0, NULL, NULL),(1, 10, 20, 30, 40),(2, 20, 40, 60, 80);
   169  CREATE TABLE t2(pk int PRIMARY KEY);
   170  INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),
   171  (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
   172  (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
   173  (31),(32),(33),(34),(35),(36),(37),(38),(39),(40),
   174  (41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
   175  (51),(52),(53),(54),(55),(56),(57),(58),(59),(60),
   176  (61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
   177  (71),(72),(73),(74),(75),(76),(77),(78),(79),(80);
   178  SELECT 1  WHERE EXISTS (SELECT * FROM t1 AS it);
   179  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1);
   180  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1 WHERE FALSE);
   181  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it);
   182  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = 1);
   183  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = 1);
   184  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik = 1);
   185  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = 1);
   186  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   187  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.uk);
   188  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.ukn);
   189  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.d);
   190  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk > ot.pk);
   191  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk > ot.uk);
   192  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn > ot.ukn);
   193  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik > ot.ik);
   194  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d > ot.d);
   195  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   196  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.pk);
   197  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.pk);
   198  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.pk);
   199  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t2 AS it WHERE ot.d = it.pk - 1);
   200  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it1 JOIN t2 AS it2 ON it1.pk > it2.pk WHERE ot.d = it2.pk);
   201  
   202  
   203  create view v1 as SELECT 1  WHERE EXISTS (SELECT * FROM t1 AS it);
   204  create view v2 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1);
   205  create view v3 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1 WHERE FALSE);
   206  create view v4 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it);
   207  create view v5 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = 1);
   208  create view v6 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = 1);
   209  create view v7 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik = 1);
   210  create view v8 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = 1);
   211  create view v9 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   212  create view v10 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.uk);
   213  create view v11 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.ukn);
   214  create view v12 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.d);
   215  create view v13 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk > ot.pk);
   216  create view v14 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk > ot.uk);
   217  create view v15 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn > ot.ukn);
   218  create view v16 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik > ot.ik);
   219  create view v17 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d > ot.d);
   220  create view v18 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   221  create view v19 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.pk);
   222  create view v20 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.pk);
   223  create view v21 as SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.pk);
   224  create view v22 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t2 AS it WHERE ot.d = it.pk - 1);
   225  create view v23 as SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it1 JOIN t2 AS it2 ON it1.pk > it2.pk WHERE ot.d = it2.pk);
   226  
   227  select * from v1;
   228  select * from v2;
   229  select * from v3;
   230  select * from v4;
   231  select * from v5;
   232  select * from v6;
   233  select * from v7;
   234  select * from v8;
   235  select * from v9;
   236  select * from v10;
   237  select * from v11;
   238  select * from v12;
   239  select * from v13;
   240  select * from v14;
   241  select * from v15;
   242  select * from v16;
   243  select * from v17;
   244  select * from v18;
   245  select * from v19;
   246  select * from v20;
   247  select * from v21;
   248  select * from v22;
   249  select * from v23;
   250  
   251  drop view v1;
   252  drop view v2;
   253  drop view v3;
   254  drop view v4;
   255  drop view v5;
   256  drop view v6;
   257  drop view v7;
   258  drop view v8;
   259  drop view v9;
   260  drop view v10;
   261  drop view v11;
   262  drop view v12;
   263  drop view v13;
   264  drop view v14;
   265  drop view v15;
   266  drop view v16;
   267  drop view v17;
   268  drop view v18;
   269  drop view v19;
   270  drop view v20;
   271  drop view v21;
   272  drop view v22;
   273  drop view v23;
   274  
   275  
   276  drop table if exists t1;
   277  drop table if exists t2;
   278  drop table if exists t3;
   279  CREATE TABLE t1 (a int);
   280  SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE 127 = 55);
   281  
   282  -- @case
   283  -- @desc:test for  subquery with group by and having
   284  -- @label:bvt
   285  drop table if exists t1;
   286  drop table if exists t2;
   287  drop table if exists t3;
   288  create table t1 (s1 int);
   289  create table t2 (s1 int);
   290  insert into t1 values (1);
   291  insert into t2 values (1);
   292  select * from t1 where exists (select s1 from t2 group by s1 having max(t2.s1)=t1.s1);
   293  
   294  create view v1 as select * from t1 where exists (select s1 from t2 group by s1 having max(t2.s1)=t1.s1);
   295  select * from v1;
   296  drop view v1;
   297  
   298  drop table if exists t1;
   299  drop table if exists t2;
   300  create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
   301  insert into t1 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text11'), (12, 'text12');
   302  select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
   303  
   304  create view v1 as select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null);
   305  select * from v1;
   306  drop view v1;
   307  
   308  
   309  drop table if exists t1;
   310  drop table if exists t2;
   311  drop table if exists t3;
   312  CREATE TABLE t1 (id int NOT NULL, st CHAR(2));
   313  INSERT INTO t1 VALUES (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
   314  CREATE TABLE t2 (id int NOT NULL);
   315  INSERT INTO t2 VALUES (7), (5), (1), (3);
   316  SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   317  SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id;
   318  SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   319  SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id;
   320  
   321  create view v1 as SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   322  create view v2 as SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id;
   323  create view v3 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   324  create view v4 as SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id;
   325  
   326  select * from v1;
   327  select * from v2;
   328  select * from v3;
   329  select * from v4;
   330  
   331  drop view v1;
   332  drop view v2;
   333  drop view v3;
   334  drop view v4;
   335  
   336  drop table if exists t1;
   337  drop table if exists t2;
   338  drop table if exists t3;
   339  CREATE TABLE t1 (a INT, b INT);
   340  INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
   341  SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1 AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a) GROUP BY a1.a;
   342  create view v1 as SELECT a1.a, COUNT(*) FROM t1 a1 WHERE a1.a = 1 AND EXISTS( SELECT a2.a FROM t1 a2 WHERE a2.a = a1.a) GROUP BY a1.a;
   343  select * from v1;
   344  drop view v1;
   345  DROP TABLE if exists t1;
   346  
   347  
   348  -- @case
   349  -- @desc:test for  subquery with func
   350  -- @label:bvt
   351  drop table if exists t1;
   352  drop table if exists t2;
   353  CREATE TABLE t1 ( a int, b int );
   354  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   355  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a) IS NULL from t1 a;
   356  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a) IS NOT NULL from t1 a;
   357  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a) IS NULL from t1 a;
   358  create view v1 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a) IS NULL from t1 a;
   359  create view v2 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a) IS NOT NULL from t1 a;
   360  create view v3 as SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a) IS NULL from t1 a;
   361  select * from v1;
   362  select * from v2;
   363  select * from v3;
   364  
   365  drop view v1;
   366  drop view v2;
   367  drop view v3;
   368  drop table if exists t1;
   369  
   370  -- @case
   371  -- @desc:test for  subquery with Arithmetic calculation
   372  -- @label:bvt
   373  drop table if exists t1;
   374  create table t1 (df decimal(5,1));
   375  insert into t1 values(1.1);
   376  select 1.1 * exists(select * from t1);
   377  create view v1 as select 1.1 * exists(select * from t1);
   378  select * from v1;
   379  drop view v1;
   380  drop table if exists t1;
   381  
   382  -- @case
   383  -- @desc:test for  subquery with uion
   384  -- @label:bvt
   385  drop table if exists t1;
   386  CREATE TABLE t1 (i INT);
   387  SELECT * FROM t1 WHERE NOT EXISTS
   388    (
   389     (SELECT i FROM t1) UNION
   390     (SELECT i FROM t1)
   391    );
   392  SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
   393  
   394  create view v1 as SELECT * FROM t1 WHERE NOT EXISTS
   395    (
   396     (SELECT i FROM t1) UNION
   397     (SELECT i FROM t1)
   398    );
   399  create view v2 as SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
   400  select * from v1;
   401  select * from v2;
   402  
   403  drop view v1;
   404  drop view v2;
   405  
   406  drop table if exists t1;
   407  drop table if exists t2;
   408  drop table if exists t3;
   409  CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
   410  CREATE TABLE t2 (c int);
   411  INSERT INTO t1 VALUES ('aa', 1);
   412  INSERT INTO t2 VALUES (1);
   413  -- @bvt:issue#4354
   414  SELECT * FROM t1
   415    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   416                  UNION
   417                  SELECT c from t2 WHERE c=t1.c);
   418  
   419  
   420  create view v1 as SELECT * FROM t1
   421    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   422                  UNION
   423                  SELECT c from t2 WHERE c=t1.c);
   424  select * from v1;
   425  drop view v1;
   426  
   427  -- @bvt:issue
   428  INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
   429  -- @bvt:issue#4354
   430  SELECT * FROM t1
   431    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   432                  UNION
   433                  SELECT c from t2 WHERE c=t1.c);
   434  create view v1 as SELECT * FROM t1
   435    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   436                  UNION
   437                  SELECT c from t2 WHERE c=t1.c);
   438  select * from v1;
   439  drop view v1;
   440  -- @bvt:issue
   441  INSERT INTO t2 VALUES (2);
   442  CREATE TABLE t3 (c int);
   443  INSERT INTO t3 VALUES (1);
   444  -- @bvt:issue#4354
   445  SELECT * FROM t1
   446    WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
   447                  UNION
   448                  SELECT c from t2 WHERE c=t1.c);
   449  create view v1 as SELECT * FROM t1
   450    WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
   451                  UNION
   452                  SELECT c from t2 WHERE c=t1.c);
   453  select * from v1;
   454  drop view v1;
   455  -- @bvt:issue
   456  
   457  drop table if exists t1;
   458  drop table if exists t2;
   459  drop table if exists t3;
   460  CREATE TABLE t1 (a INT);
   461  CREATE TABLE t2 (a INT);
   462  INSERT INTO t1 VALUES (1),(2);
   463  INSERT INTO t2 VALUES (1),(2);
   464  SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
   465  SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a));
   466  
   467  create view v1 as SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
   468  create view v2 as SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a) UNION (SELECT 1 FROM t2 WHERE t1.a = t2.a));
   469  select * from v1;
   470  select * from v2;
   471  
   472  drop view v1;
   473  drop view v2;
   474  
   475  drop table if exists t1;
   476  drop table if exists t2;
   477  
   478  -- @case
   479  -- @desc:test for  subquery with join
   480  -- @label:bvt
   481  drop table if exists t1;
   482  drop table if exists t2;
   483  drop table if exists t3;
   484  CREATE TABLE t1 ( c1 int );
   485  INSERT INTO t1 VALUES ( 1 );
   486  INSERT INTO t1 VALUES ( 2 );
   487  INSERT INTO t1 VALUES ( 3 );
   488  INSERT INTO t1 VALUES ( 6 );
   489  
   490  CREATE TABLE t2 ( c2 int );
   491  INSERT INTO t2 VALUES ( 1 );
   492  INSERT INTO t2 VALUES ( 4 );
   493  INSERT INTO t2 VALUES ( 5 );
   494  INSERT INTO t2 VALUES ( 6 );
   495  
   496  CREATE TABLE t3 ( c3 int );
   497  INSERT INTO t3 VALUES ( 7 );
   498  INSERT INTO t3 VALUES ( 8 );
   499  
   500  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
   501    WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
   502    
   503  create view v1 as SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
   504    WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
   505  select * from v1;
   506  drop view v1;
   507  
   508  drop table if exists t1;
   509  drop table if exists t2;
   510  drop table if exists t3;
   511