github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/view/view-subquery-with-in.sql (about)

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