github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/view/view-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  create view v1 as select * from t3 where a <> any (select b from t2);
    23  create view v2 as select * from t3 where a <> some (select b from t2);
    24  create view v3 as select * from t3 where a = some (select b from t2);
    25  create view v4 as select * from t3 where a = any (select b from t2);
    26  select * from v1;
    27  select * from v2;
    28  select * from v3;
    29  select * from v4;
    30  
    31  drop view v1;
    32  drop view v2;
    33  drop view v3;
    34  drop view v4;
    35  
    36  
    37  insert into t2 values (100, 5);
    38  select * from t3 where a < any (select b from t2);
    39  select * from t3 where a >= any (select b from t2);
    40  select * from t3 where a < some (select b from t2);
    41  select * from t3 where a >= some (select b from t2);
    42  select * from t3 where a >= some (select b from t2);
    43  create view v1 as select * from t3 where a < any (select b from t2);
    44  create view v2 as select * from t3 where a >= any (select b from t2);
    45  create view v3 as select * from t3 where a < some (select b from t2);
    46  create view v4 as select * from t3 where a >= some (select b from t2);
    47  create view v5 as select * from t3 where a >= some (select b from t2);
    48  select * from v1;
    49  select * from v2;
    50  select * from v3;
    51  select * from v4;
    52  select * from v5;
    53  
    54  drop view v1;
    55  drop view v2;
    56  drop view v3;
    57  drop view v4;
    58  drop view v5;
    59  
    60  DROP TABLE IF EXISTS t1;
    61  DROP TABLE IF EXISTS t2;
    62  DROP TABLE IF EXISTS t3;
    63  DROP TABLE IF EXISTS t4;
    64  create table t1 (s1 char(5));
    65  create table t2 (s1 char(5));
    66  insert into t1 values ('a1'),('a2'),('a3');
    67  insert into t2 values ('a1'),('a2');
    68  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    69  select s1, s1 < ANY (SELECT s1 FROM t2) from t1;
    70  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    71  create view v1 as select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    72  create view v2 as select s1, s1 < ANY (SELECT s1 FROM t2) from t1;
    73  create view v3 as select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
    74  select * from v1;
    75  select * from v2;
    76  select * from v3;
    77  drop view v1;
    78  drop view v2;
    79  drop view v3;
    80  
    81  DROP TABLE IF EXISTS t1;
    82  DROP TABLE IF EXISTS t2;
    83  DROP TABLE IF EXISTS t3;
    84  
    85  create table t2 (a int, b int);
    86  create table t3 (a int);
    87  insert into t3 values (6),(7),(3);
    88  insert into t2 values (2,2), (2,1), (3,3), (3,1);
    89  select * from t3 where a >= some (select b from t2);
    90  select * from t3 where a >= some (select b from t2 group by 1);
    91  select * from t3 where NULL >= any (select b from t2);
    92  select * from t3 where NULL >= any (select b from t2 group by 1);
    93  select * from t3 where NULL >= some (select b from t2);
    94  select * from t3 where NULL >= some (select b from t2 group by 1);
    95  create view v1 as select * from t3 where a >= some (select b from t2);
    96  create view v2 as select * from t3 where a >= some (select b from t2 group by 1);
    97  create view v3 as select * from t3 where NULL >= any (select b from t2);
    98  create view v4 as select * from t3 where NULL >= any (select b from t2 group by 1);
    99  create view v5 as select * from t3 where NULL >= some (select b from t2);
   100  create view v6 as select * from t3 where NULL >= some (select b from t2 group by 1);
   101  select * from v1;
   102  select * from v2;
   103  select * from v3;
   104  select * from v4;
   105  select * from v5;
   106  select * from v6;
   107  
   108  drop view v1;
   109  drop view v2;
   110  drop view v3;
   111  drop view v4;
   112  drop view v5;
   113  drop view v6;
   114  
   115  
   116  DROP TABLE IF EXISTS t1;
   117  DROP TABLE IF EXISTS t2;
   118  DROP TABLE IF EXISTS t3;
   119  CREATE TABLE t1 ( a int, b int );
   120  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   121  SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
   122  SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
   123  SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
   124  SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
   125  SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
   126  SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
   127  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
   128  SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   129  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   130  -- error
   131  SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
   132  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
   133  SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   134  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   135  SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
   136  
   137  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
   138  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
   139  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);
   140  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);
   141  
   142  -- 
   143  create view v1 as SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
   144  create view v2 as SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
   145  create view v3 as SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
   146  create view v4 as SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
   147  create view v5 as SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
   148  create view v6 as SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
   149  create view v7 as SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
   150  create view v8 as SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   151  create view v9 as SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   152  -- error
   153  create view v10 as SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
   154  create view v11 as SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
   155  create view v12 as SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   156  create view v13 as SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   157  create view v14 as SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
   158  
   159  create view v15 as SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
   160  create view v16 as SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
   161  create view v17 as 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);
   162  create view v18 as 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);
   163  
   164  select * from v1;
   165  select * from v2;
   166  select * from v3;
   167  select * from v4;
   168  select * from v5;
   169  select * from v6;
   170  select * from v7;
   171  select * from v8;
   172  select * from v9;
   173  select * from v10;
   174  select * from v11;
   175  select * from v12;
   176  select * from v13;
   177  select * from v14;
   178  select * from v15;
   179  select * from v16;
   180  select * from v17;
   181  select * from v18;
   182  
   183  drop view v1;
   184  drop view v2;
   185  drop view v3;
   186  drop view v4;
   187  drop view v5;
   188  drop view v6;
   189  drop view v7;
   190  drop view v8;
   191  drop view v9;
   192  drop view v10;
   193  drop view v11;
   194  drop view v12;
   195  drop view v13;
   196  drop view v14;
   197  drop view v15;
   198  drop view v16;
   199  drop view v17;
   200  drop view v18;
   201  
   202  -- ------
   203  
   204  DROP TABLE IF EXISTS t1;
   205  CREATE TABLE t1 ( a double, b double );
   206  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   207  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
   208  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
   209  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
   210  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
   211  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
   212  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
   213  
   214  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
   215  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
   216  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
   217  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
   218  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
   219  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
   220  select * from v1;
   221  select * from v2;
   222  select * from v3;
   223  select * from v4;
   224  select * from v5;
   225  select * from v6;
   226  
   227  drop view v1;
   228  drop view v2;
   229  drop view v3;
   230  drop view v4;
   231  drop view v5;
   232  drop view v6;
   233  
   234  
   235  
   236  DROP TABLE IF EXISTS t1;
   237  CREATE TABLE t1 ( a char(1), b char(1));
   238  INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
   239  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
   240  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
   241  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
   242  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
   243  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
   244  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
   245  
   246  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
   247  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
   248  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
   249  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
   250  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
   251  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
   252  select * from v1;
   253  select * from v2;
   254  select * from v3;
   255  select * from v4;
   256  select * from v5;
   257  select * from v6;
   258  
   259  drop view v1;
   260  drop view v2;
   261  drop view v3;
   262  drop view v4;
   263  drop view v5;
   264  drop view v6;
   265  
   266  
   267  DROP TABLE IF EXISTS t1;
   268  DROP TABLE IF EXISTS t2;
   269  create table t1 (a1 int);
   270  create table t2 (b1 int);
   271  --  error
   272  select * from t1 where a2 > any(select b1 from t2);
   273  select * from t1 where a1 > any(select b1 from t2);
   274  create view v1 as select * from t1 where a2 > any(select b1 from t2);
   275  create view v2 as select * from t1 where a1 > any(select b1 from t2);
   276  select * from v1;
   277  select * from v2;
   278  drop view v1;
   279  drop view v2;
   280  
   281  
   282  DROP TABLE IF EXISTS t1;
   283  DROP TABLE IF EXISTS t2;
   284  create table t1 (s1 char);
   285  insert into t1 values ('1'),('2');
   286  select * from t1 where (s1 < any (select s1 from t1));
   287  create view v1 as select * from t1 where (s1 < any (select s1 from t1));
   288  select * from t1 where not (s1 < any (select s1 from t1));
   289  create view v2 as select * from t1 where not (s1 < any (select s1 from t1));
   290  select * from t1 where (s1+1 = ANY (select s1 from t1));
   291  select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
   292  
   293  create view v3 as select * from t1 where (s1+1 = ANY (select s1 from t1));
   294  create view v4 as select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
   295  select * from v1;
   296  select * from v2;
   297  select * from v3;
   298  select * from v4;
   299  
   300  drop view v1;
   301  drop view v2;
   302  drop view v3;
   303  drop view v4;
   304  
   305  
   306  DROP TABLE IF EXISTS t1;
   307  DROP TABLE IF EXISTS t2;
   308  CREATE TABLE t1 (s1 CHAR(1));
   309  INSERT INTO t1 VALUES ('a');
   310  SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
   311  create view v1 as SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
   312  select * from v1;
   313  drop view v1;
   314  
   315  DROP TABLE IF EXISTS t1;
   316  DROP TABLE IF EXISTS t2;
   317  
   318  -- @case
   319  -- @desc:test for [any] subquery with with * and mutil tuple
   320  -- @label:bvt
   321  create table t1 (a integer, b integer);
   322  -- @bvt:issue#7691
   323  select (select * from t1) = (select 1,2);
   324  select (select 1,2) = (select * from t1);
   325  -- @bvt:issue
   326  select  (1,2) = ANY (select * from t1);
   327  select  (1,2) != ALL (select * from t1);
   328  -- @bvt:issue#7691
   329  create view v1 as select (select * from t1) = (select 1,2);
   330  create view v2 as select (select 1,2) = (select * from t1);
   331  -- @bvt:issue
   332  create view v3 as select  (1,2) = ANY (select * from t1);
   333  create view v4 as select  (1,2) != ALL (select * from t1);
   334  
   335  -- @bvt:issue#7691
   336  select * from v1;
   337  select * from v2;
   338  -- @bvt:issue
   339  select * from v3;
   340  select * from v4;
   341  
   342  -- @bvt:issue#7691
   343  drop view v1;
   344  drop view v2;
   345  -- @bvt:issue
   346  drop view v3;
   347  drop view v4;
   348  
   349  DROP TABLE IF EXISTS t1;
   350  
   351  -- @case
   352  -- @desc:test for [any] subquery with with without any tables gives wrong results
   353  -- @label:bvt
   354  select 1 from dual where 1 < any (select 2);
   355  select 1 from dual where 2 > any (select 1);
   356  
   357  -- @case
   358  -- @desc:test for [any] subquery with group by and having
   359  -- @label:bvt
   360  CREATE TABLE `t1` (
   361    `numeropost` int(8) unsigned NOT NULL,
   362    `maxnumrep` int(10) unsigned NOT NULL default 0,
   363    PRIMARY KEY  (`numeropost`)
   364  );
   365  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
   366  CREATE TABLE `t2` (
   367        `mot` varchar(30) NOT NULL default '',
   368        `topic` int(8) unsigned NOT NULL default 0,
   369        `dt` date,
   370        `pseudo` varchar(35) NOT NULL default ''
   371      );
   372  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
   373  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
   374  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
   375  -- @bvt:issue#3307
   376  SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
   377  -- @bvt:issue
   378  SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
   379  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
   380  
   381  create view v1 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
   382  create view v2 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
   383  create view v3 as SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
   384  create view v4 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
   385  
   386  select * from v1;
   387  select * from v2;
   388  select * from v3;
   389  select * from v4;
   390  
   391  drop view v1;
   392  drop view v2;
   393  drop view v3;
   394  drop view v4;
   395  
   396  
   397  DROP TABLE IF EXISTS t1;
   398  DROP TABLE IF EXISTS t2;
   399  CREATE TABLE t1 ( a int, b int );
   400  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   401  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   402  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   403  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   404  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   405  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   406  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   407  
   408  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   409  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   410  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   411  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   412  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   413  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   414  
   415  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
   416  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
   417  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
   418  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   419  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   420  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
   421  
   422  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   423  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   424  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   425  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   426  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   427  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   428  
   429  create view v7 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   430  create view v8 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   431  create view v9 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   432  create view v10 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   433  create view v11 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   434  create view v12 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   435  
   436  create view v13 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
   437  create view v14 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
   438  create view v15 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
   439  create view v16 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   440  create view v17 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   441  create view v18 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
   442  
   443  select * from v1;
   444  select * from v2;
   445  select * from v3;
   446  select * from v4;
   447  select * from v5;
   448  select * from v6;
   449  select * from v7;
   450  select * from v8;
   451  select * from v9;
   452  select * from v10;
   453  select * from v11;
   454  select * from v12;
   455  select * from v13;
   456  select * from v14;
   457  select * from v15;
   458  select * from v16;
   459  select * from v17;
   460  select * from v18;
   461  
   462  drop view v1;
   463  drop view v2;
   464  drop view v3;
   465  drop view v4;
   466  drop view v5;
   467  drop view v6;
   468  drop view v7;
   469  drop view v8;
   470  drop view v9;
   471  drop view v10;
   472  drop view v11;
   473  drop view v12;
   474  drop view v13;
   475  drop view v14;
   476  drop view v15;
   477  drop view v16;
   478  drop view v17;
   479  drop view v18;
   480  
   481  DROP TABLE IF EXISTS t1;
   482  DROP TABLE IF EXISTS t2;
   483  CREATE TABLE `t1` ( `a` int(11) default NULL);
   484  insert into t1 values (1);
   485  CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL);
   486  insert into t2 values (1,2);
   487  select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
   488  
   489  
   490  DROP TABLE IF EXISTS t1;
   491  DROP TABLE IF EXISTS t2;
   492  CREATE TABLE t1 (
   493   pk INT NOT NULL PRIMARY KEY,
   494   number INT
   495  );
   496  INSERT INTO t1 VALUES (8,8);
   497  
   498  CREATE TABLE t2 (
   499   pk INT NOT NULL PRIMARY KEY,
   500   number INT
   501  );
   502  INSERT INTO t2 VALUES (1,2);
   503  INSERT INTO t2 VALUES (2,8);
   504  INSERT INTO t2 VALUES (3,NULL);
   505  INSERT INTO t2 VALUES (4,166);
   506  
   507  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
   508  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
   509  create view v1 as SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
   510  create view v2 as SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
   511  select * from v1;
   512  select * from v2;
   513  drop view v1;
   514  drop view v2;
   515  
   516  DROP TABLE IF EXISTS t1;
   517  DROP TABLE IF EXISTS t2;
   518  CREATE TABLE t1 (a varchar(5), b varchar(10));
   519  INSERT INTO t1 VALUES ('AAA', '5'), ('BBB', '4'), ('BBB', '1'), ('CCC', '2'), ('CCC', '7'), ('AAA', '2'), ('AAA', '4'), ('BBB', '3'), ('AAA', '8');
   520  SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
   521  create view v1 as SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
   522  select * from v1;
   523  drop view v1;
   524  DROP TABLE IF EXISTS t1;
   525  DROP TABLE IF EXISTS t2;
   526  
   527  -- @case
   528  -- @desc:test for [any] subquery with uion
   529  -- @label:bvt
   530  create table t1 (s1 char);
   531  insert into t1 values ('e');
   532  select * from t1 where 'f' > any (select s1 from t1);
   533  
   534  DROP TABLE IF EXISTS t1;
   535  DROP TABLE IF EXISTS t2;
   536  CREATE TABLE t1 ( a int, b int );
   537  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   538  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2);
   539  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2);
   540  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2);
   541  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2);
   542  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2);
   543  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2);
   544  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   545  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   546  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   547  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   548  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   549  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   550  
   551  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2);
   552  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2);
   553  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2);
   554  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2);
   555  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2);
   556  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2);
   557  create view v7 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   558  create view v8 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   559  create view v9 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   560  create view v10 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   561  create view v11 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   562  create view v12 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   563  
   564  select * from v1;
   565  select * from v2;
   566  select * from v3;
   567  select * from v4;
   568  select * from v5;
   569  select * from v6;
   570  select * from v7;
   571  select * from v8;
   572  select * from v9;
   573  select * from v10;
   574  select * from v11;
   575  select * from v12;
   576  
   577  drop view v1;
   578  drop view v2;
   579  drop view v3;
   580  drop view v4;
   581  drop view v5;
   582  drop view v6;
   583  drop view v7;
   584  drop view v8;
   585  drop view v9;
   586  drop view v10;
   587  drop view v11;
   588  drop view v12;
   589  
   590  
   591  -- @case
   592  -- @desc:test for [any] subquery with NULL
   593  -- @label:bvt
   594  DROP TABLE IF EXISTS t1;
   595  create table t1 (a int);
   596  insert into t1 values (1),(2),(3);
   597  update t1 set a=NULL where a=2;
   598  select 1 > ANY (SELECT * from t1);
   599  select 10 > ANY (SELECT * from t1);
   600  create view v1 as select 1 > ANY (SELECT * from t1);
   601  create view v2 as select 10 > ANY (SELECT * from t1);
   602  select * from v1;
   603  select * from v2;
   604  drop view v1;
   605  drop view v2;
   606  
   607  DROP TABLE IF EXISTS t1;
   608  create table t1 (a varchar(20));
   609  insert into t1 values ('A'),('BC'),('DEF');
   610  update t1 set a=NULL where a='BC';
   611  select 'A' > ANY (SELECT * from t1);
   612  select 'XYZS' > ANY (SELECT * from t1);
   613  
   614  create view v1 as select 'A' > ANY (SELECT * from t1);
   615  create view v2 as select 'XYZS' > ANY (SELECT * from t1);
   616  select * from v1;
   617  select * from v2;
   618  drop view v1;
   619  drop view v2;
   620  
   621  DROP TABLE IF EXISTS t1;
   622  create table t1 (a float);
   623  insert into t1 values (1.5),(2.5),(3.5);
   624  update t1 set a=NULL where a=2.5;
   625  select 1.5 > ANY (SELECT * from t1);
   626  select 10.5 > ANY (SELECT * from t1);
   627  create view v1 as select 1.5 > ANY (SELECT * from t1);
   628  create view v2 as select 10.5 > ANY (SELECT * from t1);
   629  select * from v1;
   630  select * from v2;
   631  drop view v1;
   632  drop view v2;
   633  
   634  DROP TABLE IF EXISTS t1;
   635  create table t1 (s1 int);
   636  insert into t1 values (1),(null);
   637  select * from t1 where s1 < all (select s1 from t1);
   638  create view v1 as select * from t1 where s1 < all (select s1 from t1);
   639  select * from t1 where s1 < all (select s1 from t1);
   640  create view v2 as select * from t1 where s1 < all (select s1 from t1);
   641  select * from v1;
   642  select * from v2;
   643  drop view v1;
   644  drop view v2;
   645  
   646  DROP TABLE IF EXISTS t1;
   647  CREATE TABLE t1( a INT );
   648  INSERT INTO t1 VALUES (1),(2);
   649  CREATE TABLE t2( a INT, b INT );
   650  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
   651  SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
   652  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
   653  create view v1 as SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
   654  create view v2 as SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
   655  create view v3 as SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
   656  select * from v1;
   657  select * from v2;
   658  select * from v3;
   659  drop view v1;
   660  drop view v2;
   661  drop view v3;
   662  
   663  DROP TABLE IF EXISTS t1;
   664  DROP TABLE IF EXISTS t2;
   665  
   666  
   667  
   668  
   669  
   670  
   671  
   672