github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/subquery/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  drop table if exists t1;
     8  drop table if exists t2;
     9  drop table if exists t3;
    10  drop table if exists t4;
    11  drop table if exists t5;
    12  drop table if exists t6;
    13  drop table if exists t7;
    14  create table t1 (a int);
    15  create table t2 (a int, b int);
    16  create table t3 (a int);
    17  create table t4 (a int not null, b int not null);
    18  insert into t1 values (2);
    19  insert into t2 values (1,7),(2,7);
    20  insert into t4 values (4,8),(3,8),(5,9);
    21  insert into t3 values (6),(7),(3);
    22  select * from t3 where exists (select * from t2 where t2.b=t3.a);
    23  select * from t3 where not exists (select * from t2 where t2.b=t3.a);
    24  
    25  insert into t4 values (12,7),(1,7),(10,9),(9,6),(7,6),(3,9),(1,10);
    26  insert into t2 values (2,10);
    27  create table t5 (a int);
    28  insert into t5 values (5);
    29  insert into t5 values (2);
    30  create table t6 (patient_uq int, clinic_uq int);
    31  create table t7( uq int primary key, name char(25));
    32  insert into t7 values(1,"Oblastnaia bolnitsa"),(2,"Bolnitsa Krasnogo Kresta");
    33  insert into t6 values (1,1),(1,2),(2,2),(1,3);
    34  select * from t6 where exists (select * from t7 where uq = clinic_uq);
    35  
    36  drop table if exists t1;
    37  drop table if exists t2;
    38  drop table if exists t3;
    39  drop table if exists t4;
    40  drop table if exists t5;
    41  drop table if exists t6;
    42  drop table if exists t7;
    43  CREATE TABLE `t1` (
    44    `numeropost` int(8) unsigned NOT NULL,
    45    `maxnumrep` int(10) unsigned NOT NULL default 0,
    46    PRIMARY KEY  (`numeropost`)
    47  );
    48  
    49  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
    50  
    51  CREATE TABLE `t2` (
    52        `mot` varchar(30) NOT NULL default '',
    53        `topic` int(8) unsigned NOT NULL default 0,
    54        `dt` date,
    55        `pseudo` varchar(35) NOT NULL default ''
    56      );
    57  
    58  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
    59  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;
    60  
    61  drop table if exists t1;
    62  drop table if exists t2;
    63  drop table if exists t3;
    64  CREATE TABLE `t1` (
    65    `mot` varchar(30) NOT NULL default '',
    66    `topic` int(8) unsigned NOT NULL default 0,
    67    `dt` date ,
    68    `pseudo` varchar(35) NOT NULL default ''
    69  );
    70  CREATE TABLE `t2` (
    71    `mot` varchar(30) NOT NULL default '',
    72    `topic` int(8) unsigned NOT NULL default 0,
    73    `dt` date,
    74    `pseudo` varchar(35) NOT NULL default ''
    75  ) ;
    76  CREATE TABLE `t3` (
    77    `numeropost` int(8) unsigned NOT NULL,
    78    `maxnumrep` int(10) unsigned NOT NULL default 0,
    79    PRIMARY KEY  (`numeropost`)
    80  );
    81  INSERT INTO t1 VALUES ('joce','1',null,'joce'),('test','2',null,'test');
    82  INSERT INTO t2 VALUES ('joce','1',null,'joce'),('test','2',null,'test');
    83  INSERT INTO t3 VALUES (1,1);
    84  SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic);
    85  DELETE FROM t1 WHERE topic IN (SELECT DISTINCT topic FROM t2 WHERE NOT EXISTS(SELECT * FROM t3 WHERE numeropost=topic));
    86  select * from t1;
    87  drop table if exists t1;
    88  drop table if exists t2;
    89  drop table if exists t3;
    90  
    91  create table t1 (a int, b int);
    92  insert into t1 values (1,2),(3,4);
    93  select * from t1 up where exists (select * from t1 where t1.a=up.a);
    94  
    95  drop table if exists t1;
    96  drop table if exists t2;
    97  drop table if exists t3;
    98  CREATE TABLE t1 (a INT, b INT);
    99  INSERT INTO t1 VALUES (1,1),(2,2);
   100  CREATE TABLE t2 (a INT, b INT);
   101  INSERT INTO t2 VALUES (1,1),(2,2);
   102  CREATE TABLE t3 (a INT, b INT);
   103  -- @bvt:issue#3307
   104  SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   105  SELECT COUNT(*) FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY COUNT(*);
   106  -- @bvt:issue
   107  
   108  drop table if exists t1;
   109  drop table if exists t2;
   110  drop table if exists t3;
   111  CREATE TABLE t1 (f1 varchar(1));
   112  INSERT INTO t1 VALUES ('v'),('s');
   113  CREATE TABLE t2 (f1_key varchar(1));
   114  INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),('d'),('y'),('t'),('d'),('s');
   115  SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
   116  WHERE EXISTS
   117  (
   118  SELECT DISTINCT f1_key
   119  FROM t2
   120  WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
   121  
   122  drop table if exists t1;
   123  drop table if exists t2;
   124  drop table if exists t3;
   125  CREATE TABLE t1( pk int PRIMARY KEY,uk int,ukn int NOT NULL,ik int,d int);
   126  INSERT INTO t1 VALUES (0, NULL, 0, NULL, NULL),(1, 10, 20, 30, 40),(2, 20, 40, 60, 80);
   127  CREATE TABLE t2(pk int PRIMARY KEY);
   128  INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9),(10),
   129  (11),(12),(13),(14),(15),(16),(17),(18),(19),(20),
   130  (21),(22),(23),(24),(25),(26),(27),(28),(29),(30),
   131  (31),(32),(33),(34),(35),(36),(37),(38),(39),(40),
   132  (41),(42),(43),(44),(45),(46),(47),(48),(49),(50),
   133  (51),(52),(53),(54),(55),(56),(57),(58),(59),(60),
   134  (61),(62),(63),(64),(65),(66),(67),(68),(69),(70),
   135  (71),(72),(73),(74),(75),(76),(77),(78),(79),(80);
   136  SELECT 1  WHERE EXISTS (SELECT * FROM t1 AS it);
   137  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1);
   138  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT 1 WHERE FALSE);
   139  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it);
   140  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = 1);
   141  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = 1);
   142  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik = 1);
   143  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = 1);
   144  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   145  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.uk);
   146  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.ukn);
   147  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.d);
   148  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk > ot.pk);
   149  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk > ot.uk);
   150  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn > ot.ukn);
   151  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ik > ot.ik);
   152  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d > ot.d);
   153  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.pk = ot.pk);
   154  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.uk = ot.pk);
   155  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.ukn = ot.pk);
   156  SELECT * FROM t2 AS ot WHERE EXISTS (SELECT * FROM t1 AS it WHERE it.d = ot.pk);
   157  SELECT * FROM t1 AS ot WHERE EXISTS (SELECT * FROM t2 AS it WHERE ot.d = it.pk - 1);
   158  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);
   159  
   160  drop table if exists t1;
   161  drop table if exists t2;
   162  drop table if exists t3;
   163  CREATE TABLE t1 (a int);
   164  SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t1 WHERE 127 = 55);
   165  
   166  -- @case
   167  -- @desc:test for  subquery with group by and having
   168  -- @label:bvt
   169  drop table if exists t1;
   170  drop table if exists t2;
   171  drop table if exists t3;
   172  create table t1 (s1 int);
   173  create table t2 (s1 int);
   174  insert into t1 values (1);
   175  insert into t2 values (1);
   176  select * from t1 where exists (select s1 from t2 group by s1 having max(t2.s1)=t1.s1);
   177  
   178  drop table if exists t1;
   179  drop table if exists t2;
   180  create table t1 (id int not null, text varchar(20) not null default '', primary key (id));
   181  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');
   182  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);
   183  
   184  drop table if exists t1;
   185  drop table if exists t2;
   186  drop table if exists t3;
   187  CREATE TABLE t1 (a int, b int);
   188  CREATE TABLE t2 (c int, d int);
   189  CREATE TABLE t3 (e int);
   190  INSERT INTO t1 VALUES(1,10), (2,10), (1,20), (2,20), (3,20), (2,30), (4,40);
   191  INSERT INTO t2 VALUES(2,10), (2,20), (4,10), (5,10), (3,20), (2,40);
   192  INSERT INTO t3 VALUES (10), (30), (10), (20) ;
   193  -- @bvt:issue#3307
   194  SELECT a FROM t1 GROUP BY a
   195    HAVING a IN (SELECT c FROM t2
   196                   WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
   197  SELECT a FROM t1 GROUP BY a
   198    HAVING a IN (SELECT c FROM t2
   199                   WHERE  EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e < d));
   200  SELECT a FROM t1 GROUP BY a
   201    HAVING a IN (SELECT c FROM t2
   202                   WHERE MIN(b) < d AND
   203                         EXISTS(SELECT e FROM t3 WHERE MAX(b)=e AND e <= d));
   204  -- @bvt:issue
   205  -- @bvt:issue#3307
   206  SELECT a FROM t1
   207     WHERE EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c) GROUP BY a;
   208  -- @bvt:issue
   209  SELECT a FROM t1 GROUP BY a
   210     HAVING EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) = c);
   211  -- @bvt:issue#3307
   212  SELECT a FROM t1
   213     WHERE a < 3 AND
   214           EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c) GROUP BY a;
   215  SELECT a FROM t1
   216     WHERE a < 3 AND
   217           EXISTS(SELECT c FROM t2 GROUP BY c HAVING SUM(a) != c);
   218  -- @bvt:issue
   219  -- @bvt:issue#3307
   220  SELECT t1.a FROM t1 GROUP BY t1.a
   221    HAVING t1.a < ALL(SELECT t2.c FROM t2 GROUP BY t2.c
   222                         HAVING EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
   223                                         HAVING SUM(t1.a+t2.c) < t3.e/4));
   224  SELECT t1.a FROM t1 GROUP BY t1.a
   225         HAVING t1.a > ALL(SELECT t2.c FROM t2
   226                             WHERE EXISTS(SELECT t3.e FROM t3 GROUP BY t3.e
   227                                            HAVING SUM(t1.a+t2.c) < t3.e/4));
   228  -- @bvt:issue
   229  -- @bvt:issue#3307
   230  SELECT t1.a FROM t1 GROUP BY t1.a
   231         HAVING t1.a > ALL(SELECT t2.c FROM t2
   232                             WHERE EXISTS(SELECT t3.e FROM t3
   233                                            WHERE SUM(t1.a+t2.c) < t3.e/4));
   234  -- @bvt:issue
   235  
   236  drop table if exists t1;
   237  drop table if exists t2;
   238  drop table if exists t3;
   239  CREATE TABLE t1 (id int NOT NULL, st CHAR(2));
   240  INSERT INTO t1 VALUES (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
   241  CREATE TABLE t2 (id int NOT NULL);
   242  INSERT INTO t2 VALUES (7), (5), (1), (3);
   243  SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   244  SELECT id, st FROM t1  WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id) GROUP BY id;
   245  SELECT id, st FROM t1 WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
   246  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;
   247  
   248  drop table if exists t1;
   249  drop table if exists t2;
   250  drop table if exists t3;
   251  CREATE TABLE t1 (a INT, b INT);
   252  INSERT INTO t1 VALUES (1, 2), (1,3), (1,4), (2,1), (2,2);
   253  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;
   254  DROP TABLE if exists t1;
   255  
   256  
   257  -- @case
   258  -- @desc:test for  subquery with func
   259  -- @label:bvt
   260  drop table if exists t1;
   261  drop table if exists t2;
   262  CREATE TABLE t1 ( a int, b int );
   263  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   264  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a > t1.a) IS NULL from t1 a;
   265  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a < t1.a) IS NOT NULL from t1 a;
   266  SELECT EXISTS(SELECT a FROM t1 WHERE b = 2 and a.a = t1.a) IS NULL from t1 a;
   267  drop table if exists t1;
   268  
   269  -- @case
   270  -- @desc:test for  subquery with Arithmetic calculation
   271  -- @label:bvt
   272  drop table if exists t1;
   273  create table t1 (df decimal(5,1));
   274  insert into t1 values(1.1);
   275  select 1.1 * exists(select * from t1);
   276  drop table if exists t1;
   277  
   278  -- @case
   279  -- @desc:test for  subquery with uion
   280  -- @label:bvt
   281  drop table if exists t1;
   282  CREATE TABLE t1 (i INT);
   283  SELECT * FROM t1 WHERE NOT EXISTS
   284    (
   285     (SELECT i FROM t1) UNION
   286     (SELECT i FROM t1)
   287    );
   288  SELECT * FROM t1 WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1)));
   289  
   290  drop table if exists t1;
   291  drop table if exists t2;
   292  drop table if exists t3;
   293  CREATE TABLE t1 (id char(4) PRIMARY KEY, c int);
   294  CREATE TABLE t2 (c int);
   295  INSERT INTO t1 VALUES ('aa', 1);
   296  INSERT INTO t2 VALUES (1);
   297  -- @bvt:issue#4354
   298  SELECT * FROM t1
   299    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   300                  UNION
   301                  SELECT c from t2 WHERE c=t1.c);
   302  -- @bvt:issue
   303  INSERT INTO t1 VALUES ('bb', 2), ('cc', 3), ('dd',1);
   304  -- @bvt:issue#4354
   305  SELECT * FROM t1
   306    WHERE EXISTS (SELECT c FROM t2 WHERE c=1
   307                  UNION
   308                  SELECT c from t2 WHERE c=t1.c);
   309  -- @bvt:issue
   310  INSERT INTO t2 VALUES (2);
   311  CREATE TABLE t3 (c int);
   312  INSERT INTO t3 VALUES (1);
   313  -- @bvt:issue#4354
   314  SELECT * FROM t1
   315    WHERE EXISTS (SELECT t2.c FROM t2 JOIN t3 ON t2.c=t3.c WHERE t2.c=1
   316                  UNION
   317                  SELECT c from t2 WHERE c=t1.c);
   318  -- @bvt:issue
   319  
   320  drop table if exists t1;
   321  drop table if exists t2;
   322  drop table if exists t3;
   323  CREATE TABLE t1 (a INT);
   324  CREATE TABLE t2 (a INT);
   325  INSERT INTO t1 VALUES (1),(2);
   326  INSERT INTO t2 VALUES (1),(2);
   327  SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
   328  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));
   329  
   330  drop table if exists t1;
   331  drop table if exists t2;
   332  
   333  -- @case
   334  -- @desc:test for  subquery with join
   335  -- @label:bvt
   336  drop table if exists t1;
   337  drop table if exists t2;
   338  drop table if exists t3;
   339  CREATE TABLE t1 ( c1 int );
   340  INSERT INTO t1 VALUES ( 1 );
   341  INSERT INTO t1 VALUES ( 2 );
   342  INSERT INTO t1 VALUES ( 3 );
   343  INSERT INTO t1 VALUES ( 6 );
   344  
   345  CREATE TABLE t2 ( c2 int );
   346  INSERT INTO t2 VALUES ( 1 );
   347  INSERT INTO t2 VALUES ( 4 );
   348  INSERT INTO t2 VALUES ( 5 );
   349  INSERT INTO t2 VALUES ( 6 );
   350  
   351  CREATE TABLE t3 ( c3 int );
   352  INSERT INTO t3 VALUES ( 7 );
   353  INSERT INTO t3 VALUES ( 8 );
   354  
   355  SELECT c1,c2 FROM t1 LEFT JOIN t2 ON c1 = c2
   356    WHERE EXISTS (SELECT c3 FROM t3 WHERE c2 IS NULL );
   357  
   358  drop table if exists t1;
   359  drop table if exists t2;
   360  drop table if exists t3;
   361  CREATE TABLE t1 (a int);
   362  INSERT INTO t1 VALUES
   363   (1), (2), (3), (4), (5), (6), (7), (8), (9),(10);
   364  SELECT * FROM t1
   365  WHERE EXISTS (SELECT * FROM t1) AND
   366        EXISTS (SELECT * FROM t1) AND
   367        EXISTS (SELECT * FROM t1) AND
   368        EXISTS (SELECT * FROM t1) AND
   369        EXISTS (SELECT * FROM t1) AND
   370        EXISTS (SELECT * FROM t1) AND
   371        EXISTS (SELECT * FROM t1) AND
   372        EXISTS (SELECT * FROM t1) AND
   373        EXISTS (SELECT * FROM t1) AND
   374        EXISTS (SELECT * FROM t1) AND
   375        EXISTS (SELECT * FROM t1) AND
   376        EXISTS (SELECT * FROM t1) AND
   377        EXISTS (SELECT * FROM t1) AND
   378        EXISTS (SELECT * FROM t1) AND
   379        EXISTS (SELECT * FROM t1) AND
   380        EXISTS (SELECT * FROM t1) AND
   381        EXISTS (SELECT * FROM t1) AND
   382        EXISTS (SELECT * FROM t1) AND
   383        EXISTS (SELECT * FROM t1) AND
   384        EXISTS (SELECT * FROM t1) AND
   385        EXISTS (SELECT * FROM t1) AND
   386        EXISTS (SELECT * FROM t1) AND
   387        EXISTS (SELECT * FROM t1) AND
   388        EXISTS (SELECT * FROM t1) AND
   389        EXISTS (SELECT * FROM t1) AND
   390        EXISTS (SELECT * FROM t1) AND
   391        EXISTS (SELECT * FROM t1) AND
   392        EXISTS (SELECT * FROM t1) AND
   393        EXISTS (SELECT * FROM t1) AND
   394        EXISTS (SELECT * FROM t1);
   395  
   396  -- @case
   397  -- @desc:test for  subquery with DISTINCT and ORDER BY
   398  -- @label:bvt
   399  drop table if exists t1;
   400  drop table if exists t2;
   401  CREATE TABLE t1(pk INT PRIMARY KEY, a INT);
   402  INSERT INTO t1 VALUES (1, 10), (3, 30), (2, 20);
   403  CREATE TABLE t2(pk INT PRIMARY KEY, a INT, b INT);
   404  INSERT INTO t2 VALUES (2, 20, 700), (1, 10, 200), (4, 10, 100);
   405  SELECT * FROM t1  WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
   406  
   407  drop table if exists t1;
   408  drop table if exists t2;