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

     1  DROP TABLE IF EXISTS t1;
     2  DROP TABLE IF EXISTS t2;
     3  DROP TABLE IF EXISTS t3;
     4  DROP TABLE IF EXISTS t4;
     5  create table t1 (a int);
     6  create table t2 (a int, b int);
     7  create table t3 (a int);
     8  create table t4 (a int not null, b int not null);
     9  insert into t1 values (2);
    10  insert into t2 values (1,7),(2,7);
    11  insert into t4 values (4,8),(3,8),(5,9);
    12  insert into t3 values (6),(7),(3);
    13  select * from t3 where a <> any (select b from t2);
    14  a
    15  6
    16  3
    17  select * from t3 where a <> some (select b from t2);
    18  a
    19  6
    20  3
    21  select * from t3 where a = some (select b from t2);
    22  a
    23  7
    24  select * from t3 where a = any (select b from t2);
    25  a
    26  7
    27  create view v1 as select * from t3 where a <> any (select b from t2);
    28  create view v2 as select * from t3 where a <> some (select b from t2);
    29  create view v3 as select * from t3 where a = some (select b from t2);
    30  create view v4 as select * from t3 where a = any (select b from t2);
    31  select * from v1;
    32  a
    33  6
    34  3
    35  select * from v2;
    36  a
    37  6
    38  3
    39  select * from v3;
    40  a
    41  7
    42  select * from v4;
    43  a
    44  7
    45  drop view v1;
    46  drop view v2;
    47  drop view v3;
    48  drop view v4;
    49  insert into t2 values (100, 5);
    50  select * from t3 where a < any (select b from t2);
    51  a
    52  6
    53  3
    54  select * from t3 where a >= any (select b from t2);
    55  a
    56  6
    57  7
    58  select * from t3 where a < some (select b from t2);
    59  a
    60  6
    61  3
    62  select * from t3 where a >= some (select b from t2);
    63  a
    64  6
    65  7
    66  select * from t3 where a >= some (select b from t2);
    67  a
    68  6
    69  7
    70  create view v1 as select * from t3 where a < any (select b from t2);
    71  create view v2 as select * from t3 where a >= any (select b from t2);
    72  create view v3 as select * from t3 where a < some (select b from t2);
    73  create view v4 as select * from t3 where a >= some (select b from t2);
    74  create view v5 as select * from t3 where a >= some (select b from t2);
    75  select * from v1;
    76  a
    77  6
    78  3
    79  select * from v2;
    80  a
    81  6
    82  7
    83  select * from v3;
    84  a
    85  6
    86  3
    87  select * from v4;
    88  a
    89  6
    90  7
    91  select * from v5;
    92  a
    93  6
    94  7
    95  drop view v1;
    96  drop view v2;
    97  drop view v3;
    98  drop view v4;
    99  drop view v5;
   100  DROP TABLE IF EXISTS t1;
   101  DROP TABLE IF EXISTS t2;
   102  DROP TABLE IF EXISTS t3;
   103  DROP TABLE IF EXISTS t4;
   104  create table t1 (s1 char(5));
   105  create table t2 (s1 char(5));
   106  insert into t1 values ('a1'),('a2'),('a3');
   107  insert into t2 values ('a1'),('a2');
   108  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
   109  s1    s1 = any (select s1 from t2)
   110  a1    true
   111  a2    true
   112  a3    false
   113  select s1, s1 < ANY (SELECT s1 FROM t2) from t1;
   114  s1    s1 < any (select s1 from t2)
   115  a1    true
   116  a2    false
   117  a3    false
   118  select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
   119  s1    s1 = any (select s1 from t2)
   120  a1    true
   121  a2    true
   122  a3    false
   123  create view v1 as select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
   124  create view v2 as select s1, s1 < ANY (SELECT s1 FROM t2) from t1;
   125  create view v3 as select s1, s1 = ANY (SELECT s1 FROM t2) from t1;
   126  select * from v1;
   127  s1    s1 = any (select s1 from t2)
   128  a1    true
   129  a2    true
   130  a3    false
   131  select * from v2;
   132  s1    s1 < any (select s1 from t2)
   133  a1    true
   134  a2    false
   135  a3    false
   136  select * from v3;
   137  s1    s1 = any (select s1 from t2)
   138  a1    true
   139  a2    true
   140  a3    false
   141  drop view v1;
   142  drop view v2;
   143  drop view v3;
   144  DROP TABLE IF EXISTS t1;
   145  DROP TABLE IF EXISTS t2;
   146  DROP TABLE IF EXISTS t3;
   147  create table t2 (a int, b int);
   148  create table t3 (a int);
   149  insert into t3 values (6),(7),(3);
   150  insert into t2 values (2,2), (2,1), (3,3), (3,1);
   151  select * from t3 where a >= some (select b from t2);
   152  a
   153  6
   154  7
   155  3
   156  select * from t3 where a >= some (select b from t2 group by 1);
   157  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   158  select * from t3 where NULL >= any (select b from t2);
   159  a
   160  select * from t3 where NULL >= any (select b from t2 group by 1);
   161  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   162  select * from t3 where NULL >= some (select b from t2);
   163  a
   164  select * from t3 where NULL >= some (select b from t2 group by 1);
   165  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   166  create view v1 as select * from t3 where a >= some (select b from t2);
   167  create view v2 as select * from t3 where a >= some (select b from t2 group by 1);
   168  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   169  create view v3 as select * from t3 where NULL >= any (select b from t2);
   170  create view v4 as select * from t3 where NULL >= any (select b from t2 group by 1);
   171  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   172  create view v5 as select * from t3 where NULL >= some (select b from t2);
   173  create view v6 as select * from t3 where NULL >= some (select b from t2 group by 1);
   174  SQL syntax error: column "t2.b" must appear in the GROUP BY clause or be used in an aggregate function
   175  select * from v1;
   176  a
   177  6
   178  7
   179  3
   180  select * from v2;
   181  SQL parser error: table "v2" does not exist
   182  select * from v3;
   183  a
   184  select * from v4;
   185  SQL parser error: table "v4" does not exist
   186  select * from v5;
   187  a
   188  select * from v6;
   189  SQL parser error: table "v6" does not exist
   190  drop view v1;
   191  drop view v2;
   192  invalid view 'view-subquery-with-any.v2'
   193  drop view v3;
   194  drop view v4;
   195  invalid view 'view-subquery-with-any.v4'
   196  drop view v5;
   197  drop view v6;
   198  invalid view 'view-subquery-with-any.v6'
   199  DROP TABLE IF EXISTS t1;
   200  DROP TABLE IF EXISTS t2;
   201  DROP TABLE IF EXISTS t3;
   202  CREATE TABLE t1 ( a int, b int );
   203  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   204  SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
   205  a
   206  3
   207  SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
   208  a
   209  1
   210  SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
   211  a
   212  2
   213  SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
   214  a
   215  2
   216  3
   217  SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
   218  a
   219  1
   220  2
   221  SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
   222  a
   223  1
   224  3
   225  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
   226  invalid input: subquery should return 2 columns
   227  SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   228  invalid input: subquery should return 1 column
   229  SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   230  a
   231  SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
   232  a
   233  1
   234  2
   235  3
   236  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
   237  invalid input: subquery should return 2 columns
   238  SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   239  invalid input: subquery should return 1 column
   240  SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   241  a
   242  SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
   243  invalid input: subquery should return 2 columns
   244  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
   245  a
   246  2
   247  SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
   248  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   249  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);
   250  a
   251  2
   252  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);
   253  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   254  create view v1 as SELECT a FROM t1 WHERE a > ANY ( SELECT a FROM t1 WHERE b = 2 );
   255  create view v2 as SELECT a FROM t1 WHERE a < ANY ( SELECT a FROM t1 WHERE b = 2 );
   256  create view v3 as SELECT a FROM t1 WHERE a = ANY ( SELECT a FROM t1 WHERE b = 2 );
   257  create view v4 as SELECT a FROM t1 WHERE a >= ANY ( SELECT a FROM t1 WHERE b = 2 );
   258  create view v5 as SELECT a FROM t1 WHERE a <= ANY ( SELECT a FROM t1 WHERE b = 2 );
   259  create view v6 as SELECT a FROM t1 WHERE a <> ANY ( SELECT a FROM t1 WHERE b = 2 );
   260  create view v7 as SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a FROM t1 WHERE b = 2);
   261  invalid input: subquery should return 2 columns
   262  create view v8 as SELECT a FROM t1 WHERE a > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   263  invalid input: subquery should return 1 column
   264  create view v9 as SELECT a FROM t1 WHERE (1,2) > ANY (SELECT a,2 FROM t1 WHERE b = 2);
   265  create view v10 as SELECT a FROM t1 WHERE (1,2) <> ANY (SELECT a,2 FROM t1 WHERE b = 2);
   266  create view v11 as SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a FROM t1 WHERE b = 2);
   267  invalid input: subquery should return 2 columns
   268  create view v12 as SELECT a FROM t1 WHERE a = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   269  invalid input: subquery should return 1 column
   270  create view v13 as SELECT a FROM t1 WHERE (1,2) = ANY (SELECT a,2 FROM t1 WHERE b = 2);
   271  create view v14 as SELECT a FROM t1 WHERE (1,2) <> ALL (SELECT a FROM t1 WHERE b = 2);
   272  invalid input: subquery should return 2 columns
   273  create view v15 as SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 WHERE b = 2);
   274  create view v16 as SELECT a FROM t1 WHERE (a,1) = ANY (SELECT a,1 FROM t1 HAVING a = 2);
   275  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   276  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);
   277  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);
   278  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   279  select * from v1;
   280  a
   281  3
   282  select * from v2;
   283  a
   284  1
   285  select * from v3;
   286  a
   287  2
   288  select * from v4;
   289  a
   290  2
   291  3
   292  select * from v5;
   293  a
   294  1
   295  2
   296  select * from v6;
   297  a
   298  1
   299  3
   300  select * from v7;
   301  SQL parser error: table "v7" does not exist
   302  select * from v8;
   303  SQL parser error: table "v8" does not exist
   304  select * from v9;
   305  a
   306  select * from v10;
   307  a
   308  1
   309  2
   310  3
   311  select * from v11;
   312  SQL parser error: table "v11" does not exist
   313  select * from v12;
   314  SQL parser error: table "v12" does not exist
   315  select * from v13;
   316  a
   317  select * from v14;
   318  SQL parser error: table "v14" does not exist
   319  select * from v15;
   320  a
   321  2
   322  select * from v16;
   323  SQL parser error: table "v16" does not exist
   324  select * from v17;
   325  a
   326  2
   327  select * from v18;
   328  SQL parser error: table "v18" does not exist
   329  drop view v1;
   330  drop view v2;
   331  drop view v3;
   332  drop view v4;
   333  drop view v5;
   334  drop view v6;
   335  drop view v7;
   336  invalid view 'view-subquery-with-any.v7'
   337  drop view v8;
   338  invalid view 'view-subquery-with-any.v8'
   339  drop view v9;
   340  drop view v10;
   341  drop view v11;
   342  invalid view 'view-subquery-with-any.v11'
   343  drop view v12;
   344  invalid view 'view-subquery-with-any.v12'
   345  drop view v13;
   346  drop view v14;
   347  invalid view 'view-subquery-with-any.v14'
   348  drop view v15;
   349  drop view v16;
   350  invalid view 'view-subquery-with-any.v16'
   351  drop view v17;
   352  drop view v18;
   353  invalid view 'view-subquery-with-any.v18'
   354  DROP TABLE IF EXISTS t1;
   355  CREATE TABLE t1 ( a double, b double );
   356  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   357  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
   358  a
   359  3.0
   360  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
   361  a
   362  1.0
   363  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
   364  a
   365  2.0
   366  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
   367  a
   368  2.0
   369  3.0
   370  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
   371  a
   372  1.0
   373  2.0
   374  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
   375  a
   376  1.0
   377  3.0
   378  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2e0);
   379  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2e0);
   380  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2e0);
   381  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2e0);
   382  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2e0);
   383  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2e0);
   384  select * from v1;
   385  a
   386  3.0
   387  select * from v2;
   388  a
   389  1.0
   390  select * from v3;
   391  a
   392  2.0
   393  select * from v4;
   394  a
   395  2.0
   396  3.0
   397  select * from v5;
   398  a
   399  1.0
   400  2.0
   401  select * from v6;
   402  a
   403  1.0
   404  3.0
   405  drop view v1;
   406  drop view v2;
   407  drop view v3;
   408  drop view v4;
   409  drop view v5;
   410  drop view v6;
   411  DROP TABLE IF EXISTS t1;
   412  CREATE TABLE t1 ( a char(1), b char(1));
   413  INSERT INTO t1 VALUES ('1','1'),('2','2'),('3','3');
   414  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
   415  a
   416  3
   417  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
   418  a
   419  1
   420  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
   421  a
   422  2
   423  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
   424  a
   425  2
   426  3
   427  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
   428  a
   429  1
   430  2
   431  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
   432  a
   433  1
   434  3
   435  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = '2');
   436  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = '2');
   437  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = '2');
   438  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = '2');
   439  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = '2');
   440  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = '2');
   441  select * from v1;
   442  a
   443  3
   444  select * from v2;
   445  a
   446  1
   447  select * from v3;
   448  a
   449  2
   450  select * from v4;
   451  a
   452  2
   453  3
   454  select * from v5;
   455  a
   456  1
   457  2
   458  select * from v6;
   459  a
   460  1
   461  3
   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 TABLE IF EXISTS t1;
   469  DROP TABLE IF EXISTS t2;
   470  create table t1 (a1 int);
   471  create table t2 (b1 int);
   472  select * from t1 where a2 > any(select b1 from t2);
   473  invalid input: column a2 does not exist
   474  select * from t1 where a1 > any(select b1 from t2);
   475  a1
   476  create view v1 as select * from t1 where a2 > any(select b1 from t2);
   477  invalid input: column a2 does not exist
   478  create view v2 as select * from t1 where a1 > any(select b1 from t2);
   479  select * from v1;
   480  SQL parser error: table "v1" does not exist
   481  select * from v2;
   482  a1
   483  drop view v1;
   484  invalid view 'view-subquery-with-any.v1'
   485  drop view v2;
   486  DROP TABLE IF EXISTS t1;
   487  DROP TABLE IF EXISTS t2;
   488  create table t1 (s1 char);
   489  insert into t1 values ('1'),('2');
   490  select * from t1 where (s1 < any (select s1 from t1));
   491  s1
   492  1
   493  create view v1 as select * from t1 where (s1 < any (select s1 from t1));
   494  select * from t1 where not (s1 < any (select s1 from t1));
   495  s1
   496  2
   497  create view v2 as select * from t1 where not (s1 < any (select s1 from t1));
   498  select * from t1 where (s1+1 = ANY (select s1 from t1));
   499  s1
   500  1
   501  select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
   502  s1
   503  2
   504  create view v3 as select * from t1 where (s1+1 = ANY (select s1 from t1));
   505  create view v4 as select * from t1 where NOT(s1+1 = ANY (select s1 from t1));
   506  select * from v1;
   507  s1
   508  1
   509  select * from v2;
   510  s1
   511  2
   512  select * from v3;
   513  s1
   514  1
   515  select * from v4;
   516  s1
   517  2
   518  drop view v1;
   519  drop view v2;
   520  drop view v3;
   521  drop view v4;
   522  DROP TABLE IF EXISTS t1;
   523  DROP TABLE IF EXISTS t2;
   524  CREATE TABLE t1 (s1 CHAR(1));
   525  INSERT INTO t1 VALUES ('a');
   526  SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
   527  s1
   528  a
   529  create view v1 as SELECT * FROM t1 WHERE 'a' = ANY (SELECT s1 FROM t1);
   530  select * from v1;
   531  s1
   532  a
   533  drop view v1;
   534  DROP TABLE IF EXISTS t1;
   535  DROP TABLE IF EXISTS t2;
   536  create table t1 (a integer, b integer);
   537  select (select * from t1) = (select 1,2);
   538  [unknown result because it is related to issue#7691]
   539  select (select 1,2) = (select * from t1);
   540  [unknown result because it is related to issue#7691]
   541  select  (1,2) = ANY (select * from t1);
   542  (1, 2) = any (select * from t1)
   543  false
   544  select  (1,2) != ALL (select * from t1);
   545  (1, 2) != all (select * from t1)
   546  true
   547  create view v1 as select (select * from t1) = (select 1,2);
   548  [unknown result because it is related to issue#7691]
   549  create view v2 as select (select 1,2) = (select * from t1);
   550  [unknown result because it is related to issue#7691]
   551  create view v3 as select  (1,2) = ANY (select * from t1);
   552  create view v4 as select  (1,2) != ALL (select * from t1);
   553  select * from v1;
   554  [unknown result because it is related to issue#7691]
   555  select * from v2;
   556  [unknown result because it is related to issue#7691]
   557  select * from v3;
   558  (1, 2) = any (select * from t1)
   559  false
   560  select * from v4;
   561  (1, 2) != all (select * from t1)
   562  true
   563  drop view v1;
   564  [unknown result because it is related to issue#7691]
   565  drop view v2;
   566  [unknown result because it is related to issue#7691]
   567  drop view v3;
   568  drop view v4;
   569  DROP TABLE IF EXISTS t1;
   570  select 1 from dual where 1 < any (select 2);
   571  1
   572  1
   573  select 1 from dual where 2 > any (select 1);
   574  1
   575  1
   576  CREATE TABLE `t1` (
   577  `numeropost` int(8) unsigned NOT NULL,
   578  `maxnumrep` int(10) unsigned NOT NULL default 0,
   579  PRIMARY KEY  (`numeropost`)
   580  );
   581  INSERT INTO t1 (numeropost,maxnumrep) VALUES (40143,1),(43506,2);
   582  CREATE TABLE `t2` (
   583  `mot` varchar(30) NOT NULL default '',
   584  `topic` int(8) unsigned NOT NULL default 0,
   585  `dt` date,
   586  `pseudo` varchar(35) NOT NULL default ''
   587  );
   588  INSERT INTO t2 (mot,topic,dt,pseudo) VALUES ('joce','40143','2002-10-22','joce'), ('joce','43506','2002-10-22','joce');
   589  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
   590  mot    topic    dt    pseudo
   591  joce    40143    2002-10-22    joce
   592  joce    43506    2002-10-22    joce
   593  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
   594  mot    topic    dt    pseudo
   595  SELECT * from t2 where topic = any (SELECT SUM(topic) FROM t1);
   596  [unknown result because it is related to issue#3307]
   597  SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
   598  mot    topic    dt    pseudo
   599  joce    40143    2002-10-22    joce
   600  joce    43506    2002-10-22    joce
   601  SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
   602  mot    topic    dt    pseudo
   603  joce    40143    2002-10-22    joce
   604  create view v1 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic);
   605  create view v2 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 4100);
   606  create view v3 as SELECT * from t2 where topic <> any (SELECT SUM(topic) FROM t2);
   607  create view v4 as SELECT * from t2 where topic = any (SELECT topic FROM t2 GROUP BY topic HAVING topic < 41000);
   608  select * from v1;
   609  mot    topic    dt    pseudo
   610  joce    40143    2002-10-22    joce
   611  joce    43506    2002-10-22    joce
   612  select * from v2;
   613  mot    topic    dt    pseudo
   614  select * from v3;
   615  mot    topic    dt    pseudo
   616  joce    40143    2002-10-22    joce
   617  joce    43506    2002-10-22    joce
   618  select * from v4;
   619  mot    topic    dt    pseudo
   620  joce    40143    2002-10-22    joce
   621  drop view v1;
   622  drop view v2;
   623  drop view v3;
   624  drop view v4;
   625  DROP TABLE IF EXISTS t1;
   626  DROP TABLE IF EXISTS t2;
   627  CREATE TABLE t1 ( a int, b int );
   628  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   629  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   630  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   631  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   632  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   633  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   634  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   635  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   636  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   637  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   638  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   639  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   640  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   641  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   642  a
   643  3
   644  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   645  a
   646  1
   647  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   648  a
   649  2
   650  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   651  a
   652  2
   653  3
   654  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   655  a
   656  1
   657  2
   658  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   659  a
   660  1
   661  3
   662  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
   663  a
   664  3
   665  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
   666  a
   667  1
   668  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
   669  a
   670  2
   671  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   672  a
   673  2
   674  3
   675  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   676  a
   677  1
   678  2
   679  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
   680  a
   681  1
   682  3
   683  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   684  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   685  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   686  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   687  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   688  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   689  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   690  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   691  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   692  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   693  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   694  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   695  create view v7 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   696  create view v8 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   697  create view v9 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   698  create view v10 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   699  create view v11 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   700  create view v12 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2 group by a);
   701  create view v13 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 group by a HAVING a = 2);
   702  create view v14 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 group by a HAVING a = 2);
   703  create view v15 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 group by a HAVING a = 2);
   704  create view v16 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   705  create view v17 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 group by a HAVING a = 2);
   706  create view v18 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 group by a HAVING a = 2);
   707  select * from v1;
   708  SQL parser error: table "v1" does not exist
   709  select * from v2;
   710  SQL parser error: table "v2" does not exist
   711  select * from v3;
   712  SQL parser error: table "v3" does not exist
   713  select * from v4;
   714  SQL parser error: table "v4" does not exist
   715  select * from v5;
   716  SQL parser error: table "v5" does not exist
   717  select * from v6;
   718  SQL parser error: table "v6" does not exist
   719  select * from v7;
   720  a
   721  3
   722  select * from v8;
   723  a
   724  1
   725  select * from v9;
   726  a
   727  2
   728  select * from v10;
   729  a
   730  2
   731  3
   732  select * from v11;
   733  a
   734  1
   735  2
   736  select * from v12;
   737  a
   738  1
   739  3
   740  select * from v13;
   741  a
   742  3
   743  select * from v14;
   744  a
   745  1
   746  select * from v15;
   747  a
   748  2
   749  select * from v16;
   750  a
   751  2
   752  3
   753  select * from v17;
   754  a
   755  1
   756  2
   757  select * from v18;
   758  a
   759  1
   760  3
   761  drop view v1;
   762  invalid view 'view-subquery-with-any.v1'
   763  drop view v2;
   764  invalid view 'view-subquery-with-any.v2'
   765  drop view v3;
   766  invalid view 'view-subquery-with-any.v3'
   767  drop view v4;
   768  invalid view 'view-subquery-with-any.v4'
   769  drop view v5;
   770  invalid view 'view-subquery-with-any.v5'
   771  drop view v6;
   772  invalid view 'view-subquery-with-any.v6'
   773  drop view v7;
   774  drop view v8;
   775  drop view v9;
   776  drop view v10;
   777  drop view v11;
   778  drop view v12;
   779  drop view v13;
   780  drop view v14;
   781  drop view v15;
   782  drop view v16;
   783  drop view v17;
   784  drop view v18;
   785  DROP TABLE IF EXISTS t1;
   786  DROP TABLE IF EXISTS t2;
   787  CREATE TABLE `t1` ( `a` int(11) default NULL);
   788  insert into t1 values (1);
   789  CREATE TABLE `t2` ( `b` int(11) default NULL, `a` int(11) default NULL);
   790  insert into t2 values (1,2);
   791  select t000.a, count(*) `C` FROM t1 t000 GROUP BY t000.a HAVING count(*) > ALL (SELECT count(*) FROM t2 t001 WHERE t001.a=1);
   792  a    C
   793  1    1
   794  DROP TABLE IF EXISTS t1;
   795  DROP TABLE IF EXISTS t2;
   796  CREATE TABLE t1 (
   797  pk INT NOT NULL PRIMARY KEY,
   798  number INT
   799  );
   800  INSERT INTO t1 VALUES (8,8);
   801  CREATE TABLE t2 (
   802  pk INT NOT NULL PRIMARY KEY,
   803  number INT
   804  );
   805  INSERT INTO t2 VALUES (1,2);
   806  INSERT INTO t2 VALUES (2,8);
   807  INSERT INTO t2 VALUES (3,NULL);
   808  INSERT INTO t2 VALUES (4,166);
   809  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
   810  pk    number
   811  8    8
   812  SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
   813  pk    number
   814  8    8
   815  create view v1 as SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2 GROUP BY number);
   816  create view v2 as SELECT * FROM t1 WHERE t1.number < ANY(SELECT number FROM t2);
   817  select * from v1;
   818  pk    number
   819  8    8
   820  select * from v2;
   821  pk    number
   822  8    8
   823  drop view v1;
   824  drop view v2;
   825  DROP TABLE IF EXISTS t1;
   826  DROP TABLE IF EXISTS t2;
   827  CREATE TABLE t1 (a varchar(5), b varchar(10));
   828  INSERT INTO t1 VALUES ('AAA', '5'), ('BBB', '4'), ('BBB', '1'), ('CCC', '2'), ('CCC', '7'), ('AAA', '2'), ('AAA', '4'), ('BBB', '3'), ('AAA', '8');
   829  SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
   830  a    b
   831  BBB    4
   832  CCC    7
   833  AAA    8
   834  create view v1 as SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
   835  select * from v1;
   836  a    b
   837  BBB    4
   838  CCC    7
   839  AAA    8
   840  drop view v1;
   841  DROP TABLE IF EXISTS t1;
   842  DROP TABLE IF EXISTS t2;
   843  create table t1 (s1 char);
   844  insert into t1 values ('e');
   845  select * from t1 where 'f' > any (select s1 from t1);
   846  s1
   847  e
   848  DROP TABLE IF EXISTS t1;
   849  DROP TABLE IF EXISTS t2;
   850  CREATE TABLE t1 ( a int, b int );
   851  INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
   852  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2);
   853  a
   854  3
   855  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2);
   856  a
   857  1
   858  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2);
   859  a
   860  2
   861  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2);
   862  a
   863  2
   864  3
   865  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2);
   866  a
   867  1
   868  2
   869  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2);
   870  a
   871  1
   872  3
   873  SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   874  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   875  SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   876  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   877  SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   878  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   879  SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   880  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   881  SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   882  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   883  SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   884  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   885  create view v1 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 WHERE b = 2);
   886  create view v2 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 WHERE b = 2);
   887  create view v3 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 WHERE b = 2);
   888  create view v4 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 WHERE b = 2);
   889  create view v5 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 WHERE b = 2);
   890  create view v6 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 WHERE b = 2);
   891  create view v7 as SELECT a FROM t1 WHERE a > ANY (SELECT a FROM t1 HAVING a = 2);
   892  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   893  create view v8 as SELECT a FROM t1 WHERE a < ANY (SELECT a FROM t1 HAVING a = 2);
   894  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   895  create view v9 as SELECT a FROM t1 WHERE a = ANY (SELECT a FROM t1 HAVING a = 2);
   896  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   897  create view v10 as SELECT a FROM t1 WHERE a >= ANY (SELECT a FROM t1 HAVING a = 2);
   898  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   899  create view v11 as SELECT a FROM t1 WHERE a <= ANY (SELECT a FROM t1 HAVING a = 2);
   900  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   901  create view v12 as SELECT a FROM t1 WHERE a <> ANY (SELECT a FROM t1 HAVING a = 2);
   902  SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
   903  select * from v1;
   904  a
   905  3
   906  select * from v2;
   907  a
   908  1
   909  select * from v3;
   910  a
   911  2
   912  select * from v4;
   913  a
   914  2
   915  3
   916  select * from v5;
   917  a
   918  1
   919  2
   920  select * from v6;
   921  a
   922  1
   923  3
   924  select * from v7;
   925  SQL parser error: table "v7" does not exist
   926  select * from v8;
   927  SQL parser error: table "v8" does not exist
   928  select * from v9;
   929  SQL parser error: table "v9" does not exist
   930  select * from v10;
   931  SQL parser error: table "v10" does not exist
   932  select * from v11;
   933  SQL parser error: table "v11" does not exist
   934  select * from v12;
   935  SQL parser error: table "v12" does not exist
   936  drop view v1;
   937  drop view v2;
   938  drop view v3;
   939  drop view v4;
   940  drop view v5;
   941  drop view v6;
   942  drop view v7;
   943  invalid view 'view-subquery-with-any.v7'
   944  drop view v8;
   945  invalid view 'view-subquery-with-any.v8'
   946  drop view v9;
   947  invalid view 'view-subquery-with-any.v9'
   948  drop view v10;
   949  invalid view 'view-subquery-with-any.v10'
   950  drop view v11;
   951  invalid view 'view-subquery-with-any.v11'
   952  drop view v12;
   953  invalid view 'view-subquery-with-any.v12'
   954  DROP TABLE IF EXISTS t1;
   955  create table t1 (a int);
   956  insert into t1 values (1),(2),(3);
   957  update t1 set a=NULL where a=2;
   958  select 1 > ANY (SELECT * from t1);
   959  1 > any (select * from t1)
   960  null
   961  select 10 > ANY (SELECT * from t1);
   962  10 > any (select * from t1)
   963  true
   964  create view v1 as select 1 > ANY (SELECT * from t1);
   965  create view v2 as select 10 > ANY (SELECT * from t1);
   966  select * from v1;
   967  1 > any (select * from t1)
   968  null
   969  select * from v2;
   970  10 > any (select * from t1)
   971  true
   972  drop view v1;
   973  drop view v2;
   974  DROP TABLE IF EXISTS t1;
   975  create table t1 (a varchar(20));
   976  insert into t1 values ('A'),('BC'),('DEF');
   977  update t1 set a=NULL where a='BC';
   978  select 'A' > ANY (SELECT * from t1);
   979  A > any (select * from t1)
   980  null
   981  select 'XYZS' > ANY (SELECT * from t1);
   982  XYZS > any (select * from t1)
   983  true
   984  create view v1 as select 'A' > ANY (SELECT * from t1);
   985  create view v2 as select 'XYZS' > ANY (SELECT * from t1);
   986  select * from v1;
   987  A > any (select * from t1)
   988  null
   989  select * from v2;
   990  XYZS > any (select * from t1)
   991  true
   992  drop view v1;
   993  drop view v2;
   994  DROP TABLE IF EXISTS t1;
   995  create table t1 (a float);
   996  insert into t1 values (1.5),(2.5),(3.5);
   997  update t1 set a=NULL where a=2.5;
   998  select 1.5 > ANY (SELECT * from t1);
   999  1.5 > any (select * from t1)
  1000  null
  1001  select 10.5 > ANY (SELECT * from t1);
  1002  10.5 > any (select * from t1)
  1003  true
  1004  create view v1 as select 1.5 > ANY (SELECT * from t1);
  1005  create view v2 as select 10.5 > ANY (SELECT * from t1);
  1006  select * from v1;
  1007  1.5 > any (select * from t1)
  1008  null
  1009  select * from v2;
  1010  10.5 > any (select * from t1)
  1011  true
  1012  drop view v1;
  1013  drop view v2;
  1014  DROP TABLE IF EXISTS t1;
  1015  create table t1 (s1 int);
  1016  insert into t1 values (1),(null);
  1017  select * from t1 where s1 < all (select s1 from t1);
  1018  s1
  1019  create view v1 as select * from t1 where s1 < all (select s1 from t1);
  1020  select * from t1 where s1 < all (select s1 from t1);
  1021  s1
  1022  create view v2 as select * from t1 where s1 < all (select s1 from t1);
  1023  select * from v1;
  1024  s1
  1025  select * from v2;
  1026  s1
  1027  drop view v1;
  1028  drop view v2;
  1029  DROP TABLE IF EXISTS t1;
  1030  CREATE TABLE t1( a INT );
  1031  INSERT INTO t1 VALUES (1),(2);
  1032  CREATE TABLE t2( a INT, b INT );
  1033  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
  1034  a
  1035  1
  1036  SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
  1037  a
  1038  1
  1039  SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
  1040  a
  1041  1
  1042  create view v1 as SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) );
  1043  create view v2 as SELECT * FROM t1 WHERE a = ANY ( ( SELECT 1 UNION SELECT 1 )  UNION SELECT 1 );
  1044  create view v3 as SELECT * FROM t1 WHERE a = ANY ( SELECT 1 UNION SELECT 1 UNION SELECT 1 );
  1045  select * from v1;
  1046  a
  1047  1
  1048  select * from v2;
  1049  a
  1050  1
  1051  select * from v3;
  1052  a
  1053  1
  1054  drop view v1;
  1055  drop view v2;
  1056  drop view v3;
  1057  DROP TABLE IF EXISTS t1;
  1058  DROP TABLE IF EXISTS t2;