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

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for [any] subquery with operand-is-column
     5  -- @label:bvt
     6  DROP TABLE IF EXISTS t1;
     7  DROP TABLE IF EXISTS t2;
     8  DROP TABLE IF EXISTS t3;
     9  DROP TABLE IF EXISTS t4;
    10  create table t1 (a int);
    11  create table t2 (a int, b int);
    12  create table t3 (a int);
    13  create table t4 (a int not null, b int not null);
    14  insert into t1 values (2);
    15  insert into t2 values (1,7),(2,7);
    16  insert into t4 values (4,8),(3,8),(5,9);
    17  insert into t3 values (6),(7),(3);
    18  select * from t3 where a <> any (select b from t2);
    19  select * from t3 where a <> some (select b from t2);
    20  select * from t3 where a = some (select b from t2);
    21  select * from t3 where a = any (select b from t2);
    22  
    23  insert into t2 values (100, 5);
    24  select * from t3 where a < any (select b from t2);
    25  select * from t3 where a >= any (select b from t2);
    26  select * from t3 where a < some (select b from t2);
    27  select * from t3 where a >= some (select b from t2);
    28  select * from t3 where a >= some (select b from t2);
    29  
    30  DROP TABLE IF EXISTS t1;
    31  DROP TABLE IF EXISTS t2;
    32  DROP TABLE IF EXISTS t3;
    33  DROP TABLE IF EXISTS t4;
    34  create table t1 (s1 char(5));
    35  create table t2 (s1 char(5));
    36  insert into t1 values ('a1'),('a2'),('a3');
    37  insert into t2 values ('a1'),('a2');
    38  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    39  select s1, s1 < ANY (SELECT s1 FROM t2) from t1;
    40  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    41  
    42  DROP TABLE IF EXISTS t1;
    43  DROP TABLE IF EXISTS t2;
    44  DROP TABLE IF EXISTS t3;
    45  create table t2 (a int, b int);
    46  create table t3 (a int);
    47  insert into t3 values (6),(7),(3);
    48  select * from t3 where a >= some (select b from t2);
    49  select * from t3 where a >= some (select b from t2 group by 1);
    50  select * from t3 where NULL >= any (select b from t2);
    51  select * from t3 where NULL >= any (select b from t2 group by 1);
    52  select * from t3 where NULL >= some (select b from t2);
    53  select * from t3 where NULL >= some (select b from t2 group by 1);
    54  insert into t2 values (2,2), (2,1), (3,3), (3,1);
    55  
    56  DROP TABLE IF EXISTS t1;
    57  DROP TABLE IF EXISTS t2;
    58  DROP TABLE IF EXISTS t3;
    59  CREATE TABLE t1 ( a int, b int );
    60  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
    61  SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
    62  SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
    63  SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
    64  SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
    65  SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
    66  SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
    67  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
    68  SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
    69  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
    70  -- error
    71  SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
    72  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
    73  SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
    74  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
    75  SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
    76  
    77  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
    78  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
    79  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2 UNION SELECT a,1 FROM t1 WHERE b = 2);
    80  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2 UNION SELECT a,1 FROM t1 HAVING a = 2);
    81  
    82  DROP TABLE IF EXISTS t1;
    83  CREATE TABLE t1 ( a double, b double );
    84  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
    85  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
    86  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
    87  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
    88  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
    89  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
    90  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
    91  
    92  DROP TABLE IF EXISTS t1;
    93  CREATE TABLE t1 ( a char(1), b char(1));
    94  INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
    95  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
    96  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
    97  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
    98  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
    99  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
   100  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
   101  
   102  DROP TABLE IF EXISTS t1;
   103  DROP TABLE IF EXISTS t2;
   104  create table t1 (a1 int);
   105  create table t2 (b1 int);
   106  --error
   107  select * from t1 where a2 > any(select b1 from t2);
   108  select * from t1 where a1 > any(select b1 from t2);
   109  
   110  DROP TABLE IF EXISTS t1;
   111  DROP TABLE IF EXISTS t2;
   112  create table t1 (s1 char);
   113  insert into t1 values ('1'),('2');
   114  select * from t1 where (s1 < any (select s1 from t1));
   115  select * from t1 where not (s1 < any (select s1 from t1));
   116  select * from t1 where (s1+1 = ANY (select s1 from t1));
   117  select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
   118  
   119  DROP TABLE IF EXISTS t1;
   120  DROP TABLE IF EXISTS t2;
   121  CREATE TABLE t1 (s1 CHAR(1));
   122  INSERT INTO t1 VALUES ('a');
   123  SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
   124  
   125  DROP TABLE IF EXISTS t1;
   126  DROP TABLE IF EXISTS t2;
   127  
   128  -- @case
   129  -- @desc:test for [any] subquery with with * and mutil tuple
   130  -- @label:bvt
   131  create table t1 (a integer, b integer);
   132  -- @bvt:issue#7691
   133  select (select * from t1) = (select 1,2);
   134  select (select 1,2) = (select * from t1);
   135  -- @bvt:issue
   136  select  (1,2) = ANY (select * from t1);
   137  select  (1,2) != ALL (select * from t1);
   138  DROP TABLE IF EXISTS t1;
   139  
   140  -- @case
   141  -- @desc:test for [any] subquery with with without any tables gives wrong results
   142  -- @label:bvt
   143  select 1 from dual where 1 < any (select 2);
   144  select 1 from dual where 2 > any (select 1);
   145  
   146  -- @case
   147  -- @desc:test for [any] subquery with group by and having
   148  -- @label:bvt
   149  CREATE TABLE `t1` (
   150    `numeropost` int(8) unsigned NOT NULL,
   151    `maxnumrep` int(10) unsigned NOT NULL default 0,
   152    PRIMARY KEY  (`numeropost`)
   153  );
   154  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
   155  CREATE TABLE `t2` (
   156        `mot` varchar(30) NOT NULL default '',
   157        `topic` int(8) unsigned NOT NULL default 0,
   158        `dt` date,
   159        `pseudo` varchar(35) NOT NULL default ''
   160      );
   161  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
   162  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
   163  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
   164  -- @bvt:issue#3307
   165  SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
   166  -- @bvt:issue
   167  SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
   168  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
   169  
   170  DROP TABLE IF EXISTS t1;
   171  DROP TABLE IF EXISTS t2;
   172  CREATE TABLE t1 ( a int, b int );
   173  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   174  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   175  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   176  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   177  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   178  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   179  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   180  
   181  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   182  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   183  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   184  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   185  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   186  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   187  
   188  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
   189  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
   190  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
   191  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   192  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   193  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
   194  
   195  DROP TABLE IF EXISTS t1;
   196  DROP TABLE IF EXISTS t2;
   197  CREATE TABLE `t1` ( `a` int(11) default NULL);
   198  insert into t1 values (1);
   199  CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL);
   200  insert into t2 values (1,2);
   201  select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
   202  
   203  DROP TABLE IF EXISTS t1;
   204  DROP TABLE IF EXISTS t2;
   205  CREATE TABLE t1 (
   206    field1 int NOT NULL,
   207    field2 int NOT NULL,
   208    field3 int NOT NULL
   209  );
   210  CREATE TABLE t2 (
   211    fieldA int NOT NULL,
   212    fieldB int NOT NULL
   213  );
   214  INSERT INTO t1 VALUES (1,1,1), (1,1,2), (1,2,1), (1,2,2), (1,2,3), (1,3,1);
   215  INSERT INTO t2 VALUES (1,1), (1,2), (1,3);
   216  SELECT field1, field2
   217    FROM  t1
   218      GROUP BY field1, field2
   219        HAVING COUNT(*) < ANY (SELECT fieldB
   220                                 FROM t2 WHERE fieldA = field1);
   221  
   222  DROP TABLE IF EXISTS t1;
   223  DROP TABLE IF EXISTS t2;
   224  CREATE TABLE t1 (
   225   pk INT NOT NULL PRIMARY KEY,
   226   number INT
   227  );
   228  INSERT INTO t1 VALUES (8,8);
   229  
   230  CREATE TABLE t2 (
   231   pk INT NOT NULL PRIMARY KEY,
   232   number INT
   233  );
   234  INSERT INTO t2 VALUES (1,2);
   235  INSERT INTO t2 VALUES (2,8);
   236  INSERT INTO t2 VALUES (3,NULL);
   237  INSERT INTO t2 VALUES (4,166);
   238  
   239  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
   240  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
   241  
   242  DROP TABLE IF EXISTS t1;
   243  DROP TABLE IF EXISTS t2;
   244  CREATE TABLE t1 (a varchar(5), b varchar(10));
   245  INSERT INTO t1 VALUES ('AAA', '5'), ('BBB', '4'), ('BBB', '1'), ('CCC', '2'), ('CCC', '7'), ('AAA', '2'), ('AAA', '4'), ('BBB', '3'), ('AAA', '8');
   246  SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
   247  DROP TABLE IF EXISTS t1;
   248  DROP TABLE IF EXISTS t2;
   249  
   250  -- @case
   251  -- @desc:test for [any] subquery with uion
   252  -- @label:bvt
   253  create table t1 (s1 char);
   254  insert into t1 values ('e');
   255  select * from t1 where 'f' > any (select s1 from t1);
   256  
   257  DROP TABLE IF EXISTS t1;
   258  DROP TABLE IF EXISTS t2;
   259  CREATE TABLE t1 ( a int, b int );
   260  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   261  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2);
   262  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2);
   263  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2);
   264  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2);
   265  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2);
   266  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2);
   267  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   268  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   269  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   270  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   271  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   272  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   273  
   274  -- @case
   275  -- @desc:test for [any] subquery with NULL
   276  -- @label:bvt
   277  DROP TABLE IF EXISTS t1;
   278  create table t1 (a int);
   279  insert into t1 values (1),(2),(3);
   280  -- @ignore{
   281  update t1 set a=NULL where a=2;
   282  select 1 > ANY (SELECT * from t1);
   283  select 10 > ANY (SELECT * from t1);
   284  -- @ignore}
   285  
   286  DROP TABLE IF EXISTS t1;
   287  create table t1 (a varchar(20));
   288  insert into t1 values ('A'),('BC'),('DEF');
   289  update t1 set a=NULL where a='BC';
   290  select 'A' > ANY (SELECT * from t1);
   291  select 'XYZS' > ANY (SELECT * from t1);
   292  
   293  DROP TABLE IF EXISTS t1;
   294  create table t1 (a float);
   295  insert into t1 values (1.5),(2.5),(3.5);
   296  update t1 set a=NULL where a=2.5;
   297  select 1.5 > ANY (SELECT * from t1);
   298  select 10.5 > ANY (SELECT * from t1);
   299  
   300  DROP TABLE IF EXISTS t1;
   301  create table t1 (s1 int);
   302  insert into t1 values (1),(null);
   303  select * from t1 where s1 < all (select s1 from t1);
   304  select s1, s1 < all (select s1 from t1) from t1;
   305  
   306  DROP TABLE IF EXISTS t1;
   307  CREATE TABLE t1( a INT );
   308  INSERT INTO t1 VALUES (1),(2);
   309  CREATE TABLE t2( a INT, b INT );
   310  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
   311  SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
   312  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
   313  
   314  DROP TABLE IF EXISTS t1;
   315  DROP TABLE IF EXISTS t2;
   316  
   317  -- @case
   318  -- @desc:test for [any] subquery with join
   319  -- @label:bvt
   320  CREATE TABLE t1(i INT);
   321  INSERT INTO t1 VALUES (1), (2), (3);
   322  CREATE TABLE t1s(i INT);
   323  INSERT INTO t1s VALUES (10), (20), (30);
   324  CREATE TABLE t2s(i INT);
   325  INSERT INTO t2s VALUES (100), (200), (300);
   326  SELECT * FROM t1
   327  WHERE NOT t1.I = ANY
   328  (
   329    SELECT t2s.i
   330    FROM
   331    t1s LEFT OUTER JOIN t2s ON t2s.i = t1s.i
   332    HAVING t2s.i = 999
   333  );
   334  DROP TABLE IF EXISTS t1;
   335  DROP TABLE IF EXISTS t1s;
   336  DROP TABLE IF EXISTS t2s;
   337  
   338  
   339  
   340  
   341  
   342  
   343  
   344  
   345